Skip to content

Functions

Last updated View as MarkdownAgent setup

Function names are case-insensitive.

Aggregate functions

Function Description
COUNT(*) or COUNT() Count matching rows or represented events. COUNT(expression) is not supported.
SUM(expression) Sum a numeric expression.
AVG(expression) Calculate the arithmetic mean of a numeric expression.
MIN(expression), MAX(expression) Return an exact extremum on an unsampled dataset.
APPROX_MIN(expression), APPROX_MAX(expression) Return the smallest or largest value observed in retained rows of an adaptively sampled dataset.
countIf(condition) Count rows that satisfy a condition.
sumIf(expression, condition) Sum values from rows that satisfy a condition.
avgIf(expression, condition) Average values from rows that satisfy a condition.
first_value(expression), last_value(expression) Return the first or last value in the aggregate input.
argMax(value, expression), argMin(value, expression) Return value from the row with the largest or smallest expression.
topK(expression) Return an array of the most frequent values. Supports ClickHouse-style parameters such as topK(10)(expression).
topKWeighted(expression, weight) Return the most frequent values using explicit weights. Supports parameters such as topKWeighted(10)(expression, weight).
quantileWeighted(level, expression, weight) Calculate a weighted quantile. level must be between 0 and 1.
quantileExactWeighted(level)(expression, weight) Alias form of quantileWeighted.

topK accepts up to three optional parameters before its value expression. topKWeighted accepts the same parameters before its value and weight expressions. The requested top-k size and load factor must each be between 1 and 100.

The compatibility aggregates from countIf through quantileExactWeighted in the table are supported by ClickHouse-backed datasets. Log Explorer-backed datasets support COUNT, SUM, AVG, MIN, MAX, APPROX_MIN, and APPROX_MAX.

Adaptive sampling

For adaptively sampled event and log datasets, the SQL API automatically applies sample weights to COUNT, SUM, AVG, countIf, sumIf, avgIf, and topK. You do not need to use sampleInterval yourself for these functions. Weighted averages exclude null expression values from both the numerator and denominator.

topKWeighted and quantileWeighted use the explicit weight argument supplied by the query. Pass sampleInterval when you want that weight to represent the dataset's adaptive sampling.

SELECT countIf(edgeResponseStatus >= 500) AS server_errors
FROM events.httpRequests
WHERE accountTag = '<ACCOUNT_TAG>'
  AND timestamp >= NOW() - INTERVAL '1' HOUR

Exact MIN and MAX are rejected for adaptively sampled event and log datasets. Use APPROX_MIN and APPROX_MAX to calculate extrema from retained sample rows. These functions cannot reconstruct values from events that sampling did not retain.

argMax and argMin are also rejected on adaptively sampled datasets, except Workers Analytics Engine datasets, which preserve their existing compatibility behavior.

DISTINCT aggregates

The following forms are supported by ClickHouse-backed unsampled datasets:

COUNT(DISTINCT expression)
SUM(DISTINCT expression)
AVG(DISTINCT expression)

Other DISTINCT aggregates and row-level SELECT DISTINCT are not supported. Adaptively sampled datasets reject DISTINCT aggregates because omitted values cannot be reconstructed. Workers Analytics Engine datasets retain COUNT, SUM, and AVG DISTINCT for compatibility.

Log Explorer-backed datasets support COUNT(DISTINCT expression) and SUM(DISTINCT expression), but not AVG(DISTINCT expression).

State datasets

State dataset rows represent observations of a system's state rather than unique events. Unrestricted aggregation could produce misleading or nonsensical results, so each state dataset exposes a valid_aggregations list through introspection. The list can contain count, sum, avg, min, or max.

countIf, sumIf, and avgIf require the corresponding base aggregation. Other compatibility aggregates, including approximate extrema, are not supported on state datasets.

Scalar functions

The following scalar functions are supported on ClickHouse-backed datasets. Function names are case-insensitive.

Category Functions
Conditional if(condition, then, else)
Numeric intDiv, round, ceil, floor, log, pow
String length, isEmpty, toLower, toUpper, startsWith, endsWith, substring, position, format
Conversion and representation toUInt8, toUInt32, bin, hex
Bitwise bitAnd, bitCount, bitHammingDistance, bitNot, bitOr, bitRotateLeft, bitRotateRight, bitShiftLeft, bitShiftRight, bitTest, bitXor
Date parts date_part, EXTRACT, toYear, toMonth, toDayOfMonth, toDayOfWeek, toHour, toMinute, toSecond, toYYYYMM
Date and time conversion toUnixTimestamp, formatDateTime, toDateTime, toDate, timezone
Time buckets toStartOfInterval, toStartOfDay, toStartOfMinute, toStartOfFiveMinutes, toStartOfTenMinutes, toStartOfFifteenMinutes, toStartOfHour, toStartOfWeek, toStartOfMonth, toStartOfYear
Interval constructors toIntervalSecond, toIntervalMinute, toIntervalHour, toIntervalDay, toIntervalWeek, toIntervalMonth, toIntervalQuarter, toIntervalYear

Supported aliases include lengthUTF8, empty, lower, lowerUTF8, upper, upperUTF8, substr, strpos, ceiling, ln, dayOfWeek, and toStartOfFiveMinute.

Log Explorer-backed datasets support only a subset of these functions: if, isEmpty, startsWith, endsWith, pow, length, toLower, toUpper, position, one-argument ceil and floor, log, toYear, toMonth, toHour, toMinute, toSecond, toDayOfMonth, toDayOfWeek, toYYYYMM, toDate, toStartOfWeek, toStartOfMonth, and toStartOfYear. The API returns HTTP 422 when the selected backend does not support a function.

Current date and time

The API resolves current-time functions once when it plans the query. Repeated uses in one query represent the same instant.

Function Description
NOW() Return the current UTC timestamp.
CURRENT_TIMESTAMP() Equivalent to NOW().
TODAY() Return the start of the current day in UTC.
CURRENT_DATE() Equivalent to TODAY(). 
SELECT CURRENT_TIMESTAMP() AS queried_at, COUNT(*) AS requests
FROM events.httpRequests
WHERE accountTag = '<ACCOUNT_TAG>'
  AND timestamp >= TODAY()

Time buckets and intervals

toStartOfInterval(timestamp, interval[, timezone]) rounds a DateTime or DateTime64(3) value down to the beginning of an interval:

SELECT
  toStartOfInterval(timestamp, INTERVAL '15' MINUTE) AS bucket,
  COUNT(*) AS requests
FROM events.httpRequests
WHERE accountTag = '<ACCOUNT_TAG>'
  AND timestamp >= NOW() - INTERVAL '1' DAY
GROUP BY bucket

The interval must contain a positive integer and one of the following units: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, or NANOSECOND. The optional timezone must be a string literal. toStartOfInterval is not supported for Log Explorer-backed datasets.

Add an interval to or subtract an interval from a timestamp expression:

NOW() - INTERVAL '15' MINUTE
NOW() - INTERVAL '7' DAY

The SQL API rejects scalar and aggregate functions that are not listed on this page.

Was this helpful?