История версий
Требования

YMatrix

Быстрый старт

Развертывание

Моделирование данных

Подключение

Запись данных

Миграция

Запросы

Операции и обслуживание

Настройка производительности

Устранение неполадок

Справочник

Справочник по SQL

Часто задаваемые вопросы

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

Помимо базовых операций подключения, агрегации и группировки, YMatrix также предоставляет множество расширенных аналитических функций, таких как оконные функции, CTE (Common Table Expression), упорядоченные агрегатные функции, часто используемые временные функции и т.д. В этом разделе вы ознакомитесь с методами расширенных запросов, commonly используемых в YMatrix.

Ниже приведен пример статистики использования дискового пространства, демонстрирующий выполнение расширенных запросов в YMatrix. В образце метрической таблицы disk мы спроектировали поля скорости чтения и записи диска, используя механизм хранения MARS2.
Таблицы MARS2 зависят от расширения matrixts. Перед созданием таблицы необходимо сначала создать расширение в базе данных с использованием этого механизма хранения. Это расширение действует на уровне базы данных и не требует повторного создания.

CREATE EXTENSION matrixts;

Создайте метрическую таблицу disk.

CREATE TABLE disk (
    time timestamp with time zone,
    tag_id int,
    read float,
    write float
)
USING mars2
DISTRIBUTED BY (tag_id);

После успешного создания таблицы MARS2 необходимо дополнительно создать индекс типа mars2_btree, чтобы обеспечить нормальную запись и чтение.

CREATE INDEX idx_mars2 ON disk 
USING mars2_btree(tag_id);

Дополнительную информацию о MARS2 см. в разделе Storage Engine

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

Оконные функции в основном используются для обработки сравнительно сложных сценариев статистического анализа отчетов. «Окно» определяет набор данных, в котором выполняются агрегированные запросы между строками, связанными с текущей строкой. Различие между оконными функциями и другими SQL-функциями заключается в наличии предложения OVER. Если функция содержит предложение OVER, то это оконная функция. Вы можете понять разницу между оконными функциями и обычными агрегатными функциями по следующей таблице.

Обычные агрегатные функции Оконные функции
Вывод Одна запись Несколько записей
Функции max(), min(), count(), sum() и т.д. avg(), sum(), rank() и т.д.
Использование обычно используется совместно с предложением GROUP BY используется совместно с предложением OVER. Как правило, предложение OVER записывается сразу после имени и параметров оконной функции. В предложении OVER обычно можно использовать три подпредложения: PARTITION BY, ORDER BY и ROWS BETWEEN. Использование только подпредложения PARTITION BY формирует статическое окно, размер и положение которого не изменяются; если помимо PARTITION BY используются одно или два из подпредложений ORDER BY и ROWS BETWEEN, формируется скользящее окно, то есть размер и положение окна постоянно меняются.

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

1.1 Накопительная сумма

Используя вложенную функцию SUM, можно рассчитать накопительную сумму.

Следующий SQL-запрос вычисляет накопительную сумму чтения и записи диска с tag_id = 1 за период с 2021-04-10 21:00:00 до 2021-04-10 21:00:10:

ymatrix=# SELECT time,
    sum(sum(read)) OVER (ORDER BY time) AS read,
    sum(sum(write)) OVER (ORDER BY time) AS write
    FROM disk
    WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:00:10'::timestamp
    AND tag_id = 1 
    GROUP BY time
    ORDER BY time;

          time          |  read  |       write
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 2021-04-10 21:00:00+08 |  81.07 |               73.3
 2021-04-10 21:00:01+08 | 110.63 |             121.77
 2021-04-10 21:00:02+08 | 202.12 |             201.36
 2021-04-10 21:00:03+08 | 263.74 |             257.88
 2021-04-10 21:00:04+08 |  361.6 |              299.3
 2021-04-10 21:00:05+08 | 394.49 | 327.33000000000004
 2021-04-10 21:00:06+08 |  438.3 |             334.98
 2021-04-10 21:00:07+08 | 523.35 |             431.39
 2021-04-10 21:00:08+08 | 583.15 |             461.84
 2021-04-10 21:00:09+08 | 609.01 |             533.03
 2021-04-10 21:00:10+08 | 669.52 |              535.9
(11 rows)

1.2 Скользящее среднее

Скользящее среднее используется для расчета среднего значения за предыдущие n строк.

Следующий SQL-запрос вычисляет средние значения чтения и записи каждые 10 секунд с 2021-04-10 21:00:00 до 21:01:00 (для первых 9 данных рассчитывается среднее значение количества строк, удовлетворяющих условию):

ymatrix=# SELECT time,
    round(AVG(read) OVER(ORDER BY time ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)) AS read,
    round(AVG(write) OVER(ORDER BY time ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)) AS write
    FROM disk
    WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:01:00'::timestamp
    AND tag_id = 1
    ORDER BY time DESC;

          time          | read | write
------------------------+-------------------------------------
 2021-04-10 21:01:00+08 |   57 |    57
 2021-04-10 21:00:59+08 |   49 |    60
 2021-04-10 21:00:58+08 |   52 |    56
 2021-04-10 21:00:57+08 |   51 |    57
 2021-04-10 21:00:56+08 |   53 |    65
 2021-04-10 21:00:55+08 |   48 |    64
 2021-04-10 21:00:54+08 |   49 |    64
 2021-04-10 21:00:53+08 |   47 |    54
 2021-04-10 21:00:52+08 |   44 |    54
 2021-04-10 21:00:51+08 |   41 |    56
......

1.3 Приращение

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

Следующий запрос вычисляет изменение значения чтения диска с tag_id = 1. С 2021-04-10 21:00:00 до 21:01:00 положительное число означает рост по сравнению с предыдущей секундой, отрицательное — снижение:

ymatrix=# SELECT
    time,
    (
     CASE WHEN lag(read) OVER (ORDER BY time) IS NULL THEN NULL
     ELSE round(read - lag(read) OVER (ORDER BY time))
     END
    ) AS read
    FROM disk
    WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:01:00'::timestamp
    AND tag_id = 1
    ORDER BY time;

          time          | read
---------------------------------------------------
 2021-04-10 21:00:00+08 |
 2021-04-10 21:00:01+08 |  -52
 2021-04-10 21:00:02+08 |   62
 2021-04-10 21:00:03+08 |  -30
 2021-04-10 21:00:04+08 |   36
 2021-04-10 21:00:05+08 |  -65
 2021-04-10 21:00:06+08 |   11
 2021-04-10 21:00:07+08 |   41
 2021-04-10 21:00:08+08 |  -25
 2021-04-10 21:00:09+08 |  -34
......

1.4 Темп роста

На основе приращения деление его на интервал времени позволяет получить темп роста (так как в примере данные собираются раз в секунду, результаты аналогичны приращению):

ymatrix=# SELECT
    time,
    (
     CASE WHEN lag(read) OVER (ORDER BY time) IS NULL THEN NULL
     ELSE round(read - lag(read) OVER (ORDER BY time))
     END
    ) / extract(epoch from time - lag(time) OVER (ORDER BY time)) AS read_rate,
    extract(epoch from time - lag(time) OVER (ORDER BY time)) AS "time lag"
    FROM disk
    WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:01:00'::timestamp
    AND tag_id = 1
    ORDER BY time;

          time          | read_rate | time lag
----------------------------------------------------------------------------------------------------------------------------------
 2021-04-10 21:00:00+08 |           |
 2021-04-10 21:00:01+08 |       -52 |        1
 2021-04-10 21:00:02+08 |        62 |        1
 2021-04-10 21:00:03+08 |       -30 |        1
 2021-04-10 21:00:04+08 |        36 |        1
 2021-04-10 21:00:05+08 |       -65 |        1
 2021-04-10 21:00:06+08 |        11 |        1
 2021-04-10 21:00:07+08 |        41 |        1
 2021-04-10 21:00:08+08 |       -25 |        1
 2021-04-10 21:00:09+08 |       -34 |        1
......

1.5 Точки изменений

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

ymatrix=# SELECT time, read FROM (
        SELECT time,
            read,
            read - lag(read) OVER (ORDER BY TIME) AS diff
        FROM disk
        WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:01:00'::timestamp
        AND tag_id = 1 ) ht
    WHERE diff IS NULL OR diff != 0
    ORDER BY time;

          time          | read
-----------------------------------------------
 2021-04-10 21:00:00+08 | 81.07
 2021-04-10 21:00:01+08 | 29.56
 2021-04-10 21:00:02+08 | 91.49
 2021-04-10 21:00:03+08 | 61.62
 2021-04-10 21:00:04+08 | 97.86
 2021-04-10 21:00:05+08 | 32.89
 2021-04-10 21:00:06+08 | 43.81
 2021-04-10 21:00:07+08 | 85.05
 2021-04-10 21:00:08+08 |  59.8
 2021-04-10 21:00:09+08 | 25.86
(10 rows)

2 Упорядоченные агрегатные функции

2.1 Расчет непрерывного процентиля

Можно использовать следующую упорядоченную агрегатную функцию для расчета непрерывного процентиля.

percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)

Примеры и возвращаемые результаты приведены ниже.

ymatrix=# SELECT tag_id,
    percentile_cont(0.2) WITHIN GROUP
     (ORDER BY read) AS read,
    percentile_cont(0.3) WITHIN GROUP (ORDER BY write) AS write
    FROM disk
    GROUP BY tag_id
    ORDER BY tag_id;

 tag_id | read  | write
-------+------+-------
      1 | 19.87 | 29.86
      2 | 19.95 | 29.88
      3 | 20.06 | 29.93
(3 rows)

Параметр обозначает процент, если передано значение 0.5, это эквивалентно вычислению медианы.

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

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

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

Следующее CTE вычисляет максимальное и минимальное значения средней скорости чтения среди всех устройств:

WITH avg_read (tag_id, avg_read) AS (
    SELECT tag_id, AVG(read) AS read FROM disk GROUP BY tag_id
) SELECT MAX(avg_read), MIN(avg_read) FROM avg_read;

4 Временные функции

Компонент временных данных matrixts, предоставляемый YMatrix, также включает временные функции, часто используемые в сценариях работы с временными данными. Сначала создайте этот компонент:

stats=# CREATE EXTENSION matrixts;

4.1 time_bucket

Функция time_bucket вычисляет среднее значение за заданный временной период.

Имя параметра Описание Тип данных По умолчанию
period Размер временного окна int16;int32;int64;interval Нет
timestamp Столбец, который необходимо преобразовать int16;int32;int64;date;timestamp;timestamptz Нет

Следующий SQL-запрос вычисляет среднюю скорость чтения и записи каждые 5 минут с 2021-04-10 21:00:00 до 22:00:00:

ymatrix=# SELECT time_bucket('5 minutes', time) AS five_min,
    AVG(read) as read,
    AVG(write) as write
    FROM disk
    WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 22:00:00'::timestamp
    AND tag_id = 1
    GROUP BY five_min
    ORDER BY five_min;

        five_min        |        read         |        write
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 2021-04-10 21:00:00+08 | 48.614599999999996 |  49.48656666666666
 2021-04-10 21:05:00+08 |  50.73533333333335 | 49.992566666666654
 2021-04-10 21:10:00+08 |   51.6102333333333 |  49.99359999999999
 2021-04-10 21:15:00+08 |  49.29116666666669 |  53.89146666666666
 2021-04-10 21:20:00+08 |  49.67863333333332 |  50.47406666666665
 2021-04-10 21:25:00+08 |  51.09013333333332 | 47.766733333333335
 2021-04-10 21:30:00+08 |  49.55949999999999 | 50.440766666666654
 2021-04-10 21:35:00+08 |  48.86253333333333 |  50.57290000000001
 2021-04-10 21:40:00+08 | 51.061299999999974 | 47.028766666666684
 2021-04-10 21:45:00+08 |  52.10353333333333 | 49.861466666666665
 2021-04-10 21:50:00+08 | 51.780566666666694 |            51.4159
 2021-04-10 21:55:00+08 |  51.83549999999998 | 49.124366666666674
 2021-04-10 22:00:00+08 |              93.96 |              91.07
(13 rows)

4.2 time_bucket_gapfill

Когда в течение периода времени отсутствуют данные и требуется их очистка, можно использовать функцию time_bucket_gapfill для заполнения пропущенных данных, чтобы данные равномерно распределились по указанному временному интервалу, что облегчает анализ. Существуют две стратегии заполнения: locf (last observation carried forward) и interpolate (интерполяция).

  • locf: Заполняет значениями, появившимися ранее в группе агрегации
  • interpolate: Линейная интерполяция для заполнения пропущенных значений

Предположим, что данные в примере таблицы disk следующие (применяется только к разделу 4.2):

ymatrix=# SELECT * FROM disk ORDER BY tag_id;
          time          | tag_id | read | write
------------------------+---------------------------------------------------------------------------------------------------------
 2021-04-10 21:00:00+08 |      1 |  3.4 |   4.6
 2021-04-10 21:50:00+08 |      1 |    4 |   2.7
 2021-04-10 21:40:00+08 |      1 |  8.4 |    12
 2021-04-10 21:20:00+08 |      1 |  2.9 |     6
 2021-04-10 21:30:00+08 |      1 |    9 |  10.2
 2021-04-10 21:10:00+08 |      1 |  5.2 |   6.6
 2021-04-10 22:00:00+08 |      1 |   10 |     7
(7 rows)

Используйте функцию time_bucket_gapfill для заполнения данных каждые 5 минут с 21:00:00 до 22:00:00, чтобы получить результат.

SELECT time_bucket_gapfill('5 minutes', time) AS five_min,
    locf(AVG(read)) as locf_read,
    interpolate(AVG(read)) as interpolate_read
    FROM disk
    WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 22:00:00'::timestamp
    AND tag_id = 1
    GROUP BY five_min
    ORDER BY five_min;

        five_min        | locf_read | interpolate_read
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 2021-04-10 21:00:00+08 |       3.4 |              3.4
 2021-04-10 21:05:00+08 |       3.4 |              4.3
 2021-04-10 21:10:00+08 |       5.2 |              5.2
 2021-04-10 21:15:00+08 |       5.2 |             4.05
 2021-04-10 21:20:00+08 |       2.9 |              2.9
 2021-04-10 21:25:00+08 |       2.9 |             5.95
 2021-04-10 21:30:00+08 |         9 |                9
 2021-04-10 21:35:00+08 |         9 |              8.7
 2021-04-10 21:40:00+08 |       8.4 |              8.4
 2021-04-10 21:45:00+08 |       8.4 |              6.2
 2021-04-10 21:50:00+08 |         4 |                4
 2021-04-10 21:55:00+08 |         4 |                7
 2021-04-10 22:00:00+08 |        10 |               10
(13 rows)

4.3 first/last

first возвращает самое раннее значение по времени:

ymatrix=# SELECT tag_id,
    first(read, time) AS read,
    first(write, time) AS write
    FROM disk
    GROUP BY tag_id
    ORDER BY tag_id;

 tag_id | read  | write
-------+------+-------
      1 | 11.51 | 86.61
      2 | 50.07 |  25.9
      3 | 83.72 |  10.5
(3 rows)

last возвращает самое последнее значение:

ymatrix=# SELECT tag_id,
    last(read, time) AS read,
    last(write, time) AS write
    FROM disk
    GROUP BY tag_id
    ORDER BY tag_id;

 tag_id | read  | write
-------+------+-------
      1 |  5.32 |  4.96
      2 |  5.73 | 34.73
      3 | 49.03 | 86.02
(3 rows)

4.4 last_not_null_value

last_not_null_value эквивалентна функции last с фильтрацией not null, возвращая последнее непустое значение:

ymatrix=# SELECT last_not_null_value(read, time)
    FROM disk WHERE tag_id = 1;
 last_not_null_value
----------------------------------------------------------------------------------------------------------------------------------
                 3.1
(1 row)

4.5 last_not_null

По сравнению с last_not_null_value, функция last_not_null возвращает не только значение, но и время. Возвращаемый тип — строка, формат: '["value", "time"]':

ymatrix=# SELECT last_not_null(read, time)
    FROM disk WHERE tag_id = 1;
              last_not_null
----------------------------------------------------------------------------------------------------------------------------------
 ["3.1","2021-11-05 17:32:51.754457+08"]
(1 row)
← Предыдущая
Базовые запросы
Следующая →
Обзор