【LeetCode 571. 给定数字的频率查询中位数】WITH RECURSIVE实现Tally的逆操作

目录

• 题目地址
• 代码

题目地址

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

代码

# 你可以把这个结构理解为do while
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