SELECT * EXCLUDE, REPLACE и выражение COLUMNS()

SELECT * в стандартном SQL — инструмент по принципу «всё или ничего». Либо вы берёте все колонки, либо перечисляете нужные руками. Между этими крайностями нет ничего. И как только таблица меняется — добавляется колонка, переименовывается другая — ваш ручной список колонок устаревает молча. Возникает классический конфликт: SELECT * удобен, но тащит лишнее (например, технические колонки _loaded_at), а явный список безопасен, но хрупок и многословен.

DuckDB заполняет этот промежуток тремя конструкциями: SELECT * EXCLUDE убирает из звёздочки несколько колонок, SELECT * REPLACE подменяет выражение для отдельной колонки не ломая остальные, а выражение COLUMNS() применяет одну операцию сразу к множеству колонок, выбранных по регулярному выражению или предикату. Вместе они превращают * из грубого инструмента в точный.

EXCLUDE: звёздочка минус несколько колонок

Синтаксис SELECT * EXCLUDE (col1, col2) берёт все колонки, кроме перечисленных. Типичный случай — широкая таблица с парой технических полей, которые не нужны в выводе:

-- Таблица: id, name, email, _loaded_at, _source_file
-- Нужно всё, кроме двух служебных колонок

-- Стандартный SQL: перечисляем всё вручную
SELECT id, name, email FROM users;

-- DuckDB: вычитаем лишнее из звёздочки
SELECT * EXCLUDE (_loaded_at, _source_file) FROM users;

Разница не только в длине. Если в таблицу users завтра добавят колонку phone, ручной список id, name, email её молча потеряет — и никто не заметит, пока не хватятся данных. Вариант с EXCLUDE подхватит phone автоматически: вы декларировали «всё, кроме двух служебных полей», и это намерение остаётся верным при росте таблицы. EXCLUDE кодирует то, что вы НЕ хотите, а это меняется гораздо реже, чем полный список того, что хотите.

EXCLUDE работает и с * от конкретной таблицы в джойне:

-- Убрать дублирующийся ключ join из правой таблицы
SELECT
    orders.*,
    customers.* EXCLUDE (customer_id)
FROM orders
JOIN customers USING (customer_id);

Здесь customer_id присутствует в обеих таблицах, и без EXCLUDE он попал бы в результат дважды.

REPLACE: подменить колонку, не трогая остальные

SELECT * REPLACE (expr AS col) берёт все колонки, но для названной колонки подставляет вычисленное выражение вместо исходного значения. Это решает другую боль: нужно слегка преобразовать одну колонку широкой таблицы, а ради этого приходится переписывать * в полный список.

-- Нужно: всё как есть, но amount привести к евро (курс 1.08)

-- Без REPLACE: перечисляем все 8 колонок ради одной правки
SELECT id, region, product, amount * 1.08 AS amount, qty,
       order_date, customer_id, status
FROM sales;

-- С REPLACE: одна правка, остальное нетронуто
SELECT * REPLACE (amount * 1.08 AS amount) FROM sales;

Ключевой момент: REPLACE сохраняет позицию колонки. amount останется на своём месте в порядке колонок, просто с новым значением. Это не «убрать и добавить в конец» — это замена на месте.

EXCLUDE и REPLACE комбинируются в одном *:

SELECT * EXCLUDE (_loaded_at)
         REPLACE (upper(region) AS region, amount * 1.08 AS amount)
FROM sales;

COLUMNS(): одна операция на множество колонок

EXCLUDE и REPLACE модифицируют звёздочку точечно. COLUMNS() решает обратную задачу — применить одно и то же выражение или агрегат сразу к группе колонок. Это «цикл по колонкам», встроенный прямо в SQL.

Самая частая форма — COLUMNS() с регулярным выражением. Строка-аргумент трактуется как regex, и выражение применяется ко всем колонкам, чьи имена ему соответствуют:

-- Таблица metrics: day, clicks, impressions, revenue, cost
-- Нужна сумма по всем числовым метрикам разом

-- COLUMNS('clicks|impressions|revenue|cost') matchает 4 колонки
SELECT sum(COLUMNS('clicks|impressions|revenue|cost')) FROM metrics;

Результат — четыре колонки sum(clicks), sum(impressions), sum(revenue), sum(cost). DuckDB разворачивает COLUMNS() в список колонок ещё на этапе биндинга, до планирования запроса, и применяет обёрнутую вокруг него функцию к каждой. Это эквивалентно тому, как если бы вы написали все четыре sum(...) руками — но список не нужно поддерживать вручную.

COLUMNS() принимает не только regex. Можно передать * со звёздочными модификаторами:

-- Округлить все колонки, кроме day
SELECT COLUMNS(* EXCLUDE (day)) :: INTEGER FROM metrics;

Здесь COLUMNS(* EXCLUDE (day)) берёт все колонки кроме day, а каст :: INTEGER применяется к каждой.

Стоит понимать механику разворачивания точнее. COLUMNS() не «функция, которая возвращает много значений» — такого в SQL не бывает, выражение всегда даёт одно значение на строку. COLUMNS() — это синтаксическая конструкция, которую биндер заменяет на список выражений ещё до того, как запрос становится деревом операторов. Запрос SELECT sum(COLUMNS('a|b')) после биндинга — это буквально SELECT sum(a), sum(b): два отдельных выражения, два независимых столбца результата. Поэтому COLUMNS() можно ставить везде, где допустим список выражений, — в SELECT, в WHERE, в ORDER BY — и нельзя там, где ожидается ровно одно значение. И поэтому же COLUMNS() нельзя вложить в COLUMNS(): к моменту, когда внешний разворачивается, внутреннего уже как отдельной сущности нет.

Порядок выходных колонок при разворачивании COLUMNS() совпадает с порядком колонок в таблице, а не с порядком, в котором они «перечислены» в regex. Регулярное выражение — это фильтр-предикат над именами, а не список; COLUMNS('cost|clicks') и COLUMNS('clicks|cost') дадут один и тот же результат в одном и том же порядке — в том, в каком clicks и cost объявлены в metrics. Если важен конкретный порядок выходных колонок, его задают не через regex, а явным перечислением.

COLUMNS() с lambda-предикатом

Самая мощная форма — COLUMNS() с lambda-функцией. Лямбда получает имя колонки как строку и возвращает булево: колонка попадает в выборку, если лямбда вернула true. Это позволяет отбирать колонки по произвольному предикату над их именами:

-- Отобрать все колонки, чьё имя заканчивается на '_id'
SELECT COLUMNS(col -> col LIKE '%\_id' ESCAPE '\') FROM orders;

-- Все колонки, кроме начинающихся с подчёркивания (служебных)
SELECT COLUMNS(col -> NOT starts_with(col, '_')) FROM events;

Обратите внимание на синтаксис лямбды: col -> выражение. Имя параметра слева, стрелка, тело справа. В контексте COLUMNS() параметр лямбды — это всегда имя колонки в виде строки, и тело должно вычисляться в булево значение. Это отличается от лямбд в list-функциях (list_transform и подобных), где параметр — это элемент списка; синтаксис стрелки в обоих случаях одинаковый, разная только семантика параметра.

COLUMNS() встречается и в WHERE — тогда предикаты по нескольким колонкам объединяются через AND:

-- Строки, где ВСЕ колонки-метрики положительны
SELECT * FROM metrics
WHERE COLUMNS('clicks|revenue|cost') > 0;

Условие COLUMNS('clicks|revenue|cost') > 0 разворачивается в clicks > 0 AND revenue > 0 AND cost > 0.

Когда что применять

Три конструкции решают три разные задачи. Правило выбора:

Общий принцип всех трёх — они описывают намерение, а не результат. EXCLUDE говорит «не эти», COLUMNS('regex') говорит «все подходящие». При изменении схемы таблицы намерение чаще остаётся верным, чем явный список. Это и есть смысл friendly SQL: запрос переживает эволюцию таблицы без молчаливых поломок.

У устойчивости к изменениям схемы есть и обратная грань, которую честно стоит назвать. «Запрос подхватывает новую колонку сам» — это удобно, пока новая колонка ожидаема, и потенциально опасно, когда нет. Если в широкую таблицу добавят, например, тяжёлую колонку с большим текстом или служебное поле, SELECT * EXCLUDE (...) молча начнёт его тащить — а нижестоящий код (BI-инструмент, экспорт, следующая модель) этого не ждал. Поэтому выбор между *-формами и явным списком — это выбор между двумя видами хрупкости: явный список хрупок к переименованию и забывает новые нужные колонки, а *-формы хрупки к появлению нежелательных колонок. Практический ориентир: *-формы хороши там, где таблица под вашим контролем и её рост предсказуем (своя staging-модель), а явное перечисление надёжнее на границе системы — там, где схему результата видит внешний потребитель и она должна быть стабильным контрактом.

Попробуй сам

Создайте таблицу:

CREATE TABLE metrics AS
  SELECT * FROM (VALUES
    (DATE '2026-01-01', 100, 5000, 250.0, 80.0, 'src_a'),
    (DATE '2026-01-02', 150, 6000, 300.0, 90.0, 'src_b'),
  ) t(day, clicks, impressions, revenue, cost, _source);

Задания:

1. Выведите всю таблицу без служебной колонки _source, используя EXCLUDE.
2. Через REPLACE выведите таблицу, но с revenue, увеличенной на 10 процентов. Убедитесь, что revenue осталась на своей позиции в порядке колонок.
3. Одним запросом посчитайте сумму по clicks, impressions, revenue, cost через COLUMNS() с регулярным выражением.
4. Через COLUMNS() с lambda-предикатом отберите только колонки, чьё имя НЕ начинается с подчёркивания.
5. Намеренно сделайте опечатку в regex (например, 'reveue') и посмотрите, что произойдёт. Объясните, почему ошибка проявляется именно так.