approx_count_distinct

On this page

Estimates the number of distinct non-NULL values.

Syntax

approx_count_distinct(<expr>)

Arguments

ArgumentTypeDescription
<expr>Any typeInput value.

Returns

Returns int. Ignores NULL values. Returns 0 for empty input or when every value is NULL.

Usage notes

Use count_distinct when an exact count is required.

Examples

Count checkout events and distinct buyers for each campaign:

VALUES
    ('spring', 'buyer-17'), ('spring', 'buyer-17'), ('spring', 'buyer-42'),
    ('loyalty', 'buyer-17'), ('loyalty', 'buyer-63'), ('loyalty', 'buyer-63'), ('loyalty', 'buyer-87')
SELECT $0 AS campaign, $1 AS buyer_id
GROUP BY campaign
AGGREGATE count() AS events, approx_count_distinct(buyer_id) AS buyers
ORDER BY campaign
+----------+--------+--------+
| campaign | events | buyers |
+----------+--------+--------+
| loyalty  | 4      | 3      |
| spring   | 3      | 2      |
+----------+--------+--------+

The estimate matches the exact count in this small sample; larger inputs can produce different estimates. A buyer can appear in more than one campaign, so adding campaign counts can count the same buyer more than once.