sqlserver统计二

         接上一篇，数据统计一般都会按照日期进行统计。目标格式基本上如下：

名称	url	2011-04-01	2011-04-02	2011-04-03
a	a.com	100111	100231	101321

         这样会涉及到时间的获取，以及行列的转换，首先时间问题。将yyyy-MM-dd hh:mm:ss这种在数据库里面保存的格式，转化为天。再进行统计，得到一期目标数据格式如下：

 

名称	时间	次数
a	2011-04-01	100111
a	2011-04-02	100231
a	2011-04-03	101321
b	.......	............

 

         得到一期目标格式，就是只需要日期处理，直接上sql.

select sl.service,CONVERT(varchar(10),sl.access_date,23) adate , count(*) as  num from dbo.service_log  sl where sl.access_date between '2011-04-01' and '2011-04-03' group by CONVERT(varchar(10),sl.access_date,23),sl.service

 

        下一步就是要在这上面得到目标格式，基础知识可以参见《sql cookbook》的第12章"报表和数据仓库运算"，在上一步的基础上，利用以下sql可以得到目标格式。

select s.service, 
max(case adate when '2011-04-01' then num else 0 end) as '2011-04-01',
max(case adate when '2011-04-02' then num else 0 end) as '2011-04-02',
max(case adate when '2011-04-03' then num else 0 end) as '2011-04-03'
from (select sl.service,CONVERT(varchar(10),sl.access_date,23) adate , count(*) as  num from dbo.service_log  sl where sl.access_date between '2011-04-01' and '2011-04-30' group by CONVERT(varchar(10),sl.access_date,23),sl.service) s group by s.service

 

      还有其他的处理方式，在sqlserver中是pivot,其他数据库也有。关于pivot的基础知识可以参见《sql cookbook》第14章"若干另类目标"，利用pivot的sql如下：

select service,[2011-04-01],[2011-04-02],[2011-04-03] from
(select sl.service,CONVERT(varchar(10),sl.access_date,23) adate , count(*) as  num from dbo.service_log  sl where sl.access_date between '2011-04-01' and '2011-04-30' group by CONVERT(varchar(10),sl.access_date,23),sl.service) s
pivot (sum(s.num) for s.adate in ([2011-04-01],[2011-04-02],[2011-04-03])) as t