sql - 计算一周花费的总时间

Question

我想计算一周内花费的总时间(一周的开始时间将从给定日期开始)。

这里给定的日期是 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

Answer 1

考虑加入 recursive 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);

Online Demo

|    | 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                |