MS SQL 2005: оконные функции
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
Похожие работы на - MS SQL 2005: оконные функции
|