SQLSERVER 求中位数

[二月十六]

有id和数量两列，求每个id的中位数，我写了一个，感觉不是很好似的，也没想起来又啥好写法，贴在这，看看大家有啥好写法没有

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] INT,tcount DECIMAL(18,2))
Insert #T
select 1,1 union all
select 1,2 union all
select 1,3 union ALL
select 1,4 union ALL
select 1,5 union all
select 2,6 union all
select 2,7 union all
select 3,8
Go
--测试数据结束
;WITH cte AS (
SELECT id,tcount,
       COUNT(1) OVER (PARTITION BY id) cou,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY tcount) rn
FROM #T
)
SELECT id,AVG(cte.tcount) AS 中位数 FROM cte WHERE rn = cte.cou/2+1 AND cte.cou%2=1 GROUP BY cte.id
UNION ALL
SELECT a.id,
       sum(a.tcount + ISNULL(b.tcount, 0))/2
FROM cte a
    LEFT JOIN cte b
        ON a.id = b.id
WHERE a.rn = a.cou/2
      AND a.cou % 2 = 0
      AND b.rn = a.rn + 1
GROUP BY a.id
ORDER BY id

[唐诗三百首]

select id,中位数=avg(tcount)
 from #T
 group by id

[二月十六]

引用 2 楼 唐诗三百首 的回复:

select id,中位数=avg(tcount)
 from #T
 group by id

额，这样不太行吧……按照这个测试数据可以，最后结果一样，但是换个数就不行了，中位数和平均数还是不一样

[RINK_1]

;WITH cte AS (
SELECT id,tcount,
       COUNT(1) OVER (PARTITION BY id) cou,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY tcount) rn
FROM #T
)
SELECT id,avg(tcount) as 中位数 
FROM cte WHERE rn between ceiling(cou*1.0/2) and ceiling((cou+1)*1.0/2)
group by id

[二月十六]

引用 4 楼 RINK_1 的回复:

;WITH cte AS (
SELECT id,tcount,
       COUNT(1) OVER (PARTITION BY id) cou,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY tcount) rn
FROM #T
)
SELECT id,avg(tcount) as 中位数 
FROM cte WHERE rn between ceiling(cou*1.0/2) and ceiling((cou+1)*1.0/2)
group by id

可以

[吉普赛的歌]

引用 4 楼 RINK_1 的回复:

;WITH cte AS (
SELECT id,tcount,
       COUNT(1) OVER (PARTITION BY id) cou,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY tcount) rn
FROM #T
)
SELECT id,avg(tcount) as 中位数 
FROM cte WHERE rn between ceiling(cou*1.0/2) and ceiling((cou+1)*1.0/2)
group by id

确实很巧妙！