查看: 3141|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2018-3-15 PERCENTILE_DISC函数

[复制链接]
论坛徽章:
530
奥运会纪念徽章:垒球
日期: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:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2018-3-20 04:30 | 显示全部楼层 |阅读模式

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

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

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

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

作者: Kim Berg Hansen

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

我有一张表,保存着eSports决赛的分数:

create table qz_scores (
   player   varchar2(20)
, score    integer
)
/

insert into qz_scores values ('andersson'    , 115)
/
insert into qz_scores values ('bakanaralasa' , 115)
/
insert into qz_scores values ('christiansen' , 117)
/
insert into qz_scores values ('davidoff'     , 122)
/
insert into qz_scores values ('eberhardt'    , 130)
/
insert into qz_scores values ('frantzen'     , 130)
/
insert into qz_scores values ('gregorius'    , 137)
/
insert into qz_scores values ('huckleberry'  , 137)
/
insert into qz_scores values ('ibrahimovitch', 142)
/
commit
/

我们将第75百分位分数定义为至少有75%的球员得分不超过该分的最低分数。 (在这些数据中,9人中有6人(约66%)得分130,9人中有8人(约88%)得分137--因此第75百分位分数是137)


一个球员被称为“位于第75百分位”,如果他得分超过该75百分位分数的话,这也意味着他的分数高于至少75%的球员。

哪些选项包含一个查询,可以显示上述定义的“位于第75百分位”的球员?结果如下:

PLAYER                    SCORE
-------------------- ----------
ibrahimovitch               142

(A)
select player, score
  from (
   select player, score
        , percentile_disc(0.75) within group (order by score) over ()
             as percentile75
     from qz_scores
  )
where score > percentile75
order by score desc

(B)
select player, score
  from qz_scores
where score > (
          select min(score)
            from (
             select score
                  , cume_dist() over (order by score) as c_dist
               from qz_scores
            )
           where c_dist >= 0.75
       )
order by score desc
/

(C)
select player, score
  from qz_scores
where score > (
          select min(score)
            from (
             select score
                  , count(*) over (
                       order by score
                       range between unbounded preceding
                                 and current row
                    ) / count(*) over () as c_dist
               from qz_scores
            )
           where c_dist >= 0.75
       )
order by score desc
/

(D)
select player, score
  from qz_scores
order by score desc
fetch first 25 percent rows only
/

(E)
select player, score
  from qz_scores
where score >= percentile_disc(0.75)
                   within group (order by score) over ()
order by score desc
/

论坛徽章:
142
秀才
日期:2016-01-06 14:01:09秀才
日期:2016-02-18 10:06:46秀才
日期:2016-02-18 10:08:02秀才
日期:2016-02-18 10:08:14秀才
日期:2016-03-01 09:57:08天蝎座
日期:2016-03-18 14:23:56秀才
日期:2016-03-24 09:10:24秀才
日期:2016-03-24 09:20:52秀才
日期:2016-04-21 14:08:53秀才
日期:2016-04-21 14:11:59
发表于 2018-3-20 10:22 | 显示全部楼层
A,B,C
A PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SCORE) OVER()返回按PERCENT_RANK()OVER (....)排序值>=0.75的SCORE,完美
B CUME_DIST()OVER(...)返回按记录数的百分比(0,1]排序,满足需求
C 自己实现CUME_DIST功能
D 上帝又开始创新了
E 分析函数不能乱放位置哦

使用道具 举报

回复
认证徽章
论坛徽章:
8
奥运会纪念徽章:排球
日期:2008-10-24 13:30:01生肖徽章2007版:龙
日期:2009-11-18 16:08:48ITPUB9周年纪念徽章
日期:2010-10-08 09:31:222011新春纪念徽章
日期:2011-02-18 11:43:33凯迪拉克
日期:2013-12-02 15:23:24优秀写手
日期:2013-12-18 09:29:15大众
日期:2014-01-02 15:14:20雪佛兰
日期:2014-01-06 13:14:03
发表于 2018-3-20 10:36 | 显示全部楼层
ABC

PERCENTILE-DISC(X)函数与CUME-DIST相反,它在每一个分组中检查累积分布的数值,直到找到大于或等于X的值。
CUME_DIST 小于等于当前值的行数/分组内总行数
range between unbounded preceding
                                 and current row 对指定范围进行统计,得出第75百分位分数是137

DE语法错误

使用道具 举报

回复
论坛徽章:
368
生肖徽章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-3-20 12:07 | 显示全部楼层
答案:ABC
A: (推荐)分析函数percentile_disc 则是返回小于或等于指定的比率,集合中最接近的那个值
B:  cume_dist 累积分布是返回一个数值在一个集合中,小于等于这个值的比率
C:  利用COUNT() 计算表达了累积分布函数cume_dist的思想
D:  返回的是25%的行集,score=137也计算在内了
E:  分析函数不能这样直接用于表达式中

使用道具 举报

回复
论坛徽章:
530
奥运会纪念徽章:垒球
日期: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:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2018-3-21 05:16 | 显示全部楼层
答案ABC, 2楼得奖。

A:(推荐)
PERCENTILE_DISC 就是我们所需的函数。给定了选项中的参数和数据,PERCENTILE75会获得137这个值,因为至少75%的得分低于或等于它的值中是最小的。所以huckleberry 或 gregorius都不能够声称自己的分数高于75%的球员,在第75百分位只剩下ibrahimovitch

B:(不推荐)
PERCENTILE_DISC的定义就是这样的一个分数,它具有最小的CUME_DIST值,此值大于等于我们所需的百分比——此处是0.75。这个选项是一种PERCENTILE_DISC的DIY实现方式,所以没什么意义,它做了额外的工作,访问了两次表。

C:(不推荐)
而CUME_DIST函数的定义是具有相同或更低值的行数初一总行数,所以这个选项和前一选项相同,只是多了CUME_DIST的DIY实现方式,这也没什么意义,因为已经有内置函数了。

D: 简单地获取前25%的行数对于某些数据来说会得到和我们要寻找的百分位相同的值,但是并不通用,对于这些数据也行不通。这个选项会返回这个错误输出:

PLAYER                    SCORE
-------------------- ----------
ibrahimovitch               142
huckleberry                 137
gregorius                   137

E: 如同所有分析函数一样,我们不能够将它们用在WHERE子句,而是必须在内联视图中使用,如同A选项。这个选项会报错:
ORA-00934: group function is not allowed here.

使用道具 举报

回复

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

本版积分规则 发表回复

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