Resolving Extended GROUP BY Operator Specifications
How Teradata Database Resolves Multiple Grouping Sets Specifications
Teradata Database resolves multiple grouping sets specifications by concatenating pairwise the individual elements of the different sets. For information about grouping sets specifications, see “CUBE Grouping Set Option” on page 154, “GROUPING SETS Option” on page 157, and “ROLLUP Grouping Set Option” on page 160.
This is trivial in the case of a simple grouping specification because it is a set containing only one element. However, when applied to more complicated specifications that contain multiple grouping specification elements, the resolution is more complicated.
For example, the following two GROUP BY clauses are semantically identical:
GROUP BY GROUPING SETS ((A,B), (C)), GROUPING SETS ((X,Y),())
GROUP BY GROUPING SETS ((A,B,X,Y),(A,B),(C,X,Y),(C))
Example : Semantically Identical Grouping Sets Specifications and Their Resolution
The following three queries return the same results set because they are semantically identical.
SELECT y,m,r, SUM(u)
FROM test
GROUP BY CUBE(y,m), CUBE(r)
ORDER BY 1,2,3;
SELECT y,m,r,SUM(u)
FROM test
GROUP BY CUBE(y,m,r)
ORDER BY 1,2,3;
SELECT y,m,r,SUM(u)
FROM test
GROUP BY GROUPING SETS(y,()), GROUPING SETS(m,()),
GROUPING SETS(r,())
ORDER BY 1,2,3;
The following three queries return the same results set because they are semantically identical.
SELECT y, m, r, s, SUM(u)
FROM test
GROUP BY ROLLUP(y,m),ROLLUP(r,s)
ORDER BY 1,2,3,4;
SELECT y, m, r, s, SUM(u)
FROM test
GROUP BY GROUPING SETS((y, m),(),y),ROLLUP(r,s)
ORDER BY 1,2,3,4;
SELECT y, m, r, s, SUM(u)
FROM test
GROUP BY GROUPING SETS((y,m),(),y),GROUPING SETS((),r,(r,s))
ORDER BY 1,2,3,4;