добавлено: 15 апр 15
понравилось:0
просмотров: 98549
комментов: 0
Применяется в основном для отчетности , для следующих типов задач
1. Запросы рейтингов, первых N
2. Запросы с накопительным итогом
3. Запросы с конструкцией окна
4. Может применятся и для оптимизации запросов
Какие бывают функции в аналитическом, разберём основные
ROW_NUMBER() – номер строки в группе
LAG(f, n,m) –f имя поля, n предыдущее значение в группе, m – знач по умолчанию
LEAD(f, n,m) -f имя поля, n последующее значение в группе, m – знач по умолчанию
FIRST_VALUE(f) – f имя поля, первое значение в группе ,
LAST_VALUE(f) –f имя поля, последнее значение в группе
STD_DEV(f) – f имя поля, значение стандартного распределения в группе
SUM(f) – f имя поля, накопительная сумма по группе
AVG (f)– f имя поля, среднее по группе заданной групп
RANK(f) – f имя поля, относительный ранг записи в группе
Где Партишен — это некоторое количество записей с общими ключами на которую будет распространятся действие Аналитической функции , сортировка внутри партишена осуществляется с помощью order by
Проще всего разобраться с аналитическими функциями на примерах.
Подготовим необходимые данные для демонстрации
Запросы списка лидеров
Первые три сотрудника с самой высокой зарплатой по отделам (партишен по отделу)
Более корректно
Внимание ! Пример демонстрирует отличие rank() от row_number()
По наименованию (партишен по отделу) сортировка по name
Накопительный итог по зарплате
Среднее по зарплате в рамках отдела
Демонстрация работы lag, leed — сотрудник , отдел , зарплата , сотрудник с более большей заплатой (maxsl),
, сотрудник с менее меньшей заплатой чем данный(minsl) в рамках отдела
запрос демонстрирует конструкцию окна в рамках отдела , среднее по зарплате, вычисляется, не только в рамках отдела , но так же и в рамках окна из 3х строк
в запросе используется конструкция окна ROWS BETWEEN N PRECEDING AND CURRENT ROW
то есть, для вычисления среднего avg(sal), считаются 3 предыдущие строки перед текущей строкой
этот запрос демонстрирует применение аналитических функций first_value last_value
Также про использование предложения Partition by можно почитать в записи о сортировке по одному полю в таблице из нескольких столбцов.
Запись опубликована 01.06.2011 в 12:55 дп и размещена в рубрике Книга SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)
|
Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC
Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.
Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC.
5) COUNT(*) OVER(PARTITION BY group_id)
В первой сортировке мы просто нумеруем записи. Во второй всем строкам окна присваиваем максимальный номер, который и будет соответствовать количеству строк в окне.
|
Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:
6) MAX(value) OVER(PARTITION BY group_id)
|
7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)
Интересная вещь, которая отсутствует в MS 
Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:
