如何在SQLite单查询中计算每辆车的里程中位数？

好问题！SQLite确实没有内置的MEDIAN()聚合函数，但我们可以基于你已经用到的窗口函数思路，扩展实现这个需求。你的初始查询已经完成了第一步——给每辆车的里程记录排序编号，接下来只需补充计算每组的总记录数，再根据奇偶性筛选出中位数对应的行即可。

完整SQL语句 #

WITH ranked_mileage AS (
    SELECT 
        Car,
        Mileage,
        -- 按车辆分组、里程排序后给每行编号
        ROW_NUMBER() OVER (PARTITION BY Car ORDER BY Mileage) AS row_num,
        -- 计算每辆车的总里程记录数
        COUNT(*) OVER (PARTITION BY Car) AS total_rows
    FROM car_table
)
SELECT 
    Car,
    -- 用AVG兼容奇偶两种场景：奇数取中间值，偶数取中间两值的平均值
    AVG(Mileage) AS "Median Mileage"
FROM ranked_mileage
-- 筛选中位数所在行：奇数取中间1行，偶数取中间2行
WHERE row_num IN (
    FLOOR((total_rows + 1) / 2),
    FLOOR((total_rows + 2) / 2)
)
GROUP BY Car
ORDER BY Car;

逻辑拆解 #

1. CTE ranked_mileage：

   • 延续你的思路，用ROW_NUMBER()给每辆车的里程按从小到大的顺序编号。
   • 新增COUNT(*) OVER (PARTITION BY Car)计算每辆车的总里程记录数，这是判断中位数位置的核心依据。

2. 筛选中位数行：

   • 当总记录数为奇数时（比如5条），两个FLOOR表达式会得到同一个行号（3），此时AVG(Mileage)就是中间那一行的里程值。
   • 当总记录数为偶数时（比如4条），两个表达式会得到中间的两个行号（2和3），AVG(Mileage)会自动计算这两行里程的平均值，符合偶数个数据的中位数定义。

3. 最终聚合：
   按车辆分组后，对筛选出的中位数行计算平均值，就能得到每辆车的里程中位数了。

小提示 #

• 确保你的SQLite版本在3.25.0及以上（该版本开始支持ROW_NUMBER()这类窗口函数），如果版本较低，可以把CTE换成嵌套子查询，逻辑完全一致。
• 即使某辆车只有1条里程记录，这个查询也能正确返回该记录作为中位数。

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