oracle 每日一题-avg求平均值

原始出处：
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

)

select extract(year from date'2011-05-17')year from dual;  

      YEAR  

----------  

      2011  

select extract(month from date'2011-05-17')month from dual;  

     MONTH  

----------  

         5  

select extract(day from date'2011-05-17')day from dual;  

       DAY  

----------  

        17  

//获取两个日期之间的具体时间间隔，extract函数是最好的选择  

select extract(day from dt2-dt1)day  

      ,extract(hour from dt2-dt1)hour  

      ,extract(minute from dt2-dt1)minute  

      ,extract(second from dt2-dt1)second  

from (  

     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1  

           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2  

     from dual)  

/  

       DAY       HOUR     MINUTE     SECOND  

---------- ---------- ---------- ----------  

       102          4          1         46  

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的一个非分析函数的版本，返回了相同的错误输出。