Типичные проблемы

Коррелирующие подзапросы

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

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

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

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

Для пункта 2 по таблице Outcome_o получить на каждый день суммарный расход за этот день и все предыдущие дни.

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

Консоль

Выполнить

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

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;

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

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;

    Схема

Следующая страница