找出给定频率的中位数

Question

我想写一个SQL Server查询来查找给定频率的数字的中位数。

表：

+----------+-------------+
|  number  |  frequency  |
+----------+-------------|
|  2       |  7          |
|  3       |  1          |
|  5       |  3          |
|  7       |  1          |
+----------+-------------+

在此表中，数字是2, 2, 2, 2, 2, 2, 2, 3, 5, 5, 5, 7，因此中位数是(2 + 2) / 2 = 2

我已经使用递归CTE sqlfiddle创建了以下查询，有没有更好的方法来更高效地编写这个查询？

我的解决方案是：

/* recursive CTE to generate numbers with given frequency */

with nums as
(
    select
        number,
        frequency
    from numbers

    union all

    select
        number,
        frequency - 1
    from nums
    where frequency > 1
)

/* find median */

select
    round(avg(number * 1.0), 2) as median
from
(
    select
        number,
        count(*) over () as ttl,
     row_number() over (order by number) as rnk
    from nums
) t
where rnk = (case when ttl%2 != 0 then (ttl/2) else (ttl/2)+1 end)
or rnk = (case when ttl%2 = 0 then (ttl/2)+1 end)

Answer 1

只需做一个累加和，取中间值。我想这就是逻辑：

select avg(number)
from (select t.*,
             sum(freq) over (order by number asc) as sum_freq,
             sum(freq) over () as cnt
      from t
     ) t
where cnt <= 2 * sum_freq and
      cnt >= 2 * (sum_freq - freq);

Here是一个db<>fiddle。

Answer 2

尽管如此，现在回答可能已经太晚了，这里是我从给定频率表中找到中位数的方法。这包括奇数和偶数两种情况。

`-- 1. Create a frequency table witth the Price and its frequency
IF OBJECT_ID('tempdb.dbo.#MedianEX', 'U') IS NOT NULL
DROP TABLE #MedianEX;
create table #MedianEX (Price  int,Frequency int)
insert into #MedianEX values(2 ,3)
insert into #MedianEX values(3,7)
insert into #MedianEX values(4,2)
insert into #MedianEX values(6,5 )
insert into #MedianEX values(5,6 )
insert into #MedianEX values(7,2 )


--uncomment the below statement to make total number of element = 24(even number scenario)
update #MedianEx  set Frequency=5 where Price=5

--calculate (n-1)/2 th position and (n+1)/2 th position if odd  value and n/2 th position if even
IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL
DROP TABLE #temp;
select  *,SUM(Frequency)over (order by Price asc)as cmfreq,ceiling(SUM(Frequency) over()/2.0) as UCNT,
floor(SUM(Frequency) over()/2.0) as LCNT into #temp from #MedianEX

--select price value at the required positions and calculate their average
select cast(avg(Price * 1.0) as decimal(10,2)) from (
select top 1 Price from #temp where  UCNT<=cmfreq 
union
select top 1 Price from #temp where  LCNT<=cmfreq) t

--select * from #MedianEX`