MariaDB优化查询：计算关联表中最近5条通话时长的中位数

原查询的核心问题是内层会扫描并处理所有通话记录，当calls表数据量较大时，会产生大量中间结果，导致性能瓶颈。可以通过先筛选合格手机号，再取对应最近5条记录的方式重写查询，大幅减少处理的数据量。

优化后的查询语句（通用版本） #

WITH qualified_phones AS (
    -- 第一步：筛选出至少有5条通话记录的phone_id
    SELECT phone_id
    FROM calls
    GROUP BY phone_id
    HAVING COUNT(*) >= 5
),
recent_calls AS (
    -- 第二步：仅对合格手机号，获取最近5条通话记录
    SELECT 
        p.phone_number,
        c.duration,
        ROW_NUMBER() OVER(PARTITION BY c.phone_id ORDER BY c.startDateTime DESC) AS rn
    FROM qualified_phones qp
    JOIN calls c ON qp.phone_id = c.phone_id
    JOIN phones p ON c.phone_id = p.id
)
-- 第三步：计算每个手机号最近5条通话时长的中位数
SELECT DISTINCT
    phone_number,
    MEDIAN(duration) OVER(PARTITION BY phone_number) AS recent_5_call_duration_median
FROM recent_calls
WHERE rn <= 5;

针对支持QUALIFY语法的数据库（如BigQuery、Snowflake） #

可以进一步简化，直接在子查询中过滤前5条记录，避免额外的WHERE条件：

WITH qualified_phones AS (
    SELECT phone_id
    FROM calls
    GROUP BY phone_id
    HAVING COUNT(*) >= 5
)
SELECT DISTINCT
    p.phone_number,
    MEDIAN(c.duration) OVER(PARTITION BY p.phone_number) AS recent_5_call_duration_median
FROM qualified_phones qp
JOIN calls c ON qp.phone_id = c.phone_id
JOIN phones p ON c.phone_id = p.id
QUALIFY ROW_NUMBER() OVER(PARTITION BY c.phone_id ORDER BY c.startDateTime DESC) <= 5;

优化逻辑说明 #

1. 缩小处理范围：先通过qualified_phones筛选出总通话数≥5的手机号，排除掉不需要处理的低通话量手机号。
2. 仅取必要记录：针对筛选后的手机号，只获取其最近5条通话记录，避免处理全量通话数据。
3. 高效计算中位数：基于少量的目标数据计算中位数，计算成本更低。

额外性能建议 #

给calls表创建联合索引，加速手机号筛选和最近记录查询：

CREATE INDEX idx_calls_phoneid_startdt ON calls(phone_id, startDateTime DESC);

该索引可以让数据库快速定位到每个手机号的最近5条记录，避免全表扫描。

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