分组列表关联查询问题：如何计算各分组记录的中位数

我需要获取卖家列表，并在每个卖家旁显示其售出商品的耗时中位数。针对单个卖家（dan）的以下查询可正常返回结果：

SELECT 
  AVG(timetook) AS Median
FROM (
  SELECT 
    TIMESTAMPDIFF(MINUTE, starttime, endtime) AS timetook,
    @rownum:=@rownum+1 as `row_number`,
    @total_rows:=@rownum
  FROM items, (SELECT @rownum:=0) r
  WHERE total_price > 100 AND seller_name = "dan"
  ORDER BY timetook ASC
) AS temp
WHERE 
  `row_number` = FLOOR((@total_rows + 1) / 2) OR
  `row_number` = CEIL((@total_rows + 1) / 2)

但当尝试查询多个卖家（测试仅用ron和dan）时，卖家的中位数返回null值，对应的查询语句如下：

SELECT 
  q1.seller_name, 
  q1.records_found,
  q2.median_timetook
FROM (
  SELECT 
    seller_name,
    COUNT(*) AS records_found
  FROM items 
  WHERE 
    total_price > 100 AND 
    TIMESTAMPDIFF(MINUTE, starttime, endtime) < 60 
  GROUP BY seller_name 
  HAVING COUNT(*) >= 3
) AS q1
LEFT JOIN (
  SELECT 
    seller_name,
    AVG(timetook) AS median_timetook
  FROM (
    SELECT 
      seller_name,
      TIMESTAMPDIFF(MINUTE, starttime, endtime) AS timetook,
      @rownum:=@rownum+1 AS row_number,
      @total_rows:=@rownum
    FROM 
      items, 
      (SELECT @rownum:=0) r
    ORDER BY 
      timetook ASC
  ) AS temp
  WHERE 
    row_number = FLOOR((@total_rows + 1) / 2) OR
    row_number = CEIL((@total_rows + 1) / 2)
  GROUP BY 
    seller_name
) AS q2 ON q1.seller_name = q2.seller_name
WHERE
  q1.seller_name IN ('ron', 'dan') 
GROUP BY
  q1.seller_name
ORDER BY 
  q1.records_found DESC

问题原因及修复方案 #

问题根源 #

原多卖家查询中的@rownum是全局递增变量，没有按卖家分组重置计数。这导致每个卖家的行号是全局范围内的序号，而非该卖家内部的排序行号，最终筛选中位数的条件无法匹配到对应卖家的目标行，因此返回NULL。

修复后的查询语句 #

SELECT 
  q1.seller_name, 
  q1.records_found,
  q2.median_timetook
FROM (
  SELECT 
    seller_name,
    COUNT(*) AS records_found
  FROM items 
  WHERE 
    total_price > 100 AND 
    TIMESTAMPDIFF(MINUTE, starttime, endtime) < 60 
  GROUP BY seller_name 
  HAVING COUNT(*) >= 3
) AS q1
LEFT JOIN (
  SELECT 
    seller_name,
    AVG(timetook) AS median_timetook
  FROM (
    SELECT 
      seller_name,
      TIMESTAMPDIFF(MINUTE, starttime, endtime) AS timetook,
      -- 按卖家分组重置行号
      @rownum := CASE 
                  WHEN @prev_seller = seller_name THEN @rownum + 1 
                  ELSE 1 
                END AS row_number,
      @prev_seller := seller_name,
      -- 计算每个卖家的总记录数
      (SELECT COUNT(*) FROM items i WHERE i.seller_name = items.seller_name AND total_price > 100 AND TIMESTAMPDIFF(MINUTE, i.starttime, i.endtime) < 60) AS total_rows
    FROM 
      items, 
      (SELECT @rownum:=0, @prev_seller:='') r
    WHERE 
      total_price > 100 AND 
      TIMESTAMPDIFF(MINUTE, starttime, endtime) < 60
    ORDER BY 
      seller_name, timetook ASC
  ) AS temp
  WHERE 
    row_number = FLOOR((total_rows + 1) / 2) OR
    row_number = CEIL((total_rows + 1) / 2)
  GROUP BY 
    seller_name
) AS q2 ON q1.seller_name = q2.seller_name
WHERE
  q1.seller_name IN ('ron', 'dan') 
GROUP BY
  q1.seller_name
ORDER BY 
  q1.records_found DESC

关键修复点 #

1. 新增@prev_seller变量跟踪当前处理的卖家，当切换到新卖家时重置@rownum为1，同一卖家内则递增行号，确保行号是每个卖家内部的排序序号。
2. 为每个卖家计算其符合条件的总记录数total_rows，替代原查询中全局的@total_rows。
3. 先按seller_name排序，再按timetook排序，保证同一卖家的记录按耗时顺序排列。
4. 筛选条件使用每个卖家自己的total_rows来计算中位数对应的行号，确保匹配正确的行。

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