新公司,新工作试着用sql 存储过程写调用,所有结果都返回XML数据集,这是第一个成品,贴出来以做留念.
经过一番改进后,收获还是挺多的.
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-- =============================================
7ALTER PROCEDURE [dbo].[SP_GetData_BrowseVisit]
8(
9@hDoc int
10)
11as
12Begin
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
32End
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
*/
-- =============================================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 int10
)11
as12
Begin13
-- 参数定义14
declare @stime datetime,@etime datetime,@type nvarchar(30),@gid int15
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_code19
FROM dbo.Fact_PageAccess AS a,dbo.Dim_Date AS b,dbo.Dim_Company AS c20
WHERE a.timeid = b.id AND a.cid = c.id21
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 ALL27
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.cid31
FOR XML EXPLICIT32
End33
/**//*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-- =============================================
32ALTER PROCEDURE [dbo].[SP_GetData_BrowseVisit]
33(
34@hDoc int
35)
36as
37BEGIN
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
156End
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*/
-- =============================================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 int35
)36
as37
BEGIN 38
-- 消除多余的网络流量39
SET NOCOUNT ON40
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和站点ID53
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=@stime56
-- 结束时间57
SELECT @eid=id FROM dbo.Dim_Date WHERE the_date=@etime58
-- 平台来源 59
SELECT @frmurlid=id FROM Dim_ComeFrom WHERE [name]=@frm60
-- 来源定义61
SELECT @frmtypeid=id FROM Dim_ComeFromType WHERE frmtype=@frmtype62
-- 访客所在地域 (省份或城市)63
SELECT @areaid=id FROM Dim_Area WHERE city=@area OR province=@area64
-- 网络提供商65
SELECT @networkid=id FROM dim_network WHERE network=@network66
-- 时间段(开始/结束)67
if @shourid>068
SELECT @shourid=@shourid+169
if @ehourid>0 70
SELECT @ehourid=@ehourid+171
-- 关键字72
SELECT @keyid=id FROM Dim_KeyWord WHERE keywordname=@keyname73
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=@e78
begin79
if @s<1080
set @month='0'+cast(@s as nvarchar(1))81
else82
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
end90
else91
begin92
set @SQL='select * from ('93
while @s<@e+194
begin95
if @s<1096
set @month='0'+cast(@s as nvarchar(1))97
else98
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<@e107
set @SQL=@SQL+' UNION ALL '108
else109
set @SQL=@SQL+' ) z where 1=1'110
set @s=@s+1111
end112
END113
114
if @frmurlid>0115
set @SQL=@SQL+' and frmurlid='+cast(@frmurlid as nvarchar(10)) 116
if @networkid>0117
set @SQL=@SQL+' and networkid='+cast(@networkid as nvarchar(10))118
if @areaid>0119
set @SQL=@SQL+' and areaid='+cast(@areaid as nvarchar(10))120
if @keyid>0121
set @SQL=@SQL+' and keyid='+cast(@keyid as nvarchar(10)) 122
if @frmtypeid>0123
set @SQL=@SQL+' and frmtypeid='+cast(@frmtypeid as nvarchar(10))124
if @shourid>0125
set @SQL=@SQL+' and hourid>='+cast(@shourid as nvarchar(10))126
if @ehourid>0127
set @SQL=@SQL+' and hourid<='+cast(@ehourid as nvarchar(10))128
129
PRINT @SQL130
INSERT INTO @tmptable131
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 ALL143
SELECT 144
2,145
1,146
null,147
null,148
null,149
b.the_date,150
COUNT(DISTINCT a.sid) count, 151
COUNT(*) bcount152
FROM @tmptable a153
INNER JOIN dbo.Dim_Date b ON b.id=a.timeid154
GROUP BY b.the_date155
FOR XML EXPLICIT,root('xmldata'),type156
End157
/*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
*/
