v.20.9New Feature

Added Column Transformers EXCEPT, REPLACE, and APPLY for Enhanced SQL Queries

Official CH changelog text
Added column transformers EXCEPT, REPLACE, APPLY, which can be applied to the list of selected columns (after * or COLUMNS(...)). For example, you can write SELECT * EXCEPT(URL) REPLACE(number + 1 AS number). Another example: select * apply(length) apply(max) from wide_string_table to find out the maxium length of all string columns. #14233 (Amos Bird).
Added new column transformers EXCEPT, REPLACE, and APPLY that can be used with * or COLUMNS(...) in SELECT queries to manipulate the list of selected columns.

Why it matters

These transformers provide enhanced flexibility in selecting and modifying columns directly in the SELECT clause, allowing users to exclude specific columns, replace or modify columns with expressions, and apply functions across multiple columns efficiently. This simplifies query writing and expands the ability to process wide tables without manually listing columns.

How to use it

Use the transformers immediately after selecting columns with or COLUMNS(...). For example, to exclude a column and replace another: 
SELECT  EXCEPT(URL) REPLACE(number + 1 AS number)

To apply functions to all selected columns: 
SELECT * APPLY(length) APPLY(max) FROM wide_string_table

Related resources

PreviousNext