vertica/Vertica-Extension-Packages — pull request #9 by mattsfuller (2011-12-13 02:02:52+00:00)
Title: Group Generator to build ROLLUP, CUBE, GROUPING_SET, and NEW EE Multiple Distinct Aggegate Queries

Matt Fuller submission for the UDx competition.

See compatlib_functions/examples/group_generator.sql
See compatlib_function/mfuller_UDX_group_generator.pptx

The GroupGenerator generates N rows for every input row. N is determined by the argument to the GroupGenerator UDx.

For example, if one wants to group by A, B, C and A, B and A the group generator outputs 3 rows for every input row. Each row will still contain columns for A, B, C. But if a particular group does not include a column, that columns value will be NULL.

Using the Group Generator, we can Group and Aggregate to get the behavior of OLAP functions ROLLUP, CUBE, and GROUPING_SETs.

Additionally we can use the GroupGenerator to compute queries with Multiple Distinct Aggregates. This is important because the GroupGenerator is executed in the New EE. Whereas in 5.0, native Multiple Distinct Aggregates are executed in the Old EE which doesn't perform as well as the New

We may even be able to find other uses for the GroupGenerator. The ability to duplicate inputs rows is likely a useful one.