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

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

Расширение поддержки оконных функций в  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. 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     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;  

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;

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 устраняет неоднозначность, поскольку несколько пунктов могут иметь отчетность в один и тот же день).