Дружественный SQL

DuckDB предлагает несколько расширенных функций SQL и синтаксического сахара, чтобы сделать запросы SQL более лаконичными. Мы называем их «дружественным SQL».

Некоторые из этих функций также поддерживаются в других системах, в то время как некоторые (в настоящее время) являются эксклюзивными для DuckDB.

Операторы

• Создание таблиц и вставка данных:
  • CREATE OR REPLACE TABLE: избегайте DROP TABLE IF EXISTS операторов в скриптах.
  • CREATE TABLE ... AS SELECT (CTAS): создание новой таблицы из результата работы таблицы без ручного определения схемы.
  • INSERT INTO ... BY NAME: этот вариант оператора INSERT позволяет использовать имена столбцов вместо позиций.
  • INSERT OR IGNORE INTO ...: вставка строк, которые не приводят к конфликту из-за ограничений UNIQUE или PRIMARY KEY.
  • INSERT OR REPLACE INTO ...: вставка строк, которые не приводят к конфликту из-за ограничений UNIQUE или PRIMARY KEY. Для строк, которые приводят к конфликту, заменяются столбцы существующей строки на новые значения вставляемой строки.
• Описание таблиц и вычисление статистик:
  • DESCRIBE: предоставляет краткое описание схемы таблицы или запроса.
  • SUMMARIZE: возвращает сводную статистику для таблицы или запроса.
• Уплотнение операторов SQL:
  • FROM-первый синтаксис с необязательным SELECT оператором: DuckDB позволяет писать запросы в виде FROM tbl, которые выбирают все столбцы (выполняя SELECT * операцию).
  • GROUP BY ALL: опустить столбцы группировки, выведя их из списка атрибутов в SELECT операторе.
  • ORDER BY ALL: сокращение для сортировки по всем столбцам (например, для обеспечения детерминированных результатов).
  • SELECT * EXCLUDE: опция EXCLUDE позволяет исключить определенные столбцы из выражения *.
  • SELECT * REPLACE: опция REPLACE позволяет заменить определенные столбцы другими выражениями в выражении *.
  • UNION BY NAME: выполнить операцию UNION по именам столбцов (вместо использования позиций).
• Преобразование таблиц:
  • PIVOT для преобразования длинных таблиц в широкие.
  • UNPIVOT для преобразования широких таблиц в длинные.
• Определение переменных уровня SQL:
  • SET VARIABLE
  • RESET VARIABLE

Функции запроса

• Псевдонимы столбцов в WHERE, GROUP BY, и HAVING
• COLUMNS() выражение может использоваться для выполнения одного и того же выражения над несколькими столбцами:
  • с регулярными выражениями
  • с EXCLUDE и REPLACE
  • с лямбда-функциями
• Переиспользуемые псевдонимы столбцов, например: SELECT i + 1 AS j, j + 2 AS k FROM range(0, 3) t(i)
• Расширенные функции агрегирования для аналитических (OLAP) запросов:
  • FILTER оператор
  • GROUPING SETS, GROUP BY CUBE, GROUP BY ROLLUP операторы
• count() сокращение для count(*)

Литералы и идентификаторы

• Нечувствительность к регистру при сохранении регистра сущностей в каталоге
• Уникализация идентификаторов
• Подчеркивания как разделители цифр в числовых литералах

Типы данных

• MAP тип данных
• UNION тип данных

Импорт данных

• Автоматическое определение заголовков и схемы CSV-файлов
• Прямой запрос к CSV-файлам и Parquet-файлам
• Загрузка из файлов с использованием синтаксиса FROM 'my.csv', FROM 'my.csv.gz', FROM 'my.parquet', и т. д.
• Расширение имён файлов (globbing), например: FROM 'my-data/part-*.parquet'

Функции и выражения

• Оператор точки для цепочки функций: SELECT ('hello').upper()
• Форматировщики строк: функция format() с синтаксисом fmt и printf() function
• Списки с выражениями
• Вырезка списков
• Вырезка строк
• STRUCT.* нотация
• Простое создание LIST и STRUCT

Типы объединений

• ASOF объединения
• LATERAL объединения
• POSITIONAL объединения

Заключительные запятые

DuckDB поддерживает заключительные запятые, как при перечислении сущностей (например, имён столбцов и таблиц), так и при создании LIST элементов. Например, следующий запрос выполняется:

SELECT
    42 AS x,
    ['a', 'b', 'c',] AS y,
    'hello world' AS z,
;

"Top-N в группе" запросы

Вычисление "верхних N строк в группе", упорядоченных по определённому критерию, является распространённой задачей в SQL, которая, к сожалению, часто требует сложного запроса, включающего функции окон и/или подзапросы.

Для решения этой задачи DuckDB предоставляет агрегатные функции max(arg, n), min(arg, n), arg_max(arg, val, n), arg_min(arg, val, n), max_by(arg, val, n) и min_by(arg, val, n), чтобы эффективно возвращать "верхние" n строки в группе на основе определённого столбца в порядке возрастания или убывания.

Например, рассмотрим следующую таблицу:

SELECT * FROM t1;

┌─────────┬───────┐
│   grp   │  val  │
│ varchar │ int32 │
├─────────┼───────┤
│ a       │     2 │
│ a       │     1 │
│ b       │     5 │
│ b       │     4 │
│ a       │     3 │
│ b       │     6 │
└─────────┴───────┘

Мы хотим получить список из трёх наибольших val значений в каждой группе grp. Обычный способ сделать это — использовать функцию окна в подзапросе:

SELECT array_agg(rs.val), rs.grp
FROM
    (SELECT val, grp, row_number() OVER (PARTITION BY grp ORDER BY val DESC) AS rid
    FROM t1 ORDER BY val DESC) AS rs
WHERE rid < 4
GROUP BY rs.grp;

┌───────────────────┬─────────┐
│ array_agg(rs.val) │   grp   │
│      int32[]      │ varchar │
├───────────────────┼─────────┤
│ [3, 2, 1]         │ a       │
│ [6, 5, 4]         │ b       │
└───────────────────┴─────────┘

Но в DuckDB мы можем сделать это намного лаконичнее (и эффективнее!):

SELECT max(val, 3) FROM t1 GROUP BY grp;

┌─────────────┐
│ max(val, 3) │
│   int32[]   │
├─────────────┤
│ [3, 2, 1]   │
│ [6, 5, 4]   │
└─────────────┘

Связанные посты в блоге

• “Более понятный SQL с DuckDB” пост в блоге
• “Ещё более понятный SQL с DuckDB” пост в блоге
• “Гимнастика с SQL: Придание гибкости новым формам SQL” пост в блоге