SQLでメジアン?

達人に学ぶSQL徹底指南書を読んでいます。

そのP70でいきなりひっかかり、少し調べたメモを書きます。

id	data
1	10
2	10
3	10
4	15
5	15
6	20
7	20
8	20
9	30
10	400

表1 : tmp2

のメジアンを求めるのは
select avg(distinct data) from (
select max(t1.id), t1.data from tmp2 t1, tmp2 t2 group by t1.data having
sum( case when t2.data >= t1.data then 1 else 0 end ) >= count(*)/2 and
sum( case when t2.data <= t1.data then 1 else 0 end ) >= count(*)/2
) t ;

と すれば良いというようなことが書いてあります。

んが、理解できなかったので分解して考えてみました。　まず、内側のselect文だけを実行すると

id	data
4	15
6	20

表2

となります。

つぎに、さらに変形して調べてみます。

select max(t1.id) id, max(t1.data) data,
sum( case when t2.data >= t1.data then 1 else 0 end ) c1,
sum( case when t2.data <= t1.data then 1 else 0 end ) c2,
count(*)/2 c3
from tmp2 t1, tmp2 t2 group by t1.data ;

id	data	c1	c2	c3
1	10	30	9	15.0000
4	15	14	10	10.0000
6	20	15	24	15.0000
9	30	2	9	5.0000
10	400	1	10	5.0000

表3

つまり、t1とt2で 例えばdata=15のc2の部分を求めると
条件が成立するt2は5個{10,10,10,15,15}あって、data=15のt1は2個だから
5*2 = 10となるんですね。　きっと。

で、count(*)/2の値は、grouping されているt1の要素数 {3,2,3,1,1}と t2の要素数10との積となるっと。

とりあえず、どう動いているかまで分かった。