MS SQL 2005: оконные функции (45796)

Посмотреть архив целиком

MS SQL 2005: оконные функции

Иван Бодягин (Merle)

Введение

Ввиду того, что в следующей версии MS SQL Server, выход которой ожидается в 2005 году, нововведений просто безумное количество, слона приходится есть по частям. Данный кусочек посвящен новой функциональности называемой «оконные функции» (Window Functions), также известной широкой общественности под именем «аналитических», или OLAP-функций.

Сама по себе идея не нова. С подачи IBM, Oracle, Informix и Compaq аналитические функции были добавлены в ANSI SQL 99. В Oracle поддержка подобной функциональности, со своими специфичными расширениями, появилась с версии 8i R2, в DB2 – с версии 7.1, да и имеющиеся в T-SQL на данный момент функции CUBE и ROLUP также можно отнести к аналитическим.

С одной стороны, Microsoft и так поставляет вместе c SQL Server достаточно мощный механизм для анализа данных – Analysis Services. Отчасти по этой причине Microsoft не спешила поддерживать инициативу введения некоторых OLAP-функций в стандарт SQL.

Но с другой стороны, при необходимости некоторого анализа данных не всегда есть возможность и желание поднимать еще один сервис. К тому же, строго говоря, аналитические функции не совсем правильно относить к OLAP, они не предназначены для многомерного анализа данных, работы с гиперкубами и сложными иерархиями, эти функции осуществляют лишь статистический анализ данных в готовой выборке... Тем не менее, это достаточно мощный и удобный инструмент, и поэтому, в конце концов, Microsoft также не осталась в стороне.

Сначала несколько слов о том, что же из себя представляют и как работают аналитические функции в общем виде. Одно из главных отличий аналитических функций от обычных, скалярных, заключается в том, что этот класс операторов работает с готовой выборкой. Сначала формируется выборка, выполняются все объединения, условия WHERE, GROUP BY, HAVING – все, кроме сортировки, и только затем к практически готовому набору данных применяется аналитическая функция. Именно поэтому аналитические функции можно указывать лишь в списке выборки или в условии сортировки.

В общем случае принцип работы аналитических функций можно представить примерно следующим образом. Допустим, что у нас есть результирующий набор данных, полученный вышеописанным способом – выполнено все, кроме сортировки. На каждую запись в этом наборе накладывается так называемое «окно», размеры и положение которого определяются в соответствии с некоторым аналитическим условием (собственно отсюда и название этого класса функций – «оконные функции», window functions). В это виртуальное «окно» попадают несколько других записей из того же набора, то есть целая группа записей. При этом «окно» может быть сформировано таким образом, что в него попадут вовсе не соседние записи, а практически произвольные записи из набора, и даже сама текущая запись, на основании которой формируется «окно», может в это «окно» не попасть (в дальнейшем термин «окно» будет использоваться для обозначения именно такого набора записей). Когда «окно» сформировано, аналитическая функция вычисляет агрегированное значение по записям, входящим в «окно», и переходит к следующей записи. Для этой записи формируется новое «окно», снова вычисляется агрегированное значение - и так для всех записей в выборке. При этом размер и положение «окна» от записи к записи может меняться, в таких случаях используют термин «скользящее окно» (sliding window).

Спектр применения аналитических функций достаточно широк - это различного рода распределения (ранговое (ranking), кумулятивное и т.д.), процентирование (percentile), линейная регрессия, стандартные отклонения, дисперсии, нарастающие итоги, а также прочая низшая математика и высшая бухгалтерия. :)

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

Текущая реализация

На данный момент в MS SQL Server 2005 реализованы два типа аналитических функций – агрегатные и функции ранжирования.

Агрегатные функции

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

Допустим, у нас есть таблица с операциями клиентов, содержащая ID транзакции, ID клиента и сумму сделки, всего 15 записей...

CREATE TABLE sample (

ID_Trans int IDENTITY(1,1) PRIMARY KEY,

ID_Customer int NOT NULL,

Amount int NOT NULL )

GO


INSERT INTO sample (ID_Customer, Amount)

SELECT 1, 100

union all SELECT 2, 100

union all SELECT 3, 100

union all SELECT 1, 110

union all SELECT 1, 120

union all SELECT 2, 200

union all SELECT 2, 220

union all SELECT 3, 300

union all SELECT 3, 330

union all SELECT 3, -100

union all SELECT 2, 400

union all SELECT 1, 101

union all SELECT 2, 202

union all SELECT 1, 100

union all SELECT 2, 200

Сравним результат выполнения двух запросов. В одном SUM выступает в качестве обычного агрегата:

SELECT ID_Customer, sum(Amount) FROM sample GROUP BY ID_Customer


--- Результат вполне предсказуем:

1 531

2 1322

3 630

А в другом уже в качестве аналитической функции:

SELECT ID_Trans, ID_Customer,

sum(Amount) OVER (PARTITION BY ID_Customer)

FROM sample


--- А здесь получим следующее:

4 1 531

5 1 531

1 1 531

12 1 531

14 1 531

15 2 1322

11 2 1322

13 2 1322

2 2 1322

6 2 1322

7 2 1322

8 3 630

9 3 630

10 3 630

3 3 630

При просмотре результатов второго запроса можно заметить, что сервер не стал ругаться на указание колонки ID_Trans в выборке, несмотря на отсутствие агрегирующей функции или группировки по этой колонке. Для «обычных» агрегатов хотя бы одно из этих условий обязательно должно соблюдаться, поскольку в противном случае возникнет неоднозначность –Но на аналитические агрегаты вышеописанное ограничение не распространяется, поскольку степень детализации не уменьшается и, как следствие, не возникает неоднозначности. Что и можно наблюдать на примере второго запроса – результат агрегирующей функции просто продублировался для каждой записи внутри группы, поскольку результат агрегата для каждой записи внутри «окна» совпадает.

Самое время разобраться с синтаксисом – он довольно прост. После функции указывается конструкция

OVER ([PARTITION BY , ... [n]])

где – список полей, по которым производится группировка, при этом использование алиасов или выражений не допускается. Собственно, таким образом и формируется «окно» для работы аналитической функции. В «окно» попадают все записи, сгруппированные по указанной колонке. Эта группировка делает практически то же самое, что и оператор GROUP BY, но с парой отличий. Во-первых, как уже говорилось, такая группировка производится по уже сформированной выборке, а во-вторых, она распространяется только на тот агрегат, после которого идет конструкция OVER (…), а не на все колонки. И если есть необходимость использовать две аналитические функции в одном запросе, то для каждой функции конструкция OVER (…) указывается отдельно.

Строго говоря, результат запроса с аналитической суммой полностью аналогичен результату такого запроса, написанного в «старом стиле»:

SELECT s.ID_Trans, s.ID_Customer, t.sum_amount

FROM sample s

INNER JOIN

(SELECT sum(Amount) sum_amount, ID_Customer

FROM sample

GROUP BY ID_Customer

) t

ON s.ID_Customer = t.ID_Customer

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

В качестве аналитических функций могут также выступать и собственноручно написанные агрегаты.

Функции ранжирования

Помимо обычных агрегатов, для аналитических запросов вводятся функции ранжирования. Эти функции возвращают ранг каждой записи внутри «окна». В общем случае рангом является некое число отражающее положение или «вес» записи относительно других записей в том же наборе. Формируется «окно» точно так же, как и в случае агрегатных функций – с помощью группировки. Однако, поскольку результат работы функций ранжирования зависит от порядка обработки записей, то обязательно должен быть указан порядок записей внутри «окна» посредством конструкции ORDER BY. В зависимости от используемой функции некоторые записи могут получать один и тот же ранг. Функции ранжирования являются не детерминированными, то есть при одних и тех же входных значениях они могут возвращать разный результат.


Случайные файлы

Файл
01.doc
121013.rtf
ref-18406.doc
118588.rtf
26188-1.rtf




Чтобы не видеть здесь видео-рекламу достаточно стать зарегистрированным пользователем.
Чтобы не видеть никакую рекламу на сайте, нужно стать VIP-пользователем.
Это можно сделать совершенно бесплатно. Читайте подробности тут.