?1 -- ?=============================================
?2 -- ?Author:????????<tanke>
?3 -- ?Create?date:?<2007-4-17>
?4 -- ?Description:????<統計訪問量和綜合瀏覽量>
?5 -- ?exec?sp_sys_Master?'<?xml?version="1.0"?encoding="unicode"??><report?action="3"?gid="-774702857"?etime="2007-4-15"?stime="2007-4-10"?/>'
?6 -- ?=============================================
?7 ALTER ? PROCEDURE ? [ dbo ] . [ SP_GetData_BrowseVisit ]
?8 (
?9 @hDoc ? int
10 )
11 as
12 Begin
13 ???? -- ?參數定義
14 ???? declare ? @stime ? datetime , @etime ? datetime , @type ? nvarchar ( 30 ), @gid ? int
15 ???? declare ? @tmptab ? table (cid? int ,timeid? int ,the_date? datetime ,asc_code? int )
16 ???? -- ?寫入臨時表視圖
17 ???? INSERT ? INTO ? @tmptab (cid,timeid,the_date,asc_code)
18 ???? SELECT ?c.id,b.id,?b.the_date,c.asc_code
19 ???? FROM ?dbo.Fact_PageAccess? AS ?a,dbo.Dim_Date? AS ?b,dbo.Dim_Company? AS ?c
20 ???? WHERE ?a.timeid? = ?b.id? AND ?a.cid? = ?c.id
21
22 ???? /**/ /* ?生成返回? */
23 ???? SELECT ?? @stime = stime, @etime = etime, @gid = gid? from ?openxml( @hDoc , ' //report ' , 1 )? WITH ?(stime? datetime ,etime? datetime ,gid? int )
24 ???? -- ?查詢
25 ???? SELECT ? 1 ? AS ?tag, null ? AS ?parent, ' accountsum/browsesum ' ? as ? [ report!1!type ] , @gid ? as ? [ report!1!gid ] , convert ( varchar ( 30 ), @stime , 111 )? as ? [ report!1!stime ] , convert ( varchar ( 30 ), @etime , 111 )? as ? [ report!1!etime ] , null ? as ? [ detail!2!date ] , null ? as ? [ detail!2!count ] , null ? as ? [ detail!2!bcount ]
26 ???? UNION ? ALL
27 ???? select ? 2 , 1 , null , null , null , null ,the_date,? COUNT ( * )? AS ? count ,
28 ????( SELECT ? COUNT ( * )? AS ?bcount? FROM ?dbo.Fact_SiteAccess? AS ?a? WHERE ?(a.timeid? = ?t.timeid? AND ?a.cid = t.cid))? AS ?bcount?
29 ???? FROM ? @tmptab ?t? WHERE ?asc_code = @gid ? AND ?( DATEDIFF (d,? @etime ,?the_date)? <= ? 0 )? AND ?( DATEDIFF (d,? @stime ,?the_date)? >= ? 0 )
30 ???? GROUP ? BY ?the_date,t.timeid,t.cid
31 ???? FOR ?XML?EXPLICIT
32 End
33 /**/ /*
34 --?返回結果
35 <xmldata>
36 ??<report?type="accountsum/browsesum"?stime="2006-12-01"?etime="2006-12-04">
37 ????<detail?date="2006-12-01"?count=""?bcount=""/>
38 ????<detail?date="2006-12-01"?count=""?bcount=""/>
39 ????<detail?date="2006-12-01"?count=""?bcount=""/>
40 ??</report>
41 </xmldata>
42
43 <xmldata>
44 ??<action?id="2"?/>
45 ??<query?gid="-774702857"?stime="2007-04-11"?etime="2007-04-11"/>
46 </xmldata>
47 */

?26 -- ?=============================================
?27 -- ?Author:????????<tanke>
?28 -- ?Create?date:?<2007-4-17>
?29 -- ?Description:????<統計訪問量和綜合瀏覽量>
?30 -- ?exec?sp_sys_Master?'<xmldata><action?id="3"?/><query?gid="-774702857"?etime="2007-4-15"?stime="2007-4-10"?/></xmldata>'
?31 -- ?=============================================
?32 ALTER ? PROCEDURE ? [ dbo ] . [ SP_GetData_BrowseVisit ]
?33 (
?34 @hDoc ? int
?35 )
?36 as
?37 BEGIN ????
?38 ???? -- ?消除多余的網絡流量
?39 ???? SET ?NOCOUNT? ON
?40 ????
?41 ???? -- ?參數定義????
?42 ???? DECLARE ? @SQL ? nvarchar ( 4000 ), @stime ? nvarchar ( 10 ), @etime ? nvarchar ( 10 ), @type ? nvarchar ( 30 ), @gid ? bigint , @sid ? int , @eid ? int , @cid ? int , @s ? int , @e ? int , @month ? nvarchar ( 2 ), @part ? int , @websiteid ? int ;
?43 ???? -- ?條件參數
?44 ???? DECLARE ? @frmurlid ? int , @frm ? nvarchar ( 30 ), @frmtypeid ? int , @frmtype ? nvarchar ( 30 ), @areaid ? int , @area ? nvarchar ( 30 ), @networkid ? int , @network ? nvarchar ( 30 ), @shourid ? int , @ehourid ? int , @keyid ? int , @keyname ? nvarchar ( 30 )
?45 ???? -- ?臨時表
?46 ???? DECLARE ? @tmptable ? TABLE ( [ id ] ? [ bigint ] ,???? [ cid ] ? [ int ] , [ sid ] ? [ int ] , [ websiteid ] ? int , [ pageid ] ? [ bigint ] ,???? [ timeid ] ? [ int ] ,???? [ intime ] ? [ smalldatetime ] ?, [ outtime ] ? [ smalldatetime ] ?, [ spantime ] ? [ int ] , [ pagevalue ] ? [ int ] , [ pvalue ] ? [ int ] ?, [ vorder ] ? [ int ] ?, [ Tag ] ? [ int ] ,areaid? int ,networkid? int ,frmurlid? int ,frmtypeid? int ,keyid? int ,hourid? int )
?47 ????
?48 ???? -- ?生成返回
?49 ???? SELECT ? @stime = stime, @etime = etime, @gid = gid, @frm = isnull (frm, '' ), @frmtype = frmtype, @area = area, @network = network, @shourid = isnull (shourid, 0 ), @ehourid = isnull (ehourid, 0 ), @keyname = isnull (keyname, ' 未知 ' )? from ?openxml( @hDoc , ' //query ' , 1 )? WITH ?(stime? nvarchar ( 10 ),etime? nvarchar ( 10 ),gid? bigint ,frm? nvarchar ( 30 ),frmtype? nvarchar ( 30 ),area? nvarchar ( 30 ),network? nvarchar ( 30 ),shourid? int ,ehourid? int ,keyname? nvarchar ( 30 ));
?50 ????
?51 ???? -- ?查詢條件?--
?52 ???? -- ?公司ID和站點ID
?53 ???? SELECT ? @cid = cid, @websiteid = id? FROM ?dbo.Dim_WebSite? WHERE ?asc_code = @gid ;
?54 ???? -- ?開始時間
?55 ???? SELECT ? @sid = id? FROM ?dbo.Dim_Date? WHERE ?the_date = @stime
?56 ???? -- ?結束時間
?57 ???? SELECT ? @eid = id? FROM ?dbo.Dim_Date? WHERE ?the_date = @etime
?58 ???? -- ?平臺來源?
?59 ???? SELECT ? @frmurlid = id? FROM ?Dim_ComeFrom? WHERE ? [ name ] = @frm
?60 ???? -- ?來源定義
?61 ???? SELECT ? @frmtypeid = id? FROM ?Dim_ComeFromType? WHERE ?frmtype = @frmtype
?62 ???? -- ?訪客所在地域?(省份或城市)
?63 ???? SELECT ? @areaid = id? FROM ?Dim_Area? WHERE ?city = @area ? OR ?province = @area
?64 ???? -- ?網絡提供商
?65 ???? SELECT ? @networkid = id? FROM ?dim_network? WHERE ?network = @network
?66 ???? -- ?時間段(開始/結束)
?67 ???? if ? @shourid > 0
?68 ???????? SELECT ? @shourid = @shourid + 1
?69 ???? if ? @ehourid > 0 ????
?70 ???????? SELECT ? @ehourid = @ehourid + 1
?71 ???? -- ?關鍵字
?72 ???? SELECT ???? @keyid = id? FROM ?Dim_KeyWord? WHERE ?keywordname = @keyname
?73 ???????? select ? @part = @cid / 200 + 1 ???? -- -----查找分區
?74 ????
?75 ???? set ? @s = cast ( substring ( @stime , 6 , 2 )? as ? int )
?76 ???? set ? @e = cast ( substring ( @etime , 6 , 2 )? as ? int )
?77 ???? if ? @s = @e
?78 ???????? begin
?79 ???????????? if ? @s < 10
?80 ???????????????? set ? @month = ' 0 ' + cast ( @s ? as ? nvarchar ( 1 ))
?81 ???????????? else
?82 ???????????????? set ? @month = cast ( @s ? as ? nvarchar ( 2 ))
?83 ???????????? set ? @SQL = ' SELECT?a.*,b.areaid,b.networkid,b.frmurlid,b.frmtypeid,b.keyid,b.hourid?FROM?Fact_PageAccess_ ' + @month + ' ?a ' +
?84 ????????????????? ' ?inner?join?Fact_SiteAccess_ ' + @month + ' ?b?on?a.sid=b.sid ' +
?85 ????????????????? ' ?WHERE??a.timeid>= ' + cast ( @sid ? as ? nvarchar ( 10 )) + ' ?AND?a.timeid<= ' + cast ( @eid ? as ? nvarchar ( 10 )) +
?86 ????????????????? ' ?and?a.websiteid= ' + cast ( @websiteid ? as ? nvarchar ( 10 )) +
?87 ????????????????? ' ?AND?$PARTITION.[CidRangePFN](a.cid)= ' + cast ( @part ? as ? nvarchar ( 3 )) +
?88 ????????????????? ' ?AND?$PARTITION.[CidRangePFN](b.cid)= ' + cast ( @part ? as ? nvarchar ( 3 ))
?89 ???????? end
?90 ???? else
?91 ???????? begin
?92 ???????????? set ? @SQL = ' select?*?from?( '
?93 ???????????? while ? @s < @e + 1
?94 ???????????????? begin
?95 ???????????????????? if ? @s < 10
?96 ???????????????????????? set ? @month = ' 0 ' + cast ( @s ? as ? nvarchar ( 1 ))
?97 ???????????????????? else
?98 ???????????????????????? set ? @month = cast ( @s ? as ? nvarchar ( 2 ))
?99 ???????????????????? set ? @SQL = @SQL + ? '
100 ????????????????????????????????SELECT?a.*,b.areaid,b.networkid,b.frmurlid,b.frmtypeid,b.keyid,b.hourid?from?Fact_PageAccess_ ' + @month + ' ?a ' +
101 ???????????????????????????????? ' ?inner?join?Fact_SiteAccess_ ' + @month + ' ?b?on?a.sid=b.sid ' +
102 ???????????????????????????????? ' ?WHERE??a.timeid>= ' + cast ( @sid ? as ? nvarchar ( 10 )) + ' ?AND?a.timeid<= ' + cast ( @eid ? as ? nvarchar ( 10 )) +
103 ???????????????????????????????? ' ?and?a.websiteid= ' + cast ( @websiteid ? as ? nvarchar ( 10 )) +
104 ???????????????????????????????? ' ?AND?$PARTITION.[CidRangePFN](a.cid)= ' + cast ( @part ? as ? nvarchar ( 3 )) +
105 ???????????????????????????????? ' ?AND?$PARTITION.[CidRangePFN](b.cid)= ' + cast ( @part ? as ? nvarchar ( 3 ))
106 ???????????????? if ? @s < @e
107 ???????????????????? set ? @SQL = @SQL + ' ?UNION?ALL? '
108 ???????????????? else
109 ???????????????????? set ? @SQL = @SQL + ' ?)?z?where?1=1 '
110 ???????????????? set ? @s = @s + 1
111 ???????????????? end
112 ???????? END
113 ????????
114 ???? if ? @frmurlid > 0
115 ???????? set ? @SQL = @SQL + ' ?and?frmurlid= ' + cast ( @frmurlid ? as ? nvarchar ( 10 ))????
116 ???? if ? @networkid > 0
117 ???????? set ? @SQL = @SQL + ' ?and?networkid= ' + cast ( @networkid ? as ? nvarchar ( 10 ))
118 ???? if ? @areaid > 0
119 ???????? set ? @SQL = @SQL + ' ?and?areaid= ' + cast ( @areaid ? as ? nvarchar ( 10 ))
120 ???? if ? @keyid > 0
121 ???????? set ? @SQL = @SQL + ' ?and?keyid= ' + cast ( @keyid ? as ? nvarchar ( 10 ))????
122 ???? if ? @frmtypeid > 0
123 ???????? set ? @SQL = @SQL + ' ?and?frmtypeid= ' + cast ( @frmtypeid ? as ? nvarchar ( 10 ))
124 ???? if ? @shourid > 0
125 ???????? set ? @SQL = @SQL + ' ?and?hourid>= ' + cast ( @shourid ? as ? nvarchar ( 10 ))
126 ???? if ? @ehourid > 0
127 ???????? set ? @SQL = @SQL + ' ?and?hourid<= ' + cast ( @ehourid ? as ? nvarchar ( 10 ))
128 ????????
129 ???? PRINT ? @SQL
130 ???? INSERT ? INTO ? @tmptable
131 ???? EXEC ( @SQL )
132 ????
133 ???? SELECT ?
134 ???????? 1 ? AS ?tag,
135 ???????? null ? AS ?parent,
136 ???????? ' accountsum/browsesum ' ? AS ? [ report!1!type ] ,
137 ???????? @stime ? AS ? [ report!1!stime ] ,
138 ???????? @etime ? AS ? [ report!1!etime ] ,
139 ???????? null ? AS ? [ detail!2!date ] ,
140 ???????? null ? AS ? [ detail!2!count ] ,
141 ???????? null ? AS ? [ detail!2!bcount ]
142 ???? UNION ? ALL
143 ???? SELECT ?
144 ???????? 2 ,
145 ???????? 1 ,
146 ???????? null ,
147 ???????? null ,
148 ???????? null ,
149 ????????b.the_date,
150 ???????? COUNT ( DISTINCT ?a.sid)? count ,?
151 ???????? COUNT ( * )?bcount
152 ???? FROM ? @tmptable ?a
153 ???? INNER ? JOIN ?dbo.Dim_Date?b? ON ?b.id = a.timeid
154 ???? GROUP ? BY ?b.the_date
155 ???? FOR ?XML?EXPLICIT,root( ' xmldata ' ),type
156 End
157 /*
158 --?返回結果
159 <xmldata>
160 ??<report?type="accountsum/browsesum"?stime="2006-12-01"?etime="2006-12-04">
161 ????<detail?date="2006-12-01"?count=""?bcount=""/>
162 ????<detail?date="2006-12-01"?count=""?bcount=""/>
163 ????<detail?date="2006-12-01"?count=""?bcount=""/>
164 ??</report>
165 </xmldata>
166 */

http://www.cnblogs.com/tanke/archive/2007/05/08/739217.aspx