Накопительные итоги

Коррелирующие подзапросы можно использовать для вычисления накопительных итогов - задачи, часто возникающей на практике.

В предположении некоторой упорядоченности строк накопительный итог для каждой строки представляет собой сумму значений некоторого числового столбца для этой строки и всех строк, расположенных выше данной.

Другими словами, накопительный итог для первой строки в упорядоченном наборе будет равен значению в этой строке. Для любой другой строки накопительный итог будет равен сумме значения в этой строке и накопительного итога в предыдущей строке.

Рассмотрим, например, такую задачу.

Вот запрос, который выводит информацию о расходах на пункте 2 в порядке возрастания даты

SELECT point, date, out  
FROM Outcome_o o  
WHERE point = 2  
ORDER BY date;

Фактически, чтобы решить задачу нам нужно добавить еще один столбец, содержащий накопительный итог (run_tot). В соответствии с темой, этот столбец будет представлять собой коррелирующий подзапрос, в котором для ТОГО ЖЕ пункта, что и у ТЕКУЩЕЙ строки включающего запроса, и для всех дат, меньших либо равных дате ТЕКУЩЕЙ строки включающего запроса, будет подсчитываться сумма значений столбца out:

SELECT point, date, out,   
  (SELECT SUM(out)   
   FROM Outcome_o   
   WHERE point = o.point AND date <= o.date) run_tot   
FROM Outcome_o o  
WHERE point = 2  
ORDER BY point, date;

Собственно, использование пункта 2 продиктовано желанием уменьшить результирующую выборку. Чтобы получить накопительные итоги для каждого из пунктов, имеющихся в таблице Outcome_o, достаточно закомментировать строку

WHERE point = 2

Ну а чтобы получить “сквозной” накопительный итог для всей таблицы нужно, видимо, убрать условие на равенство пунктов:

point= o.point

Однако при этом мы получим один и тот же накопительный итог для разных пунктов, работавших в один и тот же день. Вот подобный фрагмент из результирующей выборки,

Но это не проблема, если понять, что же мы хотим в итоге получить. Если нас интересует накопление расхода по дням, то нужно из выборки вообще исключить пункт и суммировать расходы по дням:

SELECT date, SUM(out) out,   
  (SELECT SUM(out)   
   FROM Outcome_o   
   WHERE date <= o.date) run_tot   
FROM Outcome_o o  
GROUP BY date  
ORDER BY date;

В противном случае, нам нужно указать порядок, в котором к накопительному итогу будут добавляться расходы пунктов в случае, когда у нескольких пунктов совпадает дата. Например, упорядочим их по возрастанию номеров:

SELECT point, date, out,   
  (SELECT SUM(out)   
   FROM Outcome_o   
   WHERE date < o.date OR ( date = o.date AND point <= o.point)) run_tot   
FROM Outcome_o o  
ORDER BY date, point;

Рекомендуемые упражнения:  69, 101, 134

Расширение поддержки оконных функций в SQL Server 2012 позволяет решить задачу о накопительных итогах совсем просто.

Применительно к нашей задаче речь идет о следующих появившихся возможностях:

1. Использование сортировки в предложении OVER при применении агрегатных функций.
2. Спецификация диапазона, к значениям которого применяется агрегатная функция. При этом диапазон может быть как ограниченным, так и неограниченным, скажем, от текущей строки до конца или начала отсортированного набора.

Т.е. мы можем получить накопительный итог, упорядочив данные по дате и подсчитав сумму от текущей строки и (неограниченно) выше, причем сделать это с помощью одной функции!

Задачу о накопительных итогах для пункта 2, которая рассматривалась на предыдущей странице, теперь мы можем решить так:

select point, date, out,
    SUM(out) OVER (PARTITION BY point
            ORDER BY date -- сортировка по дате
            RANGE -- диапазон
            UNBOUNDED -- неограниченный
            PRECEDING -- от текущей строки и выше
            )
    FROM Outcome_o o
    WHERE point = 2
    ORDER BY point, date;

Для получения накопительных итогов по каждому пункту отдельно уберем из предыдущего запроса условие отбора по пункту:

select point, date, out,
    SUM(out) OVER (PARTITION BY point ORDER BY date RANGE UNBOUNDED PRECEDING)
    FROM Outcome_o o
    ORDER BY point, date;

Представленные здесь решения будут работать в PostgreSQL и Oracle. Что касается MySQL, то там поддержка оконных функций реализована в версии 8.0.

Если нам потребуется подсчитать накопительный итог с учетом не всех предшествующих строк, а, скажем, двух. В этом случае мы можем использовать следующий синтаксис:

SELECT point,date, out,
SUM(out) OVER (ORDER BY date,point ROWS
BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM Outcome_o o
ORDER BY date,point;

Суммирование происходит в окне, которое задается предложением

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Здесь задается диапазон строк (rows) между (between) текущей строкой (current row) и двумя строками выше (2 preceding).

В этом примере рассматриваются все строки таблицы, упорядоченные по date, point (сортировка по point устраняет неоднозначность, поскольку несколько пунктов могут иметь отчетность в один и тот же день).

Рекомендуемые упражнения:  106