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

 找回密码
 注册
查看: 4612|回复: 6

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

[复制链接]
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期: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:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 2017-1-4 05:58 | 显示全部楼层 |阅读模式

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

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

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

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

作者:Kim Berg Hansen

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

我有一张表,指定了我的安装程序需要执行的每个任务:

create table plch_installer (
   step     integer not null
, substep  integer not null
, task     varchar2(20)
, constraint plch_installer_pk primary key (step, substep)
)
/
insert into plch_installer values (10, 12, 'Copy ini file')
/
insert into plch_installer values (10, 15, 'Copy zip files')
/
insert into plch_installer values (20, 15, 'Unzip zip files')
/
insert into plch_installer values (30, 12, 'Exec initialization')
/
insert into plch_installer values (30, 16, 'Exec image sorting')
/
insert into plch_installer values (30, 18, 'Exec tree generator')
/
insert into plch_installer values (30, 19, 'Exec path pruning')
/
insert into plch_installer values (40, 15, 'Clean temp garbage')
/
commit
/

总共有四个主要步骤 (copy, unzip, exec, clean), 每个步骤有一个或多个子步骤构成,包含着实际的任务名称。

为了显示进度条,我需要知道在每个子任务执行之后的“完成百分比”。

为了计算“完成百分比”我把它定义如下:每个主要步骤花费相同的时间,在每个步骤中,每个子步骤又消耗相同的时间。

因此,由于我们有四个主要步骤,每个主要步骤消耗25%的时间,在步骤10里面我们有两个子步骤,所以它们会各自消耗25%时间的一半。
类似的,在步骤30中我们有四个子步骤,所以它们会各自消耗25%时间的四分之一。以此类推。

根据这些步骤和子步骤消耗时间的定义,“完成百分比”就是每个子步骤消耗时间的滚动累计。

哪些选项包含了一个查询,计算上述定义的PCT_DONE,结果如下:

      STEP    SUBSTEP   PCT_DONE TASK
---------- ---------- ---------- --------------------
        10         12       12.5 Copy ini file
        10         15         25 Copy zip files
        20         15         50 Unzip zip files
        30         12      56.25 Exec initialization
        30         16       62.5 Exec image sorting
        30         18      68.75 Exec tree generator
        30         19         75 Exec path pruning
        40         15        100 Clean temp garbage

(A)
select step, substep
     , sum(sub_pct) over (
          order by step, substep
          rows between unbounded preceding and current row
       ) as pct_done
     , task
  from (
   select i.step, i.substep
        , 100 / count(*) over (partition by i.step)
              / count(distinct i.step) over () as sub_pct
        , i.task
     from plch_installer i
  )
order by step, substep
/

(B)
select i.step, i.substep
     , sum(
          100 / count(*) over (partition by i.step)
              / count(distinct i.step) over ()
       ) over (
          order by i.step, i.substep
          rows between unbounded preceding and current row
       ) as pct_done
     , i.task
  from plch_installer i
order by i.step, i.substep
/

(C)
select step, substep
     , sum(100 / sub_cnt / step_cnt) over (
          order by step, substep
          rows between unbounded preceding and current row
       ) as pct_done
     , task
  from (
   select i.step, i.substep
        , count(*) over (partition by i.step) as sub_cnt
        , count(distinct i.step) over () as step_cnt
        , i.task
     from plch_installer i
  )
order by step, substep
/

(D)
select i.step, i.substep
     , 100 * (
          (
             count(distinct i.step) over (
                order by i.step
                rows between unbounded preceding and current row
             ) - 1 + (
                count(*) over (
                   partition by i.step
                   order by i.substep
                   rows between unbounded preceding and current row
                ) / count(*) over (partition by i.step)
             )
          ) / count(distinct i.step) over ()
       ) as pct_done
     , i.task
  from plch_installer i
order by i.step, i.substep
/

(E)
select i.step, i.substep
     , 100 * (
          (
             dense_rank() over (
                order by i.step
             ) - 1 + (
                row_number() over (
                   partition by i.step
                   order by i.substep
                ) / count(*) over (partition by i.step)
             )
          ) / count(distinct i.step) over ()
       ) as pct_done
     , i.task
  from plch_installer i
order by i.step, i.substep
/

论坛徽章:
15
秀才
日期:2017-01-20 11:04:31秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2016-11-09 14:32:17秀才
日期:2016-11-09 14:32:49秀才
日期:2016-11-09 14:32:49秀才
日期:2016-11-09 14:32:49秀才
日期:2016-11-09 14:32:49技术图书徽章
日期:2016-11-09 14:33:08秀才
日期:2016-11-09 14:46:36
发表于 2017-1-4 10:06 | 显示全部楼层
我选ACE:
A、先通过子查询查出各个子步骤耗时百分比(SUB_PCT),再在外部做整合
B、语法错误,聚合函数不能嵌套
C、先通过子查询查出主要步骤数(STEP_CNT)和各子步骤数(SUB_CNT),再在外部做整合
D、语法错误,order by应该替换为dense_rank函数
E、D选项的正确示范

使用道具 举报

回复
论坛徽章:
263
乌索普
日期:2016-07-29 01:46:29射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期: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
发表于 2017-1-4 10:55 | 显示全部楼层
答案: ACE

A 常规写法,先统计总的步数,以及每步中的子步数,计算好每一子步占的百分值,最后用 SUM() over(ORDER BY ..) 累加。
B 这样分析函数嵌套,一步搞定,好像分析函数不能被开窗函数嵌套
C 与A类似,只是计算分值的表达式放到了 SUM中最后计算
D 写法也是想一步搞定,但好像开窗函数不能直接用于表达式
E 总算一步搞定,这种写法的人,思想很牛X 

使用道具 举报

回复
论坛徽章:
263
乌索普
日期:2016-07-29 01:46:29射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期: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
发表于 2017-1-4 10:56 | 显示全部楼层
自己的先看题,不看后面答案的第一反应写法:

SQL> select step,
  2         substep,
  3         sum(100/(step_count*substep_count)) over(order by step,substep) pct_done,
  4         task
  5  from (
  6  select step,
  7         substep,
  8         task,
  9         count(distinct step) over() step_count,
10         count(substep) over(partition by step) substep_count
11    from plch_installer t
12  )
13  order by step,substep
14  /
                                   STEP                                 SUBSTEP   PCT_DONE TASK
--------------------------------------- --------------------------------------- ---------- --------------------
                                     10                                      12       12.5 Copy ini file
                                     10                                      15         25 Copy zip files
                                     20                                      15         50 Unzip zip files
                                     30                                      12      56.25 Exec initialization
                                     30                                      16       62.5 Exec image sorting
                                     30                                      18      68.75 Exec tree generator
                                     30                                      19         75 Exec path pruning
                                     40                                      15        100 Clean temp garbage
8 rows selected

使用道具 举报

回复
论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期: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
发表于 2017-1-4 19:44 | 显示全部楼层
如果有的子步骤还有子步骤,怎么写通用

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期: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:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
 楼主| 发表于 2017-1-5 04:03 | 显示全部楼层
答案ACE, 2楼得奖。

A: 在内联视图中,我们除以一个步骤中的子步骤的数量,然后又除以步骤的数量,这样就得到每个子步骤所花费的时间比例,乘以100得到百分比。然后在外层查询中,我们简单地对子步骤的百分比做了一个滚动累计,得到每个子步骤的总百分比。
B: 如果要在其它分析函数的结果之上再执行分析函数,方法是使用内联视图,如前一选项所示。像此处试图嵌套使用分析函数是不可能的,这会报错:
ORA-30483: window functions are not allowed here.
C: 这个和A选项很类似,只是内联视图仅仅计算了子步骤的数量和步骤的数量,然后外层查询在滚动累计分析函数中执行了百分比的计算。
D: 此处的思路是创建一个“步骤比例”的滚动累计,然后除以步骤数量。第一个分析函数COUNT应该是一个滚动的步骤计数,减去1之后我们得到0,1,2,3。然后我们加上子步骤的滚动计数,它除以步骤中的子步骤数量,得到“一个步骤的比例”。
公式本身是正确的,但是这个选项的问题在于,带DISTINCT的分析函数COUNT不能够是滚动的累计,ORDER和窗口子句不允许和DISTINCT连用。这会报错:
ORA-30847: ORDER BY not allowed here.
E: 前一选项的问题(无法执行滚动的DISTINCT计数)可以用DENSE_RANK取代来修正,这我了我们滚动DISTINCT计数相同的结果(如果允许DISTINCT滚动计数的话),这个选项还简化了前一选项的滚动子步骤计数,用ROW_NUMBER来得到相同的结果。

总之这个选项使得前一选项所描述的公式正常工作,给了我们正确的结果而无需使用内联视图。

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期: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:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
 楼主| 发表于 2017-1-5 04:04 | 显示全部楼层
〇〇 发表于 2017-1-4 19:44
如果有的子步骤还有子步骤,怎么写通用

他这个结构是不可扩展的,除非再加一列,或者改成父子指针。

使用道具 举报

回复

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

本版积分规则

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