Select

Querying partitions

Similar to indexes, partitions can improve query performance by limiting the numbers of rows that a query must scan. In the case of geo-partitioned data, partitioning can limit a query scan to data in a specific region.

Filtering on an indexed column

If you filter the query of a partitioned table on a , the cost-based optimizer creates a query plan that scans each partition in parallel, rather than performing a costly sequential scan of the entire table.

For example, suppose that the tables in the database are geo-partitioned by region, and you want to query the table for information about a specific user.

Here is the statement for the table:

copy
icon/buttons/copy

If you know the user’s id, you can filter on the column:

copy
icon/buttons/copy

An statement shows more detail about the cost-based optimizer’s plan:

copy
icon/buttons/copy

Because the column is in the primary index, directly after the partition prefix (), the optimal query is constrained by the partitioned values. This means the query scans each partition in parallel for the unique value.

If you know the set of all possible partitioned values, adding a check constraint to the table’s create statement can also improve performance. For example:

copy
icon/buttons/copy

copy
icon/buttons/copy

To see the performance improvement over a query that performs a full table scan, compare these queries to a query with a filter on a column that is not in the index.

Filtering on a non-indexed column

Suppose that you want to query the table for information about a specific user, but you only know the user’s name.

copy
icon/buttons/copy

copy
icon/buttons/copy

The query returns the same result, but because is not an indexed column, the query performs a full table scan that spans across all partition values.

Filtering on an partitioned column

If you know which partition contains the data that you are querying, using a filter (e.g. a ) on the column that is used for the partition can further improve performance by limiting the scan to the specific partition(s) that contain the data that you are querying.

Now suppose that you know the user’s name and location. You can query the table with a filter on the user’s name and city:

copy
icon/buttons/copy

The table returns the same results as before, but at a much lower cost, as the query scan now spans just the partition value.

Examples

Define a list partition on a table or secondary index

Suppose we have a table called , and secondary index on the table called , in a global online learning portal, and the primary key of the table is defined as . We can define partitions on the table and index by list:

Define a range partition on a table or secondary index

Suppose we have another table called , also with a secondary index called , and the primary key of the table is defined as . We can define partitions on the table and index by range:

Define subpartitions on a table or secondary index

Suppose we have an yet another table named , again with a secondary index called , and the primary key is defined as . We can define partitions and subpartitions on the table and index:

Ключевое слово DISTINCT в функциях агрегирования

SQL Server 2012 не поддерживает параметр DISTINCT в оконных функциях агрегирования. Представьте, что вам нужно запрашивать представление Sales.OrderValues и получить для каждого заказа число конкретных клиентов, с которыми работал текущий сотрудник с начала и до текущей даты. Вам нужно выполнить такой запрос:

Но поскольку этот запрос не поддерживается, нужно искать обходное решение. Один из вариантов — прибегнуть к помощи функции ROW_NUMBER. Я расскажу о ней подробнее чуть попозже, а пока достаточно будет сказать, что она возвращает уникальное целое значение для каждой строки секции, начиная с единицы и с шагом 1, в соответствии с определением упорядочения в окне. С помощью функции ROW_NUMBER можно назначить строкам номера, секционированные по empid и custid и упорядоченные по orderdate. Это означает, что строки с номером 1 относятся к первому случаю работы сотрудника с данным клиентом при упорядочении заказов по датам. Используя выражение CASE можно вернуть значение custid, только если номер строки равен 1, а в противном случае вернуть NULL. Вот запрос, реализующий описанную логику, с результатом его работы:

Заметьте, что для каждого сотрудника возвращается только первое значение custid при условии упорядочения по дате, а для последующих значений возвращаются NULL. Следующий шаг заключается в определении обобщенного табличного значения (CTE) на основе предыдущего запроса, а затем применении агрегирования текущего числа строк к результату выражения CASE:

Реверс инжиниринг

Посмотрим на определения функций Блэкмана и Наттела:

Они представляют из себя суммы из 3-х и 4-х косинусоид с чётными частотами (начиная с нуля) и некоторыми коэффициентами. Откуда взялись эти коэффициенты? Вряд ли они были подобраны вручную, по крайней мере, каждый по отдельности — ведь существуют граничные условия, которым окна должны соответствовать вне зависимости от их спектральных свойств — как минимум, равенство единице в центре.

Попробуем «изобрести» функцию Блэкмана самостоятельно. Для этого определим функцию с пока ещё неизвестными коэффициентами

и составим систему уравнений, определяющих граничные условия — равенства единице в центре, нулю на краях, и равенства нулю производных на краях:

Решение нашлось, но не одно, а множество — в зависимости от коэффициента a, о чём Wolfram нас учтиво предупредил. Теперь из найденного решения зададим новую функцию, зависящую от неизвестного коэффициента:

Легко видеть, что при a=0.42 получим функцию Блэкмана. Но почему именно 0.42?

Для ответа на этот вопрос нам нужно построить её спектр. Аналитическое преобразование Фурье — не самая простая задача, но Wolfram справляется и с ней, избавляя нас от рутинной работы.

В таком виде график функции пока ещё мало информативен, поскольку спектр удобнее анализировать в логарифмическом масштабе. Добавив соответствующее преобразование в децибелы , получим тот самый привычный график спектра оконных функций:

В процессе изменения параметра мы будем наблюдать нечто подобное:

В зависимости от параметра a меняется уровень боковых лепестков, и при a=0.42 он боле-менее минимальный и равномерный. При а=0.409 мы можем получить результат чуточку лучше, если под «лучше» понимать минимально возможный уровень боковых лепестков.

В обход ограничений

Я объяснил, почему запретили использование оконных функций на этапах логической обработки запроса, предшествующих предложению SELECT. Но что, если нужно выполнять фильтрацию или группировку на основе вычислений, выполненных в оконных функциях? Решение заключается в использовании табличного выражения, такого как CTE или производная таблица. Заставьте запрос вызывать оконную функцию в его списке SELECT, назначив выражению псевдоним. Определите на основе этого запроса табличное выражение, после чего сошлитесь на него в запросе по псевдониму.

Вот пример, демонстрирующий, как можно фильтровать на основе результатов оконной функции с использованием CTE:

В инструкциях, изменяющих данные, оконные функции полностью запрещены, потому что в этих инструкциях не поддерживаются предложения SELECT и ORDER BY. Но есть случаи, когда оконные функции нужны в изменяющих данные инструкциях. Табличные выражения позволяют решить и эту проблему, потому что T-SQL позволяет менять данные через табличные выражения. Продемонстрирую это поведение на примере UPDATE. Сначала выполните следующий код, чтобы создать таблицу T1 со столбцами col1 и col2 и наполнить ее данными:

Значения столбца col2 определены явно, a col1 был заполнен значениями NULL.

Представьте, что эта таблица иллюстрирует ситуацию с проблемами с качеством данных. В этой таблице не создан ключ, поэтому невозможно уникально идентифицировать строки. Вы хотите назначить уникальные значения в столбце col1 для всех строк. Вы подумали, что удобно было бы использовать функцию ROW_NUMBER в инструкции UPDATE следующим образом:

Но, как вы помните, в такой инструкции это запрещено. Обходной способ заключается в создании запроса по отношению к T1, который возвращает col1, и выражения, основанного на функции ROW_NUMBER (назовем ее rownum); определите табличное выражение, основанное на этом запросе, и, наконец, примените инструкцию UPDATE к CTE для присвоения значения rownum столбцу col1:

Получите данные из T1 — вы увидите, что все строки получили уникальное значение в столбце col1:

Упорядочение

Элемент упорядочения определяет упорядочения вычисления в секции, если это необходимо. В стандартном SQL все функции поддерживают элемент упорядочения. Что касается SQL Server, то поначалу он не поддерживал элемент упорядочения в агрегатных функциях — поддерживалось только секционирование. Поддержка упорядочения в агрегатах появилась в SQL Server 2012.

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

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

Этот рисунок представляет окна только трех операций вычисления ранга. Ясно, что их много больше — 1660, если быть совсем точным. Дело в том, что задействовано 830 строк, и в каждой строке выполняются две операции вычисления ранга. Самое интересное то, что концептуально это выглядит так, как если бы все эти окна сосуществовали одновременно.

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

Пользовательские агрегатные и оконные функции в PostgreSQL и Oracle +39

  • 13.03.18 09:46


erogov

#351008

Хабрахабр

4300

PostgreSQL, Oracle, SQL, Блог компании Postgres Professional

В этой статье мы посмотрим, как в двух системах создавать пользовательские агрегатные и оконные (в терминологии Oracle — аналитические) функции. Несмотря на различия в синтаксисе и в целом в подходе к расширяемости, механизм этих функций очень похож. Но и различия тоже имеются.
Надо признать, что собственные агрегатные и оконные функции встречается довольно редко. Оконные функции вообще по каким-то причинам традиционно относят к разряду «продвинутого» SQL и считают сложными для понимания и освоения. Тут бы разобраться с теми функциями, которые уже имеются в СУБД!
Зачем тогда вообще вникать в этот вопрос? Могу назвать несколько причин:

  • Хотя оконные функции объективно сложнее обычных агрегатных, но ничего запредельного в них нет; это абсолютно необходимый инструмент для SQL-разработчика. А создание собственной оконной функции, даже совсем простой, позволяет лучше разобраться с тем, как работают стандартные.
  • Оконные и агрегатные функции — прекрасный способ совместить процедурную обработку с декларативной логикой. В некоторых ситуациях получается выполнить сложные действия, оставаясь в рамках парадигмы решения задачи одним SQL-запросом.
  • Да и просто интересная тема, а уж тем более интересно сравнить две системы.

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

  • Состояние (контекст),
  • Функция обработки очередной строки,
  • Функция выдачи итогового результата,
  • Указание на то, что предыдущие три пункта составляют агрегатную функцию.

PostgreSQL

  • Состояние было установлено в (0,0),
  • Функция average_transition последовательно вызывалась пять раз, постепенно изменяя состояние,
  • В конце была вызвана функция average_final, которая и получила 3 = 15/5.

Oracle

Оконные функции: OVER()

рамки,

1.      2.      3.      4.      5.
+---+   +---+   +---+   +---+   +---+
| 1 |   | 1 |   | 1 |   | 1 |   | 1 |
| 2 |   | 2 |   | 2 |   | 2 |   | 2 |
| 3 |   | 3 |   | 3 |   | 3 |   | 3 |
| 4 |   | 4 |   | 4 |   | 4 |   | 4 |
| 5 |   | 5 |   | 5 |   | 5 |   | 5 |
+---+   +---+   +---+   +---+   +---+

PostgreSQL

Oracle

Оконные функции: OVER(PARTITION BY)

1.      2.      3.      4.      5.
+---+   +---+
| 1 |   | 1 |
| 2 |   | 2 |   +---+   +---+   +---+
+---+   +---+   | 3 |   | 3 |   | 3 |
                | 4 |   | 4 |   | 4 |
                | 5 |   | 5 |   | 5 |
                +---+   +---+   +---+

PostgreSQL

Oracle

Оконные функции: OVER(ORDER BY)

нарастаниянарастающим итогом

1.      2.      3.      4.      5.
+---+   +---+   +---+   +---+   +---+
| 1 |   | 1 |   | 1 |   | 1 |   | 1 |
+---+   | 2 |   | 2 |   | 2 |   | 2 |
        +---+   | 3 |   | 3 |   | 3 |
                +---+   | 4 |   | 4 |
                        +---+   | 5 |
                                +---+

PostgreSQL

Oracle

Оконные функции: OVER(PARTITION BY ORDER BY)

1.      2.      3.      4.      5.
+---+   +---+
| 1 |   | 1 |
+---+   | 2 |   +---+   +---+   +---+
        +---+   | 3 |   | 3 |   | 3 |
                +---+   | 4 |   | 4 |
                        +---+   | 5 |
                                +---+

PostgreSQL

Oracle

Оконные функции со скользящей рамкой

скользящего среднего.

1.      2.      3.      4.      5.
+---+
|   |   +---+
|   |   |   |   +---+
| 1 |   | 1 |   | 1 |   +---+
+---+   | 2 |   | 2 |   | 2 |   +---+
        +---+   | 3 |   | 3 |   | 3 |
                +---+   | 4 |   | 4 |
                        +---+   | 5 |
                                +---+

PostgreSQL

добавленияудаления

Oracle

Параллельность

объединения,

PostgreSQL

  • два запланированных рабочих процесса, выполняющих частичное агрегирование (Partial Aggregate),
  • узел Gather, собирающий информацию,
  • и итоговое объединение состояний (Finalize Aggregate).

Oracle

  • частичную агрегацию (4),
  • координатора, получающего частичные контексты (2),
  • и итоговое объединение контекстов (1).

Документация

  • Пользовательские агрегатные функции
  • CREATE AGGREGATE
  • Стандартные агрегатные и оконные функции
  • Стандартные и функции

Предложения, поддерживающие оконные функции

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

Я попытаюсь продемонстрировать эту неоднозначность на примере. Сначала выполните следующий код, чтобы создать таблицу T1 и наполнить ее данными:

Допустим, что оконные функции разрешены на этапах, предшествующих SELECT, например на этапе WHERE. Посмотрите на следующий запрос и попытайтесь определить, какие значения col1 должны содержаться в результате:

Прежде чем говорить, что это очевидно, что это должны быть значения C, D и Е, вспомните о принципе «все сразу» в SQL. Этот принцип подразумевает, что с точки зрения концепции все выражения одного логического этапа выполняются одновременно. Это значит, что порядок следования выражений не должен влиять на результат. Если так, то следующий запрос должен быть семантически эквивалентен такому:

Сможете ли вы на этот раз определить, какое выражение правильное? Это C, D и Е или только C?

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

При анализе блок-схемы на рисунке выше вы могли заметить, что на этапе SELECT оконные функции поддерживает шаг 5-1 (Вычисление выражений) и он выполняет перед шагом 5-2 (Удаление дубликатов)

Если вы спросите, почему так важно знать такие детали, я продемонстрирую, зачем это нужно

Вот вопрос, возвращающий атрибуты empid и country всех сотрудников из таблицы сотрудников Employees:

А теперь посмотрите на следующий запрос и попытайтесь до выполнения запроса определить, каким будет результат:

Некоторые будут ожидать такой результат:

Но на самом деле вы получите это:

А теперь вспомните, что в этом запросе функция ROW_NUMBER вычисляется на шаге 5-1, на котором вычисляются выражения списка SELECT — до удаления дубликатов на шаге 5-2. Функция ROW_NUMBER назначает девять уникальных номеров строк, содержащих информацию о сотрудниках, поэтому предложению DISTINCT нечего удалять.

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

Можете ли вы представить себе другие способы решения задачи, по крайней мере проще, чем это?

Тот факт, что оконные функции оцениваются на этапе SELECT или ORDER BY означает, что окно, определенное для вычисления, — до применения последующих ограничений — является промежуточной формой строк, полученной после всех предшествующих фаз, то есть после применения FROM со всеми табличными операторами (например, соединениями), а также фильтрации с применением WHERE, группировки и фильтрации групп. Такой запрос можно считать примером:

Сначала вычисляется предложение FROM, после чего выполняется соединение. Затем фильтр оставляет только строки, относящиеся к 2007 году. После этого оставшиеся строки группируются по идентификатору сотрудника. Только после этого вычисляются выражения в списке SELECT, в числе которых функция RANK, которая вычисляется с использование упорядочения по убыванию общего количества. Если бы в списке SELECT были другие оконные функции, в них в качестве исходной точки использовался этот же набор результатов.

Вспомните, что ранее при обсуждении альтернатив оконным функциям (например, вложенных запросов) мы говорили, что они начинают просмотр данных с нуля, то есть нужно повторять всю логику внешнего запроса в каждом вложенном запросе, что сильно увеличивает объем кода.

Типы оконных функций

Основная статья: Окно (весовая функция)

Прямоугольное окно

Прямоугольное окно; B=1.00

w(n)={1,n∈,N−1,n∉,N−1{\displaystyle w(n)=\left\{{\begin{matrix}1,&n\in \\0,&n\notin \\\end{matrix}}\right.}

Получается автоматически при ограничении выборки N отсчетами. Максимальный уровень боковых лепестков частотной характеристики: -13 дБ.

Окно Ханна (Хеннинга)

Окно Ханна; B = 1.50

w(n)=0.5(1−cos⁡(2πnN−1)){\displaystyle w(n)=0.5\;\left(1-\cos \left({\frac {2\pi n}{N-1}}\right)\right)}

где N — ширина окна. Уровень боковых лепестков: -31.5 дБ.

Окно Хэмминга

Окно Хэмминга

w(n)=0.53836−0.46164cos⁡(2πnN−1){\displaystyle w(n)=0.53836-0.46164\;\cos \left({\frac {2\pi n}{N-1}}\right)}

Уровень боковых лепестков: -42 дБ.

Окно Блэкмана

Окно Блэкмана; α = 0.16; B=1.73

w(n)=a−a1cos⁡(2πnN−1)+a2cos⁡(4πnN−1){\displaystyle w(n)=a_{0}-a_{1}\cos \left({\frac {2\pi n}{N-1}}\right)+a_{2}\cos \left({\frac {4\pi n}{N-1}}\right)}
a=1−α2;a1=12;a2=α2{\displaystyle a_{0}={\frac {1-\alpha }{2}};\quad a_{1}={\frac {1}{2}};\quad a_{2}={\frac {\alpha }{2}}}

Уровень боковых лепестков: -58 дБ (α=0.16).

Окно Кайзера

Окно Кайзера, α =2; B=1.5

Окно Кайзера, α =3; B=1.8

w(n)=|I(β1−(2n−N+1N−1)2)||I(β)|{\displaystyle w(n)={\frac {|I_{0}\left(\beta {\sqrt {1-\left({\frac {2n-N+1}{N-1}}\right)^{2}}}\right)|}{|I_{0}(\beta )|}}}

где I{\displaystyle I_{0}} — модифицированная функция Бесселя первого рода нулевого порядка; β{\displaystyle \beta } — коэффициент определяющий долю энергии, сосредоточенной в главном лепестке спектра оконной функции. Чем больше β{\displaystyle \beta } тем больше доля энергии, и шире главный лепесток, и меньше уровень боковых лепестков. На практике используются значения от 4 до 9.

Кадрирование

Кадрирование по сути является еще одним фильтром, а не ограничителем строк в секции. Оно применяется как к агрегатным функциям, так и к трем функциям сдвига: FIRST_VALUE, LAST_VALUE и NTH_VALUE. Этот элемент поддержки окон можно считать определяющим две точки секции текущей строки на основе данного упорядочения, обеспечивающий кадрирование строк, к которым будет применяться данное вычисление.

К параметрам кадрирования в стандарте относится ROWS или RANGE, который определяет начальный и конечный строки кадра, а также параметр исключения оконного кадра. В SQL Server 2012 появилась поддержка кадрирования с полной реализацией параметра ROWS и частичной — параметра RANGE, но реализация исключения кадров из окна отсутствует.

Параметр ROWS позволяет задать две точки в кадре как смещение (число строк) относительно текущей строки. Параметр RANGE более динамичен и определяет смещения как разницу между значением точки кадра и значением в текущей строке.

Параметр исключения кадров из окна определяет, что делать с текущей строкой и ее «братьями» при наличии связей. Это объяснение кажется туманным и недостаточным, но пока я не хочу углубляться в детали. Далее этих подробностей будет достаточно. На данный момент я просто хочу сформулировать принцип и предоставить простой пример. Вот пример запроса представления EmpOrders, в котором вычисляется количество нарастающим итогом для каждого сотрудника по каждому месяцу:

Заметьте, что в оконной функции применяется агрегат SUM по атрибуту qty, окно секционируется по empid, строки секции упорядочиваются по ordermonth, а затем секция кадрируется от неограниченной предшествующей (нет ограничений снизу) и до текущей строки. Иначе говоря, результат содержит сумму всех предыдущих строк в кадре, включая текущую строку. Этот запрос дает следующий результат (сокращено):

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector