На главную страницу
MS SQL 2005: оконные функции
Иван Бодягин (Merle) (оригинал)
Краткое переложение Карасевой
Н.В.
Одно из главных отличий аналитических функций от обычных, скалярных, заключается
в том, что этот класс операторов работает с готовой выборкой.
Сначала формируется выборка, выполняются все объединения, условия WHERE, GROUP BY,
HAVING - все, кроме сортировки, и только затем к практически готовому набору данных
применяется аналитическая функция. Именно поэтому аналитические функции можно указывать
лишь в списке выборки или в условии сортировки.
В общем случае принцип работы аналитических функций можно представить примерно следующим
образом. Допустим, что у нас есть результирующий набор данных, полученный вышеописанным
способом - выполнено все, кроме сортировки. На каждую запись в этом наборе накладывается
так называемое "окно", размеры и положение которого определяются в соответствии
с некоторым аналитическим условием (собственно отсюда и название этого класса функций
- "оконные функции", window functions).
В это виртуальное "окно" попадают несколько других записей из того же
набора, то есть целая группа записей. При этом "окно" может быть сформировано
таким образом, что в него попадут вовсе не соседние записи, а практически произвольные
записи из набора, и даже сама текущая запись, на основании которой формируется "окно",
может в это "окно" не попасть (в дальнейшем термин "окно" будет
использоваться для обозначения именно такого набора записей). Когда "окно"
сформировано, аналитическая функция вычисляет агрегированное значение по записям,
входящим в "окно", и переходит к следующей записи. Для этой записи формируется
новое "окно", снова вычисляется агрегированное значение - и так для всех
записей в выборке. При этом размер и положение "окна" от записи к записи
может меняться.
Строго говоря, практически все, что можно получить с помощью аналитических функций,
можно получить и без них, с помощью обычного T-SQL, но это будет заведомо более
громоздко и зачастую не так эффективно.
В MS SQL Server 2005 реализованы два типа аналитических функций - агрегатные и функции
ранжирования.
Агрегатные функции
Поскольку аналитическая функция возвращает агрегированный результат обработки записей,
попавших в "окно", то самые обычные агрегатные функции теперь имеют возможность
выступить в качестве аналитических.
Отличие состоит в том, что "обычные" агрегаты уменьшают степень детализации
результирующего набора, а в аналитическом варианте степень детализации не уменьшается.
Это относится не только к агрегатам, но и к другим типам аналитических функций.
Сравним результат выполнения двух запросов (база данных "Окраска"). В
одном COUNT выступает в качестве обычного агрегата:
SELECT B_Q_ID,B_V_ID,COUNT(*)C
FROM UTB
WHERE B_Q_ID < 4 -- чтобы уменьшить объем выборки
GROUP BY B_Q_ID,B_V_ID
Результат
B_Q_ID B_V_ID C
-------------------
1 1 2
2 2 1
3 3 1
1 4 1
2 5 1
3 6 1
1 7 1
2 8 1
3 9 1
А в другом уже в качестве аналитической функции:
SELECT B_DATETIME, B_Q_ID, B_V_ID,
COUNT(*)OVER(PARTITION BY B_Q_ID,B_V_ID) C
FROM UTB
WHERE B_Q_ID < 4 -- чтобы уменьшить объем выборки
Результат
B_DATETIME B_Q_ID B_V_ID C
-----------------------------------------
2003-01-01 01:12:01.000 1 1 2
2003-06-23 01:12:02.000 1 1 2
2003-01-01 01:12:05.000 1 4 1
2003-01-01 01:12:08.000 1 7 1
2003-01-01 01:12:03.000 2 2 1
2003-01-01 01:12:06.000 2 5 1
2003-01-01 01:12:09.000 2 8 1
2003-01-01 01:12:04.000 3 3 1
2003-01-01 01:12:07.000 3 6 1
2003-01-01 01:12:10.000 3 9 1
При просмотре результатов второго запроса можно заметить, что сервер не стал ругаться
на указание колонки B_DATETIME выборке, несмотря на отсутствие агрегирующей функции
или группировки по этой колонке.
Для "обычных" агрегатов хотя бы одно из этих условий обязательно должно
соблюдаться, поскольку в противном случае возникнет неоднозначность, - но на аналитические
агрегаты вышеописанное ограничение не распространяется, поскольку степень детализации
не уменьшается и, как следствие, не возникает неоднозначности. Что и можно наблюдать
на примере второго запроса - результат агрегирующей функции просто продублировался
для каждой записи внутри группы, поскольку результат агрегата для каждой записи
внутри "окна" совпадает.
Синтаксис прост. После функции указывается конструкция:
OVER ( [ PARTITION BY value_expression , ... [ n ] ])
где [n]- список полей, по которым производится группировка, при этом использование
алиасов или выражений не допускается. Собственно, таким образом и формируется "окно"
для работы аналитической функции. В "окно" попадают все записи, сгруппированные
по указанной колонке. Эта группировка делает практически то же самое, что и оператор
GROUP BY, но с парой отличий.
Во-первых, как уже говорилось, такая группировка производится по уже сформированной
выборке, а во-вторых, она распространяется только на тот агрегат, после которого
идет конструкция OVER (…), а не на все колонки. И если есть необходимость
использовать две аналитические функции в одном запросе, то для каждой функции конструкция
OVER (…) указывается отдельно.
Строго говоря, результат запроса с аналитической суммой полностью аналогичен результату
такого запроса, написанного в "старом стиле":
SELECT A.B_DATETIME,A.B_Q_ID,A.B_V_ID,C
FROM UTB A
JOIN
(SELECT B_Q_ID,B_V_ID,COUNT(*) C
FROM UTB
GROUP BY B_Q_ID,B_V_ID) B
ON A.B_Q_ID=B.B_Q_ID AND A.B_V_ID=B.B_V_ID
Функции ранжирования
Помимо обычных агрегатов, для аналитических запросов вводятся функции ранжирования.
Эти функции возвращают ранг каждой записи внутри "окна". В общем случае
рангом является некое число, отражающее положение или "вес" записи относительно
других записей в том же наборе. Формируется "окно" точно так же, как и
в случае агрегатных функций - с помощью группировки. Однако, поскольку результат
работы функций ранжирования зависит от порядка обработки записей, то обязательно
должен быть указан порядок записей внутри "окна" посредством конструкции
ORDER BY. В зависимости от используемой функции некоторые записи могут получать
один и тот же ранг.
Функции ранжирования являются не детерминированными, то есть при одних и тех же
входных значениях они могут возвращать разный результат.
На данный момент имеется 4 функции ранжирования:
ROW_NUMBER()
Эта функция нумерует записи в указанном порядке внутри "окна". Но если
в конструкции OVER опустить секцию PARTITION BY, то за "окно" будет принята
вся выборка - что дает возможность пронумеровать все записи в должном порядке. Причем
порядок нумерации может не совпадать с порядком записей в результирующей выборке,
то есть оператор ORDER BY внутри OVER(…), определяющий порядок сортировки
записей внутри "окна", и, соответственно, порядок нумерации записей может
не совпадать с оператором ORDER BY в конструкции SELECT, определяющей порядок выдачи
записей клиенту. Нумерация всегда начинается с единицы.
RANK()
Эта функция предназначена для ранжирования записей внутри "окна", но опять-таки,
если колонка для группировки не задана явным образом, то за "окно" принимается
вся выборка. Это та же нумерация, что и в ROW_NUMBER(), которая начинается с той
же единицы. Различие в том, что одинаковые записи получают одинаковый номер, а следующая
отличающаяся от них запись получает такой номер, как если бы ROW_NUMBER() и использовалась,
и все предыдущие записи получили свои уникальные номера. Таким образом, образуется
дырка в нумерации, равная количеству одинаковых записей минус единица.
DENSE_RANK()
Эта функция выполняет "плотное" ранжирование, то есть делает ровно то
же самое, что и предыдущая, но без дырок" в нумерации.
NTILE()
Данная функция позволяет разделить записи внутри "окна" на указанное количество
групп. Для каждой записи она вернет номер группы, к которой принадлежит данная запись.
Нумерация групп также начинается с единицы. Если количество записей в "окне"
не делится на количество групп, то получится два типа групп с разным количеством
записей, отличающимся на единицу, при этом сначала будут выведены группы с большим
количеством записей, а затем - с меньшим.
Для демонстрации различий функций ранжирования можно выполнить следующий запрос:
SELECT B_Q_ID, B_V_ID,
ROW_NUMBER() OVER(PARTITION BY B_Q_ID ORDER
BY B_V_ID DESC) N_Row,
RANK() OVER(PARTITION BY B_Q_ID ORDER
BY B_V_ID DESC)RANK,
DENSE_RANK() OVER(PARTITION BY B_Q_ID ORDER
BY B_V_ID DESC)DENSE_RANK,
NTILE(2) OVER(PARTITION BY B_Q_ID ORDER BY B_V_ID DESC)NTILE
FROM UTB WHERE B_Q_ID = 4
Результат
B_Q_ID B_V_ID N_Row RANK DENSE_RANK NTILE
----------- ------- ------ ----- ----------- ------
4 37 1 1 1 1
4 37 2 1 1 1
4 10 3 3 2 2
На главную страницу