问题起源:
大家在做开发的时候是不是常碰到那种统计流量、网站访问量、时段记录数等等问题?
如果写一个通用的存储过程进行封装那是不是很爽?!要求:
1、实现按时、按天、按周、按月、按季度、按年统计关联表格记录[可以是你的流量等等]
2、关联表格、关键时间字段、条件子句、GroupBy子句、统计分类(时hour,天day,周week,月month,年year)可以由用户自定义(作为输入参数)经过半天的努力终于完成一小部分(按时统计),
不知道方法是否简捷,大家可以进行拓展.最后的答案希望可以给大家带来方便!
请邹建指点迷津!
多谢!代码如下:
/**********************************************************************************//*
功能:以小时为单位统计表格记录[可以多个表进行关联]
程式:Swanzy
时间:2005-08-09
修改:2005-08-12 增加表名,关键日期字段,GroupBy字段名参数
2005-08-13 增加WHERE子句参数
调用事例:EXEC UPROC_StatisticByHour 'Sp_Resv','ResvTime','AND State = 1','ServiceCode','2005-08-12'
*/
CREATE PROC UPROC_StatisticByHour
(
@NameTable VARCHAR(20), /*统计表名*/
@NameFieldDate VARCHAR(20), /*关键日期字段*/
@WhereClause VARCHAR(100), /*WHERE子句*/
@NameFieldGroupBy VARCHAR(100), /*GroupBy字段名,可以为空*/
@DateStart VARCHAR(10), /*参照日期*/
@DateEnd VARCHAR(10) /*此参数暂时未用:*/
/*拓展用:如果相等则按时统计*/
/*否则..应该进行验证,*/
/*比如后一日期不能小与前一日期等*/
)
ASDECLARE
@i int, /*每小时循环变量*/
@StrSql VARCHAR(8000), /*最后返回SQL语句*/
@NameFieldGroupByD VARCHAR(20) /*如果有分组字段则在后面加上","*/SET @i = 0
SET @StrSql = ''WHILE @i < 24BEGIN
SET @StrSql = @StrSql + 'SUM(CASE RIGHT(CONVERT(CHAR(13),'+@NameFieldDate+',120),2) WHEN '+STR(@i)+' THEN 1 ELSE 0 END ) AS ['+LTRIM(STR(@i))+'时],'
SET @i = @i + 1
END--合计
SET @StrSql = @StrSql + 'SUM(CASE CONVERT(CHAR(10),'+@NameFieldDate+',120) WHEN '''+@DateStart+''' THEN 1 ELSE 0 END ) AS 合计'IF @NameFieldGroupBy <> ''
SET @NameFieldGroupByD = @NameFieldGroupBy+','
ELSE
SET @NameFieldGroupByD = ''
--去掉最后一个","因为有'合计'所以就不要此行语句
--SET @StrSql = LEFT(@StrSql,len(@StrSql)-1)
SET @StrSql = 'SELECT '+@NameFieldGroupByD+@StrSql+ ' FROM '+@NameTable+' WHERE CONVERT(CHAR(10),'+@NameFieldDate+',120)='''+@DateStart+''''+@WhereClause+' 'IF @NameFieldGroupBy <> ''
SET @StrSql = @StrSql+' GROUP BY '+@NameFieldGroupBy
ELSE
SET @StrSql = @StrSql--PRINT @StrSql
EXEC(@StrSql)
GO/**********************************************************************************/
大家在做开发的时候是不是常碰到那种统计流量、网站访问量、时段记录数等等问题?
如果写一个通用的存储过程进行封装那是不是很爽?!要求:
1、实现按时、按天、按周、按月、按季度、按年统计关联表格记录[可以是你的流量等等]
2、关联表格、关键时间字段、条件子句、GroupBy子句、统计分类(时hour,天day,周week,月month,年year)可以由用户自定义(作为输入参数)经过半天的努力终于完成一小部分(按时统计),
不知道方法是否简捷,大家可以进行拓展.最后的答案希望可以给大家带来方便!
请邹建指点迷津!
多谢!代码如下:
/**********************************************************************************//*
功能:以小时为单位统计表格记录[可以多个表进行关联]
程式:Swanzy
时间:2005-08-09
修改:2005-08-12 增加表名,关键日期字段,GroupBy字段名参数
2005-08-13 增加WHERE子句参数
调用事例:EXEC UPROC_StatisticByHour 'Sp_Resv','ResvTime','AND State = 1','ServiceCode','2005-08-12'
*/
CREATE PROC UPROC_StatisticByHour
(
@NameTable VARCHAR(20), /*统计表名*/
@NameFieldDate VARCHAR(20), /*关键日期字段*/
@WhereClause VARCHAR(100), /*WHERE子句*/
@NameFieldGroupBy VARCHAR(100), /*GroupBy字段名,可以为空*/
@DateStart VARCHAR(10), /*参照日期*/
@DateEnd VARCHAR(10) /*此参数暂时未用:*/
/*拓展用:如果相等则按时统计*/
/*否则..应该进行验证,*/
/*比如后一日期不能小与前一日期等*/
)
ASDECLARE
@i int, /*每小时循环变量*/
@StrSql VARCHAR(8000), /*最后返回SQL语句*/
@NameFieldGroupByD VARCHAR(20) /*如果有分组字段则在后面加上","*/SET @i = 0
SET @StrSql = ''WHILE @i < 24BEGIN
SET @StrSql = @StrSql + 'SUM(CASE RIGHT(CONVERT(CHAR(13),'+@NameFieldDate+',120),2) WHEN '+STR(@i)+' THEN 1 ELSE 0 END ) AS ['+LTRIM(STR(@i))+'时],'
SET @i = @i + 1
END--合计
SET @StrSql = @StrSql + 'SUM(CASE CONVERT(CHAR(10),'+@NameFieldDate+',120) WHEN '''+@DateStart+''' THEN 1 ELSE 0 END ) AS 合计'IF @NameFieldGroupBy <> ''
SET @NameFieldGroupByD = @NameFieldGroupBy+','
ELSE
SET @NameFieldGroupByD = ''
--去掉最后一个","因为有'合计'所以就不要此行语句
--SET @StrSql = LEFT(@StrSql,len(@StrSql)-1)
SET @StrSql = 'SELECT '+@NameFieldGroupByD+@StrSql+ ' FROM '+@NameTable+' WHERE CONVERT(CHAR(10),'+@NameFieldDate+',120)='''+@DateStart+''''+@WhereClause+' 'IF @NameFieldGroupBy <> ''
SET @StrSql = @StrSql+' GROUP BY '+@NameFieldGroupBy
ELSE
SET @StrSql = @StrSql--PRINT @StrSql
EXEC(@StrSql)
GO/**********************************************************************************/
具体调用方法上面有,可以统计任意表格中根据任意一关键日期字段进行统计.试试看就知道啦.
Group by 应该可以更加灵活,就是user传入小时/天数/年,我就分别按照小时/天数/年来group by,统计!
不知道我说的是否对你的sp有改善空间,FYI *^o^*....
1、这只是一个存储过程,方便程序员设计而已,不存在空间一说(不知是不是我没有搞懂你的意思)2、如果记录统计数据——这个倒没有想到,不过应该是不可取的3、按照天、周、月、年进行统计正是本存储过程待扩展的问题。再次感谢你的参与!好运!
我的视力还不至于"存储过程"这几个大字都看不清楚,呵......
相对于你写的这个sp,我是一个user,我只要用就可以调用,当然我的开发效率是提高了,但是从另外一个角度,我--也要考虑成本,我记得曾经我负责的一个系统,原来是打算用RFID--无线射频识别技术
由于成本过高,后来没有用,换为一般的卡机!可是折腾死我这个开发程式的人!我觉得首先
你的这个统计表的资料量很大,每分钟内,那么多人存取DATABASE,多么恐怖的数据量!我觉得
要用流水表纪录的CODE不大容易转化为实际生产力2、如果记录统计数据——这个倒没有想到,不过应该是不可取的
我有这样做过,不过是我的一个DEMO,我就是用个TEXT文件,纪录登陆的总数,每次用户登陆一次,就UPDATE TEXT文件的纪录总数,这样即使断电,那么下次我的WEB SERVER起来还是可以继续纪录下去!
鉴于流水数据量太大,分时间段纪录总数应该是比你纪录每刻每人的纪录数据量来的小!3、按照天、周、月、年进行统计正是本存储过程待扩展的问题。
--加油*^O^*......Good Day!
功能:按时、周、天、月、季度、年统计表格记录[可以多个表进行关联]
程式:Swanzy
时间:2005-08-09
修改:2005-08-12 增加表名,关键日期字段,GroupBy字段名参数
2005-08-13 增加WHERE子句参数
2005-08-16 整合按时、天统计 2005-08-17 完成调用事例:
EXEC UPROC_StatisticByTime
'Sp_UserInfo',
'RegTime',
'AND UserName = ''MM''',
'AreaCode',
'2005-08-03','2005-08-03'
*/
CREATE PROC UPROC_StatisticByTime
(
@NameTable VARCHAR(8000), /*统计表名,可以通过where子句关联其他表格*/
@NameFieldDate VARCHAR(8000), /*关键日期字段*/
@WhereClause VARCHAR(8000), /*WHERE子句*/
@NameFieldGroupBy VARCHAR(8000), /*GroupBy字段名,可以为空*/
@DateStart VARCHAR(10), /*参照起始日期*/
@DateEnd VARCHAR(10) /*参照结束日期*/
)
AS
DECLARE
@i INT, @j INT, /*循环变量,循环次数*/
@DayCount INT, /*参照日期间隔*/
@NameCol VARCHAR(10), /*列名定义*/
@NameSum VARCHAR(8000), /*SUM统计语句排列*/
@StrSql VARCHAR(8000), /*最后返回SQL语句*/
@NameFieldGroupByD VARCHAR(8000) /*如果有分组字段则在后面加上","*/SET @i = 0
SET @DayCount = DATEDIFF(DAY,@DateStart,@DateEnd)
SET @StrSql = ''IF @DayCount = 0
WHILE @i < 24
BEGIN
SET @NameCol = LTRIM(STR(@i))+'时'
SET @NameSum = 'SUM(CASE DATEPART(HH,'+@NameFieldDate+') WHEN '+LTRIM(STR(@i))+' THEN 1 ELSE 0 END ) '
SET @StrSql = @StrSql + ''+@NameSum+' AS ['+@NameCol+'],'
SET @i = @i + 1
END
IF @DayCount > 0
WHILE @i < @DayCount + 1
BEGIN
SET @NameCol = CONVERT(CHAR(10),DATEADD(DAY,@i,@DateStart),120)
SET @NameSum = 'SUM(CASE CONVERT(CHAR(10),'+@NameFieldDate+',120) WHEN '''+@NameCol+''' THEN 1 ELSE 0 END )'
SET @StrSql = @StrSql + ''+@NameSum+' AS ['+@NameCol+'],'
SET @i = @i + 1
END--合计
SET @StrSql = @StrSql + 'SUM(CASE WHEN CONVERT(CHAR(10),'+@NameFieldDate+',120) BETWEEN '''+@DateStart+''' AND '''+@DateEnd+''' THEN 1 ELSE 0 END ) AS 合计'IF @NameFieldGroupBy <> ''
SET @NameFieldGroupByD = @NameFieldGroupBy+','
ELSE
SET @NameFieldGroupByD = ''SET @StrSql = 'SELECT '+@NameFieldGroupByD+@StrSql+ ' FROM '+@NameTable+' WHERE CONVERT(CHAR(10),'+@NameFieldDate+',120) BETWEEN '''+@DateStart+''' AND '''+@DateEnd+''' '+@WhereClause+' 'IF @NameFieldGroupBy <> ''
SET @StrSql = @StrSql+' GROUP BY '+@NameFieldGroupBy
ELSE
SET @StrSql = @StrSql--PRINT @StrSql
EXEC(@StrSql)GO
===================================================================