SQL 中位数 函数

[maiuth]

请问SQL Server 中有没有类似Excel的取中位数的函数？没有的话，函数怎么写？

[道素]

CREATE TABLE T1
(
  id  INT NOT NULL IDENTITY CONSTRAINT PK_T1 PRIMARY KEY,
  grp INT NOT NULL,
  val INT NOT NULL
);

CREATE INDEX idx_grp_val ON dbo.T1(grp, val);

INSERT INTO dbo.T1(grp, val)
  VALUES(1, 30),(1, 10),(1, 100),(2, 65),(2, 60),(2, 65),(2, 10);



SELECT grp, Median = AVG(1.0 * val)
FROM
(
   SELECT grp, o.val, rn=ROW_NUMBER() OVER (PARTITION BY grp ORDER BY o.val), c=COUNT(0)OVER(PARTITION BY grp)
   FROM t1 AS o
  
) AS x
WHERE rn IN ((c + 1)/2, (c + 2)/2)
GROUP BY grp;

--SQL Server 2012
SELECT grp, Median=MAX(Median)
FROM
(
   SELECT grp, val
      ,Median=PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY grp)
   FROM t1
) a
GROUP BY grp;

/*
grp         Median
----------- ---------------------------------------
1           30.000000
2           62.500000

(2 row(s) affected)

grp         Median
----------- ----------------------
1           30
2           62.5

(2 row(s) affected)
*/

[m1raco]

取字段 加两列row number，要么相等，要么相差1，你理会一下就能写出来啦

[卖水果的net]

楼主的源数据，是什么样子的呢？

[LongRui888]

sql 中并没有这样的函数，不过你可以百度一下，就有的。 计算中位数，一般是按照某个字段排序，然后去序号中的中间值，如果是偶数就是中间2个值的平均值，如果是奇数，就取那个数。 实际上用sql来实现，不是太方便，个人感觉。

[baidu_35289351]

是不是这个 EXCL ：MID(text,start_num,num_chars) SQL Server ：SUBSTRING ( expression, start, length )

[maiuth]

表：学号，学生姓名，考试科目，学生成绩 分考试科目取学生成绩的中位数

[大咏为恒]

参考楼上各位所说，有了以下思路，还请指教。
select avg(nvl(sales,0))
from
(select *,row_number() over(sort by sales) as rn
from table) a
where rn in (floor((count(rn)+1)%2),floor((count(rn+2)%2))

[大咏为恒]

where里面不能用统计函数，自己批判下自己。

[大咏为恒]

在网上搜到两个解决方案：
1.https://blog.csdn.net/qiujialongjjj/article/details/8007000
WITH OrdersRN AS(
SELECT pid, coadcladcon, ROW_NUMBER() OVER(PARTITION BY pid ORDER BY coadcladcon) AS RowNum,
COUNT(*) OVER(PARTITION BY pid) AS Cnt
FROM #tb)
select pid,avg(coadcladcon) coadcladcon
from
(SELECT pid,coadcladcon,RowNum,Cnt
FROM OrdersRN o
WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2) ) a
group by pid
2.https://blog.csdn.net/NextAction/article/details/80421854
select AVG(DISTINCT income)
from (
select T1.income from graduates T1,graduates T2
group by T1.income
having sum(case when T2.income >= T1.income then 1 else 0 end) >= count(*)/2
and sum(case when T2.income <= T1.income then 1 else 0 end) >= count(*)/2
) tmp;