max over partition by oracle

добавлено: 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)

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для 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)

В первой сортировке мы просто нумеруем записи. Во второй всем строкам окна присваиваем максимальный номер, который и будет соответствовать количеству строк в окне.

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

Оцените статью
Добавить комментарий

Adblock
detector