Skip to main content

Categories: Aggregate, Window

NDV

Returns an approximate distinct value number, similar to COUNT(DISTINCT col). NDV can return results faster than using the combination of COUNT and DISTINCT while using a constant amount of memory, resulting in less memory usage for columns with high cardinality.

Syntax​

NDV(expression numeric [, scale numeric]) → bigint​

  • expression: The name of the column whose records you wish to evaluate.
  • scale (optional): Argument that maps to a precision used by the HyperLogLog (HLL) algorithm based on the mapping formula: precision = scale +8. Enter an integer in the range from 1 to 10.

Examples

NDV example 
SELECT NDV(column_name)
-- 163
NDV example 
SELECT NDV(column_name, 1)
-- 162

Usage Notes​

The NDV function is used internally by the COMPUTE STATS statement for computing the number of distinct values in a column. This function might not reflect the precise number of different values in the column, especially if the cardinality is very low or very high. This function accepts the DISTINCT and ALL keywords: NDV([DISTINCT | ALL] expression).