使用CTE与GROUP BY在SQLite中计算中位数出现空值问题排查

我在SQLite中计算多表关联后生成的变量r的中位数，参考了方案但输出的median_r字段全为空。我的查询语句如下：

WITH cte AS (
    SELECT m.round,
           f.f * (m.won * o.odds - 1) AS r
    FROM matches m
    INNER JOIN f
    ON f.match_id = m.match_id
        AND f.player_id = m.player_id
    INNER JOIN odds o
    ON o.match_id = m.match_id
        AND o.player_id = m.player_id
)

SELECT round,
       COUNT(*) AS n,
       ROUND(AVG(r), 3) AS avg_r,
       ROUND(AVG(
           CASE counter % 2
               WHEN 0 THEN CASE WHEN rn IN (counter / 2, counter / 2 + 1) THEN r END
               WHEN 1 THEN CASE WHEN rn = counter / 2 + 1 THEN r END
           END
       ) OVER (PARTITION BY round), 3) median_r,
       ROUND(MIN(r), 3) AS min_r,
       ROUND(MAX(r), 3) AS max_r
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY round ORDER BY r) rn,
    COUNT(*) OVER (PARTITION BY round) counter
    FROM cte)
GROUP BY round

补充说明：CTE的前25行数据如下（注意部分round字段为空）：

round,f
R32,5.434575672657226
R32,4.662480056088388
R32,4.205099630165803
qualifier,3.845729295512344
qualifier,3.6497091129740933
R32,3.5610333161115517
QF,3.346344544760134
,3.220331570271259
R16,3.1176543317988417
,2.9963085045132583
R16,2.814289763584883
R32,2.8081039581780614
R32,2.7321826199364434
R32,2.5748915361198526
R32,2.5748753002036873
R16,2.574872228663704
R16,2.574872228663704
R32,2.574872228663704
R32,2.574872228663704
R32,2.574872228663704
R32,2.574872228663704
R32,2.574872228663704
R32,2.574872228663704
R32,2.574872228663704
,2.574872228663704
SF,2.188892038343798

错误原因 #

• GROUP BY与窗口函数的冲突：外层用GROUP BY round后，每个分组仅保留一行数据，导致子查询中用于中位数计算的rn（行号）和counter（分组总数）信息丢失，CASE语句无法匹配到符合条件的行，AVG计算返回空。
• 整数除法的潜在歧义：SQLite中整数间除法自动取整，虽然逻辑上奇数/偶数的中位数位置计算是对的，但GROUP BY丢失行数据后，这些逻辑根本无法触发。
• 空round值干扰：CTE中存在空round的行，分组后生成的空分组同样会因为上述问题返回空中位数。

修正后的SQL方案 #

方案一：用窗口函数替代GROUP BY，一次性计算所有统计量 #

WITH cte AS (
    SELECT m.round,
           f.f * (m.won * o.odds - 1) AS r
    FROM matches m
    INNER JOIN f
    ON f.match_id = m.match_id
        AND f.player_id = m.player_id
    INNER JOIN odds o
    ON o.match_id = m.match_id
        AND o.player_id = m.player_id
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY round ORDER BY r) rn,
           COUNT(*) OVER (PARTITION BY round) counter,
           AVG(r) OVER (PARTITION BY round) avg_r,
           MIN(r) OVER (PARTITION BY round) min_r,
           MAX(r) OVER (PARTITION BY round) max_r,
           COUNT(*) OVER (PARTITION BY round) n
    FROM cte
)
SELECT DISTINCT
       round,
       ROUND(n, 0) AS n,
       ROUND(avg_r, 3) AS avg_r,
       ROUND(AVG(CASE
           WHEN counter % 2 = 1 AND rn = (counter + 1)/2 THEN r
           WHEN counter % 2 = 0 AND rn IN (counter/2, counter/2 + 1) THEN r
       END) OVER (PARTITION BY round), 3) AS median_r,
       ROUND(min_r, 3) AS min_r,
       ROUND(max_r, 3) AS max_r
FROM ranked
ORDER BY round;

方案二：拆分计算中位数与统计量，再关联结果 #

WITH cte AS (
    SELECT m.round,
           f.f * (m.won * o.odds - 1) AS r
    FROM matches m
    INNER JOIN f
    ON f.match_id = m.match_id
        AND f.player_id = m.player_id
    INNER JOIN odds o
    ON o.match_id = m.match_id
        AND o.player_id = m.player_id
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY round ORDER BY r) rn,
           COUNT(*) OVER (PARTITION BY round) counter
    FROM cte
),
medians AS (
    SELECT round,
           ROUND(AVG(r), 3) AS median_r
    FROM ranked
    WHERE (counter % 2 = 1 AND rn = (counter + 1)/2)
       OR (counter % 2 = 0 AND rn IN (counter/2, counter/2 + 1))
    GROUP BY round
),
stats AS (
    SELECT round,
           COUNT(*) AS n,
           ROUND(AVG(r), 3) AS avg_r,
           ROUND(MIN(r), 3) AS min_r,
           ROUND(MAX(r), 3) AS max_r
    FROM cte
    GROUP BY round
)
SELECT s.round,
       s.n,
       s.avg_r,
       m.median_r,
       s.min_r,
       s.max_r
FROM stats s
LEFT JOIN medians m ON s.round = m.round
ORDER BY s.round;

修正说明 #

• 方案一通过窗口函数提前计算所有聚合值，用DISTINCT去重避免GROUP BY丢失行信息；
• 方案二拆分中位数和常规统计量的计算逻辑，再通过JOIN合并结果，可读性更强；
• 调整奇数中位数的位置计算为(counter +1)/2，消除整数除法的歧义；
• 保留空round分组的计算，若不需要可在WHERE子句中添加round IS NOT NULL过滤。

内容的提问来源于stack exchange，提问作者HJA24