Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Топовые новости
Новое

Секционирование


Секционирование строк

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

Следующий запрос иллюстрирует результат секционирования по столбцу ODD. Сумма четных чисел равна 2 (0 + 2), а сумма нечетных -4(1 + 3). Во втором столбце результирующего набора выводится сумма всех значений раздела, к которому принадлежит данная строка. Этот запрос выдает суммарные результаты по каждой строке.

Без использования секционирования запрос просуммирует все числа в столбце NUM для каждой строки, ‘возвращаемой запросом. В итоге весь результирующий набор будет считаться одним большим разделом.


Сортировка

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

Как можно видеть, предложения ORDER BY ь вызовах оконной функции влияют на сортировку строк в соответствующих разделах при вычислении значений функции. Так, предложение ORDER BY NUM ASC сортирует разделы по возрастанию и 0 становится первым значением в разделе с четными числами, а 1 — первым значением в разделе с нечетными числами. Предложение ORDER BY NUM DESC приводит к противоположному результату.

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


Группировка и обработка методом окна

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

В следующем примере для Oracle предложение кадрирование используется в аналитическом варианте функции SUM для вычисления промежуточной суммы значений в первом столбце. Предложение секционирование не используется, так что каждый вызов функции SUM работает со всем результирующим набором. Однако предложение ORDER В Y сортирует строки для функции SUM по возрастанию значений в столбце NUM, а предложение BETWEEN (которое является оконным предложением) приводит к тому, что в каждый вызов функции SUM включаются только значения до текущей строки. Каждый следующий вызов функции SUM включает новое значение из столбца NUM, в порядке от наименьшего значения NUM к наибольшему.

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


Список оконных функций

В стандарте SQL 2003 определяется, что любая агрегатная функция может использоваться в качестве оконной функции. Платформы Oracle и DB2 в этом отношении в довольно большой степени соответствуют стандарту, так что вы можете взять почти любую агрегатную функцию (и особенно стандартную) и применить в ней оконный синтаксис, описанный в предыдущих разделах.

Помимо агрегатных функций в стандарте SQL 2003 определяются оконные функции, описываемые в следующих разделах. В настоящее время эти функции реализованы только в Oracle и DB2. Во всех примерах используется следующая таблица и данные.

Платформо-специфические функции Oracle (в DB2 таких нет) включены в список в разделе «Платформо-специфические расширения» ниже в этой главе.

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

Количество совпадающих или предшествующих строк / Количество строк в разделе

Поскольку результат для данной строки зависит от количества строк, предшествующих данной строке в разделе, нужно всегда указывать при вызове этой функции предложение ORDER BY.

Синтаксис SOL2003

CUME_DIST() OVER {имя_окна (спецификация_окна)}.

DB2

Платформа DB2 не поддерживает оконную функцию CUME_DIST().

Oracle

Платформа Oracle не позволяет использовать в окопном синтаксисе предложение кадрирование. Обязательным является использование предложения сортировка.

CUME_DIST() OVER {[секционирование] сортировка)

Пример:

В следующем примере для Oracle функция CUMEJDISTQ используется для генерации относительного ранга для каждой строки и сортировки по столбцу NUM после секционирования по столбцу ODD.

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

Ниже приводится объяснение вычислений ранга строки, в которой значение NUM равно О.

  • В соответствии с предложением ORDER BY строки раздела сортируются следующим
  • Перед строкой NUM=0 нет других строк.
  • Есть только одна строка, с которой совпадает строка NUM=0, — сама строка NUM=0. Поэтому делитель будет равен 1.
  • Всего в разделе три строки, поэтому делимое будет равно 3.
  • Результатом деления 1/3 будет 0.33333 (в периоде), и это видно в выходных данных примера.
  • Ранг присваивается всем строкам раздела, который нужно отсортировать в определенном порядке. Ранг данной строки вычисляется путем подсчета числа строк, предшествующих данной строке, после чего к результату прибавляется 1. Строки со значениями, которые в предложении ORDER BY дублируются, будут иметь одинаковый ранг. В отличие от функции RANKQ две строки с одинаковым рангом не будут приводить к появлению пробелов в нумерации рангов.


    DENSE RANK

    Синтаксис SQL 2003

    DENSE_RANK() OVER {имя_окна |(спецификация_окна)}.

    DB2

    Платформа DB2 требует наличия предложения сортировка и не позволяет использовать предложение кадрирование.

    DENSE_RANK() OVER ([секционирование] сортировка).

    Oracle

    Платформа Oracle также требует наличия предложения сортировка и не позволяет использовать предложение кадрирование.

    DENSE_RANK() OVER ([секционирование] сортировка).

    Пример:

    Сравните результаты следующего примера для Oracle с тем, который приведен в разделе, посвященном функции RANKQ.

    Две строки, где значение NUM=3, обе получают ранг 3. Следующая по возрастанию строка получает ранг 4. Номера рангов не пропускаются, отсюда и термин «dense» (плотный).

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

    Синтаксис SQL 2003

    RANK() OVER {имя_окна (спецификация_окна)}

    DB2

    Платформа DB2 требует наличия предложения сортировка и не позволяет использовать предложение кадрирование.

    RANK() OVER ([секционирование] сортировка).

    Oracle

    Платформа Oracle также требует наличия предложения сортировка и не позволяет использовать предложение кадрирование.

    RANK() OVER ([секционирование] сортировка).

    Пример:

    В следующем примере для Oracle столбец NUM используется для ранжирования строк в таблице test4.

    Поскольку обе строки, где NUM=3, получают ранг 4, то следующая по возрастанию строка получает ранг 6. Ранг 5 пропускается.


    PERCENT_RANK

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

    (ранг — 1) / (число строк — 1)

    Сравните это вычисление с тем, которое используется в функции CUME_DIST().

    Синтаксис SQL 2003

    PERCENT_RANK() OVER {имя_окна |(спецификация_окна)}

    DB2

    Платформа DB2 не поддерживает оконную функцию PERCENT_RANK().

    Oracle

    Платформа Oracle также требует наличия предложения сортировка и не позволяет использовать предложение кадрирование.

    PERCENT_RANK() OVER {[секционирование] сортировка)

    Пример:

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

    SELECT
    NUM,
    ODD,
    PERCENT_RANK()
    OVER (PARTITION BY ODD ORDER BY NUM) cumedist
    FROM test4;

    Ниже приводится объяснение вычисления ранга строки со значением NUM=2.

  • Строка NUM=2 является второй строкой раздела, следовательно ее, ранг будет равен 2.
  • Вычитаем 1 из 2 и получаем делитель 1.
  • Для делимого возьмем общее число строк раздела, то есть 3.
  • Вычтем 1 из 3 и получим делимое 2.
  • Результат 1/2=0.5, что и видно на примере.
  • Источник