SQL如何求统计学中的中位数

SQL怎么求统计学中的中位数？

SQL code

/*中位数定义：将数从小到大排列，若总数为奇数，取中间位置的数值。若总数为偶数，取中间位置两个数的平均值。*/create table #tb(num int)declare @i intset @i=1while @i<10    --#tb的行数可为奇数，也可为偶数begin     insert into #tb values(cast(RAND()*100 as int))   --表中可能有重复值     set @i=@i+1end

SQL code

create table #tb(num int)declare @i intset @i=1while @i<10    --#tb的行数可为奇数，也可为偶数begin     insert into #tb values(cast(RAND()*100 as int))   --表中可能有重复值     set @i=@i+1endselect * from #tb order by num ascif OBJECT_ID('pro_test')is not nulldrop proc pro_testgocreate proc pro_testasdeclare @count intselect @count=COUNT(1) from #tbif @count%2=0begin   select AVG(num) as 中位数 from(   select        ROW_NUMBER()over(order by num) as id,num    from        #tb   )t where id in(@count/2,(@count/2)+1)endelsebegin   select num as 中位数 from(   select        ROW_NUMBER()over(order by num) as id,num    from        #tb   )t where id=(@count/2)+1endexec pro_test/*中位数42*/
[解决办法]
SQL code
/*中位数定义：将数从小到大排列，若总数为奇数，取中间位置的数值。若总数为偶数，取中间位置两个数的平均值。*/--奇数测试if OBJECT_ID('tb','U') is not null drop table tbgocreate table tb(num int)--得到数据declare @i intset @i=1while @i<10    --#tb的行数可为奇数，也可为偶数begin     insert into tb values(cast(RAND()*100 as int))   --表中可能有重复值     set @i=@i+1end--奇数结果  若总数为奇数，取中间位置的数值 位置 为5的if (select COUNT(1)%2 from tb)=1Begin    with cte as    (        select             num,            ROW_NUMBER() over(order by num) as v_squence        from tb     )select a.num from cte a    where a.v_squence =(select COUNT(1)/2+1 from cte )Endelse  --偶数结果  取中间位置两个数的平均值。Begin    with cte as    (        select             num,            ROW_NUMBER() over(order by num) as v_squence        from tb     )select         sum(num)/2     from cte a    where a.v_squence =(select COUNT(1)/2+1 from cte ) or          a.v_squence =(select COUNT(1)/2 from cte )end