SqlServer按半小时分组统计
SqlServer 统计 DB    2017-07-19 22:10:19    4   
lightingfire   SqlServer 统计 DB
一、正常的时间格式

SELECT StartTime,CONVERT(datetime,case when datepart(mi,StartTime)<30 then CONVERT(varchar(100), StartTime, 23)+ ' ' +datename(hh,StartTime)+':00:00' else CONVERT(varchar(100), StartTime, 23)+ ' ' +datename(hh,StartTime)+':30:00' end) as HourPart 
   FROM
   WGS_MainCDR_201401
   TEMP
   GROUP BY StartTime,CONVERT(datetime,case when datepart(mi,StartTime)<30 then CONVERT(varchar(100), StartTime, 23)+ ' ' +datename(hh,StartTime)+':00:00' else CONVERT(varchar(100), StartTime, 23)+ ' ' +datename(hh,StartTime)+':30:00' end)
   
    结果如下:
   StartTime                           HourPart
2014-01-16 01:59:25.203 2014-01-16 01:30:00.000
2014-01-16 01:59:30.627 2014-01-16 01:30:00.000
2014-01-16 01:59:35.297 2014-01-16 01:30:00.000
2014-01-16 01:59:46.063 2014-01-16 01:30:00.000
2014-01-16 02:00:15.327 2014-01-16 02:00:00.000
2014-01-16 02:00:19.157 2014-01-16 02:00:00.000
2014-01-16 02:27:46.327 2014-01-16 02:00:00.000
2014-01-16 02:28:46.327 2014-01-16 02:00:00.000
2014-01-16 02:39:31.000 2014-01-16 02:30:00.000
2014-01-16 02:41:55.843 2014-01-16 02:30:00.000

二、
SELECT case when datepart(mi,StartTime)<30 then datename(hh,StartTime)+':00' else datename(hh,StartTime)+':30' end as HourPart
   FROM
   WGS_MainCDR_201307
   TEMP
   GROUP BY (case when datepart(mi,StartTime)<30 then datename(hh,StartTime)+':00' else datename(hh,StartTime)+':30' end)

结果如下:
HourPart
20:00
21:00
10:00
9:30
22:30
13:30
15:30
17:00

三、

select dateadd(mi,(datediff(mi,convert(varchar(10),StartTime,120),StartTime)),convert(varchar(10),StartTime,120))
 as '时间段', count(*) as '行数'
from WGS_MainCDR
group by
dateadd(mi,(datediff(mi,convert(varchar(10),StartTime,120),StartTime)),convert(varchar(10),StartTime,120))
 
 
 

 

by 刘迎光@萤火虫工作室
OpenBI交流群:495266201
MicroService 微服务交流群:217722918
mail: liuyg#liuyingguang.cn
博主首页(防止爬虫):http://blog.liuyingguang.cn
OpenBI问答社区:http://openbi.liuyingguang.cn/

Pre: VMware非正常关闭,导致无法获取虚拟机所有权的解决办法

Next: maven 打包的时候跳过测试


Table of content