100分求一sql, 按时间段分组统计?

2007-11-30 10:12:13 1k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL时间计算实战：从DATEDIFF到分组统计与性能优化在数据库开发与数据分析中，时间计算是绕不开的基础技能，但许多人在处理时间点、时间段与时间分量时常常混淆概念，导致统计结果偏差。以DATEDIFF为代表的日期函数，虽然语法简单，却存在边界计数、空值比较、索引失效等隐蔽陷阱。理解其底层原理，才能写出既准确又高效的查询。掌握日期函数的技术价值，在于能灵活支撑业务需求：从计算工单处理耗时、筛选超时未响应任务，到按天、周、月聚合请求趋势，再到分析请求创建时段分布，都是日常报表与运维监控的高频场景。本文以一个真实的开发请求表为例，系统拆解SQL时间计算中的核心逻辑、分阅读详情

把时间按5分钟分隔, 来统计数据, 求解

表结构:
时间金额
2007-1-1 10:00:23 8
2007-1-1 10:01:24 4
2007-1-1 10:05:00 2
2007-1-1 10:06:12 3
2007-1-1 10:08:00 1
2007-1-1 10:12:11 5
......
统计后
时间段行数总金额
2007-1-1 10:05:00 3 14
2007-1-1 10:10:00 2 4
2007-1-1 10:15:00 1 5
......

...全文

Unity学习笔记之【IK反向动力学操作】反向动力学，简称IK。相较于正向动力学，反向动力学旨在子级对父级产生的影响。使用IK，可以实现根据目标位置或方向来计算并调整角色的关节（骨骼）链，以使角色的末端（如手臂、腿部等）达到预期的位置或取向。通过使用 Unity 的 IK 功能，你可以实现各种复杂的角色动画效果，如角色抓取、足部对齐、手臂跟随等。 2. 将所视的目标物体拖拽绑定 4. 实现效果如下：移动角色时注释立方体实现效果如下阅读详情

zhaixing0101

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

leo_lesley 2007-11-30

打赏
举报

楼主的分真多啊,每回都开100分的贴

wuyanbing 2007-11-30

打赏
举报

先顶

fwacky 2007-11-30

打赏
举报





create table T (date datetime, value int)

insert into T select '2007-1-1 10:00:23',8

insert into T select '2007-1-1 10:01:24',4

insert into T select '2007-1-1 10:05:00',2

insert into T select '2007-1-1 10:06:12',3

insert into T select '2007-1-1 10:08:00',1

insert into T select '2007-1-1 10:12:11',5





declare @min datetime

select @min=convert(datetime,convert(varchar(16),min(date),120))  from T



select  dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min) as [date] ,count(*) as cout ,sum(value) as sumary

from T

group by  dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min)

fa_ge 2007-11-30

打赏
举报



create table # (

时间 datetime,

金额 int

)

insert # select

    '2007-1-1 10:00:23',           8 

union all select

    '2007-1-1 10:01:24',           4 

union all select

    '2007-1-1 10:05:00',           2   

union all select

    '2007-1-1 10:06:12',           3 

union all select

    '2007-1-1 10:08:00',           1 

union all select

    '2007-1-1 10:12:11',           5 







select 

	[time],

	count(1)as 'line',

	sum( 金额 )as ' 金额 '

from

(

select 

	*, 

	[time]=case when 时间<='2007-01-01 10:05:00'then '2007-01-01 10:05:00'

                    else

                         dateadd(minute,(datediff(minute,'2007-01-01 10:05:00',时间)/5+1) *5 ,'2007-01-01 10:05:00')  

               end 

 

from #

)a

group by [time]



/*

time                                                   line         金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:05:00.000                                3           14

2007-01-01 10:10:00.000                                2           4

2007-01-01 10:15:00.000                                1           5



（所影响的行数为 3 行）

*/

fa_ge 2007-11-30

打赏
举报



create table # (

时间 datetime,

金额 int

)

insert # select

    '2007-1-1 10:00:23',           8 

union all select

    '2007-1-1 10:01:24',           4 

union all select

    '2007-1-1 10:05:00',           2   

union all select

    '2007-1-1 10:06:12',           3 

union all select

    '2007-1-1 10:08:00',           1 

union all select

    '2007-1-1 10:12:11',           5 







select time,count(1)as 'line',sum( 金额 )as ' 金额 '

from

(

select *, [time]=case when 时间<='2007-01-01 10:05:00'then '2007-01-01 10:05:00'

                   else

                       dateadd(minute,(abs(datediff(minute,时间,'2007-01-01 10:05:00'))/5+1) *5 ,'2007-01-01 10:05:00')  

              end 

 

from #

)a

group by [time]



/*

time                                                   line         金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:05:00.000                                3           14

2007-01-01 10:10:00.000                                2           4

2007-01-01 10:15:00.000                                1           5



（所影响的行数为 3 行）



*/

dawugui 2007-11-30

打赏
举报

create table tb(时间 datetime , 金额 int)

insert into tb values('2007-1-1 10:00:23' ,          8 )

insert into tb values('2007-1-1 10:01:24' ,          4 )

insert into tb values('2007-1-1 10:05:00' ,          2 )  

insert into tb values('2007-1-1 10:06:12' ,          3 )

insert into tb values('2007-1-1 10:08:00' ,          1 )

insert into tb values('2007-1-1 10:12:11' ,          5 )

go



--时间段>=10:00:00 and 时间段<10:05:00

select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,

       count(*) as 行数,

       sum(金额) as 总金额

from tb

group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))

/*

时间段                                                    行数          总金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:00:00.000                                3           14

2007-01-01 10:05:00.000                                2           4

2007-01-01 10:10:00.000                                1           5

（所影响的行数为 3 行）

*/



--时间段>10:00:00 and 时间段<=10:05:00

select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,

       count(*) as 行数,

       sum(金额) as 总金额

from tb

group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))

/*

时间段                                                    行数          总金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:00:00.000                                2           12

2007-01-01 10:05:00.000                                3           6

2007-01-01 10:10:00.000                                1           5



（所影响的行数为 3 行）

*/



drop table tb

DengXingJie 2007-11-30

打赏
举报

頂了

昵称被占用了 2007-11-30

打赏
举报

好像有点差别，改下：



--环境

declare @t table (

时间 datetime,

金额 int

)

insert @t select

    '2007-1-1 10:00:23',           8 

union all select

    '2007-1-1 10:01:24',           4 

union all select

    '2007-1-1 10:05:00',           2   

union all select

    '2007-1-1 10:06:12',           3 

union all select

    '2007-1-1 10:08:00',           1 

union all select

    '2007-1-1 10:12:11',           5 



select dateadd(ss,(cast(datediff(ss,convert(varchar(10),时间,112),时间)-0.5 as int)/300+1)*300,convert(varchar(10),时间,112)) as 时间段,

count(*) as 行数,sum(金额) as 总金额

from @t

group by dateadd(ss,(cast(datediff(ss,convert(varchar(10),时间,112),时间)-0.5 as int)/300+1)*300,convert(varchar(10),时间,112))



--结果

时间段                                                    行数          总金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:05:00.000                                3           14

2007-01-01 10:10:00.000                                2           4

2007-01-01 10:15:00.000                                1           5



（所影响的行数为 3 行）

playwarcraft 2007-11-30

打赏
举报



create table T (date datetime, value int)

insert into T select '2007-1-1 10:00:23',8

insert into T select '2007-1-1 10:01:24',4

insert into T select '2007-1-1 10:05:00',2

insert into T select '2007-1-1 10:06:12',3

insert into T select '2007-1-1 10:08:00',1

insert into T select '2007-1-1 10:12:11',5





declare @min datetime

select @min=convert(datetime,convert(varchar(16),min(date),120))  from T



select  dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min) as [date] ,count(*) as cout ,sum(value) as sumary

from T

group by  dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min)



/*

date                                                   cout        sumary      

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:05:00.000                                3           14

2007-01-01 10:10:00.000                                2           4

2007-01-01 10:15:00.000                                1           5

*/



drop table T

dawugui 2007-11-30

打赏
举报

--先给个按小时的分组.

比如有一个表timeTable,有字段id,score,inputDate.

数据如下

  id       score    inputDate

  1        2         '2007-4-5 3:33:33'

  2        1         '2007-4-5 4:33:33'

  3        4         '2007-3-5 3:33:33'

  4        2         '2007-4-2 2:33:33'

我要按时间分组统计score，结果如下

0:00-1:00   0

1:00-2:00   0

2:00-3:00   2

3:00-4:00   6

4:00-5:00   1

5:00-6:00   0

....

23:00-24:00 0



declare @a table(id int, score int, inputDate smalldatetime)

insert @a select 1, 2, '2007-4-5 3:33:33'

union all select 2, 1, '2007-4-5 4:33:33'

union all select 3, 4, '2007-3-5 3:33:33'

union all select 4, 2, '2007-4-2 2:33:33'



select right(100+a,2)+':00-'+right(100+b,2)+':00',

sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end) 

from @a a ,

(select 0 a,1 b 

union all select  1,2

union all select  2,3

union all select  3,4

union all select  4,5

union all select  5,6

union all select  6,7

union all select  7,8

union all select  8,9

union all select  9,10

union all select  10,11

union all select  11,12

union all select  12,13

union all select  13,14

union all select  14,15

union all select  15,16

union all select  16,17

union all select  17,18

union all select  18,19

union all select  19,20

union all select  20,21

union all select  21,22

union all select  22,23

union all select  23,24

)aa

group by right(100+a,2)+':00-'+right(100+b,2)+':00'







--------------- ----------- 

00:00-01:00     0

01:00-02:00     0

02:00-03:00     2

03:00-04:00     6

04:00-05:00     1

05:00-06:00     0

06:00-07:00     0

07:00-08:00     0

08:00-09:00     0

09:00-10:00     0

10:00-11:00     0

11:00-12:00     0

12:00-13:00     0

13:00-14:00     0

14:00-15:00     0

15:00-16:00     0

16:00-17:00     0

17:00-18:00     0

18:00-19:00     0

19:00-20:00     0

20:00-21:00     0

21:00-22:00     0

22:00-23:00     0

23:00-24:00     0



（所影响的行数为 24 行）

昵称被占用了 2007-11-30

打赏
举报



--环境

declare @t table (

时间 datetime,

金额 int

)

insert @t select

    '2007-1-1 10:00:23',           8 

union all select

    '2007-1-1 10:01:24',           4 

union all select

    '2007-1-1 10:05:00',           2   

union all select

    '2007-1-1 10:06:12',           3 

union all select

    '2007-1-1 10:08:00',           1 

union all select

    '2007-1-1 10:12:11',           5 



select dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112)) as 时间段,

count(*) as 行数,sum(金额) as 总金额

from @t

group by dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112))



--结果

时间段                                                    行数          总金额         

------------------------------------------------------ ----------- ----------- 

2007-01-01 10:05:00.000                                2           12

2007-01-01 10:10:00.000                                3           6

2007-01-01 10:15:00.000                                1           5



（所影响的行数为 3 行）

100分求一sql, 按时间段分组统计?

相关推荐