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

 找回密码
 注册
查看: 2161|回复: 2

[每日一题] PL/SQL Challenge 每日一题:2017-2-3 AVG求平均值

[复制链接]
论坛徽章:
453
秀才
日期:2015-08-18 09:49:27举人
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-2-10 04:55 | 显示全部楼层 |阅读模式

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

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

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

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

作者:Kim Berg Hansen

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

我有一张月度的销售表:

create table plch_sales (
   item  varchar2(10) not null
, mth   date         not null
, qty   number       not null
)
/

insert into plch_sales values ('Snowshoe', date '2014-12-01', 12);
insert into plch_sales values ('Snowshoe', date '2015-01-01', 24);
insert into plch_sales values ('Snowshoe', date '2015-02-01', 38);
insert into plch_sales values ('Snowshoe', date '2015-12-01', 10);
insert into plch_sales values ('Snowshoe', date '2016-01-01', 22);
insert into plch_sales values ('Snowshoe', date '2016-02-01', 36);
insert into plch_sales values ('Snowshoe', date '2016-12-01', 11);
insert into plch_sales values ('Snowshoe', date '2017-01-01', 26);

insert into plch_sales values ('Sunshade', date '2014-07-01', 52);
insert into plch_sales values ('Sunshade', date '2014-08-01', 66);
insert into plch_sales values ('Sunshade', date '2015-06-01', 43);
insert into plch_sales values ('Sunshade', date '2015-07-01', 55);
insert into plch_sales values ('Sunshade', date '2015-08-01', 64);
insert into plch_sales values ('Sunshade', date '2016-06-01', 41);
insert into plch_sales values ('Sunshade', date '2016-07-01', 58);
insert into plch_sales values ('Sunshade', date '2016-08-01', 65);

commit
/

我想要一张报表,每行是该项物品在不同的年份、相同的月份的销售平均额。例如,雪鞋(Snowshoe)十二月份的销售额在2014是12, 2015是10,2016是11, 这样月平均就是11。

为此我写了这个未完成的查询:

select item
     , to_char(mth, 'YYYY Mon') as month
     , qty
     , ##REPLACE##
          as mth_avg
  from plch_sales
order by item, mth
/

哪些选项包含了一个列别名为MTH_AVG的表达式,可以用来取代 ##REPLACE##,使得查询返回这个输出:

ITEM       MONTH                    QTY    MTH_AVG
---------- ----------------- ---------- ----------
Snowshoe   2014 Dec                  12         11
Snowshoe   2015 Jan                  24         24
Snowshoe   2015 Feb                  38         37
Snowshoe   2015 Dec                  10         11
Snowshoe   2016 Jan                  22         24
Snowshoe   2016 Feb                  36         37
Snowshoe   2016 Dec                  11         11
Snowshoe   2017 Jan                  26         24
Sunshade   2014 Jul                  52         55
Sunshade   2014 Aug                  66         65
Sunshade   2015 Jun                  43         42
Sunshade   2015 Jul                  55         55
Sunshade   2015 Aug                  64         65
Sunshade   2016 Jun                  41         42
Sunshade   2016 Jul                  58         55
Sunshade   2016 Aug                  65         65


(A)
avg(qty) over (
   partition by item, extract(month from mth)
)

(B)
avg(qty) over (
   partition by item, mth
)

(C)
avg(qty) over (
   partition by item, trunc(mth, 'Mon')
)

(D)
avg(qty) over (
   partition by item, to_char(mth, 'Mon')
)

(E)
avg(qty) over (
   partition by item
   order by extract(month from mth)
)

(F)
sum(qty) over (
   partition by item, extract(month from mth)
) / count(*) over (
   partition by item, extract(month from mth)
)

(G)
(
   select avg(qty)
     from plch_sales mth_sales
    where mth_sales.item = plch_sales.item
      and extract(month from mth_sales.mth) =
             extract(month from plch_sales.mth)
)

(H)
(
   select avg(qty)
     from plch_sales mth_sales
    where mth_sales.item = plch_sales.item
      and mth_sales.mth  = plch_sales.mth
)
论坛徽章:
25
ITPUB官方微博粉丝徽章
日期:2011-08-17 10:35:36秀才
日期:2017-03-02 10:35:322016猴年福章
日期:2016-02-18 09:31:302016猴年福章
日期:2016-02-23 09:58:342015年新春福章
日期:2015-03-06 11:57:312014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:31ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262012新春纪念徽章
日期:2012-01-04 11:53:29
发表于 2017-2-10 11:50 | 显示全部楼层
正确答案:A ,D, F,G

B,C 日期中有年信息所以没法group到一起
E 加了order by变成了逐行AVG了
H 也是因为有年的信息关联出来只能是1:1

使用道具 举报

回复
论坛徽章:
453
秀才
日期:2015-08-18 09:49:27举人
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-2-11 05:26 | 显示全部楼层
答案ADFG, 2楼得奖。

A:(推荐)
利用分析函数AVG,我们能够用物品以及月份数作分区,得到我们想要的结果。
B: 嗯,在PARTITION子句中使用ITEM和MTH会使得每个分区变成单独一行记录,因此AVG和QTY是相同的,返回了这个错误结果:

ITEM       MONTH                    QTY    MTH_AVG
---------- ----------------- ---------- ----------
Snowshoe   2014 Dec                  12         12
Snowshoe   2015 Jan                  24         24
Snowshoe   2015 Feb                  38         38
Snowshoe   2015 Dec                  10         10
Snowshoe   2016 Jan                  22         22
Snowshoe   2016 Feb                  36         36
Snowshoe   2016 Dec                  11         11
Snowshoe   2017 Jan                  26         26
Sunshade   2014 Jul                  52         52
Sunshade   2014 Aug                  66         66
Sunshade   2015 Jun                  43         43
Sunshade   2015 Jul                  55         55
Sunshade   2015 Aug                  64         64
Sunshade   2016 Jun                  41         41
Sunshade   2016 Jul                  58         58
Sunshade   2016 Aug                  65         65

C: 把MTH截断到最近一个月毫无帮助,因为数据中的MTH值实际上已经被截断过了。所以我们得到了和B一样的错误结果。
D: 在MTH上使用TO_CHAR而不是TRUNC是可以的,只要我们使用的日期格式能够给我们唯一确定的月份而不带年份。因为我们此处用了'Mon', 我们得到和A一样的正确结果。

E: 在分析函数的ORDER BY子句中使用月份数是用错了地方,它应该入选项A放在PARTITION子句中才对。这个选项产生了某种“滚动平均数”的错误结果:

ITEM       MONTH                    QTY    MTH_AVG
---------- ----------------- ---------- ----------
Snowshoe   2014 Dec                  12     22.375
Snowshoe   2015 Jan                  24         24
Snowshoe   2015 Feb                  38       29.2
Snowshoe   2015 Dec                  10     22.375
Snowshoe   2016 Jan                  22         24
Snowshoe   2016 Feb                  36       29.2
Snowshoe   2016 Dec                  11     22.375
Snowshoe   2017 Jan                  26         24
Sunshade   2014 Jul                  52       49.8
Sunshade   2014 Aug                  66       55.5
Sunshade   2015 Jun                  43         42
Sunshade   2015 Jul                  55       49.8
Sunshade   2015 Aug                  64       55.5
Sunshade   2016 Jun                  41         42
Sunshade   2016 Jul                  58       49.8
Sunshade   2016 Aug                  65       55.5

F:(不推荐)
用分析函数SUM除以分析函数COUNT确实(至少在这个例子中)给了我们和A选项的AVG相同的结果。
G:(不推荐)
这是选项A的一个非分析函数(性能差一点)的版本,返回了相同的正确输出。
H: 这是选项B的一个非分析函数的版本,返回了相同的错误输出。

使用道具 举报

回复

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

本版积分规则

久等啦!10张门票开启你的DTCC2017之旅~

2017中国数据库技术大会将于2017年5月11-13日如约而至,本届大会以“数据驱动•价值发现”为主题,共设定2大主场和21个技术专场,云集海内外120+位技术大牛,共同探讨Oracle、MySQL、NoSQL、云端数据库、区块链、深度学习等领域的前瞻性热点话题。
即日起,填写DTCC2017会前调查问卷,即有机会赢取价值2600元的大会门票1张!仅限10张!
----------------------------------------
活动截止时间:2017年5月5日统一公布

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