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

 找回密码
 注册
查看: 3334|回复: 7

[每日一题] PL/SQL Challenge 每日一题:2018-4-24 分区外连接

[复制链接]
论坛徽章:
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
发表于 2018-4-27 22:39 | 显示全部楼层 |阅读模式
(原发表于 2011-9-16)

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

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

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

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

作者: mentzel.iudith

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

我定义了三个表和数据:
一个客户表,一个产品表,一个销售表,如下:

CREATE TABLE plch_customer (
  cust_id    INTEGER PRIMARY KEY
, cust_name  VARCHAR2(100)
)
/

BEGIN
   INSERT INTO plch_customer VALUES (100, 'Customer A');
   INSERT INTO plch_customer VALUES (200, 'Customer B');
   INSERT INTO plch_customer VALUES (300, 'Customer C');
   INSERT INTO plch_customer VALUES (400, 'Customer D');

   COMMIT;
END;
/

CREATE TABLE plch_product (
  prod_id   INTEGER PRIMARY KEY
, prod_name VARCHAR2(100)
)
/

BEGIN
   INSERT INTO plch_product  VALUES (10,'Mouse');
   INSERT INTO plch_product  VALUES (20,'Keyboard');
   INSERT INTO plch_product  VALUES (30,'Monitor');

   COMMIT;
END;
/

CREATE TABLE plch_sales (
  cust_id   INTEGER NOT NULL
, prod_id   INTEGER NOT NULL
, quantity  NUMBER  NOT NULL
)
/

BEGIN
   INSERT INTO plch_sales  VALUES (100, 10, 500);
   INSERT INTO plch_sales  VALUES (100, 10, 800);
   INSERT INTO plch_sales  VALUES (100, 20, 600);

   INSERT INTO plch_sales  VALUES (200, 10, 400);
   INSERT INTO plch_sales  VALUES (200, 20, 300);
   INSERT INTO plch_sales  VALUES (200, 20, 700);

   INSERT INTO plch_sales  VALUES (300, 10, 100);
   INSERT INTO plch_sales  VALUES (300, 10, 200);
   INSERT INTO plch_sales  VALUES (300, 10, 900);

   COMMIT;
END;
/

我们想要一个列表,显示卖给每个客户的每种产品的总数量,需求如下:

一个产品仅当它被卖给至少一名客户时才会在列表中出现。
对于列表中的每种产品,卖给plch_customer中的每个客户的总销售额将被显示,如果一个客户没有购买则销售额显示为0。
输出看起来如下:

      CUST_ID       PROD_ID         TOTAL
------------- ------------- -------------
          100            10          1300
          100            20           600
          200            10           400
          200            20          1000
          300            10          1200
          300            20             0
          400            10             0
          400            20             0

下列哪些SELECT语句正确实现了这个需求?

(A)
SELECT s.cust_id  cust_id,
       s.prod_id  prod_id,
       SUM(s.quantity)  total
FROM plch_sales s
GROUP BY
       s.cust_id,
       s.prod_id
UNION ALL
SELECT c.cust_id  cust_id,
       p.prod_id  prod_id,
       0  total
FROM plch_customer  c,
   ( SELECT DISTINCT s.prod_id
     FROM plch_sales s )  p
WHERE NOT EXISTS
    ( SELECT '1'
      FROM plch_sales s2
      WHERE s2.cust_id = c.cust_id
      AND   s2.prod_id = p.prod_id )
ORDER BY cust_id, prod_id
/

(B)
SELECT c.cust_id,
       s.prod_id,
       NVL(SUM(s.quantity),0)   total
FROM plch_sales s  
     PARTITION BY (s.prod_id)
     RIGHT OUTER JOIN  plch_customer  c
  ON ( c.cust_id = s.cust_id )
GROUP BY
       c.cust_id,
       s.prod_id
ORDER BY
       c.cust_id,
       s.prod_id
/


(C)
SELECT c.cust_id,
       s.prod_id,
       NVL(SUM(s.quantity),0)   total
FROM plch_sales s  
     PARTITION BY (s.prod_id)
     LEFT OUTER JOIN  plch_customer  c
  ON ( c.cust_id = s.cust_id )
GROUP BY
       c.cust_id,
       s.prod_id
ORDER BY
       c.cust_id,
       s.prod_id
/

(D)
SELECT c.cust_id,
       s.prod_id,
       NVL(SUM(s.quantity),0)   total
FROM  plch_customer  c
      LEFT OUTER JOIN plch_sales s  
      PARTITION BY (s.prod_id)
  ON ( c.cust_id = s.cust_id )
GROUP BY
       c.cust_id,
       s.prod_id
ORDER BY
       c.cust_id,
       s.prod_id
/

(E)
SELECT c.cust_id,
       p.prod_id,
       NVL(SUM(s.quantity),0)  total
FROM
       plch_customer  c
       CROSS JOIN plch_product  p
       LEFT OUTER JOIN plch_sales  s
       ON (    s.cust_id = c.cust_id
           AND s.prod_id = p.prod_id
          )
GROUP BY
      c.cust_id,
      p.prod_id
ORDER BY
      c.cust_id,
      p.prod_id
/

(F)
SELECT s.cust_id,     
       p.prod_id,
       NVL(SUM(s.quantity),0)  total
FROM
      plch_product  p
      LEFT OUTER JOIN plch_sales  s
      ON ( s.prod_id = p.prod_id )
GROUP BY
      s.cust_id,
      p.prod_id
ORDER BY
      s.cust_id,
      p.prod_id
/

(G)
SELECT c.cust_id,     
       s.prod_id,
       NVL(SUM(s.quantity),0)  total
FROM
      plch_customer  c
      LEFT OUTER JOIN plch_sales  s
      ON ( s.cust_id = c.cust_id )
GROUP BY
      c.cust_id,
      s.prod_id
ORDER BY
      c.cust_id,
      s.prod_id
/

(H)
SELECT c.cust_id,
       p.prod_id,
       NVL(SUM(s.quantity),0)  total
FROM
       plch_customer  c
       CROSS JOIN (SELECT DISTINCT prod_id
                     FROM plch_sales)  p
       LEFT OUTER JOIN plch_sales  s
       ON (    s.cust_id = c.cust_id
           AND s.prod_id = p.prod_id
          )
GROUP BY
      c.cust_id,
      p.prod_id
ORDER BY
      c.cust_id,
      p.prod_id
/


论坛徽章:
293
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2018-4-28 14:12 | 显示全部楼层
答案:ABDH
A:  先将plch_sales 按s.cust_id, s.prod_id 分组聚合,再把所有客户和在plch_sales中的产品JOIN并排除上一步已经聚合的记录,这个部分的TOTAL设置为0; 方法可行但是很笨拙
B:  分区外连接,优雅推荐
C:  相较于B ,plch_customer  写在了JOIN的右边,不匹配的客户也全显,所以应该是 RIGHT ,而不是LEFT
D:  plch_customer 写在了join的左边,所以LEFT 这个是对的
E:  plch_customer  c CROSS JOIN plch_product  p ,没有任何客户买的 30的产品也显示出来了,当 LEFT OUTER JOIN plch_sales 之后
F:  全显了产品,而不是客户
G:  显示了全部客户,但没有控制产品的显示
H: 纠正了F,G 客户和已销售的产品先CROSS join,再 LEFT OUTER JOIN plch_sales ,OK,最后group by 求聚合

我的传统的第一感觉写法:

SQL> select c.cust_id,
  2              ps.prod_id,
  3              sum(nvl(s.quantity, 0)) total
  4         from plch_customer c,
  5              (select *
  6                 from plch_product p
  7                where p.prod_id in (select prod_id from plch_sales)
  8               ) ps,
  9              plch_sales s
10        where c.cust_id  = s.cust_id(+)
11          and ps.prod_id = s.prod_id(+)
12        group by c.cust_id, ps.prod_id
13        order by c.cust_id, ps.prod_id;
                                CUST_ID                                 PROD_ID      TOTAL
--------------------------------------- --------------------------------------- ----------
                                    100                                      10       1300
                                    100                                      20        600
                                    200                                      10        400
                                    200                                      20       1000
                                    300                                      10       1200
                                    300                                      20          0
                                    400                                      10          0
                                    400                                      20          0
8 rows selected

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2018-4-29 15:33 来自手机 | 显示全部楼层
我也想到二楼的方法,另外,每一一题真是提问的典范

使用道具 举报

回复
论坛徽章:
293
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2018-4-29 19:03 | 显示全部楼层
〇〇 发表于 2018-4-29 15:33
我也想到二楼的方法,另外,每一一题真是提问的典范

newkid还是dingjun,好像几年前翻译过一篇关于分区外连接的专著的帖子,那个是知道
这个功能,但没有领悟,不常用,所以我们碰到这样的需求第一反应还是很传统的写法
B,D  差不多属于同类,H跟我们的写法差不多,B,D算是学习一下。。

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2018-4-29 20:39 | 显示全部楼层
solomon_007 发表于 2018-4-29 19:03
newkid还是dingjun,好像几年前翻译过一篇关于分区外连接的专著的帖子,那个是知道
这个功能,但没有领 ...

dingjun的稠化报表

使用道具 举报

回复
论坛徽章:
293
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2018-4-30 21:05 | 显示全部楼层
〇〇 发表于 2018-4-29 20:39
dingjun的稠化报表

正解!

使用道具 举报

回复
论坛徽章:
27
ITPUB官方微博粉丝徽章
日期:2011-08-17 10:35:36托尼托尼·乔巴
日期:2017-10-25 16:45:57秀才
日期:2017-04-05 13:18:06秀才
日期:2017-03-02 10:35:322016猴年福章
日期:2016-02-23 09:58:342016猴年福章
日期:2016-02-18 09:31:302015年新春福章
日期:2015-03-06 11:57:312014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:31
发表于 2018-5-2 12:03 | 显示全部楼层
〇〇 发表于 2018-4-29 20:39
dingjun的稠化报表

这帖子好怀念啊,当年用这个解决了一个报表SQL

使用道具 举报

回复
论坛徽章:
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
 楼主| 发表于 2018-5-2 17:35 | 显示全部楼层
答案ABDH, 2楼得奖。

A:
这是对的,但在10g以上这是一种比较笨的做法。UNION中的第一个SELECT统计了plch_sales中的“真正”销售额,第二个SELECT对于每个plch_customer表中的客户和plch_sales中的没有“真正”销售额的产品加上了一个零总计的行。
B: 这个SELECT用了分区外连接PARTITIONED OUTER JOIN, 这是此类数据稠密化需求的理想解决办法。它对plch_sales表通过PROD_ID进行了“逻辑上的分区”,然后对于每个PROD_ID值所对应的“子表”,它单独地执行了一个对plch_customer表的 RIGHT OUTER JOIN,客户一侧的数据是“满”的,也即,对应于每个客户,如果CUST_ID 和 PROD_ID缺失,就会加上一个NULL
C: 这个方法有个问题,具体地说,它指定的是LEFT OUTER JOIN 而非 RIGHT OUTER JOIN, 这意味着外连接的主侧(满数据的一侧)是plch_sales表(对应于每个PROD_ID的子表)。既然在plch_sales表中的每个CUST_ID值确实也出现在plch_customer,这样一个外连接的结果实际上是内连接,而不会为结果集加上缺失的零总计行。
D:
这个SELECT用了分区外连接PARTITIONED OUTER JOIN, 这是此类数据稠密化需求的理想解决办法。它对plch_sales表通过PROD_ID进行了“逻辑上的分区”,然后对于每个PROD_ID值所对应的“子表”,它单独地执行了一个对plch_customer表的 LEFT OUTER JOIN ,客户一侧的数据是“满”的,也即,对应于每个客户,如果CUST_ID 和 PROD_ID缺失,就会加上一个NULL。这个选项表明,PARTITIONED OUTER JOIN总是可以被用两种等价的方式来表达,一种是RIGHT OUTER JOIN 另一种是 LEFT OUTER JOIN, 只是表的顺序对调了一下。

E: 这个SELECT执行了一个所有客户和所有产品的CROSS JOIN(笛卡尔积)。然后它正确地用了LEFT OUTER JOIN将这个笛卡尔积和plch_sales进行连接。唯一的问题是,结果中会包含plch_product的所有产品,而不仅仅是需求所说的卖给至少一名客户的产品。

F: 这个LEFT OUTER JOIN用了plch_product的所有产品,对于plch_sales中没有相应的行的每种产品加上了一个NULL作为cust_id,所以结果并不是需求所要的。


G: 这个LEFT OUTER JOIN 正确地用了来自plch_customer表的所有客户,但是对于plch_sales中没有出现的每个cust_id,它会加上一个空的prod_id和一个零总计,而不是为每个plch_sales表中的PROD_ID(卖给其他至少一名客户)加上一个零总计。而且,对于那些plch_sales中出现过的客户它就不会加上那些缺失的PROD_ID


H: 这个选项正确地为来自plch_customer表的所有客户以及plch_sales表中出现的所有产品执行了一个CROSS JOIN,这产生了结果集所要求的所有的(CUST_ID,PROD_ID)对。然后,对plch_sales表的外连接对所有这样的数据对加上了销售总额,包括缺失的那些零总计。

使用道具 举报

回复

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

本版积分规则

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