問題:計算一列數字值的中間值（中間值就是一組有序元素中間成員的值）。例如，查找DEPTNO 20中工資的中間數。如下列工資：
select sal
?? from emp
where deptno = 20
order by sal
??????? SAL
----------
??????? 800
?????? 1100
?????? 2975
?????? 3000
?????? 3000
中間數為2975。
解決方案
除了Oracle解決方案（用函數計算中間數）之外，其他所有解決方案都是以Rozenshtein、Abramovich和Birger在 Optimizing Transact-SQL: Advanced Programming Techniques (SQL Forum Press, 1997)中描述的方法為基礎的。與傳統的自聯接相比，窗口函數的引入，使解決方案更為有效。
DB2
使用窗口函數COUNT(*) OVER和ROW_NUMBER，查找中間數：
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
使用自聯接查找中間數：
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
使用函數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
對于Oracle8i Database，使用DB2解決方案。對于Oracle8i Database之前的版本，可以采用PostgreSQL/MySQL解決方案。
SQL Server
使用窗口函數COUNT(*) OVER和ROW_NUMBER，可得到中間數：
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 解決方案的唯一差別是語法的稍許不同：SQL Server用“%”求模，而DB2使用MOD函數；其余的都相同。內聯視圖X返回三個不同的計數值，TOTAL、MID和NEXT，還用到由 ROW_NUMBER生成的RN。這些附加列有助于求解中間數。檢驗內聯視圖X的結果集，就會看到這些列表示的意義：
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
要得到中間數，一定要把SAL值由低到高排序。由于DEPTNO 20中的職員數是奇數，因此它的中間數就是其RN與NEXT相等的SAL（即大于職員總數除以2的最小整數）。
如果結果集返回奇數行，WHERE子句的第一部分（第11～13行）條件不滿足。如果能夠確定結果集是奇數行，則可以簡化為：
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
令人遺憾的是，如果結果集包含偶數行，上述簡化的解決方案就行不通。在最初的解決方案中，采用MID列中的值處理偶數行。想想DEPTNO 30的內聯視圖X的結果會怎樣，該部門有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
由于返回了偶數行，則采用下述方式計算中間數：計算RN分別等于MID和MID + 1兩行的平均數。
MySQL和PostgreSQL
根據第一個自聯接表EMP計算中間數，而該表返回了所有工資的笛卡兒積（GROUP BY??? E.SAL會去掉重復值）。HAVING子句使用函數SUM計算E.SAL等于D.SAL的次數；如果這個值大于等于E.SAL且大于D.SAL次數，那 么該行就是中間數。在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提供的函數計算中間數；對于Oracle8i Database，可以采用DB2解決方案；其他版本必須采用PostgreSQL解決方案。顯然可以用MEDIAN函數計算中間值，用 PERCENTILE_CONT函數也可以計算中間值就不那么顯而易見了。傳遞給PERCENTILE_CONT的值0.5是一個百分比值。子句 WITHIN GROUP (ORDER BY SAL)確定PERCENTILE_CONT要搜索哪些有序行（記住，中間值就是一組已排序值的中間值）。返回的值就是搜索的有序行中符合給定百分比（在 這個例子中是0.5，因為其兩個邊界值分別為0和1）的值。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
