APPROX_DISTINCT() GROUP BY ...

All functions > GROUP BY > APPROX_DISTINCT() GROUP BY ...

Returns the approximate number of distinct values in the group.

Syntax

APPROX_DISTINCT(expr [, precision])
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

APPROX_DISTINCT(expr [, precision])
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

Notes

• Uses HyperLogLog algorithm for efficient approximate counting
• Much faster than COUNT(DISTINCT) for large datasets
• Provides probabilistic estimate with controllable error rate
• NULL values are excluded from the count
• Optional second argument is a max standard-error bound
• Typical accuracy without a second argument: within 2-3% of exact count
• Returns 0 for empty groups
• Can be used with WHERE clause to filter before aggregation
• Can be used with GROUP BY clause for grouped aggregation

Examples

-- Approximate distinct count on a small group
RETURN
    f1 := ZIP(ARRAY(1, 2, 3) AS value).TRANSFORM(RETURN APPROX_DISTINCT(value)).UNWRAP_ONE()
;