• 题目地址
• 代码

题目地址

https://leetcode.cn/problems/find-median-given-frequency-of-numbers/description/

代码

WITH RECURSIVE RecCTE AS (
    SELECT num, frequency - 1 as remaining_frequency
    FROM Numbers
    WHERE frequency > 0
    UNION ALL
    SELECT num, remaining_frequency - 1
    FROM RecCTE
    WHERE remaining_frequency > 0
)

, Ordered AS (
    SELECT num,
           ROW_NUMBER() OVER(ORDER BY num) AS rownum,
           COUNT(*) OVER() AS total_count
    FROM RecCTE
)

SELECT 
    CASE 
        WHEN total_count % 2 = 1 THEN 
            num
        ELSE
            AVG(num) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
    END AS median
FROM Ordered
WHERE rownum IN (CEIL(total_count / 2), CEIL(total_count / 2) + 1)
LIMIT 1;

评论区大佬使用用户变量编程的解法

select avg(n) as median from 
(
    select Number as n, @c1 + 1 as 'c1', (@c1 := @c1 + Frequency) as 'c2', t2.s
    from Numbers, (select @c1 := 0) t1, (select sum(Frequency) as s
    from Numbers) t2
    order by n
) tmp
where c1 <= s/2 + 1 and c2 >= s/2