Реферат: MS SQL 2005: оконные функции
где <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 К-во Просмотров: 283
Бесплатно скачать Реферат: MS SQL 2005: оконные функции
|