[每日一题] PL/SQL Challenge 每日一题：2016-7-21 MEDIAN函数 学习笔记
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者： Kim Berg Hansen
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我有一张表，保存着统计测试的结果：
create table plch_stats (
test_id integer not null
, attempt integer not null
, result integer not null
)
/
insert into plch_stats values (10, 1, 3)
/
insert into plch_stats values (10, 2, 54)
/
insert into plch_stats values (10, 3, 52)
/
insert into plch_stats values (10, 4, 55)
/
insert into plch_stats values (10, 5, 49)
/
insert into plch_stats values (20, 1, 14)
/
insert into plch_stats values (20, 2, 13)
/
insert into plch_stats values (20, 3, 15)
/
insert into plch_stats values (20, 4, 13)
/
insert into plch_stats values (20, 5, 15)
/
insert into plch_stats values (20, 6, 123)
/
commit
/
对于每个TEST_ID，测试被运行多次，每次尝试的结果都被保存。
我希望知道每个TEST_ID的中位数结果。（中位数的定义是一个有序集合的中间值；如果中间值不止一个，则取两个中间值的平均）
哪些选项包含了一个查询，可以向我显示每个测试的中位数，输出如下：
TEST_ID TEST_MEDIAN
---------- -----------
10 52
20 14.5
(A)
select test_id
, avg(result) test_median
from plch_stats
group by test_id
order by test_id
/
(B)
select test_id
, median(result) test_median
from plch_stats
group by test_id
order by test_id
/
(C)
select test_id
, percentile_cont(.5) within group (order by result) test_median
from plch_stats
group by test_id
order by test_id
/
(D)
select test_id
, percentile_disc(.5) within group (order by result) test_median
from plch_stats
group by test_id
order by test_id
/
(E)
select test_id
, case mod(max(cnt), 2)
when 1 then
max(case bucket when 1 then result end)
else
( max(case bucket when 1 then result end)
+ min(case bucket when 2 then result end)
) / 2
end test_median
from (
select test_id
, result
, ntile(2) over (partition by test_id order by result) bucket
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
(F)
select test_id
, avg(case
when rn between floor(1 + (cnt - 1) / 2)
and ceil (1 + (cnt - 1) / 2)
then result
end) test_median
from (
select test_id
, result
, row_number() over (partition by test_id order by result) rn
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者： Kim Berg Hansen
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我有一张表，保存着统计测试的结果：
create table plch_stats (
test_id integer not null
, attempt integer not null
, result integer not null
)
/
insert into plch_stats values (10, 1, 3)
/
insert into plch_stats values (10, 2, 54)
/
insert into plch_stats values (10, 3, 52)
/
insert into plch_stats values (10, 4, 55)
/
insert into plch_stats values (10, 5, 49)
/
insert into plch_stats values (20, 1, 14)
/
insert into plch_stats values (20, 2, 13)
/
insert into plch_stats values (20, 3, 15)
/
insert into plch_stats values (20, 4, 13)
/
insert into plch_stats values (20, 5, 15)
/
insert into plch_stats values (20, 6, 123)
/
commit
/
对于每个TEST_ID，测试被运行多次，每次尝试的结果都被保存。
我希望知道每个TEST_ID的中位数结果。（中位数的定义是一个有序集合的中间值；如果中间值不止一个，则取两个中间值的平均）
哪些选项包含了一个查询，可以向我显示每个测试的中位数，输出如下：
TEST_ID TEST_MEDIAN
---------- -----------
10 52
20 14.5
(A)
select test_id
, avg(result) test_median
from plch_stats
group by test_id
order by test_id
/
(B)
select test_id
, median(result) test_median
from plch_stats
group by test_id
order by test_id
/
(C)
select test_id
, percentile_cont(.5) within group (order by result) test_median
from plch_stats
group by test_id
order by test_id
/
(D)
select test_id
, percentile_disc(.5) within group (order by result) test_median
from plch_stats
group by test_id
order by test_id
/
(E)
select test_id
, case mod(max(cnt), 2)
when 1 then
max(case bucket when 1 then result end)
else
( max(case bucket when 1 then result end)
+ min(case bucket when 2 then result end)
) / 2
end test_median
from (
select test_id
, result
, ntile(2) over (partition by test_id order by result) bucket
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
(F)
select test_id
, avg(case
when rn between floor(1 + (cnt - 1) / 2)
and ceil (1 + (cnt - 1) / 2)
then result
end) test_median
from (
select test_id
, result
, row_number() over (partition by test_id order by result) rn
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
答案A
求平均数，错误。
答案B
median（）为求中位数函数，正确。
答案C，D
C可以，D不可以。
具体参考以下文章
http://blog.itpub.net/29074224/viewspace-2123194/
具体参考以下文章
http://blog.itpub.net/29074224/viewspace-2123194/
答案E
ntile(n)函数
将数据分为n片，并排序，eg
SQL> select id,it,ntile(3) over (order by it) from t6;
ID IT NTILE(3)OVER(ORDERBYIT)
---------- ---------- -----------------------
1 1 1
1 2 1
1 3 2
1 4 2
1 5 3
将数据分为n片，并排序，eg
SQL> select id,it,ntile(3) over (order by it) from t6;
ID IT NTILE(3)OVER(ORDERBYIT)
---------- ---------- -----------------------
1 1 1
1 2 1
1 3 2
1 4 2
1 5 3
select test_id
, case mod(max(cnt), 2) 取余
when 1 then
max(case bucket when 1 then result end)
else
( max(case bucket when 1 then result end)
+ min(case bucket when 2 then result end)
) / 2
end test_median
from (
select test_id
, result
, ntile(2) over (partition by test_id order by result) bucket
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
, case mod(max(cnt), 2) 取余
when 1 then
max(case bucket when 1 then result end)
else
( max(case bucket when 1 then result end)
+ min(case bucket when 2 then result end)
) / 2
end test_median
from (
select test_id
, result
, ntile(2) over (partition by test_id order by result) bucket
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
答案F
ceil和floor函数
ceil(n) 取大于等于数值n的最小整数；
floor(n)取小于等于数值n的最大整数
select test_id
, avg(case
when rn between floor(1 + (cnt - 1) / 2)
and ceil (1 + (cnt - 1) / 2)
then result
end) test_median
from (
select test_id
, result
, row_number() over (partition by test_id order by result) rn
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
, avg(case
when rn between floor(1 + (cnt - 1) / 2)
and ceil (1 + (cnt - 1) / 2)
then result
end) test_median
from (
select test_id
, result
, row_number() over (partition by test_id order by result) rn
, count(*) over (partition by test_id) cnt
from plch_stats s
)
group by test_id
order by test_id
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29074224/viewspace-2123186/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
