MariaDB同查询混用聚合函数与median()时计算异常问题咨询

问题复现 #

测试表结构与数据：

CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL, 
  `pk` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `test` (`a`,  `pk`) VALUES
(1,  18),
(2,  19),
(3,   20),
(2,   21);

单独执行窗口函数查询时结果正确：

SELECT median(a) over () FROM test;

返回结果为2，符合中位数的统计值。但将聚合函数与median()窗口函数同查时：

SELECT
sum(a),
median(a) over ()   
FROM test;

得到结果8 1，中位数异常为1，且替换sum为max、min、avg等聚合函数时均会出现此问题。

问题原因 #

当查询中同时存在无GROUP BY的聚合函数和窗口函数时，MariaDB的执行逻辑会优先处理聚合函数：此时聚合函数会将全表视为一个分组，返回单条汇总记录；而窗口函数的执行上下文被错误绑定到了这条聚合后的单条记录上，而非原表的全部数据。也就是说，此时median(a) over ()实际只基于聚合后记录中的a值（即原表第一条记录的a=1）计算，因此得到错误结果。

解决方法 #

方法1：将聚合函数转为窗口函数 #

让聚合函数与median()窗口函数在相同的全表上下文执行，最后用LIMIT 1获取唯一的汇总结果：

SELECT
  sum(a) over () AS sum_a,
  median(a) over () AS median_a
FROM test
LIMIT 1;

方法2：使用子查询分离计算逻辑 #

通过子查询分别计算聚合值和中位数，再合并结果：

SELECT
  (SELECT sum(a) FROM test) AS sum_a,
  median(a) over () AS median_a
FROM test
LIMIT 1;

或者用交叉连接的方式合并两个子查询的结果：

SELECT
  t2.sum_a,
  t1.median_a
FROM (SELECT median(a) over () AS median_a FROM test LIMIT 1) t1
CROSS JOIN (SELECT sum(a) AS sum_a FROM test) t2;

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