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.
Usage notes
Only Hologres V1.3 and later support the UNIQ function. If the version of your instance is earlier than V1.3, contact technical support in the DingTalk group (ID 32314975) to apply for an instance update.
Syntax
The UNIQ function can be used to calculate the number of rows that have distinct values in a column. Duplicated values are counted as a single value.
UNIQ ( < column > );| 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. |
- In general, the UNIQ function performs better than the COUNT DISTINCT function if the GROUP BY key has high cardinality. In addition, the UNIQ function occupies less memory than the COUNT DISTINCT function. The UNIQ function is suitable if the memory is insufficient for using the COUNT DISTINCT function.
- The performance of the COUNT DISTINCT function is optimized in various scenarios in Hologres V1.3 and later. Specifically, if the number of distinct values that exist is greater than 1, the code is automatically rewritten at the backend to invoke the UNIQ function instead of the COUNT DISTINCT function. The result and syntax remain unchanged and are transparent to the upper layer. This improves query performance.
Example
To calculate the number of rows that have distinct values in the O_CUSTKEY column of the ORDERS table, execute the following statement:
SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;
-- To calculate the number of rows that have distinct values in the O_CUSTKEY column based on different values of the O_ORDERSTATUS column, execute the following statement:
SELECT O_ORDERSTATUS, UNIQ ( O_CUSTKEY ) FROM ORDERS GROUP BY O_ORDERSTATUS;