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

Question

我在Oracle SQL中显示现有日期数据集中的连续假期时遇到麻烦。例如，在 2017 年 12 月 20 日至 30 日之间，有以下休息日(因为圣诞节和周末(：

• 23.12.2017 星期六
• 24.12.2017 星期日
• 25.12.2017 圣诞节
• 30.12.2017 星期六

现在我希望我的结果数据集看起来像这样(需要 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 到该行的 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;

Answer 2

您可以使用递归递归子查询分解 - 前提是您的日期是连续的，没有间隙(或者您有一些不同的行号序列要遵循一个步骤(。

WITH t1(dat, isoff, runtot) AS (
  SELECT dat, isoff, 0 runtot
  FROM   tab 
  WHERE  DAT = DATE'2017-12-20'
  UNION ALL
  SELECT t2.dat, t2.isoff,  
          case when t2.isoff = 0 then 0 else runtot + t2.isoff end as runtot
  FROM   tab t2, t1
  WHERE  t2.dat = t1.dat + 1
)
SELECT  dat, isoff, runtot
FROM   t1;
DAT                      ISOFF     RUNTOT
------------------- ---------- ----------
20.12.2017 00:00:00          0          0
21.12.2017 00:00:00          0          0
22.12.2017 00:00:00          0          0
23.12.2017 00:00:00          1          1
24.12.2017 00:00:00          1          2
25.12.2017 00:00:00          1          3
26.12.2017 00:00:00          0          0
27.12.2017 00:00:00          0          0
28.12.2017 00:00:00          0          0
29.12.2017 00:00:00          0          0
30.12.2017 00:00:00          1          1

Answer 3

另一个变体，不需要子查询或 CTE，但确实需要所有日期都存在并具有相同的时间，是 - 仅适用于假日日期(其中isoff = 1( - 查看自上次非假日日期以来已经过去了多少天：

select dat,
  isoff,
  case
    when isoff = 1 then
      coalesce(dat - max(case when isoff = 0 then dat end)
              over (order by dat range between unbounded preceding and 1 preceding), 1)
    else 0
  end as runtot
from time_dataset
order by dat;
DAT             ISOFF     RUNTOT
---------- ---------- ----------
2017-12-20          0          0
2017-12-21          0          0
2017-12-22          0          0
2017-12-23          1          1
2017-12-24          1          2
2017-12-25          1          3
2017-12-26          0          0
2017-12-27          0          0
2017-12-28          0          0
2017-12-29          0          0
2017-12-30          1          1

如果范围中的第一个日期是假日，则coalesce()存在 - 因为没有以前的非假日日期可以比较，因此减法将变为空。

db<>摆弄稍大的数据集。