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.