ClickHouse SQL Functions Quick Reference
Commonly used SQL functions available in chdb.
For the full list, see ClickHouse documentation.
Table of Contents
Aggregate Functions
| Function |
Description |
Example |
count() |
Row count |
SELECT count() FROM t |
count(col) |
Non-null count |
SELECT count(email) FROM users |
sum(col) |
Sum |
SELECT sum(amount) FROM orders |
avg(col) |
Average |
SELECT avg(salary) FROM employees |
min(col), max(col) |
Min/Max |
SELECT min(price), max(price) FROM products |
uniqExact(col) |
Exact distinct count |
SELECT uniqExact(user_id) FROM events |
uniq(col) |
Approximate distinct count (faster) |
SELECT uniq(user_id) FROM events |
groupArray(col) |
Collect values into array |
SELECT dept, groupArray(name) FROM emp GROUP BY dept |
quantile(level)(col) |
Quantile |
SELECT quantile(0.95)(latency) FROM requests |
quantiles(0.5, 0.9, 0.99)(col) |
Multiple quantiles |
SELECT quantiles(0.5, 0.9, 0.99)(duration) |
median(col) |
Median (= quantile(0.5)) |
SELECT median(age) FROM users |
stddevPop(col) |
Population std dev |
SELECT stddevPop(value) FROM measurements |
varPop(col) |
Population variance |
SELECT varPop(value) FROM measurements |
argMax(col, val) |
Value of col at max val |
SELECT argMax(name, score) FROM students |
argMin(col, val) |
Value of col at min val |
SELECT argMin(name, score) FROM students |
topK(N)(col) |
Most frequent N values |
SELECT topK(10)(search_term) FROM queries |
String Functions
| Function |
Description |
Example |
lower(s) |
Lowercase |
SELECT lower('Hello') → 'hello' |
upper(s) |
Uppercase |
SELECT upper('Hello') → 'HELLO' |
trim(s) |
Remove whitespace |
SELECT trim(' hi ') → 'hi' |
length(s) |
String length |
SELECT length('hello') → 5 |
substring(s, offset, length) |
Extract substring |
SELECT substring('hello', 1, 3) → 'hel' |
concat(a, b, ...) |
Concatenate |
SELECT concat(first, ' ', last) |
like(s, pattern) |
LIKE match |
WHERE like(email, '%@gmail.com') |
match(s, pattern) |
Regex match |
WHERE match(url, '^https?://') |
extract(s, pattern) |
Regex extract |
SELECT extract(url, '://([^/]+)') |
replaceAll(s, from, to) |
Replace all occurrences |
SELECT replaceAll(text, '\n', ' ') |
replaceOne(s, from, to) |
Replace first occurrence |
SELECT replaceOne(s, 'old', 'new') |
splitByChar(sep, s) |
Split string to array |
SELECT splitByChar(',', 'a,b,c') |
splitByString(sep, s) |
Split by substring |
SELECT splitByString('::', path) |
format(template, ...) |
Format string |
SELECT format('{} - {}', name, dept) |
reverse(s) |
Reverse string |
SELECT reverse('hello') → 'olleh' |
base64Encode(s) |
Base64 encode |
SELECT base64Encode('hello') |
base64Decode(s) |
Base64 decode |
SELECT base64Decode(encoded) |
Date & Time Functions
| Function |
Description |
Example |
today() |
Current date |
WHERE date = today() |
now() |
Current datetime |
SELECT now() |
toDate(x) |
Convert to Date |
SELECT toDate('2025-01-15') |
toDateTime(x) |
Convert to DateTime |
SELECT toDateTime('2025-01-15 10:30:00') |
toYear(d) |
Extract year |
SELECT toYear(order_date) |
toMonth(d) |
Extract month |
SELECT toMonth(order_date) |
toDayOfWeek(d) |
Day of week (1=Mon) |
SELECT toDayOfWeek(date) |
toDayOfYear(d) |
Day of year |
SELECT toDayOfYear(date) |
toHour(dt) |
Extract hour |
SELECT toHour(timestamp) |
toMinute(dt) |
Extract minute |
SELECT toMinute(timestamp) |
dateDiff(unit, d1, d2) |
Date difference |
SELECT dateDiff('day', start, end) |
dateAdd(unit, n, d) |
Add to date |
SELECT dateAdd('month', 1, today()) |
dateSub(unit, n, d) |
Subtract from date |
SELECT dateSub('day', 7, today()) |
formatDateTime(dt, fmt) |
Format datetime |
SELECT formatDateTime(now(), '%Y-%m-%d %H:%M') |
toStartOfMonth(d) |
First day of month |
SELECT toStartOfMonth(date) |
toStartOfWeek(d) |
First day of week |
SELECT toStartOfWeek(date) |
toStartOfHour(dt) |
Truncate to hour |
SELECT toStartOfHour(timestamp) |
toMonday(d) |
Previous Monday |
SELECT toMonday(date) |
Date units for dateDiff/dateAdd/dateSub: 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'.
Type Conversion
| Function |
Description |
Example |
toInt32(x) |
Convert to Int32 |
SELECT toInt32('42') |
toUInt64(x) |
Convert to UInt64 |
SELECT toUInt64(id) |
toFloat64(x) |
Convert to Float64 |
SELECT toFloat64('3.14') |
toString(x) |
Convert to String |
SELECT toString(123) |
CAST(x AS Type) |
SQL-style cast |
SELECT CAST(price AS Decimal(10,2)) |
toFixedString(s, n) |
Fixed-length string |
SELECT toFixedString(code, 3) |
toDecimal64(x, s) |
Decimal with scale |
SELECT toDecimal64(price, 2) |
parseDateTimeBestEffort(s) |
Smart datetime parse |
SELECT parseDateTimeBestEffort('Jan 15 2025') |
toTypeName(x) |
Get type name |
SELECT toTypeName(column) |
Conditional Functions
| Function |
Description |
Example |
if(cond, then, else) |
Ternary |
SELECT if(age >= 18, 'adult', 'minor') |
multiIf(c1,v1, c2,v2, ..., default) |
Multi-branch |
SELECT multiIf(x>100,'high', x>50,'mid', 'low') |
CASE WHEN ... THEN ... END |
SQL CASE |
CASE WHEN status=1 THEN 'active' ELSE 'inactive' END |
coalesce(a, b, ...) |
First non-null |
SELECT coalesce(nickname, name, 'Unknown') |
nullIf(a, b) |
NULL if a=b |
SELECT nullIf(value, 0) |
ifNull(x, alt) |
Replace NULL |
SELECT ifNull(email, 'no-email') |
isNull(x) |
Check NULL |
WHERE isNull(deleted_at) |
isNotNull(x) |
Check not NULL |
WHERE isNotNull(email) |
Array Functions
| Function |
Description |
Example |
arrayJoin(arr) |
Expand array to rows |
SELECT arrayJoin([1, 2, 3]) |
length(arr) |
Array length |
SELECT length(tags) |
arrayMap(f, arr) |
Transform elements |
SELECT arrayMap(x -> x * 2, [1, 2, 3]) |
arrayFilter(f, arr) |
Filter elements |
SELECT arrayFilter(x -> x > 1, [1, 2, 3]) |
arrayExists(f, arr) |
Any element matches |
WHERE arrayExists(x -> x = 'admin', roles) |
arrayAll(f, arr) |
All elements match |
WHERE arrayAll(x -> x > 0, scores) |
arraySort(arr) |
Sort array |
SELECT arraySort([3, 1, 2]) → [1, 2, 3] |
arrayDistinct(arr) |
Unique elements |
SELECT arrayDistinct(tags) |
arrayConcat(a, b) |
Merge arrays |
SELECT arrayConcat([1, 2], [3, 4]) |
has(arr, elem) |
Contains element |
WHERE has(tags, 'important') |
indexOf(arr, elem) |
Find element index |
SELECT indexOf(arr, 'target') |
arraySlice(arr, offset, length) |
Sub-array |
SELECT arraySlice(arr, 1, 3) |
JSON Functions
| Function |
Description |
Example |
JSONExtract(json, key, Type) |
Extract typed value |
SELECT JSONExtract(data, 'age', 'Int32') |
JSONExtractString(json, key) |
Extract as string |
SELECT JSONExtractString(data, 'name') |
JSONExtractInt(json, key) |
Extract as integer |
SELECT JSONExtractInt(data, 'count') |
JSONExtractFloat(json, key) |
Extract as float |
SELECT JSONExtractFloat(data, 'price') |
JSONExtractBool(json, key) |
Extract as boolean |
SELECT JSONExtractBool(data, 'active') |
JSONExtractArrayRaw(json, key) |
Extract array as strings |
SELECT JSONExtractArrayRaw(data, 'tags') |
simpleJSONExtractString(json, key) |
Fast string extract (flat JSON) |
SELECT simpleJSONExtractString(log, 'level') |
JSONHas(json, key) |
Key exists |
WHERE JSONHas(data, 'email') |
JSONLength(json, key) |
Array/object length |
SELECT JSONLength(data, 'items') |
JSONType(json, key) |
Value type |
SELECT JSONType(data, 'value') |
Nested access: Use path syntax: JSONExtractString(data, 'user', 'address', 'city')
Window Functions
Window functions compute values across a set of rows related to the current row.
Syntax
function() OVER (
[PARTITION BY col1, col2, ...]
[ORDER BY col1 [ASC|DESC], ...]
[ROWS|RANGE BETWEEN ... AND ...]
)
Ranking Functions
| Function |
Description |
row_number() |
Sequential number (no ties) |
rank() |
Rank with gaps for ties |
dense_rank() |
Rank without gaps |
ntile(n) |
Distribute into n buckets |
SELECT name, dept, salary,
row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
rank() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees
Value Functions
| Function |
Description |
lag(col, offset, default) |
Previous row value |
lead(col, offset, default) |
Next row value |
first_value(col) |
First value in window |
last_value(col) |
Last value in window |
SELECT date, revenue,
lag(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue,
revenue - lag(revenue, 1, 0) OVER (ORDER BY date) AS daily_change
FROM daily_sales
Aggregate as Window
SELECT date, revenue,
sum(revenue) OVER (ORDER BY date) AS cumulative,
avg(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily_sales
