关于grouping,grouping_id,grouping sets()的练习
-
caisanpx
2012-11-12 23:19:48 -
Linux操作系统
- 原创
2011-6-25 GROUPING SETS
作者：Kim Berg Hansen
难度：中
我创建了如下的表并填入数据：
CREATE TABLE plch_sales
(
product VARCHAR2(10)
, country VARCHAR2(10)
, year NUMBER
, sales NUMBER
)
/
BEGIN
INSERT INTO plch_sales VALUES ('BANANA', 'US', 2009, 200);
INSERT INTO plch_sales VALUES ('BANANA', 'US', 2010, 300);
INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2009, 400);
INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2010, 350);
INSERT INTO plch_sales VALUES ('BANANA', 'DK', 2010, 250);
INSERT INTO plch_sales VALUES ('APPLE' , 'US', 2009, 100);
INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2009, 150);
INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2010, 150);
INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2009, 250);
INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2010, 250);
INSERT INTO plch_sales VALUES ('PEAR' , 'GB', 2010, 150);
INSERT INTO plch_sales VALUES ('PEAR' , 'DK', 2009, 300);
INSERT INTO plch_sales VALUES ('PEAR' , 'DK', 2010, 350);
COMMIT;
END;
/
我的老板要我按照产品、国家汇总销售数据，最后必须有总合计。
哪个选项能够产生符合要求的如下输出：
TOTAL PRODUCT COUNTRY SALES
------- ---------- ---------- ----------
Product APPLE TOTAL 900
Product BANANA TOTAL 1500
Product PEAR TOTAL 800
Country TOTAL DK 1400
Country TOTAL GB 1200
Country TOTAL US 600
Grand TOTAL TOTAL 3200
(A)
SELECT 'Product' total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, 'TOTAL' country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.product
UNION ALL
SELECT 'Country' total
, 'TOTAL' product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.country
UNION ALL
SELECT 'Grand' total
, 'TOTAL' product
, 'TOTAL' country
, SUM(s.sales) sales
FROM plch_sales s
ORDER BY product, country;
(B)
SELECT * FROM (
SELECT 'Product' total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, 'TOTAL' country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.product
ORDER BY s.product
)
UNION ALL
SELECT * FROM (
SELECT 'Country' total
, 'TOTAL' product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.country
ORDER BY s.country
)
UNION ALL
SELECT 'Grand' total
, 'TOTAL' product
, 'TOTAL' country
, SUM(s.sales) sales
FROM plch_sales s;
(C)
SELECT CASE GROUPING_ID(s.product, s.country)
WHEN 1 THEN 'Product'
WHEN 2 THEN 'Country'
WHEN 3 THEN 'Grand'
END total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY ROLLUP(s.product, s.country)
ORDER BY s.product, s.country;
(D)
SELECT CASE GROUPING_ID(s.product, s.country)
WHEN 1 THEN 'Product'
WHEN 2 THEN 'Country'
WHEN 3 THEN 'Grand'
END total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY GROUPING SETS(
(s.product),
(s.country),
()
)
ORDER BY s.product, s.country;
(E)
SELECT CASE GROUPING_ID(s.product, s.country)
WHEN 1 THEN 'Product'
WHEN 2 THEN 'Country'
WHEN 3 THEN 'Grand'
END total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY CUBE(s.product, s.country)
HAVING GROUPING_ID(s.product, s.country) > 0
ORDER BY s.product, s.country;
(F)
SELECT CASE GROUPING_ID(product, country)
WHEN 1 THEN 'Product'
WHEN 2 THEN 'Country'
WHEN 3 THEN 'Grand'
END total
, CASE GROUPING(product)
WHEN 1 THEN 'TOTAL'
ELSE product
END product
, CASE GROUPING(country)
WHEN 1 THEN 'TOTAL'
ELSE country
END country
, SUM(sales) sales
FROM plch_sales
GROUP BY GROUPING SETS(
(product),
(country),
()
)
ORDER BY product, country;
这个扩展分组题目不错，考察两点：
1.扩展分组中rollup,cube,grouping_sets的使用以及如何补充NULL分组加小计、合计功能（在扩展分组中，NULL分组可以用()代替）以及考察三大函数grouping,grouping_id,group_id
2.考察select经过计算的列与原始列同名，排序不加前缀，那么默认是按select列排序的规则
补充两个，排序就按照steven的排就可以了，当然也可以改进下，反正就几种
SELECT
product,
country,SUM(sales)
FROM plch_sales
GROUP BY GROUPING SETS(rollup(product),rollup(country))
HAVING group_id()=0;
SELECT product,country,SUM(sales)
FROM plch_sales
GROUP BY ROLLUP(product),ROLLUP(country)
HAVING product IS NULL OR country IS NULL ;
答案BDE.
A: 按照别名的 ORDER BY导致总合计不在最后一行。
B: 虽然正确，但依赖于一个前提，即UNION ALL不修改结果集的顺序。更好的写法：
SELECT s2.total
, s2.product
, s2.country
, s2.sales
FROM (
SELECT 1 subselect
, GROUPING(s.product) subtotal
, 'Product' total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, 'TOTAL' country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.product
UNION ALL
SELECT 2 subselect
, GROUPING(s.country) subtotal
, 'Country' total
, 'TOTAL' product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY s.country
UNION ALL
SELECT 3 subselect
, 1 subtotal
, 'Grand' total
, 'TOTAL' product
, 'TOTAL' country
, SUM(s.sales) sales
FROM plch_sales s
) s2
ORDER BY s2.subselect
, s2.subtotal
, s2.product
, s2.country;
C:错误：ROLLUP(s.product, s.country)会按照(product,country)和(product)汇总，不会产生(country)汇总。
D:正确：GROUPING SET允许你自己定义GROUP BY组合。
E:正确：CUBE产生了所有四种可能的 GROUP BY 组合， HAVING GROUPING_ID 去除了 (product,country) 组合。
F:错误：按照别名的 ORDER BY 导致了排序错误。