T-SQL 2012: Оконные функции(Подробное изложение)

В данной статье мы рассмотрим работу с оконными функциями в T-SQL 2012.

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

—Создаем базу и и добавляем тестовые данные
USE [master];
GO

IF DB_ID('sbase') IS NOT NULL 
DROP DATABASE sbase;

CREATE DATABASE sbase;
GO

USE sbase;

—id — номер заказа, product — товар, qty — количество, cost — цена
CREATE TABLE sales(
       id SMALLINT,
       product VARCHAR(MAX),
       qty SMALLINT,
       cost SMALLINT);

INSERT INTO sales 
VALUES        (1,        'wine',        10,        30)
            ,(1,    'beer',        5,        15)
            ,(1,    'cognac',    4,        50)
            ,(2,    'cognac',    8,        40)
            ,(2,    'vodka',    2,        30)
            ,(3,    'beer',        15,        12)
            ,(3,    'cognac',    12,        46)
            ,(3,    'vodka',    10,        25)
            ,(4,    'vodka',    1,        30);

—С помощью 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

На этом наша статья окончена.

При создании использовал материалы с msdn, описание некоторых функций взял отсюда , очень доступно человек расписал , что некоторые функции делают: http://alexanderkobelev.blogspot.ru/2013/03/tsql-over-sql-server-2012.html

Для дальнейшего освоения темы рекомендую книгу: Microsoft SQL Server 2012. Высокопроизводительный код T-SQL. Оконные функции

Так-же замечу, что не все примеры работают в ms sql server 2012 express , в редакции стандарт все примеры — работают без проблем.

Комментарии пользователей

В оракловых аналитических функциях можно использовать предложение partition by для группировки данных возвращаемых SQL-select запросом. Именно для данных в этих группах будет применена аналитическая функция. Создадим тестовую таблицу для демонстрации примера:

Теперь заполним таблицу некоторым количеством данных — парами из цифр и дат:

Теперь создадим вьюху/представление которое будет иметь флаг указывающий на наибольшую/последнюю дату в месяце. Под последней датой имеем ввиду не 30.06.2000 для примера, а 14.06.2000 — последняя дата в июне, присутствующая в нашей таблице. Для реализации задуманного используем аналитическую функцию max вместе с предложением partition by.

Предложение OVER (Transact-SQL)

C помощью partition by сгруппируем данные возвращаемые запросом по месяцам, а функцию max используем для нахождения максимальной даты в каждой группе:

Запрос вернет следующее:

Источник: http://adp-gmbh.ch/ora/sql/analytical/partition_by.html

Также про использование предложения Partition by можно почитать в записи о сортировке по одному полю в таблице из нескольких столбцов.

Запись опубликована 01.06.2011 в 12:55 дп и размещена в рубрике Книга SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

Я долго считал партиционирование плохой практикой, а само слово не любил из-за кальки с английского, которую крайне сложно выговорить с первого раза. И если слово «партиционирование» я так с первого раза и не выговариваю, то саму практику пришлось признать как необходимое и неизбежное зло. Чтобы никто не подумал, что я делаю что-то плохое, я использую для этого термин «инженерный компромисс», звучит умнее и не так обидно.

Если бы партиционирование укладывалось в рамки официальной документации, то и писать бы о нем не стоило.

Предложение OVER (Transact-SQL)

Но есть особенности, которые не сразу ясны из официальной документации, либо, вообще, в ней не раскрываются.

Сразу скажу, что если есть возможность не делать партиционирование, то лучше его не делать. Зачастую дешевле увеличить размер памяти у вашего сервера БД, чтобы он начал запросто переваривать большие таблицы. И только когда вы упретесь в то, что такого количества памяти нет в продаже, стоит приступать к активным действиям.

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

Во-первых, в должны быть IMMUTABLE функции. День у меня ушел на то, чтобы понять, что TIMESTAMP WITH TIME ZONE не является IMMUTABLE.

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

После выполнения мы получим 36 новых таблиц в БД и триггер, похожий на этот.

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

Более того, надо понимать, что в случае с партиционироваными таблицами, вы можете иметь одинаковые id для разных записей, так как уникальность id проверяется (если проверяется) только на уровне конкретной дочерней таблицы. Если вы вставляете данные только в главную таблицу , то id гарантированно будут отличаться, потому что триггер использует sequence от главной таблицы. Но ничего не запрещает вам вставить в дочернюю таблицу данные напрямую.

Какой выигрыш от такого усложнения? Во-первых, вместо одного большого индекса у вас будет теперь много маленьких, которые помещаются в память. Если вам надо сделать выборку по дате, то seq scan будет идти только по нужным партициям. В нашем случае, например, все запросы, в основном, делаются по последнему месяцу, поэтому она оказывается в кэше БД и, самое главное, помещается туда целиком. А как мы знаем, БД для web-проекта либо помещается в память, либо не работает, но об этом я напишу как-нибудь в другой раз.

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