Расширенные запросы

В этом документе описываются функции и выражения, предоставляемые YMatrix для выполнения запросов к данным.

Помимо базовых операторов запросов, YMatrix предоставляет множество расширенных аналитических функций и выражений SQL.

1 Оконные функции

Оконные функции поддерживают сложные операции отчетности и анализа, часто используемые в OLAP и сценариях временных рядов.

«Окно» определяет набор строк, связанных с текущей строкой. Ключевое различие между оконными функциями и другими функциями — наличие предложения OVER. Если функция содержит предложение OVER, она становится оконной функцией. Это предложение задает окно, над которым выполняется функция.

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

	Агрегирующие оконные функции	Специализированные оконные функции
Результат	Одна строка на каждую входную строку	Несколько строк
Функции	`max()`, `min()`, `count()`, `sum()`, `avg()`, `sum()` и др.	Помимо стандартных агрегатов, специализированные функции, такие как `row_number()`, `rank()`, `dense_rank()`, `first_value()`, `last_value()` и др.
Использование	Обычно используются с `GROUP BY`; могут также использоваться как оконные функции при сочетании с `OVER`	Используются с `OVER`. Обычно предложение `OVER` следует сразу после имени функции и аргументов. Внутри `OVER` можно использовать `PARTITION BY`, `ORDER BY` или `ROWS BETWEEN`. Использование только `PARTITION BY`, либо комбинация `PARTITION BY` и `ORDER BY`, формирует статическое окно (фиксированного размера и положения). При использовании `PARTITION BY` вместе с `ROWS BETWEEN`, либо обоих `ROWS BETWEEN` и `ORDER BY`, образуется скользящее окно — применяется окно фиксированного размера последовательно ко всем входным строкам.

1.1 Синтаксис

Как и агрегатные функции, оконные функции появляются в предложении SELECT выражения таблицы:

=# SELECT 
    <function_name(arguments)> OVER (
        PARTITION BY …
        ORDER BY …
    )
   FROM …;

Предложение OVER определяет область действия окна:

Список PARTITION BY разделяет строки на секции; каждая секция действует как отдельное окно. Если он опущен, все строки принадлежат одной секции.
Предложение ORDER BY определяет порядок строк внутри секции.

1.2 Специализированные оконные функции

YMatrix поддерживает следующие специализированные оконные функции:

Функция	Описание
row_number()	Присваивает каждой строке в секции результата уникальный последовательный целочисленный номер, начиная с 1
rank()	Присваивает каждой строке в секции значение ранга. Связанные значения получают одинаковый ранг, а последующие ранги увеличиваются соответствующим образом. Ранги могут быть не последовательными при наличии совпадений
dense_rank()	Присваивает каждой строке в секции значение ранга. Связанные значения получают одинаковый ранг, но следующий ранг продолжается без пропусков. Таким образом, ранги остаются последовательными даже при совпадениях
first_value()	Возвращает первое значение в упорядоченной секции
last_value()	Возвращает последнее значение в упорядоченной секции
ntile()	Делит упорядоченную секцию на n групп (ведер) равного размера
cume_dist()	Вычисляет долю строк в текущей секции со значениями, большими или равными значению текущей строки (включая саму строку)
percent_rank()	Вычисляет относительный ранг строки: долю строк в секции (исключая текущую строку), значения которых выше значения текущей строки
lag(,)	Получает значение столбца из указанного числа строк до текущей строки
lead(,)	Получает значение столбца из указанного числа строк после текущей строки

1.2.1 Функции статистики распределения

Синтаксис:

=# SELECT …,
    ntile(<n>) OVER (ORDER BY …),
    cume_dist() OVER (ORDER BY …),
    percent_rank() OVER (ORDER BY …)  
FROM …;

Примечание!
Функции cume_dist() и percent_rank() отличаются как числителем, так и знаменателем:

Знаменатель cume_dist() — общее количество строк в текущей секции, тогда как знаменатель percent_rank() — общее количество строк в текущей секции минус один.

Числитель cume_dist() — количество строк со значениями, большими или равными значению текущей строки, плюс один (включает саму текущую строку), тогда как числитель percent_rank() — количество строк со значениями, превышающими значение текущей строки (не включает текущую строку).

1.2.2 Функции вычисления смещения

lag() и lead() используются для доступа к данным из предыдущих или будущих строк. Они часто применяются при сравнении текущих данных с историческими.

Синтаксис:

=# SELECT 
          lag(<offset_column>,<offset_rows>) OVER (ORDER BY …) AS …,
          lead(<offset_column>,<offset_rows>) OVER (ORDER BY …) AS …
    FROM …;

1.3 Агрегирующие оконные функции

YMatrix поддерживает, но не ограничивается следующими агрегатными функциями:

Функция	Описание
count(/*)	Подсчитывает ненулевые значения (`count(*)` включает NULL)
sum()	Сумма значений
avg()	Среднее значение
min()	Минимальное значение
max()	Максимальное значение

Использование агрегатных функций с OVER отличается от их использования с GROUP BY:

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

1.4 Оконная рамка / Скользящее окно

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

Следует за предложением ORDER BY. Полный синтаксис:

=# <aggregate_function(…)> OVER (… ORDER BY … ROWS|RANGE {
          UNBOUNDED PRECEDING
        | n PRECEDING
        | CURRENT ROW
        | BETWEEN <frame_boundary> AND <frame_boundary> })

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

Граница рамки	Описание
UNBOUNDED PRECEDING	Первая строка секции
n PRECEDING	n строк до текущей строки
CURRENT ROW	Текущая строка
n FOLLOWING	n строк после текущей строки
UNBOUNDED FOLLOWING	Последняя строка секции

Примечание!
Примеры использования оконных функций см. в разделе Типичные запросы в OLAP-сценариях.

2 Агрегатные функции упорядоченного множества

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

Функция	Описание	Синтаксис
mode()	Возвращает наиболее частое входное значение (первое, если несколько значений имеют одинаковую частоту)	`mode() WITHIN GROUP (ORDER BY …)`
percentile_cont()	Вычисляет непрерывные перцентили: ࠾ Единичный перцентиль: возвращает значение, соответствующее указанной дробной позиции в отсортированном наборе данных. Если точного совпадения нет, используется линейная интерполяция ࠾ Несколько перцентилей: возвращает массив той же формы, что и входной массив дробей, каждый элемент которого заменяется соответствующим значением перцентиля	Один: `percentile_cont(<分数>) WITHIN GROUP (ORDER BY …)` Несколько: `percentile_cont(<分数数组>) WITHIN GROUP (ORDER BY …)`
percentile_disc()	Вычисляет дискретные перцентили: ࠾ Единичный перцентиль: возвращает первое фактическое входное значение, чья позиция в порядке сортировки равна или превышает указанную долю. Интерполяция не выполняется ࠾ Несколько перцентилей: возвращает массив, в котором каждый ненулевой элемент заменяется соответствующим значением входных данных на этом перцентиле	Один: `percentile_disc(<分数>) WITHIN GROUP (ORDER BY …)` Несколько: `percentile_disc(<分数数组>) WITHIN GROUP (ORDER BY …)`

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

Значения NULL во входных данных игнорируются при вычислении. Однако если параметр fraction равен NULL, результат будет NULL.
Доля должна быть допустимым значением между 0 и 1. Указание значения вне этого диапазона вызывает ошибку.
NULL-значение доли приводит к возврату NULL, что указывает на недостаточность информации для вычисления результата.

Примечание!
Примеры использования см. в разделе Непрерывный перцентиль.

3 Функции временных рядов

Примечание!
Для использования этих функций необходимо заранее создать расширение matrixts.

Функция	Описание
time_bucket('', )	Группирует данные временных рядов по фиксированным интервалам для агрегирования
time_bucket_gapfill('', )	Заполняет отсутствующие точки данных для создания гладких распределений временных рядов. Поддерживает две стратегии: `locf` (последнее наблюдение переносится вперед) и `interpolate`
first(, )	Возвращает первое зарегистрированное значение метрики. Примечание: второй параметр не обязательно должен быть временем; помечен как таковой для контекста временных рядов
last(, )	Возвращает последнее зарегистрированное значение метрики. То же примечание
last_not_null_value(, )	Возвращает последнее ненулевое значение указанного столбца
last_not_null(, )	Возвращает последнее ненулевое значение и его отметку времени

3.1 Функция time_bucket()

Эта функция работает с агрегатными функциями для вычисления агрегированных значений по фиксированным временным интервалам.

Параметр	Поддерживаемые типы данных	Поддерживаемые единицы
Интервал	`int16`; `int32`; `int64`; interval	`us` / `ms` / `s` / `minute`(`min`, `minutes`) / `hour` / `day`, например, `1 day`
Столбец времени	`int16`; `int32`; `int64`; `date`; `timestamp`; `timestamptz`

Пример синтаксиса:

=# SELECT time_bucket('<interval>', <timestamp_column>) AS …,
    <aggregate_function> AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;

3.2 Функция time_bucket_gapfill()

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

Параметр	Поддерживаемые типы данных	Поддерживаемые единицы
Интервал	`int16`; `int32`; `int64`; interval	`us` / `ms` / `s` / `minute`(`min`, `minutes`) / `hour` / `day`, например, `1 day`
Столбец времени	`int16`; `int32`; `int64`; `date`; `timestamp`; `timestamptz`
Столбец значений	Исходный тип столбца	Исходная единица

Поддерживаются две стратегии заполнения: locf и interpolate:

locf: Использует последнее зарегистрированное непропущенное значение (LOCF). Сохраняет тренд и возвращает существующие фактические значения.
interpolate: Выполняет линейную интерполяцию между известными точками. Возвращает вычисленные оценки.

Примеры синтаксиса:

Без заполнения пробелов

=# SELECT time_bucket_gapfill('<interval>', <timestamp_column>) AS …,
    <aggregate_function> AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;

Стратегия locf

=# SELECT time_bucket_gapfill('<interval>', <timestamp_column>) AS …,
    locf(<aggregate_function>) AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;

Стратегия interpolate

=# SELECT time_bucket_gapfill('<interval>', <timestamp_column>) AS …,
    interpolate(<aggregate_function>) AS …
    FROM …
    WHERE …
    GROUP BY …
    ORDER BY …;

Примечание!
Примеры использования см. в разделе Пространственно-временные запросы.

4 Общие табличные выражения (CTE)

Общее табличное выражение (CTE) позволяет определить временное представление для упрощения сложных запросов. Реализуется с помощью ключевого слова WITH. В следующей таблице сравниваются CTE и операторы CREATE VIEW:

	CTE	CREATE VIEW
Определение	Не является самостоятельным SQL-оператором; часть более крупного выражения	Самостоятельный SQL-оператор
Область видимости	Временное представление, доступное только внутри обрамляющего запроса	Представление, доступное глобально для всех запросов

Синтаксис:

=# WITH <cte_name> (<column_list>, …) AS (
    SELECT … FROM …
) SELECT … FROM …;

Примечание!
Примеры использования см. в разделе Анализ сравнения метрик.

5 Вложенные запросы

5.1 Размещение подзапросов

Подзапросы могут появляться в различных частях запроса:

В списке SELECT, например:
```
=# SELECT (SELECT …) AS … FROM …;
```

В списке FROM, например:

=# SELECT …
 FROM … AS …, (SELECT … FROM …) AS …
 WHERE …;

Примечание!
Подзапросы в списке FROM должны иметь псевдоним.

В выражениях WHERE, например:

=# SELECT … 
 FROM …
 WHERE … IN (SELECT … FROM … WHERE …);

5.2 Скалярные подзапросы

Когда подзапрос возвращает одно значение, он может появляться в списке SELECT/FROM или в условиях WHERE. Его можно использовать с операторами сравнения, такими как =, <, <=, >, >=, !=.

Предположим, есть таблицы t1 (со столбцом c1) и t2 (со столбцом c2):

=# SELECT (SELECT max(c1) FROM t1), t2.*
   FROM t2
   WHERE c2 > (SELECT avg(c2) FROM t2);

Здесь скалярные агрегатные подзапросы появляются как в списке SELECT, так и в условии WHERE.

Выполнение происходит следующим образом: сначала внутренний запрос SELECT avg(c2) FROM t2 вычисляет среднее значение столбца t2 в таблице c2. Затем внешний запрос фильтрует строки на основе этого среднего значения и других условий c2 > (SELECT avg(c2) FROM t2). Для каждой подходящей строки возвращаются все столбцы из t2 и результат SELECT max(c1) FROM t1.

5.3 Подзапросы, возвращающие набор значений

Если подзапрос возвращает несколько строк, он может выступать во временной таблице в списке FROM и участвовать в соединениях (JOIN). Как и скалярные подзапросы, они также могут использоваться с операторами сравнения.

Предположим, существуют таблицы ttt1 (столбцы c1, c4) и ttt2 (столбцы c2, c3):

=# SELECT t1.c1, t2.c2
   FROM ttt1 AS t1
   INNER JOIN (SELECT c3, c2 FROM ttt2) AS t2
   ON t1.c4 = t2.c3;

Этапы выполнения:

Выполнить подзапрос SELECT c3, c2 FROM ttt2, выбрав столбцы c3 и c2 из таблицы ttt2, в результате получится временная таблица t2.
Выполнить внутреннее соединение таблиц ttt1 и t2.
Вернуть строки, удовлетворяющие условию соединения, с выбором столбцов t1.c1 и t2.c2.

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

ALL: Условие должно выполняться для всех кортежей результата подзапроса.
ANY: Условие выполняется хотя бы для одного кортежа.
IN = ANY, за исключением того, что IN проверяет только принадлежность, тогда как ANY допускает использование операторов сравнения.
EXISTS: Возвращает истину, если подзапрос возвращает хотя бы одну строку.

Предположим, существует таблица t1:

=# CREATE TABLE t1 (
   c1 int,
   c2 text
   );
=# INSERT INTO t1 VALUES (1,'content');
   INSERT INTO t1 VALUES (2,'content');
   INSERT INTO t1 VALUES (3,'content');
   INSERT INTO t1 VALUES (4,'text');
   INSERT INTO t1 VALUES (5,'text');

Пример с ALL

=# SELECT * FROM t1
   WHERE c1 >= ALL (
    SELECT c1 FROM t1
    WHERE c2 = 'content'
    ORDER by c1
   );

Так как подзапрос возвращает (1,2,3), приведённый выше запрос эквивалентен:

=# SELECT * FROM t1
   WHERE c1 >= 1
   AND c1 >= 2
   AND c1 >= 3
   ORDER by c1;
 c1 |   c2
----+---------
  3 | content
  4 | text
  5 | text
(3 rows)

Пример с ANY

=# SELECT * FROM t1
   WHERE c1 >= ANY (
    SELECT c1 FROM t1
    WHERE c2 = 'content'
   );

Эквивалентно:

=# SELECT * FROM t1
   WHERE c1 >= 1
   OR c1 >= 2
   OR c1 >= 3;

Пример с IN

=# SELECT * FROM t1
   WHERE c1 IN (
    SELECT c1 FROM t1
    WHERE c2 = 'content'
   );

Эквивалентно:

=# SELECT * FROM t1
   WHERE c1 = 1
   OR c1 = 2
   OR c1 = 3;

Пример с EXISTS

=# SELECT * FROM t1
   WHERE EXISTS (
    SELECT * FROM t1
    WHERE c1 < 60
    AND c2 = 'content'
   );

Так как подзапрос возвращает непустой результат:

=# SELECT * FROM t1
   WHERE true;

Если результат пустой:

=# SELECT * FROM t1
   WHERE false;

5.4 Коррелированные подзапросы

Коррелированный подзапрос зависит от значений внешнего запроса. Для каждой строки внешнего запроса подзапрос пересчитывается заново.

Процесс выполнения:

Извлечь строку из внешнего запроса и передать соответствующие значения столбцов во внутренний запрос.
Выполнить внутренний запрос и получить его результат.
Оценить условие WHERE на основе результата подзапроса.
Повторить для следующей строки внешнего запроса до тех пор, пока все строки не будут обработаны.

Предположим, существуют таблицы t1 (столбец c1) и t2 (столбец c2):

=# SELECT * FROM t1
   WHERE NOT EXISTS (
    SELECT * FROM t2
    WHERE t2.c2 = t1.c1
   );

Для каждой строки внешней таблицы t1 подзапрос проверяет, существует ли соответствующая строка в t2. Выражение NOT EXISTS определяет, следует ли включать строку t1 в конечный результат.

Примечание!
Примеры использования см. в разделе Аналитика по требованию.

6 Дата-куб

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

Дата-кубы используют модель, основанную на фактах и измерениях:

Измерения — это точки зрения для анализа данных, часто иерархические (например, Продукт, Квартал, Регион).
Факты — детальные транзакционные записи (например, суммы продаж).

6.1 Предложение GROUPING SETS

GROUPING SETS позволяет выполнять несколько группировок в одном запросе. Агрегация происходит после оценки FROM и WHERE, вычисляются результаты для каждой указанной группировки, которые затем объединяются и возвращаются вместе.

Предположим, есть таблица t1 (столбцы c1, c2, c3):

=# SELECT c1, c2, sum(c3) AS sum 
   FROM t1
   GROUP BY GROUPING SETS (c1, c2)
   ORDER BY (c1,c2);
 c1 | c2 | sum
----+----+----
 a  |    | 6
 b  |    | 15
 c  |    | 24
    | aa | 12
    | bb | 15
    | cc | 18
(6 rows)

Результат показывает суммы, сгруппированные по:

c1: Сумма по c3 для a, b, c
c2: Сумма по c3 для aa, bb, cc

Использование GROUPING SETS эквивалентно выполнению нескольких отдельных запросов с UNION ALL, но является более кратким и стандартизированным способом.

6.2 Предложение ROLLUP

Помимо GROUPING SETS, YMatrix предоставляет другие упрощённые методы для указания часто используемых типов группировок.

Результаты агрегации, сгруппированные по предложению ROLLUP, последовательно «сворачиваются» слой за слоем в порядке указания ключей группировки.

На самом деле, ROLLUP ( c1, c2, … ) эквивалентно:

=# GROUPING SETS (
    ( c1, c2, … ),
    …
    ( c1 ),
    ( )
   )

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

Предположим, у нас есть таблица t1 со столбцами c1, c2 и c3:

=# SELECT c1, c2, sum(c3) AS sum 
   FROM t1
   WHERE c1 = 'a' OR c1 = 'b'
   GROUP BY ROLLUP (c1, c2)
   ORDER BY (c1,c2);
 c1 | c2 | sum
----+----+----
 a  |  aa  | 1
 a  |  bb  | 2
 a  |  cc  | 3
 a  |      | 6
 b  |  aa  | 4
 b  |  bb  | 5
 b  |  cc  | 6
 b  |      | 15
    |      | 21
(9 rows)

Из результата видно:

Строки 1–3 и 4–6 показывают сумму, сгруппированную по (c1,c2) (в данном примере — это отдельные значения c3);
Строки 4 и 8 показывают сумму, сгруппированную по (c1) (то есть итоги по строкам 1–3 и 5–7 соответственно);
Строка 9 (последняя) показывает общую сумму по всем возвращённым строкам.

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

6.3 Предложение CUBE

Результаты агрегации, сгруппированные по предложению CUBE, вычисляют сводки для каждой комбинации ключей группировки, затем объединяются с удалением дубликатов.

CUBE ( c1, c2 ) эквивалентно:

=# GROUPING SETS (
    ( c1, c2 ),
    ( c1     ),
    (     c2 ),
    (        )
   )

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

Предположим, у нас есть таблица t1 со столбцами c1, c2 и c3:

=# SELECT c1, c2, sum(c3) AS sum 
   FROM t1
   WHERE c1 = 'a' OR c1 = 'b'
   GROUP BY CUBE (c1, c2)
   ORDER BY (c1,c2);
 c1 | c2 | sum
----+----+-----
 a  | aa |   1
 a  | bb |   2
 a  | cc |   3
 a  |    |   6
 b  | aa |   4
 b  | bb |   5
 b  | cc |   6
 b  |    |  15
    | aa |   5
    | bb |   7
    | cc |   9
    |    |  21
(12 rows)

Как видно, помимо результатов, аналогичных ROLLUP (строки 1–8 и последняя строка):

Строки 9–11 показывают суммы, сгруппированные по (c2) (например, aa = 1+4, bb = 2+5).

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

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

← Предыдущая

Базовые запросы

Временные ряды

Русский English 简体中文

Расширенные запросы

1 Оконные функции

1.1 Синтаксис

1.2 Специализированные оконные функции

1.2.1 Функции статистики распределения

1.2.2 Функции вычисления смещения

1.3 Агрегирующие оконные функции

1.4 Оконная рамка / Скользящее окно

2 Агрегатные функции упорядоченного множества

3 Функции временных рядов

3.1 Функция time_bucket()

3.2 Функция time_bucket_gapfill()

4 Общие табличные выражения (CTE)

5 Вложенные запросы

5.1 Размещение подзапросов

5.2 Скалярные подзапросы

5.3 Подзапросы, возвращающие набор значений

5.4 Коррелированные подзапросы

6 Дата-куб

6.1 Предложение GROUPING SETS

6.2 Предложение ROLLUP

6.3 Предложение CUBE

Русский

English

简体中文