日期时间列表累积频率的 SQL 查询

Question

我在数据库列中有一个时间列表(代表对网站的访问)。

我需要将它们按时间间隔分组，然后获得这些日期的“累积频率”表。

例如，我可能有:

9:01
9:04
9:11
9:13
9:22
9:24
9:28

我想将其转换为

9:05 - 2
9:15 - 4
9:25 - 6
9:30 - 7

我怎样才能做到这一点？我什至可以在SQL中轻松实现这一目标吗？我可以很容易地用C#做到这一点

Answer 1

create table accu_times (time_val datetime not null, constraint pk_accu_times primary key (time_val));
go

insert into accu_times values ('9:01');
insert into accu_times values ('9:05');
insert into accu_times values ('9:11');
insert into accu_times values ('9:13');
insert into accu_times values ('9:22');
insert into accu_times values ('9:24');
insert into accu_times values ('9:28'); 
go

select rounded_time,
    (
    select count(*)
    from accu_times as at2
    where at2.time_val <= rt.rounded_time
    ) as accu_count
from (
select distinct
  dateadd(minute, round((datepart(minute, at.time_val) + 2)*2, -1)/2,
    dateadd(hour, datepart(hour, at.time_val), 0)
  ) as rounded_time
from accu_times as at
) as rt
go

drop table accu_times

结果是:

rounded_time            accu_count
----------------------- -----------
1900-01-01 09:05:00.000 2
1900-01-01 09:15:00.000 4
1900-01-01 09:25:00.000 6
1900-01-01 09:30:00.000 7