Книги и статьи по SQL Rambler's Top100 Switch language to: English 23 сентября 2019 г. 12:19:42


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

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

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.