Single DISTINCT Aggregates
Vertica computes a DISTINCT
aggregate by first removing all duplicate values of the aggregate's argument to find the distinct values. Then it computes the aggregate.
For example, you can rewrite the following query:
SELECT a, b, COUNT(DISTINCT c) AS dcnt FROM table1 GROUP BY a, b;
as:
SELECT a, b, COUNT(dcnt) FROM (SELECT a, b, c AS dcnt FROM table1 GROUP BY a, b, c) GROUP BY a, b;
For fastest execution, apply the optimization techniques for GROUP BY queries.