inputs/udf/udf-group-analytics.sql

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