The APPROXCOUNTDISTINCT
function counts the number of distinct values for an attribute.
APPROXCOUNTDISTINCT
is similar to the COUNTDISTINCT
function except that it is allowed to produce an approximation for the number of distinct values in certain circumstances. The APPROXCOUNTDISTINCT
function returns the number of unique values in a field for each GROUP BY
result. APPROXCOUNTDISTINCT
can be used for both single-assign and multi-assigned attributes.
The APPROXCOUNTDISTINCT
function uses the HyperLogLog algorithm to calculate the a set of refinements. If the number of distinct values is low, then the results will be accurate; if the number of distinct values is high, the results will be an approximation.
APPROXCOUNTDISTINCT
will also evaluate a record with an empty set (that is, an empty set is returned for any record that does not have an assignment for the specified multi-assign attribute).
APPROXCOUNTDISTINCT syntax
APPROXCOUNTDISTINCT
function is:
APPROXCOUNTDISTINCT(<attribute>)
where attribute is either a multi-assign or single-assign attribute.APPROXCOUNTDISTINCT example
Body WineID -------------------------- { Silky, Tannins } 3 { Robust, Tannins } 4 { Silky, Tannins } 5 { Robust } 6 { Robust } 8 { Silky, Tannins } 9 { Silky, Tannins } 12 { Silky, Tannins } 16 { Silky, Tannins } 18 --------------------------
RETURN Result AS SELECT APPROXCOUNTDISTINCT (Body) AS Total FROM WineState WHERE WineType = 'Red' GROUP BY WineType
Total=3, WineType=Red
Thus, there are three sets of distinct values for the Body attribute, when grouped by the WineType attribute.