中位值:在每一个group中,值从小到大排列,中间那个值;或者中间那个值及后面的值的平均值。
如下表:
VehicleModelID | NewCarAmount |
1 | 200 |
1 | 300 |
1 | 400 |
1 | 500 |
2 | 500 |
2 | 600 |
2 | 700 |
;WITH OrdersRN AS
(
SELECT [VehicleModelID], NewCarAmount,
ROW_NUMBER() OVER(PARTITION BY [VehicleModelID] ORDER BY NewCarAmount) AS RowNum,
COUNT(*) OVER(PARTITION BY [VehicleModelID]) AS Cnt
FROM ##tblNewCarAmount
)
select [VehicleModelID],avg(NewCarAmount) NewCarAmount from (
SELECT [VehicleModelID],NewCarAmount,RowNum,Cnt
FROM OrdersRN o
WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2)
)a
group by [VehicleModelID]
查询结果为:
VehicleModelID | NewCarAmount |
1 | 350 |
2 | 600 |