# OLAP Functions¶

## OLAP Basics¶

Each dimension of an OLAP cube comprises a set of related members; for example, customer, order date and order priority. Members of a dimension are often organized into a hierarchy of levels that show the parent-child relationships of the levels within a dimension, for example order date may have a hierarchy of year, month and day. Because the number of cells in a cube is the product of the size of each dimension, OLAP cubes can be very large.

## OLAP Data Set¶

All the examples and code snippets in the OLAP functions use this small subset of order data:

 o_orderkey o_custkey o_totalprice o_orderdat o_orderpriority 1 1 100.00 2008-03-03 2-HIGH 2 2 10.00 2008-03-03 2-HIGH 3 3 20.00 2008-03-03 2-HIGH 4 3 400.00 2008-03-04 1-LOW 5 4 30.00 2008-03-04 2-HIGH 6 5 10.00 2008-03-04 1-LOW 7 5 5.00 2008-03-04 2-HIGH 8 1 5.00 2008-03-04 2-HIGH 9 1 200.00 2008-03-04 1-LOW 10 3 50.00 2008-03-05 2-HIGH 11 2 60.00 2008-03-05 2-HIGH 12 2 70.00 2008-03-05 2-HIGH 13 4 5.00 2008-03-05 2-HIGH 14 4 1.00 2008-03-05 2-HIGH 15 1 10.00 2008-03-06 2-HIGH 16 1 40.00 2008-03-06 1-LOW 17 1 30.00 2008-03-06 2-HIGH 18 3 200.00 2008-03-06 2-HIGH 19 2 100.00 2008-03-06 2-HIGH 20 2 500.00 2008-03-06 2-HIGH

## Grouping Sets¶

The GROUPING SETS operator allows multiple grouping clauses to be specified in a single statement. This can be thought of as the union of two or more groups of rows into a single result set. It is logically equivalent to the union of multiple sub-selects, with the GROUP BY clause in each sub-select corresponding to one grouping set

The table below shows some GROUP BY statements using GROUPING SETS and the equivalent required using UNION and sub-selections

Using GROUPING SETS

Using UNION

SELECT …

FROM ordertab

GROUP BY

GROUPING SETS

((o_custkey,

o_orderdate,

o_orderpriority))

SELECT …

FROM ordertab

GROUP BY

o_cutkey,

o_orderdate,

o_orderpriority

SELECT …

FROM ordertab

GROUP BY

GROUPING SETS

(o_custkey,

o_orderdate,

o_orderpriority)

SELECT …

FROM ordertab

GROUP BY o_custkey

UNION ALL

GROUP BY o_orderdate

UNION ALL

GROUP BY o_orderpriority

SELECT …

FROM ordertab

GROUP BY

GROUPING SETS

(o_custkey,

(o_orderdate,

o_orderpriority))

SELECT …

FROM ordertab

GROUP BY o_custkey

UNION ALL

GROUP BY o_orderdate,

o_orderpriority

Multiple GROUPING SETS in the same GROUP BY are combined together as if they were simple fields in a GROUP BY LIST:

Using GROUPING SETS

Using UNION

SELECT …

FROM ordertab

GROUP BY

GROUPING SETS

(o_custkey),

GROUPING SETS

(o_orderdate),

GROUPING SETS

(o_orderpriority)

SELECT …

FROM ordertab

GROUP BY

o_cutkey,

o_orderdate,

o_orderpriority

SELECT …

FROM ordertab

GROUP BY

GROUPING SETS

(o_custkey),

GROUPING SETS

((o_orderdate,

o_orderpriority))

SELECT …

FROM ordertab

GROUP BY

o_cutkey,

o_orderdate,

o_orderpriority

SELECT …

FROM ordertab

GROUP BY

GROUPING SETS

(o_custkey),

GROUPING SETS

(o_orderdate,

o_orderpriority)

SELECT …

FROM ordertab

GROUP BY

o_custkey,

o_orderdate

UNION ALL

SELECT …

FROM ordertab

GROUP BY

o_custkey,

o_orderpriority

For further details on OLAP functions also see ROLLUP, CUBE and the GROUPING function.