MS SQL 2005: оконные функции - реферат

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

Введение

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

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

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

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

Поначалу несколько слов о том, что все-таки из себя представляют и как работают аналитические функции в общем виде. Одно из основных различий аналитических функций от обыденных, скалярных, состоит в том, что этот класс операторов MS SQL 2005: оконные функции - реферат работает с готовой подборкой. Поначалу формируется подборка, производятся все объединения, условия WHERE, GROUP BY, HAVING – все, не считая сортировки, и только потом к фактически готовому набору данных применяется аналитическая функция. Вот поэтому аналитические функции можно указывать только в перечне подборки либо в условии сортировки.

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

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

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

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

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

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

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

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

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

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

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

--- Итог полностью предсказуем:

1 531

2 1322

3 630

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

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 в выборке, невзирая на отсутствие агрегирующей функции либо группировки по этой колонке. Для «обычных» агрегатов MS SQL 2005: оконные функции - реферат хотя бы одно из этих критерий непременно должно соблюдаться, так как в неприятном случае возникнет неоднозначность –Но на аналитические агрегаты вышеперечисленное ограничение не распространяется, так как степень детализации не миниатюризируется и, как следствие, не появляется неоднозначности. Что и можно следить на примере второго запроса – итог агрегирующей MS SQL 2005: оконные функции - реферат функции просто продублировался для каждой записи снутри группы, так как итог агрегата для каждой записи снутри «окна» совпадает.

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

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

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

Строго говоря, итог запроса с аналитической суммой стопроцентно аналогичен результату такового запроса, написанного MS SQL 2005: оконные функции - реферат в «старом стиле»:

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

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

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

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

Кроме обыденных агрегатов, для аналитических MS SQL 2005: оконные функции - реферат запросов вводятся функции ранжирования. Эти функции возвращают ранг каждой записи снутри «окна». В общем случае рангом является некоторое число отражающее положение либо «вес» записи относительно других записей в том же наборе. Формируется «окно» точно так же, как и в случае агрегатных функций – при помощи группировки. Но, так как MS SQL 2005: оконные функции - реферат итог работы функций ранжирования находится в зависимости от порядка обработки записей, то непременно должен быть указан порядок записей снутри «окна» средством конструкции ORDER BY. Зависимо от применяемой функции некие записи могут получать один и тот же ранг. Функции ранжирования являются не детерминированными, другими словами при одних и тех MS SQL 2005: оконные функции - реферат же входных значениях они могут возвращать различный итог.

Сейчас имееется 4 функции ранжирования, разглядим их по порядку:

ROW_NUMBER()

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

RANK()

Эта функция создана для ранжирования записей снутри «окна», но опять-таки, если колонка для группировки не задана очевидным образом, то за «окно» принимается вся MS SQL 2005: оконные функции - реферат подборка. Рангом каждой записи является количество уже ранжированных записей с более высочайшим рангом, чем текущая, плюс единица. Если повстречаются несколько записей с схожим значением, по которому делается ранжирование, то этим записям будет присвоен однообразный ранг. Но при всем этом последующая запись с новым значением получит таковой ранг, будто MS SQL 2005: оконные функции - реферат бы бы прошлые записи получили собственный уникальный номер, другими словами появляется дырка.

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

DENSE_RANK()

Эта функция делает «плотное» ранжирование, другими словами делает ровно то же самое, что и предшествующая, но без «дырок MS SQL 2005: оконные функции - реферат» в нумерации.

NTILE()

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

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

SELECT ID_Customer, Amount,

ROW_NUMBER() OVER(PARTITION BY ID_Customer ORDER BY Amount DESC) N_Row MS SQL 2005: оконные функции - реферат,

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

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

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

Так как сейчас появилась возможность нумеровать записи в выборке, можно пользоваться этим для постраничной выдачи результата MS SQL 2005: оконные функции - реферат. Запрос будет смотреться приблизительно так:

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 MS SQL 2005: оконные функции - реферат + ' *

FROM sysobjects ORDER BY name ASC

) SO1

ORDER BY name DESC) SO2

ORDER BY name')

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

Очередной пример, где внедрение аналитических функций может быть и комфортным, и действенным. Часто требуется вывести, к примеру, два самых больших заказа для каждого MS SQL 2005: оконные функции - реферат клиента. Может случиться так, что заказов с наибольшей суммой окажется больше 2-ух. Для варианта, когда заказов должно быть конкретно два, запрос может смотреться так:

WITH Ranked as

(

SELECT *,

Row_Number() OVER (PARTITION BY ID_Customer

ORDER BY amount DESC) [rank]

FROM sample

)

SELECT * FROM Ranked

WHERE [rank] < 3

Таковой запрос на этих данных MS SQL 2005: оконные функции - реферат приблизительно в 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

)

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

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

Операция Цена Количество

----------------------------------------------------------

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

|--Sequence Project(...) 0.022866 1

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

Ложка дегтя

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

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

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

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


stat.txt
mozhno-li-ispolzovat-bezglyutenovuyu-dietu-dlya-lecheniya-autizma.html
mozhno-li-iz-nichego-poluchit-nechto.html