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

 
 注册
热搜:
查看: 2547|回复: 7

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

[复制链接]
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期: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
跳转到指定楼层
1#
发表于 2018-2-6 00:38 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

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

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

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

原始出处:


作者: 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
/
论坛徽章:
397
兰博基尼
日期:2013-12-15 15:36:432014年世界杯参赛球队: 洪都拉斯
日期: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喜羊羊
日期:2015-02-20 22:26:07懒羊羊
日期:2015-02-21 22:03:31懒羊羊
日期:2015-03-04 14:52:11
2#
发表于 2018-2-6 08:09 | 只看该作者
实际测试了一下,就是DENSE_RANK,dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。

使用道具 举报

回复
认证徽章
论坛徽章:
10
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-03-01 10:21:49秀才
日期:2018-03-01 10:21:25秀才
日期:2018-03-01 10:05:18秀才
日期:2015-12-14 15:09:382015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有对象
日期:2015-01-16 13:26:19优秀写手
日期:2014-10-23 06:00:14秀才
日期:2018-03-01 10:21:49
3#
发表于 2018-2-6 10:03 | 只看该作者
上班特早那个终于休假了。

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

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期: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
4#
 楼主| 发表于 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
5#
发表于 2018-2-10 11:14 | 只看该作者
表示DEF还么学到,很重要吗?

使用道具 举报

回复
论坛徽章:
0
6#
发表于 2018-2-12 16:23 | 只看该作者
newkid 发表于 2018-2-7 05:19
答案B, 3楼得奖。

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

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

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期: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
7#
 楼主| 发表于 2018-2-13 04:55 | 只看该作者
ljd2 发表于 2018-2-12 16:23
这个我有点困惑 ,题设中“列出每个类别中前三种最高价格的产品”,这里的前三种修饰的是产 ...

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

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
479
法拉利
日期:2014-01-25 00:27:172014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期: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
8#
发表于 2018-2-13 13:34 | 只看该作者
DICKY_XIE 发表于 2018-2-10 11:14
表示DEF还么学到,很重要吗?

那是12c才支持的功能

使用道具 举报

回复

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

本版积分规则

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