SQL求中值

这里说的中值指的是中位数,比如一列数1,2,3,4,5,6,7的中位数是4,如果个数为奇数则为中间一位,如果为偶数则为中间两位的平均数.
use City; go SET NOCOUNT ON if OBJECT_ID(N'A',N'U') is not null drop table A go create table A ( dt datetime not null, a1 int not null, a2 int not null, a3 int not null ) go insert into A select cast('2010-11-15 10:04:00' as datetime) as dt,4,5,1 union all select cast('2010-11-15 10:08:00' as datetime) as dt,6,2,5 union all select cast('2010-11-15 10:14:00' as datetime) as dt,3,6,6 union all select cast('2010-11-15 10:44:00' as datetime) as dt,4,11,8 union all select cast('2010-11-15 11:07:00' as datetime) as dt,12,5,9 union all select cast('2010-11-15 11:09:00' as datetime) as dt,15,7,11 union all select cast('2010-11-15 11:23:00' as datetime) as dt,4,1,3 union all select cast('2010-11-15 11:36:00 ' as datetime) as dt,7,9,6 union all select cast('2010-11-15 11:55:00' as datetime) as dt,9,6,3 union all select cast('2010-11-15 12:15:00' as datetime) as dt,23,4,1 union all select cast('2010-11-15 12:32:00' as datetime) as dt,11,5,4 union all select cast('2010-11-15 12:47:00' as datetime) as dt,9,7,7 go select * from A ; with cte as ( select '2010-11-15 '+CAST(DATEPART(HOUR,dt) as nvarchar(10))+':00:00' as myt,* from A ) ,lastcte as ( select COUNT(*) over(PARTITION by myt) as mycount,ROW_NUMBER()over(PARTITION by myt order by dt) as RN,* from cte ) ,losecte as ( select case when mycount%2<>0 and RN=(mycount+1)/2 then RN when mycount%2=0 and RN=mycount/2 then RN when mycount%2=0 and RN=(mycount/2)+1 then RN else 0 end as OS ,* from lastcte ) select myt,SUM(OS) as 中值, AVG(a2) as a2,AVG(a3) as a3 from losecte group by myt

结果如下：

 dt                      a1          a2          a3
----------------------- ----------- ----------- -----------
2010-11-15 10:04:00.000 4           5           1
2010-11-15 10:08:00.000 6           2           5
2010-11-15 10:14:00.000 3           6           6
2010-11-15 10:44:00.000 4           11          8
2010-11-15 11:07:00.000 12          5           9
2010-11-15 11:09:00.000 15          7           11
2010-11-15 11:23:00.000 4           1           3
2010-11-15 11:36:00.000 7           9           6
2010-11-15 11:55:00.000 9           6           3
2010-11-15 12:15:00.000 23          4           1
2010-11-15 12:32:00.000 11          5           4
2010-11-15 12:47:00.000 9           7           7

 

myt                         中值                   a2          a3
--------------------------- -------------------- ----------- -----------
2010-11-15 10:00:00         5                    6           5
2010-11-15 11:00:00         3                    5           6
2010-11-15 12:00:00         2                    5           4