MySQL 5.7.36按产品ID分组计算价格25百分位数及对应交易数的技术问题排查

咱们先理清楚整个场景：

环境与表结构 #

• 数据库版本：MySQL 5.7.36
• 目标表：Transactions（存储交易数据）
• 字段详情：
  • created：交易时间（DateTime类型）
  • price：交易价格
  • id：产品唯一标识符

样本数据 #

需求目标 #

按产品ID分组，完成两个核心计算：

1. 算出每个ID下价格的25百分位数（也就是第一四分位数）
2. 统计每个ID中，价格低于该百分位数的交易数量

预期结果如下：

原SQL的问题排查 #

你尝试的SQL语句存在几个关键问题：

SELECT id, MAX(CASE WHEN Quartile = 1 THEN price END) 1Quartile,
FROM (
 SELECT id, price, NTILE(4) OVER (PARTITION BY id ORDER BY price) AS Quartile
 FROM Transactions) Vals
GROUP BY id ORDER BY id

1. 语法错误：主SELECT语句里MAX(...) 1Quartile后面多了个逗号，这会直接导致SQL解析失败
2. 版本不兼容：MySQL 5.7根本不支持窗口函数（比如NTILE()+OVER()），窗口函数是MySQL 8.0才引入的特性
3. 逻辑偏差：就算版本支持，NTILE(4)的分箱逻辑在数据量不是4的倍数时，分配会不均匀，可能和你预期的百分位数结果不符

适配MySQL 5.7的解决方案 #

既然5.7没有窗口函数，咱们用子查询+字符串处理的方式来实现，具体思路是：先按ID分组排序价格，找到25百分位对应的价格，再统计低于该价格的交易数。

完整可执行SQL #

SELECT
    t.id,
    q.first_quartile AS `price 1st q`,
    COUNT(CASE WHEN t.price < q.first_quartile THEN 1 END) AS n_transactions
FROM Transactions t
JOIN (
    SELECT
        id,
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY price), ',', CEIL((COUNT(*)+1)*0.25)), ',', -1) AS first_quartile
    FROM Transactions
    GROUP BY id
) q ON t.id = q.id
GROUP BY t.id, q.first_quartile
ORDER BY t.id;

代码拆解说明 #

1. 子查询q的作用：

   • GROUP_CONCAT(price ORDER BY price)：把每个ID的价格按升序拼接成一个逗号分隔的字符串，比如ID=5会得到"1,2,3,4"，ID=2得到"5,6,7,8"
   • CEIL((COUNT(*)+1)*0.25)：按照四分位数的经典计算方式，确定25百分位的位置。比如ID=2有4条数据，(4+1)*0.25=1.25，向上取整为2，对应排序后的第2个价格（也就是6）；ID=5同样得到位置2，对应价格2，完美匹配预期
   • 两层SUBSTRING_INDEX：第一次截取前N个元素，第二次取最后一个元素，得到目标百分位价格

2. 主查询的作用：

   • 关联原交易表和百分位数据，统计每个ID中价格低于该百分位数的交易数量
   • 用CASE WHEN过滤符合条件的交易，COUNT()统计有效数量

执行这段SQL后，就能得到和预期完全一致的结果。

MySQL 8.0+ 简化版本（可选） #

如果你的数据库能升级到8.0及以上，用窗口函数会更简洁：

WITH quartile_data AS (
    SELECT
        id,
        price,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) OVER (PARTITION BY id) AS first_quartile
    FROM Transactions
)
SELECT
    id,
    MAX(first_quartile) AS `price 1st q`,
    COUNT(CASE WHEN price < first_quartile THEN 1 END) AS n_transactions
FROM quartile_data
GROUP BY id
ORDER BY id;

PERCENTILE_CONT是连续型百分位数函数，会根据数据插值计算，这里因为数据量刚好是4，结果和手动计算一致。

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