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

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

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

[复制链接]
论坛徽章:
496
目光如炬
日期:2015-11-22 22:00:00秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21探花
日期:2016-01-06 14:11:18进士
日期:2016-01-06 14:11:18
发表于 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
/


论坛徽章:
288
秀才
日期:2017-03-02 10:30:47布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-09-27 15:16:21秀才
日期:2016-09-27 15:11:30奥运会纪念徽章:自行车
日期:2016-09-26 15:54:59举人
日期:2016-06-24 09:25:21秀才
日期:2016-06-24 09:21:04双鱼座
日期:2016-06-15 17:14:38射手座
日期:2016-05-26 14:02:50白羊座
日期:2016-05-23 11:49:19
发表于 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

使用道具 举报

回复
论坛徽章:
399
优秀写手
日期:2013-12-18 09:29:08itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07懒羊羊
日期:2015-02-21 22:03:31懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18
发表于 2018-4-29 15:33 来自手机 | 显示全部楼层
我也想到二楼的方法,另外,每一一题真是提问的典范

使用道具 举报

回复
论坛徽章:
288
秀才
日期:2017-03-02 10:30:47布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-09-27 15:16:21秀才
日期:2016-09-27 15:11:30奥运会纪念徽章:自行车
日期:2016-09-26 15:54:59举人
日期:2016-06-24 09:25:21秀才
日期:2016-06-24 09:21:04双鱼座
日期:2016-06-15 17:14:38射手座
日期:2016-05-26 14:02:50白羊座
日期:2016-05-23 11:49:19
发表于 2018-4-29 19:03 | 显示全部楼层
〇〇 发表于 2018-4-29 15:33
我也想到二楼的方法,另外,每一一题真是提问的典范

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

使用道具 举报

回复
论坛徽章:
399
优秀写手
日期:2013-12-18 09:29:08itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07懒羊羊
日期:2015-02-21 22:03:31懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18
发表于 2018-4-29 20:39 | 显示全部楼层
solomon_007 发表于 2018-4-29 19:03
newkid还是dingjun,好像几年前翻译过一篇关于分区外连接的专著的帖子,那个是知道
这个功能,但没有领 ...

dingjun的稠化报表

使用道具 举报

回复
论坛徽章:
288
秀才
日期:2017-03-02 10:30:47布鲁克
日期:2016-10-08 10:06:50秀才
日期:2016-09-27 15:16:21秀才
日期:2016-09-27 15:11:30奥运会纪念徽章:自行车
日期:2016-09-26 15:54:59举人
日期:2016-06-24 09:25:21秀才
日期:2016-06-24 09:21:04双鱼座
日期:2016-06-15 17:14:38射手座
日期:2016-05-26 14:02:50白羊座
日期:2016-05-23 11:49:19
发表于 2018-4-30 21:05 | 显示全部楼层
〇〇 发表于 2018-4-29 20:39
dingjun的稠化报表

正解!

使用道具 举报

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

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

使用道具 举报

回复
论坛徽章:
496
目光如炬
日期:2015-11-22 22:00:00秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21探花
日期:2016-01-06 14:11:18进士
日期:2016-01-06 14:11:18
 楼主| 发表于 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号
  
快速回复 返回顶部 返回列表