Блог

Главная - Вопросы по программированию - Как получить текущий итог из последовательных столбцов в Oracle SQL

Как получить текущий итог из последовательных столбцов в Oracle SQL

#sql #oracle #gaps-and-islands #cumulative-sum #analytic-functions

#sql #Oracle #пробелы и острова #кумулятивная сумма #аналитические функции

Вопрос:

У меня возникли проблемы с отображением последовательных праздников из существующего набора данных дат в Oracle SQL. Например, в декабре 2017 года между 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? Или это неправильный подход к решению этой проблемы?

Спасибо за вашу помощь!

Ответ №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;

Комментарии:

1. привет @gordon, как насчет того, чтобы взять поддельную дату для каждой группы? dbfiddle.uk /… Может быть, это та же самая стоимость (?)

2. Кто-то должен опубликовать общий вопрос «У меня проблема с пробелами и островами», чтобы мы могли ответить на него и отметить все будущие вопросы, подобные этим дубликатам. Похоже, что эта же проблема возникает часто.

3. @MatthewMcPeak . . . Общего решения не существует.

4. @dani . . . Я не знаю, что такое поддельная дата. Возможно, это для другого вопроса.

5. @MatthewMcPeak, кроме того, помогает пометить вопрос как ghap-and-island can. Это готовый для этого тег. У вас есть 10 кб, будьте свободны переназначить Q.

Ответ №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

Ответ №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<> возиться с немного большим набором данных.

Метки: