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

 找回密码
 注册
查看: 2269|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2017-4-11 分析函数SUM()

[复制链接]
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-4-26 03:27 | 显示全部楼层 |阅读模式

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

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

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

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

作者:        Kim Berg Hansen

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

我们通过组队在体育馆跑道上跑步来为慈善募捐。捐助人为队员跑的每一圈付款。我们在这张表中存储每个队员跑的圈数:

create table plch_charity_runs (
   team     varchar2(10)
, member   varchar2(10)
, runs     integer
)
/

insert into plch_charity_runs values ('Pirates', 'Jonathan', 21)
/
insert into plch_charity_runs values ('Pirates', 'David'   , 17)
/
insert into plch_charity_runs values ('Pirates', 'Chris'   , 19)
/
insert into plch_charity_runs values ('Heroes' , 'Liam'    , 17)
/
insert into plch_charity_runs values ('Heroes' , 'Roger'   , 20)
/
insert into plch_charity_runs values ('Heroes' , 'Matthew' , 18)
/
insert into plch_charity_runs values ('Knights', 'Howard'  , 18)
/
insert into plch_charity_runs values ('Knights', 'Jeffrey' , 21)
/
insert into plch_charity_runs values ('Knights', 'Tristan' , 16)
/
commit
/

我想要一个清单,显示这些列:
圈数的总计(和捐助人结算用)
队名
本队总圈数
队员名字
队员圈数

这个清单应该以队的圈数降序排列(如果两个队圈数相同,则最高圈数队员所在的队应该排在前面),然后再以队员圈数降序排列。

哪些选项包含的查询返回这样一个清单:

TOTAL_RUNS TEAM        TEAM_RUNS MEMBER           RUNS
---------- ---------- ---------- ---------- ----------
       167 Pirates            57 Jonathan           21
       167 Pirates            57 Chris              19
       167 Pirates            57 David              17
       167 Knights            55 Jeffrey            21
       167 Knights            55 Howard             18
       167 Knights            55 Tristan            16
       167 Heroes             55 Roger              20
       167 Heroes             55 Matthew            18
       167 Heroes             55 Liam               17
      
      
(A)
select sum(team_runs) over () total_runs
     , team
     , team_runs
     , member
     , runs
  from (
   select team
        , sum(runs) over (partition by team) team_runs
        , member
        , runs
        , max(runs) over (partition by team) best_in_team
     from plch_charity_runs
  )
order by team_runs desc
        , best_in_team desc
        , team
        , runs desc
/


(B)
select sum(distinct team_runs) over () total_runs
     , team
     , team_runs
     , member
     , runs
  from (
   select team
        , sum(runs) over (partition by team) team_runs
        , member
        , runs
        , max(runs) over (partition by team) best_in_team
     from plch_charity_runs
  )
order by team_runs desc
        , best_in_team desc
        , team
        , runs desc
/


(C)
select sum(team_runs / members_in_team) over () total_runs
     , team
     , team_runs
     , member
     , runs
  from (
   select team
        , sum(runs) over (partition by team) team_runs
        , member
        , runs
        , max(runs) over (partition by team) best_in_team
        , count(*)  over (partition by team) members_in_team
     from plch_charity_runs
  )
order by team_runs desc
        , best_in_team desc
        , team
        , runs desc
/

(D)
select sum(runs) over () total_runs
     , team
     , sum(runs) over (partition by team) team_runs
     , member
     , runs
  from plch_charity_runs
order by team_runs desc
        , max(runs) over (partition by team) desc
        , team
        , runs desc
/

(E)
with teams as (
   select team
        , sum(runs) team_runs
        , max(runs) best_in_team
     from plch_charity_runs
    group by team
), total as (
   select sum(runs) total_runs
     from plch_charity_runs
)
select total.total_runs
     , t.team
     , t.team_runs
     , r.member
     , r.runs
  from plch_charity_runs r
  join teams t
      on t.team = r.team
  cross join total
order by t.team_runs desc
        , t.best_in_team desc
        , r.team
        , r.runs desc
/
论坛徽章:
44
处女座
日期:2016-06-02 13:45:22马上有房
日期:2014-10-17 13:54:18阿斯顿马丁
日期:2014-01-06 13:43:47复活蛋
日期:2013-05-30 16:44:312013年新春福章
日期:2013-02-25 14:51:24蜘蛛蛋
日期:2013-02-08 09:53:14ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:花样游泳
日期:2012-08-16 09:53:31迷宫蛋
日期:2013-05-10 18:23:20ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28
发表于 2017-4-26 09:19 来自手机 | 显示全部楼层
B,C,D,E

使用道具 举报

回复
论坛徽章:
234
白羊座
日期:2016-05-23 11:49:19秀才
日期:2016-03-01 09:57:08处女座
日期:2016-10-27 22:16:58天枰座
日期:2015-12-28 11:03:38巨蟹座
日期:2015-12-20 15:00:56巨蟹座
日期:2015-12-14 21:46:03天枰座
日期:2015-11-30 15:57:24天蝎座
日期:2016-08-16 09:49:11秀才
日期:2015-11-23 10:00:44白羊座
日期:2015-11-29 10:44:09
发表于 2017-4-26 12:59 | 显示全部楼层
CDE

A: sum(team_runs) over () total_runs 这个总计会根据对的小计因对员而重复,进而重复累计
B: sum(distinct team_runs) over () total_runs 这个是纠正A的写法,但不幸的是Knights  和 Heroes的小计都是 55,这就导致只算了一次,所以也不对;如果各队小计不同,结果会正确,但这样的写法不可取
C: sum(team_runs / members_in_team) over () total_runs 总计根据各队小计/成员数得到对的均值,再sum就是对的
D: sum(runs) over () total_runs 简洁干净利落,推荐
E:  不用分析函数,用聚合函数的常规写法

使用道具 举报

回复
论坛徽章:
393
雪佛兰
日期:2013-12-04 20:30:02马上有钱
日期:2014-03-11 11:59:122014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期: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
发表于 2017-4-26 19:45 | 显示全部楼层
solomon_007 发表于 2017-4-26 12:59
CDE

A: sum(team_runs) over () total_runs 这个总计会根据对的小计因对员而重复,进而重复累计

分析函数可以避免表连接和多次扫描

使用道具 举报

回复
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-4-26 23:31 | 显示全部楼层
答案CDE, 3楼得奖。

A: 分析出函数不会聚合,结果都在每一行中。这就是我们所需要的,但是它也意味着TEAM_RUNS在内联视图中被计算,其值体现在每个队员的数据行上,所以外层查询的SUM(TEAM_RUNS) 会把每个队的值统计三次,得到这个错误输出:


TOTAL_RUNS TEAM        TEAM_RUNS MEMBER           RUNS
---------- ---------- ---------- ---------- ----------
       501 Pirates            57 Jonathan           21
       501 Pirates            57 Chris              19
       501 Pirates            57 David              17
       501 Knights            55 Jeffrey            21
       501 Knights            55 Howard             18
       501 Knights            55 Tristan            16
       501 Heroes             55 Roger              20
       501 Heroes             55 Matthew            18
       501 Heroes             55 Liam               17
B: 通过SUM(DISTINCT来解决选项A的问题,仅仅在每个队的TEAM_RUNS都不同的情况下才起作用。不幸的是Heroes队和 Knights 队都有相同的值55,所以总计只会统计57和55之和,得到这个错误结果:

TOTAL_RUNS TEAM        TEAM_RUNS MEMBER           RUNS
---------- ---------- ---------- ---------- ----------
       112 Pirates            57 Jonathan           21
       112 Pirates            57 Chris              19
       112 Pirates            57 David              17
       112 Knights            55 Jeffrey            21
       112 Knights            55 Howard             18
       112 Knights            55 Tristan            16
       112 Heroes             55 Roger              20
       112 Heroes             55 Matthew            18
       112 Heroes             55 Liam               17
C: 解决A选项问题的另外的方法是通过除以队员的总数,于是总和就得到正确结果。对于更复杂的数据我们可能会碰到微观上的舍入错误,但是对于这些数据我们确实能得到正确结果。
D: (推荐)
解决这个问题的更好的方法是不要用内联视图来计算TEAM_RUNS的总计,而是简单地用不带分区的分析函数SUM来计算TOTAL_RUNS的总计。
E: (不推荐)
这样也行,它只是不用分析函数的方法之一——但是为什么要让数据库做这些不必要的事情呢?前一选项的分析函数可以非常高效地完成任务。

使用道具 举报

回复

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

本版积分规则

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