查看: 4400|回复: 7

[每日一题] PL/SQL Challenge 每日一题:2018-2-1 DENSE_RANK (12c)

[复制链接]
论坛徽章:
527
奥运会纪念徽章:垒球
日期: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-2-6 00:38 | 显示全部楼层 |阅读模式

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

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

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

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

作者: Kim Berg Hansen

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

我有一张产品类别表,还有一张各个类别中的产品表:

create table categories (
   id       integer     primary key
, name     varchar2(20)
)
/

create table items (
   id       integer     primary key
, name     varchar2(20)
, cat_id   integer     references categories
, price    number
)
/

insert into categories values (1, 'Auto')
/
insert into categories values (2, 'Mobile')
/

insert into items values (110, 'Brake disc', 1, 33)
/
insert into items values (120, 'Snow chain', 1, 15)
/
insert into items values (130, 'Sparc plug', 1, 33)
/
insert into items values (140, 'Oil filter', 1, 22)
/
insert into items values (150, 'Light bulb', 1, 28)
/
insert into items values (210, 'Handsfree' , 2, 18)
/
insert into items values (220, 'Charger'   , 2, 44)
/
insert into items values (230, 'iGloves'   , 2, 28)
/
insert into items values (240, 'Headset'   , 2, 35)
/
insert into items values (250, 'Cover'     , 2, 28)
/

commit
/

我想要一张清单,列出每个类别中前三种最高价格的产品。

哪些选项包含的查询返回这个输出:

    CAT_ID         ID NAME                      PRICE
---------- ---------- -------------------- ----------
         1        110 Brake disc                   33
         1        130 Sparc plug                   33
         1        150 Light bulb                   28
         1        140 Oil filter                   22
         2        220 Charger                      44
         2        240 Headset                      35
         2        230 iGloves                      28
         2        250 Cover                        28
         
(A)
select cat_id, id, name, price
  from (
   select cat_id, id, name, price
        , RANK() over (
             partition by cat_id order by price desc
          ) as r
     from items
          )
where r <= 3
order by cat_id, r, id
/

(B)
select cat_id, id, name, price
  from (
   select cat_id, id, name, price
        , DENSE_RANK() over (
             partition by cat_id order by price desc
          ) as r
     from items
          )
where r <= 3
order by cat_id, r, id
/

(C)
select cat_id, id, name, price
  from (
   select cat_id, id, name, price
        , ROW_NUMBER() over (
             partition by cat_id order by price desc
          ) as r
     from items
          )
where r <= 3
order by cat_id, r, id
/

(D)
select c.id as cat_id
     , i.id, i.name, i.price
  from categories c
cross join lateral (
      select id, name, price
        from items
       where items.cat_id = c.id
       order by price desc
       FETCH FIRST 3 ROWS WITH TIES
     ) i
order by c.id, i.price desc, i.id
/

(E)
select c.id as cat_id
     , i.id, i.name, i.price
  from categories c
cross join lateral (
      select id, name, price
        from items
       where items.cat_id = c.id
       order by price desc
       FETCH FIRST 3 ROWS WITH DENSE TIES
     ) i
order by c.id, i.price desc, i.id
/

(F)
select c.id as cat_id
     , i.id, i.name, i.price
  from categories c
cross join lateral (
      select id, name, price
        from items
       where items.cat_id = c.id
       order by price desc
       FETCH FIRST 3 ROWS ONLY
     ) i
order by c.id, i.price desc, i.id
/
论坛徽章:
401
紫蛋头
日期: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-2-6 08:09 | 显示全部楼层
实际测试了一下,就是DENSE_RANK,dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。

使用道具 举报

回复
认证徽章
论坛徽章:
34
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-05-22 16:13:30秀才
日期:2018-05-22 16:13:30秀才
日期:2018-05-22 16:17:26秀才
日期:2018-07-23 13:38:29技术图书徽章
日期:2018-07-23 13:38:40秀才
日期:2018-07-23 13:38:40秀才
日期:2018-07-23 13:44:01秀才
日期:2018-08-31 10:37:01秀才
日期:2018-08-31 10:37:20
发表于 2018-2-6 10:03 | 显示全部楼层
上班特早那个终于休假了。

A,不对,RANK()遇到一样得值得时候后面得值会跳一下就是并列第一就没有第二名了。
B,对,无跳号
C.不对,row_number直接排序,重复一样得也顺序排。
D.不对,但是可以学习一下,主要是第一 CAT_ID=1 得那个33要是排在最后就对了。可以看一下文档(http://przemyslawkruglej.com/arc ... ing-clause-11g-12c/)
E.没这用法把(DENSE TIES)。反正我得这个报错。
F.不对,铁铁返回3行。不可能对了

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期: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-2-7 05:19 | 显示全部楼层
答案B, 3楼得奖。

A: RANK的工作原理就像体育赛事——如果有两个人得到金牌,银牌就被跳过,第三个人得到的是铜牌。这意味着在category=1的类别中输出是错的,仅仅显示了两个最高的价格:

    CAT_ID         ID NAME                      PRICE
---------- ---------- -------------------- ----------
         1        110 Brake disc                   33
         1        130 Sparc plug                   33
         1        150 Light bulb                   28
         2        220 Charger                      44
         2        240 Headset                      35
         2        230 iGloves                      28
         2        250 Cover                        28

B: DENSE_RANK不会跳过排位——如果两个人得到金牌,第三个人得到银牌,第四个得到铜牌。这给了我们所需的输出。

C: ROW_NUMBER只是简单地(为每个分区)赋予连续数字,所以一旦出现平局,哪一行得到哪个数是不确定的。我们为每种类别会恰好得到三行,所以在类别category=1我们只看到前两个最高价格(类似选项A),在category=2,有两个产品的价格是第三高,但是我们只看到其中一个。错误的结果如下,最后一行可能是iGloves 或者 Cover:

    CAT_ID         ID NAME                      PRICE
---------- ---------- -------------------- ----------
         1        110 Brake disc                   33
         1        130 Sparc plug                   33
         1        150 Light bulb                   28
         2        220 Charger                      44
         2        240 Headset                      35
         2        230 iGloves                      28

D: 用 FETCH FIRST 语法是可能的,但是你不能用PARTITION BY,而是得用LATERAL连接来为每个类别做FETCH FIRST。那是可以的,但问题是WITH TIES使得FETCH FIRST就像选项A的RANK函数,平局被同样处理,而我们得到了和A一样的错误输出。

E: FETCH FIRST不支持DENSE关键字,你不能使得FETCH FIRST做选项B的事。解析器期待在ROWS WITH后面紧接着TIES关键字,所以这错误的语法会报错:
ORA-00905: missing keyword.

F: FETCH FIRST带ROWS ONLY是支持的语法,当用ROWS ONLY时,它就像选项D的ROW_NUMBER,给出的是相同的错误结果。

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2018-2-10 11:14 | 显示全部楼层
表示DEF还么学到,很重要吗?

使用道具 举报

回复
论坛徽章:
0
发表于 2018-2-12 16:23 | 显示全部楼层
newkid 发表于 2018-2-7 05:19
答案B, 3楼得奖。

A: RANK的工作原理就像体育赛事——如果有两个人得到金牌,银牌就被跳过,第三个人得 ...

这个我有点困惑 ,题设中“列出每个类别中前三种最高价格的产品”,这里的前三种修饰的是产品还是价格,如果是价格,那就选B,若是后者则C

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期: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-2-13 04:55 | 显示全部楼层
ljd2 发表于 2018-2-12 16:23
这个我有点困惑 ,题设中“列出每个类别中前三种最高价格的产品”,这里的前三种修饰的是产 ...

是价格,翻译不太确切,但是例子输出非常清晰。

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有房
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31
发表于 2018-2-13 13:34 | 显示全部楼层
DICKY_XIE 发表于 2018-2-10 11:14
表示DEF还么学到,很重要吗?

那是12c才支持的功能

使用道具 举报

回复

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

本版积分规则 发表回复

【有奖讨论】解决存储挑战了解一下
奖品:米家车载空气净化器 、米家声波电动牙刷 、小米运动蓝牙耳机

在数字经济时代,井喷式增长的数据,在释放大量商业价值的同时,也随之对企业的IT基础设施带来了不容忽视的挑战!如何存储、管理、使用这些数据呢?这是一条比以往更艰难的路~

活动时间:9月20日-10月11日

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