This example shows you how to implement sum and maximum value aggregators in a SQL-GR function.
This example consists of the following files:
To create the input table for this example, launch ACT on your Queen and enter these SQL statements:
drop table if exists agg_example; create table agg_example(a int) distribute by hash(a); insert into agg_example values (1), (2), (3);
To run the global_agg_example SQL-GR function, in ACT, enter this SQL-GR query:
select * from global_agg_example(on agg_example partition by a) order by 1;
This query generates the following output:
result ------------------------------------------------------------ Value of max at initialization, on vertex 1: NULL Value of max at initialization, on vertex 2: NULL Value of max at initialization, on vertex 3: NULL Value of max at iteration 0, on vertex 1: 3 Value of max at iteration 0, on vertex 2: 3 Value of max at iteration 0, on vertex 3: 3 Value of max at iteration 1, on vertex 1: 3 Value of max at iteration 1, on vertex 2: 3 Value of max at iteration 1, on vertex 3: 3 Value of max at iteration 2, on vertex 1: 3 Value of max at iteration 2, on vertex 2: 3 Value of max at iteration 2, on vertex 3: 3 Value of max at the emit phase, on vertex 1: 3 Value of max at the emit phase, on vertex 2: 3 Value of max at the emit phase, on vertex 3: 3 Value of sum_continuous at initialization, on vertex 1: 0 Value of sum_continuous at initialization, on vertex 2: 0 Value of sum_continuous at initialization, on vertex 3: 0 Value of sum_continuous at iteration 0, on vertex 1: -3 Value of sum_continuous at iteration 0, on vertex 2: -3 Value of sum_continuous at iteration 0, on vertex 3: -3 Value of sum_continuous at iteration 1, on vertex 1: 9 Value of sum_continuous at iteration 1, on vertex 2: 9 Value of sum_continuous at iteration 1, on vertex 3: 9 Value of sum_continuous at iteration 2, on vertex 1: 22 Value of sum_continuous at iteration 2, on vertex 2: 22 Value of sum_continuous at iteration 2, on vertex 3: 22 Value of sum_continuous at the emit phase, on vertex 1: 36 Value of sum_continuous at the emit phase, on vertex 2: 36 Value of sum_continuous at the emit phase, on vertex 3: 36 Value of sum_stepwise at initialization, on vertex 1: 0 Value of sum_stepwise at initialization, on vertex 2: 0 Value of sum_stepwise at initialization, on vertex 3: 0 Value of sum_stepwise at iteration 0, on vertex 1: -3 Value of sum_stepwise at iteration 0, on vertex 2: -3 Value of sum_stepwise at iteration 0, on vertex 3: -3 Value of sum_stepwise at iteration 1, on vertex 1: 12 Value of sum_stepwise at iteration 1, on vertex 2: 12 Value of sum_stepwise at iteration 1, on vertex 3: 12 Value of sum_stepwise at iteration 2, on vertex 1: 13 Value of sum_stepwise at iteration 2, on vertex 2: 13 Value of sum_stepwise at iteration 2, on vertex 3: 13 Value of sum_stepwise at the emit phase, on vertex 1: 14 Value of sum_stepwise at the emit phase, on vertex 2: 14 Value of sum_stepwise at the emit phase, on vertex 3: 14 (45 rows)