原始出处:
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
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的一个非分析函数的版本,返回了相同的错误输出。
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
利用分析函数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的一个非分析函数的版本,返回了相同的错误输出。