Язык запросов Yupana (YupanaQL)

Оглавление

Для выполнения запросов Yupana поддерживает собственный диалект SQL. Поддерживаются следующие операции:

  • SELECT – выборка данных.
  • SHOW TABLES – вывод списка таблиц.
  • SHOW COLUMNS FROM <table_name> – вывод списка полей таблицы.

Правила наименования полей

  1. Время для любой схемы указывается как поле time типа TIMESTAMP. Доступны следующие функции для работы со временем: trunc_second, trunc_minute, trunc_hour, trunc_day, trunc_month, trunc_year. extract_second, extract_minute, extract_hour, extract_day, extract_month, extract_year.

При работе с драйвером следует учитывать, что выражение WHERE обязательно и должно содержать временной интервал time >= x and time < y.

  1. Поля таблицы указываются:
    • как есть (quantity или “quantity”)
    • с указанием таблицы (“kkm_items”.”quantity” или kkm_items.quantity)
  2. Тэги указываются как есть (например kkmId)

  3. Поля внешних связей указываются в виде имясвязи_имяполя (например ItemsInvertedIndex_phrase).

  4. Фильтровать можно по размерностям, метрикам и полям внешних связей, времени используя =,!=,IN,IS NULL/NOT NULL для строк и =,!=,>,>=,<,<=,IN,IS NULL/NOT NULL для остальных типов.

Литералы

Поддерживаются литералы следующих типов:

  1. Строки: 'Hello!'
  2. Числа (целые либо с плавающей запятой): 42 или 1234.567
  3. Даты:
    • TIMESTAMP '2018-08-06'
    • TIMESTAMP '2018-08-06 16:24:50'
    • TIMESTAMP '2018-08-06 16:24:50.123'
    • { ts '2017-06-13' }
    • { ts '2017-06-13 09:15:44' }
    • { ts '2017-06-13 09:15:44.666' }
  4. Интервалы:
    • INTERVAL '06:00:00' – 6 часов
    • INTERVAL '1 12:00:00' – 1 день и 12 часов
    • INTERVAL '1' HOUR – 1 час
    • INTERVAL '30' MINUTE – 30 минут
    • INTERVAL '2 12' DAY TO HOUR – 2 дня 12 часов
    • INTERVAL '6 12:30' DAY TO MINUTE – 6 дней 12 часов 30 минут
    • INTERVAL '3' MONTH – 3 месяца
    • INTERVAL '1' YEAR – 1 год
    • INTERVAL '3-10' MONTH TO DAY – 3 месяца и 10 дней

И т.д. Важно понимать, что интервалы содержащие месяца и/или годы не могут быть использованы при сравнении длительности интервала между двумя датами. Это обуславливается тем что длина месяца или года зависит от определенной даты.

При выполнении математических операций (плюс или минус) над интервалами можно использовать любые интервалы.

Примеры запросов

Суммы продаж для указанной кассы за указанный период с разбивкой по дням:

SELECT sum(sum), day(time) as d, kkmId
  FROM items_kkm
  WHERE time >= TIMESTAMP '2019-06-01' AND time < TIMESTAMP '2019-07-01' AND kkmId = '10'
  GROUP BY d, kkmId

Суммы продаж товаров в которых встречается слово “штангенциркуль” за указанный период с разбивкой по дням:

SELECT 
    sum(sum), day(time) as d, kkmId
FROM 
    items_kkm
WHERE 
    time >= TIMESTAMP '2019-06-01' AND 
    time < TIMESTAMP '2019-07-01' AND 
    itemsInvertedIndex_phrase = 'штангенциркуль'
GROUP BY 
    d, kkmId

Первой и последней продажи селедки за сутки:

SELECT 
    min(time) as mint, max(time) as maxt, day(time) as d
FROM items_kkm
WHERE 
    time >= TIMESTAMP '2019-06-01' AND 
    time < TIMESTAMP '2019-07-01' AND
    itemsInvertedIndex_phrase = 'селедка'
GROUP BY d

Считаем количество продаж товаров, купленных в количестве больше 10:

SELECT 
    item, 
    sum(
    CASE
        WHEN quantity > 9 THEN 1
        ELSE 0 
    )
FROM items_kkm
WHERE 
    time >= TIMESTAMP '2019-06-01'
    AND time < TIMESTAMP '2019-07-01'
GROUP BY item

Применяем фильтры после расчета оконной функции:

SELECT
  kkmId,
  time AS t,
  lag(time) AS l
FROM receipt
WHERE time >= TIMESTAMP '2019-06-01' AND time < TIMESTAMP '2019-07-01'
GROUP BY kkmId
HAVING
  ((l - t) > INTERVAL '2' HOUR AND extract_hour(t) >= 8 AND extract_hour(t) <= 18) OR
  ((l - t) > INTERVAL '4' HOUR AND extract_hour(t) > 18 OR extract_hour(t) < 8)

Выбираем предыдущие три месяца:

SELECT sum(sum), day(time) as d, kkmId
FROM items_kkm
WHERE time >= trunc_month(now() - INTERVAL '3' MONTH) AND time < trunc_month(now())
GROUP BY d, kkmId

Агрегация по выражению:

SELECT kkmId,
    (CASE WHEN totalReceiptCardSum > 0 THEN 1 ELSE 0) as paymentType
FROM items_kkm
WHERE time >= TIMESTAMP '2019-06-01' AND time < TIMESTAMP '2019-07-01'
GROUP BY paymentType, kkmId

Используем арифметику (+, -, *, /):

SELECT sum(totalSum) as ts, sum(cardSum) * max(cashSum) / 2 as something
FROM receipt
WHERE 
    time >= TIMESTAMP '2019-06-01' AND time < TIMESTAMP '2019-07-01' AND
    kkmId = '11'
GROUP BY kkmId

Группируем колбасу по вкусу и считаем сумму:

SELECT
    item,
    case
      when contains_any(stem(item), stem('вареная')) then 'вареная'
      when contains_any(stem(item), stem('соленая')) then 'соленая'
      else 'невкусная' as taste,
    sum(sum)
FROM items_kkm
WHERE 
    time >= TIMESTAMP '2019-06-01' AND time < TIMESTAMP '2019-07-01' 
    AND itemsInvertedIndex_phrase = 'колбаса'
GROUP BY item, taste

Функции

Функция Тип функции Типы аргументов Тип значения Описание
min агрегация число, строка, время тот же Минимальное значение. Для строковых значение в лексикографическом порядке
max агрегация число, строка, время тот же Максимальное значение. Для строковых значение в лексикографическом порядке
sum агрегация число тот же Сумма
count агрегация любой число Количество
distinct_count агрегация любой число Количество уникальных значений
lag оконная любой тот же Предыдущее значение в группе записей. Группа определяется в запросе в секции группировки. Сортировка по времени.
trunc_year унарная время время Округление времени до года
trunc_month унарная время время Округление времени до месяца
trunc_day унарная время время Округление времени до дня
trunc_hour унарная время время Округление времени до часа
trunc_minute унарная время время Округление времени до минуты
trunc_second унарная время время Округление времени до секунды
exract_year унарная время число Извлечение значения года из времени
exract_month унарная время число Извлечение значения месяца из времени
exract_day унарная время число Извлечение значения дня из времени
exract_hour унарная время число Извлечение значения часа из времени
exract_minute унарная время число Извлечение значения минуты из времени
exract_second унарная время число Извлечение значения секунды из времени
abs унарная число число Значение числа по модулю
stem унарная строка массив строк Получение стемированых транслитерированых строк из строки
stem унарная массив строк массив строк Получение стемированых транслитерированых строк из массива строк
split унарная строка массив строк Разбиение строки на слова по пробелам
length унарная строки, массивы строки, массивы Длина строки или количество элементов в массиве
array_to_string унарная массив строка Преобразование массивы в строку в формате “( a, b, .., n)”
+ инфиксная число, строка, интервал тот же Сложение
- инфиксная число тот же Вычитание
* инфиксная число тот же Умножение
/ инфиксная число тот же Деление
+ инфиксная время и интервал время Сложение
- инфиксная время и интервал время Вычитание
- инфиксная время и время интервал Вычитание
= инфиксная число, строка, время логический Сравнение на равенство
<> или != инфиксная число, строка, время логический Сравнение на неравенство
> инфиксная число, строка, время логический Сравнение на больше
< инфиксная число, строка, время логический Сравнение на меньше
>= инфиксная число, строка, время логический Сравнение на больше или равно
<= инфиксная число, строка, время логический Сравнение на меньше или равно
contains бинарная массив и тип элемента логический True если массив содержит элемент, иначе False
contains_all бинарная массив и массив логический True если массив1 содержит все элементы массива2, иначе False
contains_any бинарная массив и массив логический True если массив1 содержит хотя бы один элемент из массива2, иначе False
contains_same бинарная массив и массив логический True если массив1 содержит те же элементы что и массив2 (в любом порядке)

Типы функций

  • Агрегация – функция вычисляющая общее значение из множества значений (например сумму или максимум). Агрегации не могут использоваться вместе с оконными функциями.
  • Оконная – функция вычисляющая общее значение из множества значении и их порядка. Оконные функции не могут использоваться вместе с агрегациями. Не поддерживаются в реализации Yupana для Spark.
  • Унарная – функция над одним значением (например length или stem).
  • Инфиксная – функция над двумя значениями, в SQL записывается между аргументами (например + или -).
  • Бинарная – функция с двумя значениями, например contains_all.

Кроме того, поддерживаются следующие SQL выражения:

Выражение Описание
x IN (1, 2 .. z) Проверка что x является одним из элементов заданного множества констант
x NOT IN (3, 4, .. z) Проверка что x не является одним из элементов заданного множества констант
x IN NULL Проверка что значение x не определено
x IS NOT NULL Проверка что значение x определено