我有以下用户登录和注销时间的数据：
| UserID | StartDate |EndDate |
| 1033 | 06/24/2018 00:11:51 | 06/24/2018 01:03:38 |
| 1033 | 06/24/2018 02:12:38 | 06/24/2018 02:15:51 |
| 1033 | 06/24/2018 02:28:08 | 06/24/2018 02:36:31 |
| 1033 | 06/24/2018 03:07:13 | 06/24/2018 06:02:05 |
| 1033 | 06/24/2018 07:33:39 | 06/24/2018 07:33:40 |
| 1033 | 06/24/2018 08:19:19 | 06/24/2018 12:20:03 |
| 1033 | 06/24/2018 12:26:55 | 06/24/2018 13:30:17 |
| 1033 | 06/24/2018 14:07:42 | 06/24/2018 14:53:03 |
| 1033 | 06/24/2018 15:15:20 | 06/24/2018 15:33:01 |
| 1033 | 06/24/2018 16:42:00 | 06/24/2018 16:58:13 |
| 1033 | 06/24/2018 17:35:04 | 06/24/2018 17:49:01 |
| 1033 | 06/24/2018 18:49:26 | 06/24/2018 19:26:18 |
| 1033 | 06/24/2018 20:06:46 | 06/24/2018 21:00:07 |
| 1033 | 06/24/2018 22:35:51 | 06/24/2018 22:43:57 |
| 1033 | 06/24/2018 23:00:52 | 06/25/2018 01:24:53 |
| 1033 | 06/25/2018 02:01:58 | 06/25/2018 02:03:47 |
| 1033 | 06/25/2018 03:01:57 | 06/25/2018 03:45:59 |
| 1033 | 06/25/2018 04:24:16 | 06/25/2018 04:43:52 |
| 1033 | 06/25/2018 05:16:15 | 06/25/2018 07:39:28 |
| 1033 | 06/25/2018 08:49:23 | 06/25/2018 09:12:06 |
| 1033 | 06/25/2018 13:38:20 | 06/25/2018 15:16:25 |
| 1033 | 06/25/2018 15:16:28 | 06/25/2018 16:54:34 |
| 1033 | 06/25/2018 17:35:24 | 06/25/2018 18:25:38 |
| 1033 | 06/25/2018 18:58:41 | 06/25/2018 19:20:56 |
| 1033 | 06/25/2018 19:46:27 | 06/25/2018 19:47:33 |
| 1033 | 06/25/2018 20:14:08 | 06/25/2018 20:40:20 |
| 1033 | 06/25/2018 21:11:01 | 06/26/2018 00:36:56 |
| 1033 | 06/26/2018 00:50:43 | 06/26/2018 09:43:53 |
| 1033 | 06/26/2018 10:32:58 | 06/26/2018 10:33:38 |
| 1033 | 06/26/2018 11:01:29 | 06/26/2018 11:41:24 |
| 1033 | 06/26/2018 13:56:29 | 06/26/2018 14:52:08 |
| 1033 | 06/26/2018 15:40:07 | 06/26/2018 16:38:18 |
| 1033 | 06/26/2018 16:56:33 | 06/26/2018 17:19:14 |
| 1033 | 06/26/2018 18:37:33 | 06/26/2018 19:10:44 |
| 1033 | 06/26/2018 19:34:44 | 06/26/2018 21:30:06 |
| 1033 | 06/26/2018 21:43:55 | 06/26/2018 21:47:51 |
| 1033 | 06/26/2018 23:03:17 | 06/27/2018 04:26:50 |
| 1033 | 06/27/2018 07:41:10 | 06/27/2018 07:41:11 |
| 1033 | 06/27/2018 07:41:23 | 06/27/2018 09:56:05 |
| 1033 | 06/27/2018 11:31:27 | 06/27/2018 12:05:42 |
| 1033 | 06/27/2018 12:48:28 | 06/27/2018 12:49:12 |
| 1033 | 06/27/2018 13:43:48 | 06/27/2018 14:13:04 |
| 1033 | 06/27/2018 15:13:32 | 06/27/2018 15:46:44 |
| 1033 | 06/27/2018 17:09:44 | 06/27/2018 17:16:15 |
| 1033 | 06/27/2018 18:01:28 | 06/27/2018 18:35:04 |
| 1033 | 06/27/2018 19:21:18 | 06/27/2018 19:33:47 |
| 1033 | 06/27/2018 20:01:51 | 06/27/2018 20:04:29 |
| 1033 | 06/27/2018 20:45:42 | 06/27/2018 22:13:48 |
| 1033 | 06/27/2018 23:14:33 | 06/27/2018 23:28:31 |
| 1033 | 06/27/2018 23:57:57 | 06/28/2018 04:16:47 |
| 1033 | 06/28/2018 04:48:50 | 06/28/2018 04:50:12 |
| 1033 | 06/28/2018 06:00:36 | 06/28/2018 08:14:20 |
| 1033 | 06/28/2018 08:53:19 | 06/28/2018 09:09:52 |
| 1033 | 06/28/2018 09:28:04 | 06/28/2018 10:07:02 |
| 1033 | 06/28/2018 10:30:47 | 06/28/2018 11:07:06 |
| 1033 | 06/28/2018 12:23:48 | 06/28/2018 12:26:52 |
| 1033 | 06/28/2018 13:12:23 | 06/28/2018 13:24:10 |
| 1033 | 06/28/2018 13:50:18 | 06/28/2018 13:59:04 |
| 1033 | 06/28/2018 14:21:08 | 06/28/2018 14:56:30 |
| 1033 | 06/28/2018 15:20:02 | 06/28/2018 15:46:18 |
| 1033 | 06/28/2018 16:44:35 | 06/28/2018 17:09:43 |
| 1033 | 06/28/2018 17:26:54 | 06/28/2018 17:35:20 |
| 1033 | 06/28/2018 18:20:17 | 06/28/2018 18:42:42 |
| 1033 | 06/28/2018 18:50:23 | 06/28/2018 19:07:15 |
| 1033 | 06/28/2018 19:12:00 | 06/28/2018 20:06:46 |
| 1033 | 06/28/2018 20:15:26 | 06/28/2018 20:46:14 |
| 1033 | 06/28/2018 21:12:03 | 06/28/2018 21:12:29 |
| 1033 | 06/28/2018 21:23:12 | 06/28/2018 21:27:14 |
| 1033 | 06/28/2018 22:04:46 | 06/28/2018 22:17:00 |
| 1033 | 06/28/2018 22:58:18 | 06/29/2018 01:21:10 |
| 1033 | 06/29/2018 02:05:34 | 06/29/2018 02:10:05 |
| 1033 | 06/29/2018 02:15:52 | 06/29/2018 07:07:20 |
| 1033 | 06/29/2018 07:46:33 | 06/29/2018 08:06:29 |
| 1033 | 06/29/2018 08:50:00 | 06/29/2018 08:54:24 |
| 1033 | 06/29/2018 10:16:49 | 06/29/2018 12:47:49 |
| 1033 | 06/29/2018 14:11:53 | 06/29/2018 15:02:08 |
| 1033 | 06/29/2018 15:35:25 | 06/29/2018 16:46:58 |
| 1033 | 06/29/2018 16:49:12 | 06/29/2018 16:55:00 |
| 1033 | 06/29/2018 17:23:20 | 06/29/2018 17:50:58 |
| 1033 | 06/29/2018 19:26:29 | 06/29/2018 19:40:41 |
| 1033 | 06/29/2018 21:28:30 | 06/29/2018 22:00:50 |
| 1033 | 06/29/2018 22:40:32 | 06/29/2018 22:41:46 |
| 1033 | 06/29/2018 23:20:08 | 06/30/2018 01:24:54 |
| 1033 | 06/30/2018 01:39:53 | 06/30/2018 06:21:02 |
| 1033 | 06/30/2018 09:17:11 | 06/30/2018 09:17:12 |
| 1033 | 06/30/2018 09:17:20 | 06/30/2018 09:45:50 |
| 1033 | 06/30/2018 10:52:58 | 06/30/2018 11:46:47 |
| 1033 | 06/30/2018 12:28:47 | 06/30/2018 14:05:42 |
| 1033 | 06/30/2018 15:30:42 | 06/30/2018 15:37:32 |
| 1033 | 06/30/2018 16:28:27 | 06/30/2018 16:39:04 |
| 1033 | 06/30/2018 17:13:20 | 06/30/2018 18:09:44 |
| 1033 | 06/30/2018 19:30:26 | 06/30/2018 20:25:35 |
| 1033 | 06/30/2018 21:30:45 | 06/30/2018 22:25:15 |
| 1033 | 06/30/2018 23:27:07 | 06/30/2018 23:27:35 |
| 1033 | 06/30/2018 23:48:45 | 06/30/2018 23:50:08 |
我需要弄清楚每个唯一日期的每小时，用户登录了多长时间(IE StartDate 到 EndDate(。
我找到了这篇文章来讨论这个问题，这是我到目前为止的查询，但它没有考虑跨越 1 个日期(6/24 到 6/25(的第 2 行的小时
数WITH Numbers (Number) AS
( SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
), SampleData (userid, StartDate, EndDate) AS
( SELECT userid, CONVERT(DATETIME2, StartDate), CONVERT(DATETIME2, EndDate)
FROM (VALUES
(1033, '06/24/2018 23:00:52', '06/25/2018 01:24:53'),
(1033, '06/25/2018 02:01:58', '06/25/2018 02:03:47'),
(1033, '06/25/2018 03:01:57', '06/25/2018 03:45:59')
) d (userid, StartDate, EndDate)
)
SELECT d.userid,
[Date] = CONVERT(DATE, d.StartDate),
[Hour] = CONVERT(TIME(0), DATEADD(HOUR, DATEPART(HOUR, d.StartDate) + n.Number, 0)),
Seconds_in = CASE
-- SPECIAL CASE: START HOUR = END HOUR
WHEN DATEPART(HOUR, d.StartDate) = DATEPART(HOUR, d.EndDate)
AND DATEDIFF(DAY, d.StartDate, d.EndDate) = 0
THEN DATEDIFF(second, d.StartDate, d.EndDate)
WHEN CONVERT(DATE, d.StartDate)<CONVERT(DATE, d.EndDate) then ?????
-- START HOUR
WHEN n.Number = 0
THEN 3600 - DATEPART(second, d.StartDate)
-- END HOUR
WHEN n.Number = DATEDIFF(HOUR, d.StartDate, d.EndDate)
THEN DATEPART(second, d.EndDate)
-- FULL HOURS IN BETWEEN START AND END
ELSE 3600
END
FROM SampleData d
INNER JOIN Numbers n
ON n.Number <= DATEDIFF(HOUR, d.StartDate, d.EndDate)
ORDER BY d.userid,[Date],n.Number;
理想的结果如下：
| UserID | Date | Hour | Seconds_in |
| 1033 | 6/24/2018 | 23:00:00 | 3548 |
| 1033 | 6/25/2018 | 0:00:00 | 3600 |
| 1033 | 6/25/2018 | 1:00:00 | 1493 |
| 1033 | 6/25/2018 | 2:00:00 | 109 |
| 1033 | 6/25/2018 | 3:00:00 | 2642 |
与以前略有不同的策略
我创建了一个临时表 - 有关完整解决方案，请参见 [SQLFiddle] 1
我创建了一个 Grp 和StartDate_Plus列，如下所示
;WITH Numbers (Number) AS
( SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
SELECT
D.UserId
,D.StartDate
,D.EndDate
,StartDate_Plus = DATEADD(HOUR, N.Number, D.StartDate) --Only for Ordering of ResultSet below
,N.Number
,Grp = MAX(N.Number)OVER(PARTITION BY UserId, StartDate)
FROM
dbo.T1 D
INNER JOIN Numbers N ON N.Number <= DATEDIFF(HOUR, D.StartDate, D.EndDate)
输出像这样
UserId StartDate EndDate StartDate_Plus Number Grp
1033 2018-06-24 00:11:51.0000000 2018-06-24 01:03:38.0000000 2018-06-24 00:11:51.0000000 0 1
1033 2018-06-24 00:11:51.0000000 2018-06-24 01:03:38.0000000 2018-06-24 01:11:51.0000000 1 1
1033 2018-06-24 02:12:38.0000000 2018-06-24 02:15:51.0000000 2018-06-24 02:12:38.0000000 0 0
1033 2018-06-24 02:28:08.0000000 2018-06-24 02:36:31.0000000 2018-06-24 02:28:08.0000000 0 0
1033 2018-06-24 03:07:13.0000000 2018-06-24 06:02:05.0000000 2018-06-24 03:07:13.0000000 0 3
1033 2018-06-24 03:07:13.0000000 2018-06-24 06:02:05.0000000 2018-06-24 04:07:13.0000000 1 3
1033 2018-06-24 03:07:13.0000000 2018-06-24 06:02:05.0000000 2018-06-24 05:07:13.0000000 2 3
1033 2018-06-24 03:07:13.0000000 2018-06-24 06:02:05.0000000 2018-06-24 06:07:13.0000000 3 3
StartDate_plus列只是添加了要StartDate和Grp的 [小时数] 只是为了为同一事件提供多行
组添加此额外的 CTE，然后您可以显示虚拟StartDate_，并EndDate_每小时的事件超过一小时
;WITH Numbers (Number) AS
( SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
),cteStartDate_Plus
AS(
SELECT
D.UserId
,D.StartDate
,D.EndDate
,StartDate_Plus = DATEADD(HOUR, N.Number, D.StartDate) --Only for Ordering of ResultSet below
,N.Number
,Grp = MAX(N.Number)OVER(PARTITION BY UserId, StartDate)
FROM
dbo.T1 D
INNER JOIN Numbers N ON N.Number <= DATEDIFF(HOUR, D.StartDate, D.EndDate)
)
SELECT TOP 100 PERCENT
UserId, StartDate, EndDate, Grp
,CS.StartDate_Plus
,CS.Number
,[StartDate_] = CASE --apply some rounding to the StartDate if required
WHEN Number = 0 THEN CS.StartDate
ELSE DATEADD(hour, DATEDIFF(HOUR, 0, CS.StartDate_Plus), 0)
END
,[EndDate_] = CASE --apply some rounding to the EndDate if required
WHEN CS.Number = CS.Grp THEN CS.EndDate
WHEN CS.StartDate_Plus > CS.EndDate THEN CS.EndDate
WHEN CS.Number <> CS.Grp AND CS.StartDate_Plus <= CS.EndDate
THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 30 + DATEPART(MINUTE, DATEADD(MINUTE, 30, CS.StartDate_Plus)),CS.StartDate_Plus)), 0)
END
,[Hour] = CONVERT(TIME(0), DATEADD(HOUR, DATEPART(HOUR, CS.StartDate_Plus), 0))
FROM
cteStartDate_Plus CS
ORDER BY
UserId, CS.StartDate_Plus
然后将上述查询封装到另一个名为 CteDummyDates 的 CTE 中，以下查询仅为您提供所需的结果
SELECT
UserId
,[Date] = CONVERT(DATE, DD.StartDate_Plus)
,DD.[Hour]
,[Seconds_in] = DATEDIFF(SECOND, DD.StartDate_, DD.EndDate_)
FROM
cteDummyDates DD
ORDER BY
DD.UserId, DD.StartDate_
输出
UserId Date Hour Seconds_in
1033 2018-06-24 00:00:00 2889
1033 2018-06-24 01:00:00 218
1033 2018-06-24 02:00:00 193
1033 2018-06-24 02:00:00 503
1033 2018-06-24 03:00:00 3167
1033 2018-06-24 04:00:00 3600
1033 2018-06-24 05:00:00 3600
1033 2018-06-24 06:00:00 125
1033 2018-06-24 07:00:00 1
1033 2018-06-24 08:00:00 2441
1033 2018-06-24 09:00:00 3600