Setting the file. One moment.
Subchapter 2.2
references/sql-functions.mdMarkdown10 KBView on GitHub
Commonly used SQL functions available in chdb. For the full list, see ClickHouse documentation.
| 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 |
| 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) |
| 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'.
| 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) |
| 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) |
| 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) |
| 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 compute values across a set of rows related to the current row.
function() OVER (
[PARTITION BY col1, col2, ...]
[ORDER BY col1 [ASC|DESC], ...]
[ROWS|RANGE BETWEEN ... AND ...]
)| 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| 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_salesSELECT 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