UNIQ is an aggregate function that calculates the number of distinct values. It is similar to the COUNT DISTINCT function. This topic describes how to use the UNIQ function in Hologres.
Limits
Only Hologres V1.3 and later support the UNIQ function. If the version of your instance is earlier than V1.3, manually upgrade your Hologres instance in the Hologres console or join the DingTalk group for technical support. For more information about how to manually upgrade your Hologres instance in the Hologres console, see the Manual upgrade (beta) section in "Instance upgrades". For more information about how to obtain technical support, see Obtain online support for Hologres.
The UNIQ function delivers better performance than the COUNT DISTINCT function only if the SQL statement contains the GROUP BY clause and values of the field based on which the GROUP BY operation is performed are evenly distributed.
Syntax
The UNIQ function is used to calculate the number of rows that have distinct values in a column. Duplicated values are counted as a single value.
UNIQ ( < column > );The following table describes the parameter in the function.
Parameter | Description |
column | The column for which you want to calculate the number of rows that have distinct values. The following data types are supported: SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, TEXT, VARCHAR, TIMESTAMP, TIMESTAMPTZ, DATE, TIMETZ, and UUID. |
Take note of the following items when you use the function:
In most cases, the UNIQ function has better performance than the COUNT DISTINCT function if the GROUP BY key has high cardinality. In addition, the UNIQ function occupies less memory space than the COUNT DISTINCT function. If the memory space is insufficient for using the COUNT DISTINCT function, you can use the UNIQ function instead.
The performance of the COUNT DISTINCT function is optimized in Hologres V2.1 and later for various scenarios, such as scenarios where one or more COUNT DISTINCT functions are used, data skew occurs, or no GROUP BY clause is used. In these scenarios in Hologres V2.1 or later, you do not need to use the UNIQ function because the COUNT DISTINCT function delivers high performance. For more information, see the Optimize the COUNT DISTINCT function section in "Optimize performance of queries on Hologres internal tables".
Examples
Execute the following statement to calculate the number of rows that have distinct values in the O_CUSTKEY column of the ORDERS table:
SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;
-- Execute the following statement to group data by O_ORDERSTATUS and calculate the number of rows that have distinct values in the O_CUSTKEY column in each group:
SELECT O_ORDERSTATUS, UNIQ ( O_CUSTKEY ) FROM ORDERS GROUP BY O_ORDERSTATUS;