問(wèn)題:計(jì)算一列數(shù)字值的中間值（中間值就是一組有序元素中間成員的值）。例如，查找DEPTNO 20中工資的中間數(shù)。如下列工資：

select sal

?? from emp

where deptno = 20

order by sal

??????? SAL

----------

??????? 800

?????? 1100

?????? 2975

?????? 3000

?????? 3000

中間數(shù)為2975。

解決方案

除了Oracle解決方案（用函數(shù)計(jì)算中間數(shù)）之外，其他所有解決方案都是以Rozenshtein、Abramovich和Birger在 Optimizing Transact-SQL: Advanced Programming Techniques (SQL Forum Press, 1997)中描述的方法為基礎(chǔ)的。與傳統(tǒng)的自聯(lián)接相比，窗口函數(shù)的引入，使解決方案更為有效。

DB2

使用窗口函數(shù)COUNT(*) OVER和ROW_NUMBER，查找中間數(shù)：

1?? select avg(sal)

2???? from (

3?? select sal,

4????????? count(*) over() total,

5????????? cast(count(*) over() as decimal)/2 mid,

6????????? ceil(cast(count(*) over() as decimal)/2) next,

7????????? row_number() over (order by sal) rn

8???? from emp

9??? where deptno = 20

10????????? ) x

11??? where ( mod(total,2) = 0

12??????????? and rn in ( mid, mid+1 )

13????????? )

14?????? or ( mod(total,2) = 1

15??????????? and rn = next

16????????? )

MySQL和PostgreSQL

使用自聯(lián)接查找中間數(shù)：

1?? select avg(sal)

2???? from (

3?? select e.sal

4???? from emp e, emp d

5??? where e.deptno = d.deptno

6????? and e.deptno = 20

7??? group by e.sal

8?? having sum(case when e.sal = d.sal then 1 else 0 end)

9???????????????????????????? >= abs(sum(sign(e.sal - d.sal)))

10????????? )

Oracle

使用函數(shù)MEDIAN（Oracle Database 10g）或PERCENTILE_CONT（Oracle9i Database）：

1 select median (sal)

2??? from emp

3?? where deptno=20

1 select percentile_cont(0.5)

2????????? within group(order by sal)

3??? from emp

4?? where deptno=20

對(duì)于Oracle8i Database，使用DB2解決方案。對(duì)于Oracle8i Database之前的版本，可以采用PostgreSQL/MySQL解決方案。

SQL Server

使用窗口函數(shù)COUNT(*) OVER和ROW_NUMBER，可得到中間數(shù)：

1?? select avg(sal)

2???? from (

3?? select sal,

4????????? count(*)over() total,

5????????? cast(count(*)over() as decimal)/2 mid,

6????????? ceiling(cast(count(*)over() as decimal)/2) next,

7????????? row_number()over(order by sal) rn

8???? from emp

9??? where deptno = 20

10????????? ) x

11??? where ( total%2 = 0

12??????????? and rn in ( mid, mid+1 )

13????????? )

14?????? or ( total%2 = 1

15??????????? and rn = next

16????????? )

討論

DB2和SQL Server

DB2和SQL Server 解決方案的唯一差別是語(yǔ)法的稍許不同：SQL Server用“%”求模，而DB2使用MOD函數(shù)；其余的都相同。內(nèi)聯(lián)視圖X返回三個(gè)不同的計(jì)數(shù)值，TOTAL、MID和NEXT，還用到由 ROW_NUMBER生成的RN。這些附加列有助于求解中間數(shù)。檢驗(yàn)內(nèi)聯(lián)視圖X的結(jié)果集，就會(huì)看到這些列表示的意義：

select sal,

??????? count(*)over() total,

??????? cast(count(*)over() as decimal)/2 mid,

??????? ceil(cast(count(*)over() as decimal)/2) next,

??????? row_number()over(order by sal) rn

?? from emp

where deptno = 20

SAL TOTAL?? MID NEXT??? RN

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

800????? 5?? 2.5???? 3???? 1

1100????? 5?? 2.5???? 3???? 2

2975????? 5?? 2.5???? 3???? 3

3000????? 5?? 2.5???? 3???? 4

3000????? 5?? 2.5???? 3???? 5

要得到中間數(shù)，一定要把SAL值由低到高排序。由于DEPTNO 20中的職員數(shù)是奇數(shù)，因此它的中間數(shù)就是其RN與NEXT相等的SAL（即大于職員總數(shù)除以2的最小整數(shù)）。

如果結(jié)果集返回奇數(shù)行，WHERE子句的第一部分（第11～13行）條件不滿足。如果能夠確定結(jié)果集是奇數(shù)行，則可以簡(jiǎn)化為：

select avg(sal)

?? from (

select sal,

??????? count(*)over() total,

??????? ceil(cast(count(*)over() as decimal)/2) next,

??????? row_number()over(order by sal) rn

?? from emp

where deptno = 20

??????? ) x

where rn = next

令人遺憾的是，如果結(jié)果集包含偶數(shù)行，上述簡(jiǎn)化的解決方案就行不通。在最初的解決方案中，采用MID列中的值處理偶數(shù)行。想想DEPTNO 30的內(nèi)聯(lián)視圖X的結(jié)果會(huì)怎樣，該部門(mén)有6名職員：

select sal,

??????? count(*)over() total,

??????? cast(count(*)over() as decimal)/2 mid,

??????? ceil(cast(count(*)over() as decimal)/2) next,

??????? row_number()over(order by sal) rn

?? from emp

where deptno = 30

SAL TOTAL?? MID NEXT??? RN

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

950????? 6???? 3???? 3???? 1

1250????? 6???? 3???? 3???? 2

1250????? 6???? 3???? 3???? 3

1500????? 6???? 3???? 3???? 4

1600????? 6???? 3???? 3???? 5

2850????? 6???? 3???? 3???? 6

由于返回了偶數(shù)行，則采用下述方式計(jì)算中間數(shù)：計(jì)算RN分別等于MID和MID + 1兩行的平均數(shù)。

MySQL和PostgreSQL

根據(jù)第一個(gè)自聯(lián)接表EMP計(jì)算中間數(shù)，而該表返回了所有工資的笛卡兒積（GROUP BY??? E.SAL會(huì)去掉重復(fù)值）。HAVING子句使用函數(shù)SUM計(jì)算E.SAL等于D.SAL的次數(shù)；如果這個(gè)值大于等于E.SAL且大于D.SAL次數(shù)，那 么該行就是中間數(shù)。在SELECT列表中加入SUM就可以觀察到這種情況：

select e.sal,

??????? sum(case when e.sal=d.sal

???????????????? then 1 else 0 end) as cnt1,

??????? abs(sum(sign(e.sal - d.sal))) as cnt2

?? from emp e, emp d

where e.deptno = d.deptno

??? and e.deptno = 20

group by e.sal

SAL CNT1 CNT2

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

800???? 1???? 4

1100???? 1???? 2

2975???? 1???? 0

3000???? 4???? 6

Oracle

在Oracle Database 10g或Oracle9i Database中，可以使用Oracle提供的函數(shù)計(jì)算中間數(shù)；對(duì)于Oracle8i Database，可以采用DB2解決方案；其他版本必須采用PostgreSQL解決方案。顯然可以用MEDIAN函數(shù)計(jì)算中間值，用 PERCENTILE_CONT函數(shù)也可以計(jì)算中間值就不那么顯而易見(jiàn)了。傳遞給PERCENTILE_CONT的值0.5是一個(gè)百分比值。子句 WITHIN GROUP (ORDER BY SAL)確定PERCENTILE_CONT要搜索哪些有序行（記住，中間值就是一組已排序值的中間值）。返回的值就是搜索的有序行中符合給定百分比（在 這個(gè)例子中是0.5，因?yàn)槠鋬蓚€(gè)邊界值分別為0和1）的值。

SQL語(yǔ)句-計(jì)算中間值