# Function reference

SQL queries may only call functions on this allowlist. Anything else is rejected before execution with `Function not allowed: <name>`.

The live list is served by the REST API, so it is the authoritative answer if this page and your deployment disagree:

```shell
curl --request GET '{API_URL}/sql/v1/catalog' \
  --header 'Authorization: Bearer <your-api-key>'
```

## Where each function works

Queries route to one of two backends depending on which tables they read: the telemetry tables (`points_double`, `points_int`, `points_string`, `points_struct`, `logs`, `channels`) or the metadata tables (`assets`, `runs`, `run_assets`, `datasets`, `events`, `event_assets`).

Most functions work on both. Some work only on the telemetry backend, and calling one against metadata tables fails with `Function <NAME> is not supported for metadata-only queries`. Those are marked **Telemetry only** below.

One function, `CURRENT_TIME`, is the reverse: it works only on the metadata backend, and calling it over telemetry tables fails with `Function CURRENT_TIME is not supported for telemetry queries`. It is marked **Metadata only** below.

The check applies per subtree, not per query, so in a query spanning both backends a telemetry-only function is fine over the telemetry side.

## Aggregate functions

| Function | Notes |
| --- | --- |
| `AVG(numeric)` |  |
| `COUNT(*)`, `COUNT(expr)` |  |
| `MAX(expr)` |  |
| `MIN(expr)` |  |
| `SUM(numeric)` |  |
| `STDDEV_POP(numeric)` | Population standard deviation. |
| `STDDEV_SAMP(numeric)` | Sample standard deviation. |
| `STDDEV(numeric)` | Synonym for `STDDEV_SAMP`. |
| `VAR_POP(numeric)` | Population variance. |
| `VAR_SAMP(numeric)` | Sample variance. |
| `RMS(numeric)` | Root mean square. |
| `PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY numeric)` | Continuous percentile. `fraction` is a number from 0 to 1, so `PERCENTILE_CONT(0.5)` is the median. |
| `MIN_BY(value, key)` | **Telemetry only.** Returns `value` from the row with the smallest `key`. |
| `MAX_BY(value, key)` | **Telemetry only.** Returns `value` from the row with the largest `key`. |
| `STRING_AGG(value, separator)` | Concatenates `value` across the group, joined by `separator`. The separator is required, and `ORDER BY` inside the call is rejected. |
| `ARRAY_AGG(value)` | **Telemetry only.** Collects `value` across the group into an array. `ORDER BY` inside the call is rejected. |

`MIN_BY` and `MAX_BY` answer “what was the reading when something else was at its highest or lowest,” which plain `MIN` and `MAX` cannot express:

```sql
SELECT MAX_BY(value, ts) AS last_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
```

`PERCENTILE_CONT` takes its column in a `WITHIN GROUP` clause rather than as an argument:

```sql
SELECT channel,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
GROUP BY channel
```

## Numeric functions

| Function | Notes |
| --- | --- |
| `ABS(numeric)` |  |
| `CEIL(numeric)` | `CEILING` is accepted as a synonym. |
| `FLOOR(numeric)` |  |
| `LN(numeric)` |  |
| `LOG10(numeric)` |  |
| `MOD(numeric, numeric)` |  |
| `POWER(numeric, numeric)` |  |
| `ROUND(numeric [, scale])` | Rounds to the nearest integer, or to `scale` decimal places when specified. |
| `SQRT(numeric)` |  |
| `IS_NAN(numeric)` | Returns whether the value is NaN. |
| `UNHEX(string)` | **Telemetry only.** Parses a hexadecimal string and returns `BIGINT`. |
| `SIN(numeric)` |  |
| `COS(numeric)` |  |
| `TAN(numeric)` |  |
| `ASIN(numeric)` |  |
| `ACOS(numeric)` |  |
| `ATAN(numeric)` |  |
| `ATAN2(numeric, numeric)` |  |
| `RADIANS(numeric)` |  |
| `DEGREES(numeric)` |  |

## String functions

| Function | Notes |
| --- | --- |
| `CHAR_LENGTH(string)` |  |
| `CHARACTER_LENGTH(string)` | Synonym for `CHAR_LENGTH`. |
| `HEX(string | numeric)` | **Telemetry only.** Returns the hexadecimal representation. Numeric arguments are cast to a 64-bit integer first, so a value that doesn’t fit returns `NULL`. |
| `INITCAP(string)` |  |
| `LOWER(string)` |  |
| `MAP_KEYS(map)` | Returns the keys of a map column as an array of strings. |
| `OVERLAY(string PLACING string FROM int [FOR int])` |  |
| `POSITION(substring IN string)` |  |
| `REGEXP_EXTRACT(string, pattern [, group])` | **Telemetry only.** Returns the first match of the regular expression `pattern` in `string`, or its capture group `group`. With no `group`, returns capture group 1 if the pattern has one. Returns an empty string when there is no match. |
| `REGEXP_LIKE(string, pattern)` | **Telemetry only.** Returns whether the regular expression `pattern` matches anywhere in `string`. |
| `SUBSTRING(string FROM int [FOR int])` |  |
| `TRIM([LEADING | TRAILING | BOTH] [chars FROM] string)` |  |
| `UPPER(string)` |  |

## Date and time functions

| Function | Notes |
| --- | --- |
| `CURRENT_DATE` |  |
| `CURRENT_TIME` | **Metadata only.** Not available over telemetry tables. |
| `CURRENT_TIMESTAMP` |  |
| `EXTRACT(field FROM datetime)` |  |
| `DATE_BIN(stride, source, origin)` | **Telemetry only.** Truncates `source` down to a multiple of `stride` measured from `origin`. Returns the type of `source`. |
| `DATE_TRUNC(unit, datetime)` | Truncates `datetime` down to `unit`. `unit` must be a string literal: `millisecond`, `microsecond`, `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, or `year`. |
| `TIMESTAMPDIFF(unit, start, end)` | Returns the whole number of `unit` intervals from `start` to `end` as `BIGINT`. `unit` is a keyword, such as `TIMESTAMPDIFF(MINUTE, start_time, end_time)`: `NANOSECOND`, `MICROSECOND`, `MILLISECOND`, `SECOND`, `MINUTE`, `HOUR`, `DAY`, or `WEEK`. |

`DATE_BIN` takes the interval **first**, which is easy to get backwards:

```sql
SELECT DATE_BIN(INTERVAL '1' MINUTE, ts, TIMESTAMP '1970-01-01 00:00:00') AS bucket,
       AVG(value) AS mean_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
GROUP BY bucket
ORDER BY bucket
```

Use `TIMESTAMPDIFF` for the time between two timestamps. Subtracting one timestamp from another is rejected.

## Conditional functions

| Function | Notes |
| --- | --- |
| `NULLIF(expr, expr)` |  |
| `COALESCE(expr [, expr]...)` | Returns the first non-null argument. |
| `GREATEST(expr [, expr]...)` | Returns the largest argument, ignoring nulls. |
| `LEAST(expr [, expr]...)` | Returns the smallest argument, ignoring nulls. |

## Window functions

These are called with an `OVER` clause, and every one of them requires an `ORDER BY` inside it.

| Function | Notes |
| --- | --- |
| `LAG(value [, offset [, default]])` | Value from a preceding row in the window. |
| `LEAD(value [, offset [, default]])` | Value from a following row in the window. |
| `FIRST_VALUE(value)` | Value from the first row in the window. |
| `LAST_VALUE(value)` | Value from the last row in the window. |
| `ROW_NUMBER()` | Sequential number within the window. |
| `DERIVATIVE(numeric)` | **Telemetry only.** Rate of change of the value per second. |
| `INTEGRAL(numeric [, method])` | **Telemetry only.** Accumulated area under the value over time. `method` is an unquoted keyword: `TRAPEZOID` (default), `L_RIEMANN`, or `R_RIEMANN`. |

`DERIVATIVE` and `INTEGRAL` compute over the ordered window rather than a single row. Their `ORDER BY` must be a single timestamp column:

```sql
SELECT ts,
       DERIVATIVE(value) OVER (ORDER BY ts) AS rate
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
ORDER BY ts
```

`INTEGRAL` defaults to trapezoidal integration. Pass a method keyword to select a different rule:

```sql
SELECT ts,
       INTEGRAL(value) OVER (ORDER BY ts) AS area_trapezoid,
       INTEGRAL(value, L_RIEMANN) OVER (ORDER BY ts) AS area_left,
       INTEGRAL(value, R_RIEMANN) OVER (ORDER BY ts) AS area_right
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
ORDER BY ts
```

## Bitwise functions

All bitwise functions are **telemetry only**, take integer-domain operands, and return `BIGINT`.

| Function | Notes |
| --- | --- |
| `BIT_AND(value, operand)` |  |
| `BIT_OR(value, operand)` |  |
| `BIT_XOR(value, operand)` |  |
| `BIT_SHIFT_LEFT(value, positions)` | `positions` is from 0 to 63. |
| `BIT_SHIFT_RIGHT(value, positions)` | `positions` is from 0 to 63. |
| `BIT_TEST(value, index)` | Tests the bit at `index`, from 0 to 63. |

These are two-operand scalar functions, not aggregates. That differs from the SQL standard, where `BIT_AND` and `BIT_OR` aggregate over a group. To combine bits across rows, aggregate the result yourself.

## Operators

Standard SQL operators and clauses are unaffected by the function allowlist: comparison and arithmetic operators, `AND`, `OR`, `NOT`, `IN`, `BETWEEN`, `LIKE`, `IS NULL`, `CASE`, and `CAST` are all available.

On the telemetry tables, `~` and `!~` test a string against a regular expression. Write casts as `CAST(expr AS type)`, since Postgres-style `::` casts are rejected.

## Next steps

- [Writing queries](https://dev.nominal.io/sql-guides/writing-queries.md): query patterns and examples.
- [SQL REST API](https://dev.nominal.io/sql-reference/rest-api.md): the endpoint contract and error codes.