sql - SQL 在按时间分组的时间范围内有效的行数
问题描述
SQL 服务器
我有一个带有 2 个时间戳的表,time_start 和 time_end。例如
ID time_start time_end
---- ------------------- -------------------
1 2019-01-01 08:30:00 2019-01-01 09:40:00
2 2019-01-01 09:10:24 2019-01-01 15:14:19
3 2019-01-01 09:21:15 2019-01-01 09:21:19
4 2019-01-01 10:39:45 2019-01-01 10:58:12
5 2019-01-01 11:39:45 2019-01-01 11:40:10
我想对它们进行分组,这样我就可以将行数按可变时间间隔分组。例如
time_interval row_count
------------------- ---------
2019-01-01 07:00:00 0
2019-01-01 08:00:00 1
2019-01-01 09:00:00 3
2019-01-01 10:00:00 2
2019-01-01 11:00:00 1
2019-01-01 12:00:00 0
我的间隔可能是 1 小时、1 分钟、30 分钟、1 天等......
将其视为登录/注销情况,我想查看用户在任何给定的分钟、小时、天等情况下是如何登录的......
解决方案
尝试这个,
DECLARE @start_date datetime='2019-01-01',
@end_date datetime='2019-01-02',
@i_minutes int=60
DECLARE @t TABLE
(
id int identity(1,1),time_start datetime,time_end datetime
)
INSERT INTO @t(time_start,time_end)VALUES
('2019-01-01 08:30:00','2019-01-01 09:40:00'),
('2019-01-01 09:10:24','2019-01-01 15:14:19'),
('2019-01-01 09:21:15','2019-01-01 09:21:19'),
('2019-01-01 10:39:45','2019-01-01 10:58:12'),
('2019-01-01 11:39:45','2019-01-01 11:40:10')
--SELECT @start_date=min(time_start),@end_date=max(time_end)
--FROM @t
;WITH CTE_time_Interval AS
(
SELECT @start_date AS time_int,@i_minutes AS i_minutes
UNION ALL
SELECT dateadd(minute,@i_minutes,time_int),i_minutes+ @i_minutes
FROM CTE_time_Interval
WHERE time_int<=@end_date
)
,CTE1 AS
(
SELECT ROW_NUMBER()OVER(ORDER BY time_int)AS r_no,time_int
FROM CTE_time_Interval
)
,CTE2 AS
(
SELECT a.time_int AS Int_start_time,b.time_int AS Int_end_time
FROM CTE1 a
INNER JOIN CTE1 b ON a.r_no+1=b.r_no
)
SELECT a.Int_start_time,a.Int_end_time,sum(iif(b.time_start is not null,1,0)) AS cnt
FROM CTE2 a
LEFT JOIN @t b ON
(
b.time_start BETWEEN a.Int_start_time AND a.Int_end_time
OR
b.time_end BETWEEN a.Int_start_time AND a.Int_end_time
OR
a.Int_start_time BETWEEN b.time_start AND b.time_end
OR
a.Int_end_time BETWEEN b.time_start AND b.time_end
)
GROUP BY a.Int_start_time,a.Int_end_time
推荐阅读
- python - Flajolet Martin 算法实现
- html - doctype 声明是 HTML 还是 SGML?
- javascript - localStorage.setItem 不想存储 JWT
- linux - 如何从 Windows Linux 子系统打开 VSCode?
- python - SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 0-1: truncated \xXX escape
- python - 使用python将一个项目从现有列中剥离到一个新列
- javascript - React Native 下载 Youtube 视频
- javascript - 为什么当我更新列表时我的列表没有重新呈现
- python - 绕过内存错误以在 Python 中读取大型 JSON 文件
- python - python 和 BaseHTTPRequestHandler :在生成的页面的标题中添加一个空的 favicon