查看: 1162|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2018-7-26 12C: 带函数的WITH子查询

[复制链接]
论坛徽章:
527
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期: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:53
发表于 2018-7-31 04:50 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者:Steven Feuerstein

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

我有一张订单表包含了订单日期和金额:

create table qz_orders (
   id          integer primary key
, orderdate   date
, amount      number
)
/

insert into qz_orders values (101, date '2018-07-01', 12500)
/
insert into qz_orders values (102, date '2018-07-02', 20725)
/
insert into qz_orders values (103, date '2018-07-03',  9475)
/
insert into qz_orders values (104, date '2018-07-05', 16400)
/
insert into qz_orders values (105, date '2018-07-07', 36050)
/
insert into qz_orders values (106, date '2018-07-08', 23450)
/
insert into qz_orders values (107, date '2018-07-10',  8375)
/
insert into qz_orders values (108, date '2018-07-14',  7950)
/
insert into qz_orders values (109, date '2018-07-15', 12325)
/
insert into qz_orders values (110, date '2018-07-16', 10975)
/
insert into qz_orders values (111, date '2018-07-18', 26225)
/
insert into qz_orders values (112, date '2018-07-19', 18050)
/

commit
/

如果我们达到了特定的销售总额的目标,就会有奖金。目标每两周定义一次,因此,为了查看我们是否达到目标,我们需要一个报表来显示每两周的总金额。

我们把两周的期限定义为两个连续的ISO星期,第一周的编号为单数,第二周为偶数。所以一年的第一个双周就是第一周和第二周,第二个双周则是第三周和第四周,以此类推。

(如果一年有53周,这一周被处理为一个特别的周期。对于这样的年份,我们把这个“双周”输出为W53-W01,即使它仅仅包含了第53周。

哪些选项包含的查询可以执行不出错,把订单数据按照上述的双周定义进行分组,在FORTNIGHT列显示该周期覆盖了哪两周,在AMOUNT列显示了该周期的销售额,于是会返回这样的输出:

FORTNIGHT        AMOUNT
------------ ----------
2018 W25-W26      12500
2018 W27-W28     134750
2018 W29-W30      55250

(A)
select to_char(
          trunc(orderdate, 'FN')
        , 'IYYY "W"IW'
       ) || to_char(
          trunc(orderdate, 'FN') + 7
        , '"-W"IW'
       ) as fortnight
     , sum(amount) as amount
  from qz_orders
group by trunc(orderdate, 'FN')
order by trunc(orderdate, 'FN')
/

(B)
select to_char(
          min(orderdate)
        , 'IYYY "W"IW'
       ) || to_char(
          max(orderdate)
        , '"-W"IW'
       ) as fortnight
     , sum(amount) as amount
  from qz_orders
group by case mod(to_number(to_char(orderdate, 'IW')), 2)
             when 1 then trunc(orderdate, 'IW')
                    else trunc(orderdate, 'IW') - 7
          end
order by case mod(to_number(to_char(orderdate, 'IW')), 2)
             when 1 then trunc(orderdate, 'IW')
                    else trunc(orderdate, 'IW') - 7
          end
/

(C)
select to_char(
          case mod(to_number(to_char(orderdate, 'IW')), 2)
             when 1 then trunc(orderdate, 'IW')
                    else trunc(orderdate, 'IW') - 7
          end
        , 'IYYY "W"IW'
       ) || to_char(
          case mod(to_number(to_char(orderdate, 'IW')), 2)
             when 1 then trunc(orderdate, 'IW')
                    else trunc(orderdate, 'IW') - 7
          end + 7
        , '"-W"IW'
       ) as fortnight
     , sum(amount) as amount
  from qz_orders
group by case mod(to_number(to_char(orderdate, 'IW')), 2)
             when 1 then trunc(orderdate, 'IW')
                    else trunc(orderdate, 'IW') - 7
          end
order by case mod(to_number(to_char(orderdate, 'IW')), 2)
             when 1 then trunc(orderdate, 'IW')
                    else trunc(orderdate, 'IW') - 7
          end
/

(D)
with
   function trunc_fortnight(p_date date)
      return date deterministic
   is
   begin
      return case mod(to_number(to_char(p_date, 'IW')), 2)
                when 1 then trunc(p_date, 'IW')
                       else trunc(p_date, 'IW') - 7
             end;
   end;
select to_char(
          trunc_fortnight(orderdate)
        , 'IYYY "W"IW'
       ) || to_char(
          trunc_fortnight(orderdate) + 7
        , '"-W"IW'
       ) as fortnight
     , sum(amount) as amount
  from qz_orders
group by trunc_fortnight(orderdate)
order by trunc_fortnight(orderdate)
/


认证徽章
论坛徽章:
25
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-05-22 15:21:47秀才
日期:2018-05-22 15:21:47秀才
日期:2018-05-22 16:13:08秀才
日期:2018-05-22 16:13:21技术图书徽章
日期: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-7-31 09:17 | 显示全部楼层
抢个沙发先

使用道具 举报

回复
认证徽章
论坛徽章:
25
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-05-22 15:21:47秀才
日期:2018-05-22 15:21:47秀才
日期:2018-05-22 16:13:08秀才
日期:2018-05-22 16:13:21技术图书徽章
日期: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-7-31 09:50 | 显示全部楼层
CD,光看不测试的实在有点难
A. to_char(sysdate,'FN') 没这个用法
B.数据当月并不是完整的一周,min 和max不能正好是2周
C。解决了B。
D。用了with函数跟C一样。


这题,确实值得好好看看。要是自己写,肯定没戏。就这个。to_char(SYSDATE,'IYYY "W"IW')都是第一次见。with以前都是造表,不知道还能造函数。

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期: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-7-31 10:58 | 显示全部楼层
with造函数应该不是sql标准

使用道具 举报

回复
论坛徽章:
527
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期: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:53
 楼主| 发表于 2018-8-1 04:59 | 显示全部楼层
答案CD, 3楼得奖。

A: 不对,不存在FN这种格式(FortNight),所以这会报错:
ORA-01821: date format not recognized.

B: 我们group by的表达式是正确的,但是用订单日期的MIN和MAX来计算则过于简单了。我们会得到这个错误结果,第一个和最后一个周期的FORTNIGHT列值是错的:

FORTNIGHT        AMOUNT
------------ ----------
2018 W26-W26      12500
2018 W27-W28     134750
2018 W29-W29      55250

C: 利用GROUP BY表达式,我们可以计算正确的FORTNIGHT值并且得到所需要的结果。

D: 我们可以把选项C变得简单一点,方法是创建一个函数,包含着"truncate to fortnight"的公式而不是在查询中重复四遍。

使用道具 举报

回复

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

本版积分规则 发表回复

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