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

 
 注册
热搜:
查看: 4328|回复: 3

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

[复制链接]
论坛徽章:
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-1-11 04:48 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
(原发表于 2011-7-22)

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

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

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

原始出处:


作者: 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))
                        
认证徽章
论坛徽章:
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
2#
发表于 2018-1-11 10:22 | 只看该作者
目测是CD,测试结果是CF,D报了个错误,F这么写法第一次见。其他的都在取最大最小值,比如A和E,B就没有这写法吧。

使用道具 举报

回复
论坛徽章:
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
3#
 楼主| 发表于 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函数被引入之前,这个方法实际上是最有效的。

使用道具 举报

回复
论坛徽章:
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
4#
发表于 2018-1-13 20:23 | 只看该作者
这个题目去掉选项可以用作应用题

使用道具 举报

回复

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

本版积分规则

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