已统计各用户GPS点数，如何计算所有行程GPS点数的中位数？

嘿，我来帮你搞定这个计算每个用户GPS点数中位数的问题！首先明确一下：我们需要先得到每个用户的GPS点数量，再对这些数量值求中位数——也就是所有用户的行程点数的中间值，对吧？

下面分不同SQL数据库给出实现方案，你可以根据自己用的数据库来选：

第一步：先获取每个用户的GPS点数（你已经完成这步啦） #

先确认我们的基础数据集，就是每个用户对应的GPS点数量：

SELECT user_id, COUNT(lat) AS lat_count 
FROM gps_track 
GROUP BY user_id

第二步：计算中位数 #

1. MySQL 8.0+（支持窗口函数） #

利用窗口函数给点数排序，再根据总数量的奇偶性取中间值：

WITH user_point_counts AS (
    -- 先得到每个用户的GPS点数
    SELECT COUNT(lat) AS lat_count 
    FROM gps_track 
    GROUP BY user_id
),
ranked_counts AS (
    -- 给点数排序，同时统计总共有多少个用户
    SELECT 
        lat_count,
        ROW_NUMBER() OVER (ORDER BY lat_count) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM user_point_counts
)
-- 根据总数量奇偶性计算中位数：奇数取中间行，偶数取中间两行的平均值
SELECT 
    AVG(lat_count) AS median_gps_points
FROM ranked_counts
WHERE row_num IN (FLOOR((total_rows + 1)/2), CEIL((total_rows + 1)/2));

2. PostgreSQL #

PostgreSQL有内置的中位数函数，用起来更省心：

SELECT 
    -- 连续型中位数：如果是偶数个用户，返回中间两个数的平均值
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lat_count) AS median_cont,
    -- 离散型中位数：直接返回排序后中间位置的实际点数
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY lat_count) AS median_disc
FROM (
    SELECT COUNT(lat) AS lat_count 
    FROM gps_track 
    GROUP BY user_id
) AS user_point_counts;

你可以根据需求选择median_cont或者median_disc，前者更适合统计场景，后者是实际存在的点数。

3. SQL Server #

和MySQL类似，用窗口函数实现：

WITH user_point_counts AS (
    SELECT COUNT(lat) AS lat_count 
    FROM gps_track 
    GROUP BY user_id
),
ranked_counts AS (
    SELECT 
        lat_count,
        ROW_NUMBER() OVER (ORDER BY lat_count) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM user_point_counts
)
SELECT 
    AVG(CAST(lat_count AS DECIMAL(10,2))) AS median_gps_points
FROM ranked_counts
WHERE row_num BETWEEN (total_rows + 1)/2 AND (total_rows + 2)/2;

或者用内置的PERCENTILE_CONT函数：

SELECT DISTINCT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lat_count) OVER () AS median_gps_points
FROM (
    SELECT COUNT(lat) AS lat_count 
    FROM gps_track 
    GROUP BY user_id
) AS user_point_counts;

核心思路 #

不管用哪种方式，核心逻辑都是：

• 先聚合得到每个用户的GPS点数集合
• 对这个集合排序后，根据元素总数的奇偶性计算中位数：
  • 奇数个元素：取排序后中间位置的数值
  • 偶数个元素：取中间两个数值的平均值

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