ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 4959|回复: 6

[每日一题] PL/SQL Challenge 每日一题:2017-12-18 GROUPING SETS

[复制链接]
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
发表于 2017-12-21 04:53 | 显示全部楼层 |阅读模式
(原发表于 2011-7-1)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者: Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我创建了下列的表和数据:

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;
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
 楼主| 发表于 2017-12-22 05:15 | 显示全部楼层
顶下。

使用道具 举报

回复
论坛徽章:
9
目光如炬
日期:2017-10-30 17:00:03火眼金睛
日期:2017-11-01 17:00:02目光如炬
日期:2017-11-05 22:00:01目光如炬
日期:2017-11-19 22:00:00火眼金睛
日期:2017-11-30 22:00:00目光如炬
日期:2017-12-10 22:00:00目光如炬
日期:2017-12-17 22:00:00目光如炬
日期:2017-12-24 22:00:01火眼金睛
日期:2018-01-31 22:00:01
发表于 2017-12-22 09:21 | 显示全部楼层
顶下

使用道具 举报

回复
论坛徽章:
35
2012新春纪念徽章
日期:2012-01-04 11:58:44秀才
日期:2016-04-29 15:10:43秀才
日期:2018-03-01 09:58:15秀才
日期:2018-03-01 09:58:47秀才
日期:2018-03-01 09:58:47秀才
日期:2018-03-01 10:05:18秀才
日期:2018-03-01 10:05:34秀才
日期:2018-03-01 10:05:34秀才
日期:2018-03-01 10:05:34技术图书徽章
日期:2018-03-01 10:05:43
发表于 2017-12-22 09:23 | 显示全部楼层
正确答案是 ABDEF , C是错误的,ROLLUP(a,b) 只会group by (a,b) 和 a列 不会group by b列

使用道具 举报

回复
论坛徽章:
35
2012新春纪念徽章
日期:2012-01-04 11:58:44秀才
日期:2016-04-29 15:10:43秀才
日期:2018-03-01 09:58:15秀才
日期:2018-03-01 09:58:47秀才
日期:2018-03-01 09:58:47秀才
日期:2018-03-01 10:05:18秀才
日期:2018-03-01 10:05:34秀才
日期:2018-03-01 10:05:34秀才
日期:2018-03-01 10:05:34技术图书徽章
日期:2018-03-01 10:05:43
发表于 2017-12-22 09:34 | 显示全部楼层
AB,使用的是UNION ALL;D使用的是GROUPING SETS和GROUPING_ID函数的组合,其中GROUPING SETS(a,b,c)会分别对a,b,c列做grouping;E使用的是GROUPING SETS和CUBE函数的组合,CUBE(a,b) 会对(a,b),(a),(b) 列进行group by 操作,F也是使用的是GROUPING SETS和GROUPING_ID函数的组合,只不过使用了别名。

使用道具 举报

回复
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
 楼主| 发表于 2017-12-22 11:15 | 显示全部楼层
答案BDE, 本期无人得奖。

A: 这会产生所有需要的小计和总计,是一种自己实现的GROUPING SETS。但是ORDER BY根据的是列别名product 和 country 对结果进行排序,于是总计就比US(美国)的国家小计更早出现。在列的别名和列名相同的时候要特别注意。

B: 这是一个自己实现的GROUPING SETS,它创建了三组的SUM然后进行UNION ALL。注意ORDER BY必须出现在每个部分才能工作(或者每个部分都得SELECT某种可以在外层ORDER BY能够用得上的东西)

警告:这依赖于 UNION ALL会保留每个子查询的行顺序。这确实可行(因为优化器不会在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;

如你所见,它更加复杂了,这正是为什么我们要理解并使用 GROUPING SETS :-)

C: ROLLUP(s.product, s.country)会生成(product,country)的小计,(product)的小计,然后是总计。它不会生成(country)小计。
D: GROUPING SETS 允许你能够轻易定义几种你想要GROUP BY的组合。此处它产生了三种GROUP BY组合然后在结果上做了UNION ALL。
E: CUBE 生成了所有四种GROUP BY组合。 HAVING GROUPING_ID 去除了 (product,country) 组合
F: 这是不错的,只是ORDER BY子句会以 product/country别名排序,这样country中的TOTAL就会出现在US之前。

使用道具 举报

回复
论坛徽章:
11
懒羊羊
日期:2018-02-27 22:52:20秀才
日期:2018-05-22 15:17:21技术图书徽章
日期:2018-05-22 15:17:21秀才
日期:2018-05-22 15:16:47举人
日期:2018-03-01 10:25:45秀才
日期:2018-03-01 10:21:25秀才
日期:2018-03-01 10:13:04秀才
日期:2018-03-01 10:13:04秀才
日期:2018-03-01 10:13:04秀才
日期:2018-03-01 10:05:18
发表于 2017-12-25 16:31 | 显示全部楼层

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表