Functions Supported for Live Aggregate Projections
Vertica can aggregate results in live aggregate projections from the following aggregate functions:
Aggregate Functions with DISTINCT
Live aggregate projections can support queries that include aggregate functions qualified with the keyword
DISTINCT. The following requirements apply:
- The aggregated expression must evaluate to a non-constant.
- The projection's
GROUP BYclause must specify the aggregated expression.
For example, the following query uses
SUM(DISTINCT) to calculate the total of all unique salaries in a given region:
SELECT customer_region, SUM(DISTINCT annual_income)::INT FROM customer_dimension GROUP BY customer_region;
This query can use the following live aggregate projection, which specifies the aggregated column (
annual_income) in its
GROUP BY clause:
CREATE PROJECTION public.TotalRegionalIncome ( customer_region, annual_income, Count ) AS SELECT customer_dimension.customer_region, customer_dimension.annual_income, count(*) AS Count FROM public.customer_dimension GROUP BY customer_dimension.customer_region, customer_dimension.annual_income ;
This projection includes the aggregate function
COUNT, which here serves no logical objective; it is included only because live aggregate projections require at least one aggregate function.