多聚合查询中如何缓存CTE临时结果以节省资源？

针对你遇到的CTE被多次重复计算的问题，以下几种方法可以实现缓存过滤结果供后续聚合复用：

1. 使用临时表存储过滤结果 #

将原CTE的过滤逻辑执行一次并写入临时表，后续所有聚合操作都从临时表读取数据，彻底避免重复扫描源表和执行过滤条件：

-- 创建临时表（不同数据库语法略有差异，以下为通用示例）
CREATE TEMPORARY TABLE result AS 
SELECT * FROM your_table WHERE filter; -- 替换为实际表名和过滤条件

-- 所有聚合操作复用临时表数据
SELECT COUNT(col1) FROM result
UNION ALL
SELECT COUNT(col2) FROM result
UNION ALL
...;

-- 临时表在会话结束后通常会自动销毁，无需手动清理（部分数据库需手动删除）

2. 利用物化CTE（部分数据库支持） #

如果使用的数据库支持物化CTE特性，可以强制数据库将CTE的结果物理存储，而非每次引用都重新计算：

• PostgreSQL 直接使用MATERIALIZED关键字：

  WITH result AS MATERIALIZED (
    SELECT * FROM your_table WHERE filter
  )
  SELECT COUNT(col1) FROM result
  UNION ALL
  SELECT COUNT(col2) FROM result
  ...;
  

• Oracle 使用查询提示强制物化：

  WITH result AS (
    SELECT /*+ MATERIALIZE */ * FROM your_table WHERE filter
  )
  SELECT COUNT(col1) FROM result
  UNION ALL
  SELECT COUNT(col2) FROM result
  ...;
  

• SQL Server 优化器在多数场景下会自动判断是否物化CTE，若需强制可结合临时表逻辑。

3. 合并聚合为单次查询 #

将多个COUNT操作合并到同一条语句中，仅扫描一次过滤后的数据集，无需额外存储：

WITH result AS (
  SELECT * FROM your_table WHERE filter
)
SELECT 
  COUNT(col1) AS count_col1,
  COUNT(col2) AS count_col2,
  ...
FROM result;

如果需要保持原有的行式输出（每个聚合结果占一行），可以通过UNPIVOT转换结果：

WITH result AS (
  SELECT * FROM your_table WHERE filter
),
aggregated AS (
  SELECT 
    COUNT(col1) AS count_col1,
    COUNT(col2) AS count_col2,
    ...
  FROM result
)
SELECT unpivoted.count_value
FROM aggregated
UNPIVOT (
  count_value FOR col_name IN (count_col1, count_col2, ...)
) AS unpivoted;

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