我有這個查詢，我需要繪制圖表。
該圖將顯示每個位置的患者總數、患者最長等待時間（分鐘）和患者中位等待時間。我需要在我的查詢中包含 Medians，這就是我卡住的地方。
示例原始資料：
查詢我到目前為止沒有中位數：
SELECT
[Location],
count([Patient_Number]) Total,
MAX([WaitTime_Min]) LongWait
FROM MyTable
where
[Location] in ('AMB', 'PEDS', 'WALK')
and [EDNurse] is NULL
group by [Location]
輸出：
我需要幫助獲取每個位置的 WaitTime 中位數的最后一列（來自原始資料）。任何幫助，將不勝感激; 謝謝！！
期望的輸出：
uj5u.com熱心網友回復：
PERCENTILE_CONT(for continues values), PERCENTILE_DISC(for discrete value)Sqlserver 2012 中有一些方法可以做到這一點。你可以在這里閱讀更多關于它的資訊。
SELECT
[Location],
count([Patient_Number]) Total,
MAX([WaitTime_Min]) LongWait,
max(MedianDisc) MedianDisc, -- Discrete median
max(MedianCont) MedianCont -- continues median
FROM (
select m.*, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY WaitTime_Min)
OVER (PARTITION BY [Location]) AS MedianCont
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY WaitTime_Min)
OVER (PARTITION BY [Location]) AS MedianDisc
from myTable m
where
[Location] in ('AMB', 'PEDS', 'WALK')
and [EDNurse] is NULL
)tt
group by [Location]
更新：基于 Aaron 評論的這種方法的性能問題，我為 Median 添加了一個純 SQL 計算：
select [Location],
count([Patient_Number]) Total,
MAX([WaitTime_Min]) LongWait,
AVG(1.0 * LongWait) As Median
FROM
(
SELECT [Location],
[Patient_Number] Total,
[WaitTime_Min] LongWait
, ra=row_number() over (partition by [Location] order by [WaitTime_Min])
, rd=row_number() over (partition by [Location] order by [WaitTime_Min] desc)
from myTable m
where [Location] in ('AMB', 'PEDS', 'WALK')
and [EDNurse] is NULL
) as x
where ra between rd-1 and rd 1
group by [Location]
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/404959.html
標籤：
上一篇：如何按間隔建立成功率？