APPROX_COUNT_DISTINCT

​

Syntax

​

Aggregate

approx_count_distinct(value)

​

Window

approx_count_distinct(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

​

Arguments

​

Aggregate and window

​

Returns

• Type: BIGINT
• NULL behavior: NULL values are ignored.
• Empty input: Returns zero on empty input.

​

Properties

• Approximation bound: Standard error is 0.8125%. This describes typical statistical variation, not a guaranteed maximum error.

​

Usage notes

• TINYINT and SMALLINT inputs return an exact count. Counts for other supported types are estimates.
• All floating-point NaN values count as one distinct value. Positive and negative zero also count as the same value.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Count distinct non-NULL values

WITH t(v) AS (VALUES (CAST(1 AS SMALLINT)), (CAST(2 AS SMALLINT)), (CAST(2 AS SMALLINT)), (CAST(NULL AS SMALLINT))) SELECT approx_count_distinct(v) AS result FROM t;

​

Count distinct strings per group

WITH sample(grp, v) AS (VALUES (1, 'red'), (1, 'red'), (1, 'blue'), (2, CAST(NULL AS VARCHAR)))
SELECT grp, approx_count_distinct(v) AS distinct_values
FROM sample GROUP BY grp ORDER BY grp;

​

Limitations

• The error level is fixed. An argument to configure relative standard deviation is not accepted.
• Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.
• ARRAY, MAP, and STRUCT inputs are not supported.