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

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

[每日一题] PL/SQL Challenge 每日一题:2017-3-29 分析函数

[复制链接]
论坛徽章:
482
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期: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
发表于 2017-4-14 05:18 | 显示全部楼层 |阅读模式

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

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

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

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

作者:Kim Berg Hansen

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

我有一张表保存着员工的职位和工资:

create table qz_emp (
   empno    integer primary key
, ename    varchar2(10)
, job      varchar2(10)
, sal      number
);

insert into qz_emp values (
   7499, 'ALLEN' , 'SALESMAN' , 1600)
/
insert into qz_emp values (
   7521, 'WARD'  , 'SALESMAN' , 1250)
/
insert into qz_emp values (
   7654, 'MARTIN', 'SALESMAN' , 1250)
/
insert into qz_emp values (
   7698, 'BLAKE' , 'MANAGER'  , 2850)
/
insert into qz_emp values (
   7844, 'TURNER', 'SALESMAN' , 1500)
/
insert into qz_emp values (
   7900, 'JAMES' , 'CLERK'    ,  950)
/

commit
/

select *
  from qz_emp
order by sal desc
/

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
      7698 BLAKE      MANAGER          2850
      7499 ALLEN      SALESMAN         1600
      7844 TURNER     SALESMAN         1500
      7521 WARD       SALESMAN         1250
      7654 MARTIN     SALESMAN         1250
      7900 JAMES      CLERK             950

我写了个未完成的查询:

select empno, ename, a_number
  from (
   select e.empno
        , e.ename
      ##REPLACE##
     from qz_emp e
  )
where a_number <= max_to_output
order by a_number
/

哪些选项包含了列名A_NUMBER 和 MAX_TO_OUTPUT的表达式,可用来取代 ##REPLACE## 使得查询返回恰好三行输出,不多也不少?

(A)
  , row_number() over (order by sal desc) as a_number
  , 3 as max_to_output

(B)
  , count(case job when 'SALESMAN' then 1 end) over ()
       as a_number
  , 3 as max_to_output

(C)
  , count(case job when 'SALESMAN' then 1 end) over (
       order by sal desc
    ) as a_number
  , 3 as max_to_output

(D)
  , rank() over (partition by job order by sal ASC)
       as a_number
  , 1 as max_to_output

(E)
  , rank() over (partition by job order by sal DESC)
       as a_number
  , 1 as max_to_output

(F)
, count(*) over (
       order by sal
       range between 100 following and 250 following
    ) as a_number
  , 0 as max_to_output
  
(G)
  , sum(sal) over (
       order by sal
       rows between unbounded preceding and 3 preceding
    ) as a_number
  , 1000000 as max_to_output
  
(H)
  , nth_value(sal, 2) over (
       partition by job
       order by SAL
    ) as a_number
  , 5000 as max_to_output   

(I)
  , nth_value(sal, 2) over (
       partition by job
       order by EMPNO
    ) as a_number
  , 5000 as max_to_output  

(J)
  , ntile(2) over (order by sal) as a_number
  , 1 as max_to_output
  
(K)
  , ntile(3) over (order by sal) as a_number
  , 2 as max_to_output


(L)
, first_value(sal) over (partition by job order by sal)
       as a_number
  , 1250 as max_to_output

(M)
  , last_value(sal) over (
       order by sal
       range between 100 following and 500 following
    ) as a_number
  , 2000 as max_to_output

(N)
  , max(sal) over (
       partition by job
       order by sal ASC
    ) as a_number
  , 1250 as max_to_output
  
(O)
  , max(sal) over (
       partition by job
       order by sal DESC
    ) as a_number
  , 1250 as max_to_output
论坛徽章:
260
乌索普
日期:2016-07-29 01:46:29白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25水瓶座
日期:2016-03-24 22:16:36摩羯座
日期:2016-03-17 15:09:14
发表于 2017-4-14 12:11 | 显示全部楼层
ACEFGIJN

A: ROW_NUMBER 根据order by 为每一返回一个唯一的从1开始的整数,对于
order by 后面相同的值,排序不确定
   所以这个查询可以返回3行
   
B: count(case job when 'SALESMAN' then 1 end) over (),这个得到的值为4,
   所以结果不会返回数据
   
C: 根据sal 的降序,和职务
   count(case job when 'SALESMAN' then 1 end) over (
        order by sal desc
    ) as a_number
   返回的值为:
    0
    1
    2
    4
    4
    4
   所以这个查询可以返回3行
   
D:  rank() over (partition by job order by sal ASC) as a_number
    按JOB做了分区,所以CLERK和MANAGER职务的人为1,但SALESMAN的sal=1250 的
    有两个人,并列为1,所以结果会返回4行
   
E: sal倒序排序后,sal=1600的SALESMAN 的rank=1,根据D,所以结果会返回3行

F: count(*) over (
       order by sal
       range between 100 following and 250 following
    ) as a_number
   
   当前行跟后面的行的SAL 相差在100到250之间,所以
   950 CLERK      0
  1250  SALESMAN  1
  1250  SALESMAN  1
  1500  SALESMAN  1
  1600  SALESMAN  0
  2850  MANAGER   0
  
  所以可以会返回3行

G:sum(sal) over (
       order by sal
       rows between unbounded preceding and 3 preceding
    ) as a_number
   当前行的前3行及以上行的SAL的累计,
   950 CLERK
1250  SALESMAN  
1250  SALESMAN  
1500  SALESMAN  950
1600  SALESMAN  2200
2850  MANAGER 3450

所以可以会返回3行

H:
nth_value(sal, 2) over (
       partition by job
       order by SAL
    ) as a_number
   
  按job分区,SAL升序,SAL在分区窗口中排第2的SAL,所以SALESMAN岗位的有4行1250
  所以结果会返回4行
  
i: 与H不同的是 order by empno,子查询的排序结果是
950 CLERK
2850  MANAGER
1600  SALESMAN  
1250  SALESMAN  1250
1250  SALESMAN  1250
1500  SALESMAN  1250
  所以可以会返回3行

J:ntile(expr) 将数据集合平均分配到expr指定的数量的桶中,将桶号分配给每一行。  
   有6行,  ntile(2) 每个桶3行
   
K: , ntile(3) over (order by sal) as a_number
  , 2 as max_to_output
  6行分配到3个桶,所以1和2号桶分别有2两行,所以a_number <= max_to_output 结果有4行
  
L: first_value(sal) over (partition by job order by sal)
    返回每个岗位的最小SAL,满足条件的有5行
   
M:  , last_value(sal) over (
       order by sal
       range between 100 following and 500 following
    ) as a_number
  , 2000 as max_to_output
   与当前行的工资相差100到500之间的后面的行中最大工资
   950 CLERK 1250
1250  SALESMAN  1600
1250  SALESMAN  1600
1500  SALESMAN  1600
1600  SALESMAN  
2850  MANAGER

结果会返回4行

N:max(sal) over (
       partition by job
       order by sal ASC
    )
   这就等于SAL, 小于1250 有3行
   
O: max(sal) over (
       partition by job
       order by sal DESC
    ) as a_number
    每个岗位的最高工资
    950 CLERK 950
2850  MANAGER 2850
1600  SALESMAN  1600
1500  SALESMAN  1600
1250  SALESMAN  1600
1250  SALESMAN  1600

结果只会返回1行

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
477
本田
日期:2014-01-05 16:51:44技术图书徽章
日期:2014-04-21 10:26:402014年世界杯参赛球队: 伊朗
日期: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
发表于 2017-4-14 17:41 | 显示全部楼层
不错,考了range和rows

使用道具 举报

回复
论坛徽章:
482
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期: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
 楼主| 发表于 2017-4-18 04:57 | 显示全部楼层
答案ACEFGIJN, 2楼得奖。

A: 数字 1, 2, 3, 4, 5, 6会以工资的降序赋予每一行。我们不知道Ward 和Martin谁会得到4,谁会得到5, 但是我们得到1,2,3行的输出,不管是谁:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7698 BLAKE               1
      7499 ALLEN               2
      7844 TURNER              3        
B: 因为我们执行的分析函数COUNT不带分区、排序和窗口,A_NUMBER对每行都是4,这个选项因此不会返回任何数据。
C: 加上了ORDER子句会以工资的降序给我们一个滚动计数值。这意味着BLAKE会得到0,因为在指定的顺序中还没有任何SALESMAN的数据。ALLEN 会得到1, TURNER 会得到2。 Ward 和 Martin都会得到4,因为我们没有指定窗口子句,缺省窗口为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。James也会得到4, 所以只有三行满足MAX_TO_OUTPUT的条件:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7698 BLAKE               0
      7499 ALLEN               1
      7844 TURNER              2
D: 在每个JOB分区中,我们以工资排序得到排名,这意味着最低的工资会得到1的排名。但是对于相同的工资RANK会给出相同的值,所以Ward和Martin都会在SALESMAN这个分区中得到第1的排名。因此我们的输出有四行:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES               1
      7654 MARTIN              1
      7521 WARD                1
      7698 BLAKE               1
E: 但是,以工资降序排名就意味着在每个JOB分区中,最大的工资会得到第1的排名。因为在salesmen中只有一个工资最高的销售人员,所以我们在三个JOB分区中,每个分区只会得到一个最高工资的员工,结果如下:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES               1
      7499 ALLEN               1
      7698 BLAKE               1

F:
Turner, Ward 和 Martin 全都有比他们多赚100到250之间的同事,所以他们的计数会返回大于零的值。另外三人却没有,所以他们的计数为零:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES               0
      7698 BLAKE               0
      7499 ALLEN               0

G: 我们的窗口只对当前行至少三行之前的数据进行总计,所以前三行在A_NUMBER的值都是NULL。NULL并不会<= 1000000,所以只有按工资排序的后三名员工被输出:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7844 TURNER            950
      7499 ALLEN            2200
      7698 BLAKE            3450

H: 在每个JOB分区中,我们要求的是以工资排序的第二名的值。但这仅仅是在窗口之中,也即缺省的RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。有两种JOB仅仅有一名员工,永远找不到第二名,所以它们变成了NULL。对于salesmen,WARD 或 MARTIN 都可能是第一,但不论是谁,另一个都还在窗口中。所以即使是第一名销售人员,窗口中也有两行,NTH_VALUE可以找到第二个值。因催对于所有的销售人员我们都会得到1250,输出有四行:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7521 WARD             1250
      7499 ALLEN            1250
      7844 TURNER           1250
      7654 MARTIN           1250
I:此处和前一选项的区别是,我们是以一个唯一值进行排序的。Allen会是salesmen中的第一名,对于他而言,窗口中仅仅有一行,即他自己,第二个值找不到,因此他得到一个NULL, 无法被输出。另外三名销售人员得到的是Ward的工资(他是以EMPNO排序的第二名),我们有三行输出:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7521 WARD             1250
      7844 TURNER           1250
      7654 MARTIN           1250

J: NTILE(2) 会把六名员工放入编号为1和2的两个桶。所以我们的输出显示了第1个桶中的三名员工:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES               1
      7654 MARTIN              1
      7521 WARD                1

K: NTILE(3) 会把六名员工放入编号为1,2和3的三个桶。所以我们的输出显示了第1, 2个桶中的四名员工:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES               1
      7521 WARD                1
      7654 MARTIN              2
      7844 TURNER              2

L:以工资排序的FIRST_VALUE(sal)给我们的是每个JOB分区中的最小工资,只有MANAGER分区不具有至少一名员工赚的钱不超过1250,所以我们的输出包含所有的salesmen 和clerk员工:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES             950
      7499 ALLEN            1250
      7844 TURNER           1250
      7521 WARD             1250
      7654 MARTIN           1250

M:
有四名员工有着比他们多赚100到500之间的同事,这四个人也是赚得最少的。在所有四人中,窗口中的LAST_VALUE(sal) 都小于2000,所以四人全被输出:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES            1250
      7654 MARTIN           1600
      7844 TURNER           1600
      7521 WARD             1600

N: 因为我们没有窗口子句,缺省的窗口使得以工资升序排列的MAX(SAL) 等于其自身的工资。所以我们的输出就是三名赚钱最少的员工:


     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES             950
      7654 MARTIN           1250
      7521 WARD             1250

O: 当我们把排序改为降序,窗口的第一行永远是最大值,所以此处的每个人都会得到其JOB分区中的最大工资。只有CLERK分区中的最大工资小于等于1250:

     EMPNO ENAME        A_NUMBER
---------- ---------- ----------
      7900 JAMES             950

使用道具 举报

回复
论坛徽章:
3
秀才
日期:2016-11-25 16:52:36秀才
日期:2016-12-20 18:14:47弗兰奇
日期:2017-02-09 15:13:43
发表于 2017-5-12 14:05 | 显示全部楼层
本帖最后由 wangzhen_518 于 2017-5-12 16:17 编辑
newkid 发表于 2017-4-18 04:57
答案ACEFGIJN, 2楼得奖。

A: 数字 1, 2, 3, 4, 5, 6会以工资的降序赋予每一行。我们不知道Ward 和Martin ...

使用道具 举报

回复

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

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

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