sql - 如何从 Oracle SQL 中的连续列获取运行总计

Question

我无法在 Oracle SQL 中显示现有日期数据集中的连续假期。例如，2017年12月20日至30日期间，有以下几天休息(因为圣诞节和周末):

• 2017 年 12 月 23 日星期六
• 2017 年 12 月 24 日星期日
• 2017 年 12 月 25 日圣诞节
• 2017 年 12 月 30 日星期六

现在我希望我的结果数据集看起来像这样(需要 RUNTOT):

DAT         ISOFF   RUNTOT
20.12.2017  0       0
21.12.2017  0       0
22.12.2017  0       0
23.12.2017  1       1
24.12.2017  1       2
25.12.2017  1       3
26.12.2017  0       0
27.12.2017  0       0
28.12.2017  0       0
29.12.2017  0       0
30.12.2017  1       1

这意味着当“ISOFF”更改时，我想对“ISOFF”为 1 的连续行进行计数(或求和)。

我尝试使用分析函数来找到解决方案，其中我将“ISOFF”总结为当前行。

  SELECT DAT,
         ISOFF,
         SUM (ISOFF)
         OVER (ORDER BY DAT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             AS RUNTOT
    FROM (TIME_DATASET)
   WHERE DAT BETWEEN DATE '2017-12-20' AND DATE '2017-12-27'
ORDER BY 1

我现在得到的是以下数据集:

DAT         ISOFF   RUNTOT
20.12.2017  0       0
21.12.2017  0       0
22.12.2017  0       0
23.12.2017  1       1
24.12.2017  1       2
25.12.2017  1       3
26.12.2017  0       3
27.12.2017  0       3
28.12.2017  0       3
29.12.2017  0       3
30.12.2017  1       4

如果 ISOFF 更改为 0，如何重置运行总计？或者这是解决这个问题的错误方法？

感谢您的帮助!

Answer 1

这是一个间隙和岛屿问题。下面是一种根据该行之前 0 的数量来分配组的方法:

select t.*,
       (case when is_off = 1
             then row_number() over (partition by grp order by dat)
        end) as runtot
from (select t.*,
             sum(case when is_off = 0 then 1 else 0 end) over (order by dat) as grp
      from TIME_DATASET t
     ) t;