我想计算一周中花费的总时间(一周的开始时间将从给定日期开始(。
此处给定的日期为2020-06-23 15:30:00。下周将从7天后开始。
如果只有第二个在第一个之后不到一小时，则活动的持续时间将根据同一id的两行之间的时间间隔计算。
select t.UserName,
1 + datediff(second, '2020-06-23 15:30:00', CompletedOn) / (24 * 60 * 60 * 7) as week_num,
sum(datediff(minute, CompletedOn, next_ts)) as duration_minutes
from (select t.*,
lead(CompletedOn) over (partition by UserName order by CompletedOn) as next_ts
from #Results t
where t.CompletedOn >= '2020-06-23 15:30:00'
) t
where datediff(minute, CompletedOn, next_ts) < 60 and CompletedOn >='2020-06-23 15:30:00' and t.UserName = 'John B'
group by t.UserName, datediff(second, '2020-06-23 15:30:00', CompletedOn) / (24 * 60 * 60 * 7)
order by t.UserName, week_num;
如果一周中没有日期条目，则上述查询不考虑显示week_num，因此将结果显示为：
UserName | week_num | duration_minutes
---------------|----------|------------------
John B | 1 | 38
John B | 2 | 10
John B | 3 | 0
John B | 5 | 0
然而，我希望输出为记录中最后一个日期之前的所有周数。
UserName | week_num | duration_minutes
---------------|----------|------------------
John B | 1 | 38
John B | 2 | 10
John B | 3 | 0
John B | 4 | 0
John B | 5 | 0
一些样本数据：
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
Truncate TABLE #Results
else
CREATE TABLE #Results
(
UserName varchar(20) not null,
CompletedOn datetime not null
)
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-23T15:30:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-23T15:31:00'
--1 min
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-30T12:57:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-30T13:06:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-30T13:34:00'
--37 min
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-30 15:31:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-30 15:33:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-06-30 15:41:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-06 08:41:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-07 14:29:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-09 15:22:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-09 16:23:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-21 15:34:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-21 17:00:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-09 15:22:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-09 16:23:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-21 15:34:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-21 17:00:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-21 17:00:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-23 06:34:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-23 08:28:00'
INSERT INTO #Results (UserName, CompletedOn)
SELECT 'John B', '2020-07-23 08:28:00'
Db Fiddle
考虑加入递归CTE，该CTE生成UserName和所有后续week_num到定义终点的配对匹配。以下使用10，但可以扩展到52。
WITH pairs AS (
SELECT DISTINCT UserName, 1 AS week_num
FROM #Results
UNION ALL
SELECT UserName, week_num + 1
FROM pairs
WHERE week_num < 10 -- ADJUST ## AS NEEDED
), sub AS (
SELECT t.UserName
, t.CompletedOn
, LEAD(CompletedOn) OVER (PARTITION BY t.UserName ORDER BY t.CompletedOn) as next_ts
FROM #Results t
WHERE t.CompletedOn >= '2020-06-23 15:30:00'
), main AS (
SELECT sub.UserName
, 1 + DATEDIFF(SECOND, '2020-06-23 15:30:00', sub.CompletedOn) / (24 * 60 * 60 * 7) AS week_num
, SUM(DATEDIFF(MINUTE, sub.CompletedOn, sub.next_ts)) AS duration_minutes
FROM sub
WHERE DATEDIFF(MINUTE, sub.CompletedOn, sub.next_ts) < 60
AND sub.CompletedOn >='2020-06-23 15:30:00'
AND sub.UserName = 'John B'
GROUP BY sub.UserName
, DATEDIFF(SECOND, '2020-06-23 15:30:00', sub.CompletedOn) / (24 * 60 * 60 * 7)
)
SELECT pairs.UserName
, pairs.week_num
, ISNULL(main.duration_minutes, 0) AS duration_minutes
FROM pairs
LEFT JOIN main
ON pairs.UserName = main.UserName
AND pairs.week_num = main.week_num
OPTION (MAXRECURSION 0);
在线演示
| | UserName | week_num | duration_minutes |
|----|----------|----------|------------------|
| 1 | John B | 1 | 38 |
| 2 | John B | 2 | 10 |
| 3 | John B | 3 | 0 |
| 4 | John B | 4 | 0 |
| 5 | John B | 5 | 0 |
| 6 | John B | 6 | 0 |
| 7 | John B | 7 | 0 |
| 8 | John B | 8 | 0 |
| 9 | John B | 9 | 0 |
| 10 | John B | 10 | 0 |