--С помощью PARTITION BY мы делим результат запроса на окна
--Общее количество товара и его стоимость по заказам, детально по товару
SELECT id AS N'Заказ', product AS N'Товар',
SUM(qty) OVER (PARTITION BY id, product) AS N'Общее кол-во в заказе',
SUM(qty * cost) OVER (PARTITION BY id, product) AS N'Общая с-мость в заказе',
SUM(qty * cost) OVER () AS N'Общая с-мость'
FROM sales
--С помощью ORDER BY мы можем задать сортировку
SELECT id, product, qty,
SUM(qty) OVER (PARTITION BY product) AS allQtyProduct,
SUM(qty) OVER (ORDER BY id DESC) AS allQtyId
FROM sales
--Запрос имеет 9 строк и 4 диапазона
--ROWS отвечпет за строки.
--RANGE отвечпет за диапазон.
--CURRENT ROW - текущая строка или диапазон
SELECT id, product, qty,
SUM(qty*cost) OVER (ORDER BY id DESC ROWS CURRENT ROW) AS IdQtyCost
FROM sales
SELECT id, product, qty,
SUM(qty*cost) OVER (ORDER BY id DESC RANGE CURRENT ROW) AS IdAllQtyCost
FROM sales
--UNBOUNDED PRECEDING - указывает, что надо учитывать все строки/диапазоны с первого и по текущий
--Будет суммировать каждую следующую строку
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS SumQty
FROM sales
--Будет суммировать каждый следующий диапазон
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) AS SumQty
FROM sales
--UNBOUNDED FOLLOWING - указывает, что надо учитывать все строки/диапазоны с текущего и по последний.
--Может быть указанным только в предложении BETWEEN как конечная точка.
--BETTWEEN - используется для указания границ.
--Будет суммировать каждую следующую строку между указанными значениями
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SumQty
FROM sales
--Будет суммировать каждый следующий диапазон между указанными значениями
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SumQty
FROM sales
--PRECEDING - указывает, что нужно учитывать текущую строку и кол-во строк до нее.
--Не допускается в предложении RANGE.
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS 1 PRECEDING) AS SumQty
FROM sales
--FOLLOWING - указывает, что нужно учитывать диапазон кол-во строк после текущей строчки.
--Может быть использовано только в предложении BETWEEN. Не допускается в предложении RANGE.
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS SumQty
FROM sales
--ROW_NUMBER() - задает каждой строчке окна уникальный, последовательный номер, начиная с единицы.
--Функция "ROW_NUMBER" должна содержать предложение OVER вместе с предложением ORDER BY
SELECT id, product, qty,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS R
FROM sales
--Пример вывода с "постраничной навигацией" используя ROW_NUMBER()
DECLARE
@pagenum AS INT = 3,
@pagesize AS INT = 2;
WITH C AS
(
SELECT ROW_NUMBER() OVER( ORDER BY product, id ) AS rownum,
id, product
FROM sales
)
SELECT id, product
FROM C
WHERE rownum BETWEEN (@pagenum - 1) * @pagesize + 1 --4
AND @pagenum * @pagesize; --6
--RANK() - Возвращает ранг каждой строки в окне.
--Ранг для каждого уникального значения столбца или столбцов указанных в ORDER BY вычисляется лишь единожды, при первом нахождении оного.
--По формуле единица плюс количество строк до строки от начала окна.
SELECT id, product, qty,
RANK() OVER (ORDER BY id) AS R
FROM sales
--DENSE_RANK() - возвращает ранг строк в окне без прыжков через значения.
--Ранг строки равен количеству уникальных значений указанных в ODER BY, предшествующих строке, увеличенному на единицу.
SELECT id, product, qty,
DENSE_RANK() OVER (ORDER BY id) AS R
FROM sales
--NTILE - Распределяет строки в окне на заданное количество групп.
--Группы нумеруются, начиная с единицы.
--Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка.
SELECT id, product, qty,
NTILE(2) OVER (PARTITION BY id ORDER BY id) AS R,
NTILE(2) OVER (ORDER BY id) AS RO
FROM sales
--LAG - возвращает предыдущее значение для указанного столбца, сгруппированого по некому столбцу
--LEAD - возвращает следующее значение для указанного столбца, сгруппированого по некому столбцу
--FIRST_VALUE - возвращает первое значение для указанного столбца, сгруппированого по некому столбцу
--LAST_VALUE - возвращает последнее значение для указанного столбца, сгруппированого по некому столбцу
SELECT id, product, qty,
LAG(qty) OVER (PARTITION BY id ORDER BY id) AS [prev]
,LEAD(qty) OVER (PARTITION BY id ORDER BY id) AS [next]
,FIRST_VALUE(qty) OVER (PARTITION BY id ORDER BY id) AS [first]
,LAST_VALUE(qty) OVER (PARTITION BY id ORDER BY id) AS [last]
FROM sales
Код обмена баннерами
, и покажем на ней основные возможности оконных функций.
, очень доступно человек расписал , что некоторые функции делают: 