查看: 6346|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2018-1-8 首行记录

[复制链接]
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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-1-11 04:48 | 显示全部楼层 |阅读模式
(原发表于 2011-7-22)

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

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

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

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

作者: mentzel.iudith

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

在我们的HR应用中,我们有如下的员工表:

CREATE TABLE plch_employees
(
   employee_id  INTEGER PRIMARY KEY
, last_name    VARCHAR2(100)
, salary       NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
     VALUES (100, 'Jobs', 1000);

   INSERT INTO plch_employees
     VALUES (200, 'Ellison', 1000);

   INSERT INTO plch_employees
     VALUES (300, 'Gates', 1000);

   COMMIT;
END;
/

我们还有一张日志表,每个员工的薪水变化都会被记录如下:

CREATE TABLE plch_emp_log
(
   employee_id  INTEGER  REFERENCES plch_employees (employee_id)
, log_date     DATE
, old_salary   NUMBER
, new_salary   NUMBER
, CONSTRAINT plch_emp_log_pk PRIMARY KEY (employee_id, log_date)
)
/

BEGIN
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-JAN-10','DD-MON-RR'), 1000, 2000);
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('18-JAN-10','DD-MON-RR'), 2000, 1800);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-JAN-10','DD-MON-RR'), 1000, 1600);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-MAR-10','DD-MON-RR'), 1600, 2500);

   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('01-APR-10','DD-MON-RR'), 1000, 2000);
   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('05-APR-10','DD-MON-RR'), 1800, 1900);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('10-MAY-10','DD-MON-RR'), 2500, 2300);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('15-MAY-10','DD-MON-RR'), 2000, 3000);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-SEP-10','DD-MON-RR'), 1900, 1500);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-SEP-10','DD-MON-RR'), 2300, 3000);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('10-OCT-10','DD-MON-RR'), 3000, 2700);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('05-JAN-11','DD-MON-RR'), 1500, 2500);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('10-FEB-11','DD-MON-RR'), 3000, 2000);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('20-FEB-11','DD-MON-RR'), 2700, 2500);

   INSERT INTO plch_emp_log  VALUES (100, TO_DATE('15-APR-11','DD-MON-RR'), 2500, 2200);
   INSERT INTO plch_emp_log  VALUES (200, TO_DATE('20-JUL-11','DD-MON-RR'), 2000, 2800);
   INSERT INTO plch_emp_log  VALUES (300, TO_DATE('15-SEP-11','DD-MON-RR'), 2500, 2900);

   COMMIT;
END;
/

我们决定要实现一个视图,使得对这个视图的简单查询(仅仅使用过滤条件,不用连接、聚合、子查询等等)可以回答如下的需求:

返回一个列表,列出在一个指定时间区间薪水发生改变的所有员工,显示出该区间的第一个和最后一个员工,这个“区间”可以是一年,一个季度,或者一个月。

下面是一些例子:

1. 指定年份,例如 2010

PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010             100          1000         1500
2010             200          1000         3000
2010             300          1000         2700

2. 指定年份和季度,例如 2010-1

PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010-1           100          1000         1800
2010-1           200          1000         2500

3. 指定年月,例如 2010-01

PERIOD   EMPLOYEE_ID  FIRST_SALARY  LAST_SALARY
-------  -----------  ------------  -----------
2010-01          100          1000         1800
2010-01          200          1000         1600

哪些选项可以用来有效地取代如下定义中的 /*FIRST_SALARY*/ 和 /*LAST_SALARY*/ ,使得这个视图可被用来回答上述需求?

CREATE OR REPLACE VIEW plch_emp_salary_change
AS
SELECT
       TO_CHAR(log_date,'YYYY')  year,
       TO_CHAR(log_date,'Q')     quarter,
       TO_CHAR(log_date,'MM')    month,
       employee_id,
       /*FIRST_SALARY*/    first_salary,
       /*LAST_SALARY*/     last_salary,
       GROUPING_ID(TO_CHAR(log_date,'YYYY'),
                   TO_CHAR(log_date,'Q'),
                   TO_CHAR(log_date,'MM'))   gid
FROM
      plch_emp_log
GROUP BY
      ROLLUP(TO_CHAR(log_date,'YYYY'),
             TO_CHAR(log_date,'Q'),
             TO_CHAR(log_date,'MM')),
      employee_id
/



(A)
MIN(old_salary)
MAX(new_salary)

(B)
FIRST_VALUE(old_salary)
LAST_VALUE(new_salary)


(C)
MIN(old_salary) KEEP(DENSE_RANK FIRST ORDER BY log_date)
MIN(new_salary) KEEP(DENSE_RANK LAST  ORDER BY log_date)

(D)
FIRST_VALUE(old_salary)   OVER(PARTITION BY employee_id
                               ORDER BY log_date)     
LAST_VALUE(new_salary)    OVER(PARTITION BY employee_id
                               ORDER BY log_date)

(E)
MIN(old_salary)    OVER(PARTITION BY employee_id
                        ORDER BY log_date)
MAX(new_salary)    OVER(PARTITION BY employee_id
                        ORDER BY log_date)

(F)
TO_NUMBER(SUBSTR(MIN(TO_CHAR(log_date,'YYYYMMDDHH24MISS')||old_salary),15))
TO_NUMBER(SUBSTR(MAX(TO_CHAR(log_date,'YYYYMMDDHH24MISS')||new_salary),15))
                        
认证徽章
论坛徽章:
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-1-11 10:22 | 显示全部楼层
目测是CD,测试结果是CF,D报了个错误,F这么写法第一次见。其他的都在取最大最小值,比如A和E,B就没有这写法吧。

使用道具 举报

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

A: 本选项正确地使用了聚合函数,但是MIN(old_salary) 和 MAX(new_salary)代表着每个薪水变更区间的最低和最高薪水,这些在时间上并不一定是该区间的第一和最后一条记录。
B: FIRST_VALUE 和 LAST_VALUE 并不是聚合函数,而是分析函数,它返回的是一个指定数据窗口的第一个和最后一个表达式的值。这个选项会报错:
ORA-30484: missing window specification for this function
C: 这是对的。FIRST 和 LAST可以被用作聚合函数,它们返回的是指定排序的第一个和最后一个表达式的值。此处的MIN函数仅仅是为了“打破平局”,处理的是被聚合的行分组中,有几个行在"order by"的列排序的顶部/底部包含了相同的值。在我们这个特例中这并不会发生,因为 (employee_id, log_date)组成了日志表的主键,所以给定一个employee_id, 则log_date值全是唯一的,排序没有平局。因此我们可以使用MIN或者MAX或者同样正确的其他函数来打破平局。

对于支持FIRST/LAST的ORACLE版本(9i或者更高)这是推荐做法,也是性能最佳的方案。
D: 这是分析函数FIRST_VALUE 和 LAST_VALUE的正确的使用语法,因为它指定了一个所要求的窗口。然而,在一个聚合查询的上下文中,这是行不通的,因为涉及到的列: old_salary, new_salary, log_date 并不属于查询的聚合级别,会导致下列错误:
ORA-00979: not a GROUP BY expression

E: MIN 和 MAX 函数是聚合函数,然而,上述语法中它们被用于分析函数。这是行不通的,因为涉及到的列:old_salary, new_salary, log_date 并不属于查询的聚合级别,会导致下列错误:
ORA-00979: not a GROUP BY expression

F: 这可能就是所谓的“暴力解法”。将log_date格式化为“可排序”的固定长度的字符串,并且将薪水拼接于它,然后应用一个简单的MIN/MAX聚合函数,确实产生了两个聚合值,它们包含了时间上的第一个和最后一个薪水值,这是可以从聚合表达式中抽取出来的。在分析函数特别是FIRST/LAST函数被引入之前,这个方法实际上是最有效的。

使用道具 举报

回复
论坛徽章:
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-1-13 20:23 | 显示全部楼层
这个题目去掉选项可以用作应用题

使用道具 举报

回复

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

本版积分规则 发表回复

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