Aggregating data through expressions

You can create projections where one or more columns are defined by expressions.

1: Querying data through expressions example

You can create projections where one or more columns are defined by expressions. An expression can reference one or more anchor table columns. For example, the following table contains two integer columns, a and b:

=> CREATE TABLE values (a INT, b INT);

You can create a projection with an expression that calculates the value of column c as the product of a and b:

=> CREATE PROJECTION values_product (a, b, c)
   AS SELECT a, b, a*b FROM values SEGMENTED BY HASH(a) ALL NODES KSAFE;

When you load data into this projection, Vertica resolves the expression a*b in column c. You can then query the projection instead of the anchor table. Vertica returns the pre-calculated data and avoids the overhead otherwise incurred by resource-intensive computations.

Using expressions in projections also lets you sort or segment data on the calculated results of an expression instead of sorting on single column values.

Note

If a projection with expressions also includes aggregate functions such as SUM or COUNT, Vertica treats it like a live aggregate projection.

Support for user-defined scalar functions

Important

Currently, support for pre-aggregating UDSF results is limited to C++.

Vertica treats user-defined scalar functions (UDSFs) like other expressions. On each load operation, the UDSF is invoked and returns its results. Vertica stores these results on disk, and returns them when you query the projection directly.

In the following example, the projection points_p1 specifies the UDSF zorder, which is invoked whenever data is loaded in the anchor table points. When data is loaded into the projection, Vertica invokes this function and stores its results for fast access by future queries.

=> CREATE TABLE points(point_id INTEGER, lat NUMERIC(12,9), long NUMERIC(12,9));

=> CREATE PROJECTION points_p1
     AS SELECT point_id, lat, long, zorder(lat, long) zorder FROM points
     ORDER BY zorder(lat, long) SEGMENTED BY hash(point_id) ALL NODES;

Requirements

Any ORDER BY expression must be in the SELECT list.
All projection columns must be named.

Restrictions

MERGE operations must be optimized if they are performed on target tables that have live aggregate projections.
Unlike live aggregate projections, Vertica does not redirect queries with expressions to an equivalent existing projection.
Projection expressions must be immutable—that is, they must always return the same result. For example, a projection cannot include expressions that use TO CHAR (depends on locale) or RANDOM (returns different value at each invocation).
Projection expressions cannot include Vertica meta-functions such as ADVANCE_EPOCH, ANALYZE_STATISTICS, EXPORT_TABLES, or START_REFRESH.

1 - Querying data through expressions example

The following example uses a table that contains two integer columns, a and b:.

The following example uses a table that contains two integer columns, a and b:

=> CREATE TABLE values (a INT, b INT);

You can create a projection with an expression that calculates the value of column c as the product of a and b:

=> CREATE PROJECTION values_product (a, b, c)
   AS SELECT a, b, a*b FROM values SEGMENTED BY HASH(a) ALL NODES KSAFE;
=> COPY values FROM STDIN DELIMITER ',' DIRECT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 3,11
>> 3,55
>> 8,9
>> 8,23
>> 16,41
>> 22,111
>> \.
=>

To query this projection, use the name that Vertica assigns to it or to its buddy projections. For example, the following queries target different instances of the projection defined earlier, and return the same results:

=> SELECT * FROM values_product_b0;
=> SELECT * FROM values_product_b1;

The following example queries the anchor table:

=> SELECT * FROM values;
 a  |  b
----+-----
  3 |  11
  3 |  55
  8 |   9
  8 |  23
 16 |  41
 22 | 111

Given the projection created earlier, querying that projection returns the following values:

VMart=> SELECT * FROM values_product_b0;
 a  |  b  | product
----+-----+---------
  3 |  11 |      33
  3 |  55 |     165
  8 |   9 |      72
  8 |  23 |     184
 16 |  41 |     656
 22 | 111 |    2442