APPROXIMATE_COUNT_DISTINCT_SYNOPSIS

Summarizes the information of distinct non-NULL values and materializes the result set in a VARBINARY or LONG VARBINARY synopsis object. The calculated result is within a specified range of error tolerance. You save the synopsis object in a Vertica table for use by APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS.

Note: If you are upgrading from a release to Vertica 8.1.1, you must drop the pre-existing synopsis object and recreate it to get the result.

Behavior Type

Immutable

Syntax

APPROXIMATE_COUNT_DISTINCT_SYNOPSIS ( expression[, error‑tolerance] ) 

Parameters

expression Value to evaluate using any data type that supports equality comparison.
error‑tolerance

Numeric value that represents the desired percentage of error tolerance, distributed around the value returned by this function. The smaller the error tolerance, the closer the approximation

You can set error‑tolerance to a minimum value of 0.88, with an error than is lognormally distributed with standard deviation. Vertica imposes no maximum restriction, but any value greater than 5 is implemented with 5% error tolerance.

If you omit this argument, Vertica uses an error tolerance of 1.75(%).

For more details, see APPROXIMATE_COUNT_DISTINCT.

Restrictions

APPROXIMATE_COUNT_DISTINCT_SYNOPSIS and DISTINCT aggregates cannot be in the same query block.

Example

See APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS.