如何在单次查询与单次扫描下实现SQL聚合结果的行列转换？

问题背景 #

现有一张超2000万行的表T，表结构和插入数据语句如下：

CREATE TABLE T (
  D DATE,
  V INT
);

INSERT INTO T VALUES ('2024-07-01', 1), ('2024-07-02', 2), ('2024-07-02', 3);

当前使用UNION实现聚合结果的行列转换，耗时约5秒：

SELECT D, 'SUM', SUM(V)
FROM T
GROUP BY D
UNION
SELECT D, 'AVG', AVG(V)
FROM T
GROUP BY D
ORDER BY 1;

所需输出格式如下：

为避免多次扫描表，改写的聚合查询耗时约1秒，但不符合输出格式：

SELECT D, SUM(V), AVG(V)
FROM T
GROUP BY D;

尝试用CTE实现格式转换，但仍会扫描表T两次，耗时仍为5秒，执行计划如下：

已找到基于JSON的方案，但效果不满意：

WITH CTE AS (
    SELECT D, JSON_ARRAY(SUM(V), AVG(V)) AS data
    FROM T
    GROUP BY D
)
SELECT
  c.D,
  CASE WHEN JT.Id = 1 THEN 'SUM'
       WHEN JT.Id = 2 THEN 'AVG'
  END AS F,
  JT.N
FROM CTE c,
JSON_TABLE(c.data, '$[*]'
  COLUMNS(
    Id for ordinality,
    N FLOAT PATH '$[0]'
  )
) AS JT;

解决方案：单扫描表的高效行列转换 #

可以通过将单次聚合结果与行构造器关联的方式，仅扫描一次表就完成格式转换，同时保持低耗时。

适用于MySQL 8.0+/MariaDB的方案 #

使用CROSS JOIN结合VALUES行构造器，拆分单行聚合结果为多行：

SELECT
  agg.D,
  metric.type,
  CASE metric.type
    WHEN 'SUM' THEN agg.sum_v
    WHEN 'AVG' THEN agg.avg_v
  END AS value
FROM (
  SELECT
    D,
    SUM(V) AS sum_v,
    AVG(V) AS avg_v
  FROM T
  GROUP BY D
) AS agg
CROSS JOIN (
  VALUES ('SUM'), ('AVG')
) AS metric(type)
ORDER BY agg.D, metric.type;

适用于支持LATERAL JOIN的数据库（如PostgreSQL、SQL Server） #

使用LATERAL JOIN生成多行结果：

SELECT
  agg.D,
  metric.type,
  metric.value
FROM (
  SELECT
    D,
    SUM(V) AS sum_v,
    AVG(V) AS avg_v
  FROM T
  GROUP BY D
) AS agg
LATERAL (
  SELECT 'SUM' AS type, agg.sum_v AS value
  UNION ALL
  SELECT 'AVG' AS type, agg.avg_v AS value
) AS metric
ORDER BY agg.D, metric.type;

方案说明 #

• 内层聚合仅扫描表T一次，计算出每个日期的SUM和AVG结果；
• 通过关联行构造器，将单行聚合结果拆分为两行，匹配所需输出格式；
• 执行计划中只会出现一次对表T的扫描，耗时与单聚合查询基本一致。

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