Teradata Advanced Grouping Options

2
584
Teradata advanced grouping

Teradata offers three advanced GROUPING options in addition to the usually used GROUP BY statement:

– GROUP BY GROUPING SETS
– GROUP BY ROLLUP
– GROUP BY CUBE

As an example we take a table containing the following information:

Flights per plane, year and month.

CREATE MULTISET TABLE Flights
(
  PLANE AS BIGINT NOT NULL,
  FLIGHTDATE AS DATE NOT NULL,
  NR_FLIGHTS INTEGER NOT NULL
) PRIMARY INDEX (PLANE);

PlaneFlightDateFlights
12019-02-265
22019-02-262
32019-02-263
12019-02-271
22019-02-272
32019-02-275
12019-02-285
22019-02-281
32019-02-281
12019-03-015
22019-03-013
32019-03-012
12019-03-021
22019-03-021
32019-03-021

GROUP BY GROUPING SETS

SELECT
PLANE,
EXTRACT(MONTH FROM FLIGHTDATE) AS TheMonth,
EXTRACT(YEAR FROM FLIGHTDATE) AS TheYear,
SUM(NR_FLIGHTS) AS NR_FLIGHTS
FROM
FLIGHTS
GROUP BY GROUPING SETS (PLANE,TheMonth,TheYear)
ORDER BY 1 DESC,2 DESC,3 DESC;

PlaneTheMonthTheYearNR_FLIGHTS
3NULLNULL12
2NULLNULL9
1NULLNULL12
NULL3NULL13
NULL2NULL25
NULLNULL201938

GROUP BY GROUPING SETS calculates the number of flights for each plane, each month, each year.

GROUP BY ROLLUP

SELECT
PLANE,
EXTRACT(MONTH FROM FLIGHTDATE) AS TheMonth,
EXTRACT(YEAR FROM FLIGHTDATE) AS TheYear,
SUM(NR_FLIGHTS) AS NR_FLIGHTS
FROM
FLIGHTS
GROUP BY ROLLUP (Plane,TheMonth,TheYear)
ORDER BY 1 DESC,2 DESC,3 DESC;

PlaneTheMonthTheYearNR_FLIGHTS
3320193
33NULL3
3220199
32NULL9
3NULLNULL12
2320194
23NULL4
2220195
22NULL5
2NULLNULL9
1320196
13NULL6
12201911
12NULL11
1NULLNULL17
NULLNULLNULL38

GROUP BY ROLLUP calculates the number of flights for each plane, each distinct month, each month per year, each year plus the grand total.

GROUP BY CUBE

SELECT
PLANE,
EXTRACT(MONTH FROM FLIGHTDATE) AS TheMonth,
EXTRACT(YEAR FROM FLIGHTDATE) AS TheYear,
SUM(NR_FLIGHTS) AS NR_FLIGHTS
FROM
FLIGHTS
GROUP BY CUBE (Plane,TheMonth,TheYear)
ORDER BY 1 DESC,2 DESC,3 DESC;

PlaneTheMonthTheYearNR_FLIGHTS
3320193
33NULL3
3220199
32NULL9
3NULL201912
3NULLNULL12
2320194
23NULL4
2220195
22NULL5
2NULL20199
2NULLNULL9
1320196
13NULL6
12201911
12NULL11
1NULL201917
1NULLNULL17
NULL3201913
NULL3NULL13
NULL2201925
NULL2NULL25
NULLNULL201938
NULLNULLNULL38

Teradata advanced grouping options help to achieve grouping on different levels at the same time, thus increasing performance

Our Reader Score
[Total: 12    Average: 4.3/5]
Teradata Advanced Grouping Options written by DWH Pro Admin on April 28, 2019 average rating 4.3/5 - 12 user ratings

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here