MS SQL 2005: оконные функции (стр. 2 из 2). Ms sql оконные функции

Оконные функции в T-SQL – инструкция OVER | Info-Comp.ru

В языке Transact-SQL существует очень полезный и мощный инструмент для формирования различных аналитических отчетов – это инструкция OVER, которая работает совместно с так называемыми «оконными функциями», именно об этом мы сегодня с Вами и поговорим.

Содержание

Инструкция OVER в Transact-SQL

OVER – это инструкция T-SQL, которая определяет окно для применения оконной функции. «Окно» в Microsoft SQL Server – это контекст, в котором работает функция с определённым набором строк, относящихся к текущей строке.

Оконная функция – это функция, которая соответственно работает с окном, т.е. набором строк, и возвращает значение на основе неких вычислений.

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

Честно скажу это очень удобный и полезный функционал Microsoft SQL Server. Впервые поддержка оконных функций появилась в версии Microsoft SQL Server 2005, в которой была реализованы базовая функциональность. В Microsoft SQL Server 2012 функционал оконных функций был расширен, и теперь он с лёгкостью решает много задач, которые до этого решались написанием дополнительного, в некоторых случаях, сложного, непонятного кода (вложенные запросы и т.д.).

Упрощенный синтаксис инструкции OVER

Оконная функция (столбец для вычислений) OVER (

[PARTITION BY столбец для группировки]

[ORDER BY столбец для сортировки]

[ROWS или RANGE выражение для ограничения строк в пределах группы]

)

В выражении для ограничения строк в группе можно использовать следующие ключевые слова:

ROWS – ограничивает строки;
RANGE - логически ограничивает строки за счет указания диапазона значений в отношении к значению текущей строки;
UNBOUNDED PRECEDING - указывает, что окно начинается с первой строки группы. Данная инструкция используется только как начальная точка окна;
UNBOUNDED FOLLOWING – с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы, соответственно, она может быть указана только как конечная точка окна;

CURRENT ROW – инструкция указывает, что окно начинается или заканчивается на текущей строке, она может быть задана как начальная или как конечная точка;
BETWEEN «граница окна» AND «граница окна» - указывает нижнюю и верхнюю границу окна, при этом верхняя граница не может быть меньше нижней границы;
«Значение» PRECEDING – определяет число строк перед текущей строкой. Эта инструкция не допускается в предложении RANGE;
«Значение» FOLLOWING - определяет число строк после текущей строки. Если FOLLOWING используется как начальная точка окна, то конечная точка должна быть также указана с помощью FOLLOWING. Эта инструкция не допускается в предложении RANGE.

Примечание! Чтобы указать выражение для дополнительного ограничения строк (ROWS или RANGE) в окне должна быть указана инструкция ORDER BY.

А сейчас давайте рассмотрим оконные функции, которые существуют в Transact-SQL.

Оконные функции в Transact-SQL

В T-SQL оконные функции можно подразделить на следующие группы:

Агрегатные функции;
Ранжирующие функции;
Функции смещения;
Аналитические функции.

В одной инструкции SELECT с одним предложением FROM можно использовать несколько оконных функций. Если инструкция PARTITION BY не указана, функция будет обрабатывать все строки результирующего набора. Некоторые функции не поддерживают инструкцию ORDER BY, ROWS или RANGE.

Исходные данные для примеров

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

В качестве сервера у меня будет выступать Microsoft SQL Server 2016 Express.

Допустим, у нас будет таблица TestTable, которая содержит список товаров с некоторыми характеристиками.

--Создание таблицы CREATE TABLE TestTable( [ProductId] [INT] IDENTITY(1,1) NOT NULL, [CategoryId] [INT] NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NULL ) GO --Вставляем в таблицу данные INSERT INTO TestTable VALUES (1, 'Клавиатура', 100), (1, 'Мышь', 50), (1, 'Системный блок', 200), (1, 'Монитор', 250), (2, 'Телефон', 300), (2, 'Планшет', 500) SELECT * FROM TestTable

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

Агрегатные функции – это функции, которые выполняют на наборе данных вычисления и возвращают итоговое значение. Агрегатные функции, я думаю, всем известны - это, например:

SUM – возвращает сумму значений в столбце;
AVG - определяет среднее значение в столбце;
MAX - определяет максимальное значение в столбце;
MIN - определяет минимальное значение в столбце;
COUNT - вычисляет количество значений в столбце (значения NULL не учитываются). Если написать COUNT(*), то будут учитываться все записи, т.е. все строки. Возвращает тип данных INT;
COUNT_BIG – работает также как COUNT, только возвращает тип данных BIGINT.

Обычно агрегатные функции используются в сочетании с инструкцией GROUP BY, которая группирует строки, но их также можно использовать и без GROUP BY, например, с использованием инструкции OVER, и в данном случае они будут вычислять значения в определённом окне (наборе данных) для каждой текущей строки. Это очень удобно, если Вам необходимо получить какую-нибудь величину по отношению к общей сумме, например.

Пример использования агрегатных оконных функций с инструкцией OVER.

В этом примере продемонстрировано простое применение некоторых агрегатных оконных функций.

SELECT ProductId, ProductName, CategoryId, Price, SUM(Price) OVER (PARTITION BY CategoryId) AS [SUM], AVG(Price) OVER (PARTITION BY CategoryId) AS [AVG], COUNT(Price) OVER (PARTITION BY CategoryId) AS [COUNT], MIN(Price) OVER (PARTITION BY CategoryId) AS [MIN], MAX(Price) OVER (PARTITION BY CategoryId) AS [MAX] FROM TestTable

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

Ранжирующие оконные функции

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

В Microsoft SQL Server существуют следующие ранжирующие функции:

ROW_NUMBER – функция возвращает номер строки, используется для нумерации строк в секции результирующего набора;
RANK - функция возвращает ранг каждой строки. В данном случае значения уже анализируются и, в случае нахождения одинаковых, возвращает одинаковый ранг с пропуском следующего;
DENSE_RANK - функция возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;
NTILE – это функция, которая возвращает результирующий набор, разделённый на группы по определенному столбцу.

Пример использования ранжирующих оконных функций с инструкцией OVER.

В данном примере мы пронумеруем строки в каждой категории, при этом используем сортировку по столбцу ProductId, а также выставим ранг каждому товару в категории на основе его цены.

SELECT ProductId, ProductName, CategoryId, Price, ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY ProductId) AS [ROW_NUMBER], RANK() OVER (PARTITION BY CategoryId ORDER BY Price) AS [RANK] FROM TestTable ORDER BY ProductId

Более детально про ранжирующие функции мы говорили в материале – Функции ранжирования и нумерации в Transact-SQL.

Оконные функции смещения

Функции смещения – это функции, которые позволяют перемещаться и, соответственно, обращаться к разным строкам в наборе данных (окне) относительно текущей строки или просто обращаться к значениям в начале или в конце окна. Эти функции появились в Microsoft SQL Server 2012.

К функциям смещения в T-SQL относятся:

LEAD – функция обращается к данным из следующей строки набора данных. Ее можно использовать, например, для того чтобы сравнить текущее значение строки со следующим. Имеет три параметра: столбец, значение которого необходимо вернуть (обязательный параметр), количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL;
LAG – функция обращается к данным из предыдущей строки набора данных. В данном случае функцию можно использовать для того, чтобы сравнить текущее значение строки с предыдущим. Имеет три параметра: столбец, значение которого необходимо вернуть (обязательный параметр), количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL;

FIRST_VALUE - функция возвращает первое значение из набора данных, в качестве параметра принимает столбец, значение которого необходимо вернуть;
LAST_VALUE - функция возвращает последнее значение из набора данных, в качестве параметра принимает столбец, значение которого необходимо вернуть.

Пример использования оконных функций смещения в T-SQL.

В этом примере сначала мы вернем следующее и предыдущее значение идентификатора товара в категории. Затем с помощью FIRST_VALUE и LAST_VALUE получим первое и последнее значение идентификатора товара в категории, при этом в качестве примера я покажу, как используется синтаксис дополнительного ограничения строк. А потом, используя необязательные параметры функций LEAD и LAG, мы сместимся уже на 2 строки относительно текущей, при этом, если после смещения функцией LAG такой строки не окажется, нам вернется 0, так как мы укажем третий необязательный параметр со значением 0.

SELECT ProductId, ProductName, CategoryId, Price, LEAD(ProductId) OVER (PARTITION BY CategoryId ORDER BY ProductId) AS [LEAD], LAG(ProductId) OVER (PARTITION BY CategoryId ORDER BY ProductId) AS [LAG], FIRST_VALUE(ProductId) OVER (PARTITION BY CategoryId ORDER BY ProductId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS [FIRST_VALUE], LAST_VALUE (ProductId) OVER (PARTITION BY CategoryId ORDER BY ProductId ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS [LAST_VALUE], LEAD(ProductId, 2) OVER (PARTITION BY CategoryId ORDER BY ProductId) AS [LEAD_2], LAG(ProductId, 2, 0) OVER (PARTITION BY CategoryId ORDER BY ProductId) AS [LAG_2] FROM TestTable ORDER BY ProductId

Аналитические оконные функции

Здесь я перечислю так называемые функции распределения, которые возвращают информацию о распределении данных. Эти функции очень специфичны и в основном используются для статистического анализа, к ним относятся:

CUME_DIST - вычисляет и возвращает интегральное распределение значений в наборе данных. Иными словами, она определяет относительное положение значения в наборе;
PERCENT_RANK - вычисляет и возвращает относительный ранг строки в наборе данных;

PERCENTILE_CONT - вычисляет процентиль на основе постоянного распределения значения столбца. В качестве параметра принимает процентиль, который необходимо вычислить;
PERCENTILE_DISC - вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить.

У функций PERCENTILE_CONT и PERCENTILE_DISC синтаксис немного отличается, столбец, по которому сортировать данные, указывается с помощью ключевого слова WITHIN GROUP.

Пример использования аналитических оконных функций в T-SQL.

SELECT ProductId, ProductName, CategoryId, Price, CUME_DIST() OVER (PARTITION BY CategoryId ORDER BY Price) AS [CUME_DIST], PERCENT_RANK() OVER (PARTITION BY CategoryId ORDER BY Price) AS [PERCENT_RANK], PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY ProductId) OVER(PARTITION BY CategoryId) AS [PERCENTILE_DISC], PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY ProductId) OVER(PARTITION BY CategoryId) AS [PERCENTILE_CONT] FROM TestTable

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

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

Звучит запутанно... :) Однако если по-простому, то это та же нумерация, что и в ROW_NUMBER(), которая начинается с той же единицы. Различие в том, что одинаковые записи получают одинаковый номер, а следующая отличающаяся от них запись получает такой номер, как если бы ROW_NUMBER() и использовалась, и все предыдущие записи получили свои уникальные номера. Таким образом, образуется дырка в нумерации, равная количеству одинаковых записей минус единица.

DENSE_RANK()

Эта функция выполняет «плотное» ранжирование, то есть делает ровно то же самое, что и предыдущая, но без «дырок» в нумерации.

NTILE()

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

Для демонстрации различий функций ранжирования можно выполнить следующий запрос:

Некоторые примеры использования

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

Поскольку теперь появилась возможность нумеровать записи в выборке, можно воспользоваться этим для постраничной выдачи результата. Запрос будет выглядеть примерно так:

Как ни странно, этот запрос будет выполняться примерно в два раза быстрее классического:

Так что сбылась еще одна мечта, об эффективной и простой постраничной выборке.. :)

Еще один пример, где использование аналитических функций может быть и удобным, и эффективным. Нередко требуется вывести, например, два самых крупных заказа для каждого клиента. Может случиться так, что заказов с максимальной суммой окажется больше двух. Для случая, когда заказов должно быть именно два, запрос может выглядеть так:

Такой запрос на этих данных примерно в 10 раз эффективнее, чем этот же запрос, выполненный в «старом стиле»:

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

План запроса с аналитической функцией:

План запроса без использования аналитической функции:

Ложка дегтя

Все это, конечно, здорово и замечательно, но есть некоторые негативные моменты, которые уменьшают радость от получения нового инструмента. Он, конечно, хорош, но пока что еще очень беден и не развит. Не считая встроенных агрегирующих функций, в SQL 2005 реализовано всего 4 ранжирующих функции, в то время как в ANSI SQL 2003 больше 30 различных типов аналитических функций...

Обидно и другое.. Как можно заметить, в синтаксисе для аналитических агрегатов отсутствует возможность указать сортировку внутри «окна». Для обычных, встроенных агрегатов это не имеет никакого значения, но в SQL Server 2005 появится возможность писать свои собственные агрегаты на CLR-совместимых языках, которыми, при желании, можно было бы расширить список функций, и вот для этих самодельных агрегирующих функций подобная возможность могла бы быть весьма полезной. Без возможности указать порядок сортировки записей в «окне» невозможно использовать целый класс агрегирующих функций, зависимых от порядка обработки данных. В принципе, ничто не мешает в процессе работы собственной агрегирующей функции складывать данные в некоторую коллекцию, сортировать их там должным образом, а затем обрабатыватать в требуемом порядке, но, очевидно, это не идеальное решение, так как приходится выполнять работу сервера.

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

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

mirznanii.com

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 )

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

www.studsell.com

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

Информация - Компьютеры, программирование

Другие материалы по предмету Компьютеры, программирование

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

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

Введение

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

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

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

CREATE TABLE sample (

ID_Trans int IDENTITY(1,1) PRIMARY KEY,

ID_Customer int NOT NULL,

Amount int NOT NULL )

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

geum.ru

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.

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

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

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

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

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

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

www.wikidocs.ru

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 также можно отнести к аналитическим.

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

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

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

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

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

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

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

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

CREATE TABLE sample (

ID_Trans int IDENTITY(1,1) PRIMARY KEY,

ID_Customer int NOT NULL,

Amount int NOT NULL )

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 <value_expression> , … [n]])

Где <value_expression> – список полей, по которым производится группировка, при этом использование алиасов или выражений не допускается. Собственно, таким образом и формируется “окно” для работы аналитической функции. В “окно” попадают все записи, сгруппированные по указанной колонке. Эта группировка делает практически то же самое, что и оператор 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. В зависимости от используемой функции некоторые записи могут получать один и тот же ранг. Функции ранжирования являются не детерминированными, то есть при одних и тех же входных значениях они могут возвращать разный результат.

На данный момент имееется 4 функции ранжирования, рассмотрим их по порядку:

ROW_NUMBER()

Сбылась голубая мечта жаждущих нумерации записей на сервере. 🙂 Теперь такая возможность появилась, однако это не основное назначение данной функции… Все-таки она призвана нумеровать записи в указанном порядке внутри “окна”. Но если в конструкции OVER опустить секцию PARTITION BY, то за “окно” будет принята вся выборка – что дает возможность пронумеровать все записи в должном порядке, причем порядок нумерации может не совпадать с порядком записей в результирующей выборке, то есть оператор ORDER BY внутри OVER(…), определяющий порядок сортировки записей внутри “окна”, и, соответственно, порядок нумерации записей может не совпадасть с оператором ORDER BY в конструкции SELECT, определяющей порядок выдачи записей клиенту. Нумерация всегда начинается с единицы.

RANK()

Эта функция предназначена для ранжирования записей внутри “окна”, но опять-таки, если колонка для группировки не задана явным образом, то за “окно” принимается вся выборка. Рангом каждой записи является количество уже ранжированных записей с более высоким рангом, чем текущая, плюс единица. Если встретятся несколько записей с одинаковым значением, по которому производится ранжирование, то этим записям будет присвоен одинаковый ранг. Однако при этом следующая запись с новым значением получит такой ранг, как будто бы предыдущие записи получили свой уникальный номер, то есть образуется дырка.

Звучит запутанно… 🙂 Однако если по-простому, то это та же нумерация, что и в ROW_NUMBER(), которая начинается с той же единицы. Различие в том, что одинаковые записи получают одинаковый номер, а следующая отличающаяся от них запись получает такой номер, как если бы ROW_NUMBER() и использовалась, и все предыдущие записи получили свои уникальные номера. Таким образом, образуется дырка в нумерации, равная количеству одинаковых записей минус единица.

DENSE_RANK()

Эта функция выполняет “плотное” ранжирование, то есть делает ровно то же самое, что и предыдущая, но без “дырок” в нумерации.

NTILE()

Данная функция позволяет разделить записи внутри “окна” на указанное количество групп. Для каждой записи она вернет номер группы, к которой принадлежит данная запись. Нумерация групп также начинается с единицы. Если количество записей в “окне” не делится на количество групп, то получится два типа групп с разным количеством записей, отличающимся на единицу, при этом сначала будут выведены группы с большим количеством записей, а затем – с меньшим.

Для демонстрации различий функций ранжирования можно выполнить следующий запрос:

SELECT ID_Customer, Amount,

ROW_NUMBER() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) N_Row,

RANK() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) RANK,

DENSE_RANK() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) DENSE_RANK,

NTILE(2) OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) NTILE

— выведем только одну группу для экономии места

FROM sample WHERE ID_Customer = 2

ID_Cust Amnt N_Row RANK D_RANK NTILE

——————————————————-

2 400 1 1 1 1

2 220 2 2 2 1

2 202 3 3 3 2

2 200 4 4 4 2

2 200 5 4 4 3

2 100 6 6 5 4

Некоторые примеры использования

WITH Numbered

(

SELECT ROW_NUMBER() OVER(ORDER BY name) N_Row, *

FROM sysobjects

)

SELECT * FROM Numbered WHERE N_Row between @First AND @Last

Как ни странно, этот запрос будет выполняться примерно в два раза быстрее классического:

EXECUTE (‘SELECT * FROM

(SELECT TOP ‘ + @Count + ‘ * FROM

(SELECT TOP ‘ + @Last + ‘ *

FROM sysobjects ORDER BY name ASC

) SO1

ORDER BY name DESC) SO2

ORDER BY name’)

Так что сбылась еще одна мечта, об эффективной и простой постраничной выборке.. 🙂

WITH Ranked as

(

SELECT *,

Row_Number() OVER (PARTITION BY ID_Customer

ORDER BY amount DESC) [rank]

FROM sample

)

SELECT * FROM Ranked

WHERE [rank] < 3

Такой запрос на этих данных примерно в 10 раз эффективнее, чем этот же запрос, выполненный в “старом стиле”:

SELECT *

FROM sample s1

WHERE ID_Trans in

(

SELECT top 2 ID_Trans

FROM sample s2

WHERE s1.ID_Customer = s2.ID_Customer

ORDER BY amount DESC

)

План запроса с аналитической функцией:

Операция Стоимость Количество

———————————————————-

|–Filter(WHERE:([Expr1003]<(3))) 0.022873 1

|–Sequence Project(…) 0.022866 1

|–Segment 0.022866 1

|–Sort(ORDER BY:(…)) 0.022864 1

|–Clustered Index Scan(…) 0.006423 1

План запроса без использования аналитической функции:

Операция Стоимость Количество

———————————————————–

|–Nested Loops(Left Semi Join …) 0.18998 1

|–Clustered Index Scan(…) 0.00642 1

|–Filter(WHERE:(…)) 0.18350 15

|–Top(TOP EXPRESSION:((2))) 0.18348 15

|–Filter(WHERE:(…)) 0.18348 15

|–Sort(ORDER([Amount] DESC)) 0.18343 15

|–Clustered Index Scan(…) 0.00665 15

Ложка дегтя

Обидно и другое.. Как можно заметить, в синтаксисе для аналитических агрегатов отсутствует возможность указать сортировку внутри “окна”. Для обычных, встроенных агрегатов это не имеет никакого значения, но в SQL Server 2005 появится возможность писать свои собственные агрегаты на CLR-совместимых языках, которыми, при желании, можно было бы расширить список функций, и вот для этих самодельных агрегирующих функций подобная возможность могла бы быть весьма полезной. Без возможности указать порядок сортировки записей в “окне” невозможно использовать целый класс агрегирующих функций, зависимых от порядка обработки данных. В принципе, ничто не мешает в процессе работы собственной агрегирующей функции складывать данные в некоторую коллекцию, сортировать их там должным образом, а затем обрабатыватать в требуемом порядке, но, очевидно, это не идеальное решение, так как приходится выполнять работу сервера.

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

student.belreferatov.net

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 также можно отнести к аналитическим.

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

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

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

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

CREATE TABLE sample (

ID_Trans int IDENTITY(1,1) PRIMARY KEY,

ID_Customer int NOT NULL,

Amount int NOT NULL )

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

yamiki.ru