sql-tests/inputs/group-analytics.sql

0001 CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
0002 (1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2)
0003 AS testData(a, b);
0004
0005 -- CUBE on overlapping columns
0006 SELECT a + b, b, SUM(a - b) FROM testData GROUP BY a + b, b WITH CUBE;
0007
0008 SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH CUBE;
0009
0010 -- ROLLUP on overlapping columns
0011 SELECT a + b, b, SUM(a - b) FROM testData GROUP BY a + b, b WITH ROLLUP;
0012
0013 SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH ROLLUP;
0014
0015 CREATE OR REPLACE TEMPORARY VIEW courseSales AS SELECT * FROM VALUES
0016 ("dotNET", 2012, 10000), ("Java", 2012, 20000), ("dotNET", 2012, 5000), ("dotNET", 2013, 48000), ("Java", 2013, 30000)
0017 AS courseSales(course, year, earnings);
0018
0019 -- ROLLUP
0020 SELECT course, year, SUM(earnings) FROM courseSales GROUP BY ROLLUP(course, year) ORDER BY course, year;
0021
0022 -- CUBE
0023 SELECT course, year, SUM(earnings) FROM courseSales GROUP BY CUBE(course, year) ORDER BY course, year;
0024
0025 -- GROUPING SETS
0026 SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(course, year);
0027 SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(course);
0028 SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(year);
0029
0030 -- GROUPING SETS with aggregate functions containing groupBy columns
0031 SELECT course, SUM(earnings) AS sum FROM courseSales
0032 GROUP BY course, earnings GROUPING SETS((), (course), (course, earnings)) ORDER BY course, sum;
0033 SELECT course, SUM(earnings) AS sum, GROUPING_ID(course, earnings) FROM courseSales
0034 GROUP BY course, earnings GROUPING SETS((), (course), (course, earnings)) ORDER BY course, sum;
0035
0036 -- GROUPING/GROUPING_ID
0037 SELECT course, year, GROUPING(course), GROUPING(year), GROUPING_ID(course, year) FROM courseSales
0038 GROUP BY CUBE(course, year);
0039 SELECT course, year, GROUPING(course) FROM courseSales GROUP BY course, year;
0040 SELECT course, year, GROUPING_ID(course, year) FROM courseSales GROUP BY course, year;
0041 SELECT course, year, grouping__id FROM courseSales GROUP BY CUBE(course, year) ORDER BY grouping__id, course, year;
0042
0043 -- GROUPING/GROUPING_ID in having clause
0044 SELECT course, year FROM courseSales GROUP BY CUBE(course, year)
0045 HAVING GROUPING(year) = 1 AND GROUPING_ID(course, year) > 0 ORDER BY course, year;
0046 SELECT course, year FROM courseSales GROUP BY course, year HAVING GROUPING(course) > 0;
0047 SELECT course, year FROM courseSales GROUP BY course, year HAVING GROUPING_ID(course) > 0;
0048 SELECT course, year FROM courseSales GROUP BY CUBE(course, year) HAVING grouping__id > 0;
0049
0050 -- GROUPING/GROUPING_ID in orderBy clause
0051 SELECT course, year, GROUPING(course), GROUPING(year) FROM courseSales GROUP BY CUBE(course, year)
0052 ORDER BY GROUPING(course), GROUPING(year), course, year;
0053 SELECT course, year, GROUPING_ID(course, year) FROM courseSales GROUP BY CUBE(course, year)
0054 ORDER BY GROUPING(course), GROUPING(year), course, year;
0055 SELECT course, year FROM courseSales GROUP BY course, year ORDER BY GROUPING(course);
0056 SELECT course, year FROM courseSales GROUP BY course, year ORDER BY GROUPING_ID(course);
0057 SELECT course, year FROM courseSales GROUP BY CUBE(course, year) ORDER BY grouping__id, course, year;
0058
0059 -- Aliases in SELECT could be used in ROLLUP/CUBE/GROUPING SETS
0060 SELECT a + b AS k1, b AS k2, SUM(a - b) FROM testData GROUP BY CUBE(k1, k2);
0061 SELECT a + b AS k, b, SUM(a - b) FROM testData GROUP BY ROLLUP(k, b);
0062 SELECT a + b, b AS k, SUM(a - b) FROM testData GROUP BY a + b, k GROUPING SETS(k)