MSSQL求中位數-常見解決方案-整理帖

--> Title   : MSSQL求中位數-常見解決方案-整理帖

--> Author  : wufeng4552

--> Date    :  2009-12-16

/*

--環境描述:

按ID和DateValue排序,希望能取到每一个的中间值,

若ID总数为奇数,取第(n+1)/2个的值;如果是偶数,取第n/2个的值

*/

if object_id('[tb]') is not null drop table [tb]

go

create table [tb] (ID int,DateValue numeric(7,4))

insert into [tb]

select 1,1.0000 union all

select 1,5.0000 union all

select 1,6.0000 union all

select 2,9.0000 union all

select 2,20.0000 union all

select 2,35.0000 union all

select 2,60.0000 union all

select 3,72.0000 union all

select 3,99.0000 union all

select 3,100.0000 union all

select 3,20.0000 union all

select 3,6.0000 union all

select 4,6.0000 union all

select 4,12.0000 union all

select 5,10.0000

--方法1

if object_id('tempdb..#')is not null drop table #

select *,px=(select count(*) from tb where id=t.id and DateValue<=t.DateValue) into #

from tb t

select distinct

       ID,

       DateValue=(select DateValue from # where ID=t.ID and px=((select max(px) from # where id=t.id)+1)/2)

from # t

/*

ID          DateValue

----------- ---------------------------------------

1           5.0000

2           20.0000

3           72.0000

4           6.0000

5           10.0000

 

(5 個資料列受到影響)

*/

--方法2

select *

from tb t

where (select count(*) from tb where id=t.id and DateValue<=t.DateValue)=

      ((select count(*) from tb where id=t.id)+1)/2

/*

ID          DateValue

----------- ---------------------------------------

1           5.0000

2           20.0000

3           72.0000

4           6.0000

5           10.0000

 

(5 個資料列受到影響)

*/

--方法3

;with cte as

(

select n=row_number() over(partition by id order by DateValue),

           ID,

           DateValue

    From tb

)

select cte.ID,

       cte.datevalue

from cte,

(

select N=case when max(n)%2=1 then max(n)/2+1 else max(n)/2 end,

       ID

from cte group by id)n

where cte.id=n.id and cte.n=n.n

/*

ID          datevalue

----------- ---------------------------------------

1           5.0000

2           20.0000

3           72.0000

4           6.0000

5           10.0000

 

(5 個資料列受到影響)

*/

 

--方法

select id,

        DateValue

from

(

select

    (select count(*) from tb where id=t.id and DateValue<=t.DateValue)px,

    (select ceiling(count(*)*1.0/2)from tb where id=t.id)cnt,

     ID,

     DateValue

from tb t

)tt where cnt=px

/*

id          DateValue

----------- ---------------------------------------

1           5.0000

2           20.0000

3           72.0000

4           6.0000

5           10.0000

 

(5 個資料列受到影響)

*/

--方法5

select id,

       DateValue

from

(select *,

        rid=row_number() over (partition by id order by datevalue),

        cnt=count(1) over (partition by  id) from

tb) t

where rid=(case when cnt%2=1 then (cnt+1)/2 else cnt/2 end)

/*

id          DateValue

----------- ---------------------------------------

1           5.0000

2           20.0000

3           72.0000

4           6.0000

5           10.0000

 

(5 個資料列受到影響)

 

*/