PostgreSQL创建视图时，能否在后续子查询中复用前置子查询结果？

当然可以！在PostgreSQL里，完全有办法避免重复执行相同的子查询，提升视图的查询效率。我给你整理几个实用的方案：

1. 使用CTE（公共表表达式，WITH子句） #

这是最直观的复用方式——先通过WITH定义一个临时的结果集，后续的查询可以多次引用它，PostgreSQL通常会优化成只执行一次这个子查询。

举个例子，假设你原来的视图写法是这样的（两次重复扫描table_a的活跃数据）：

CREATE VIEW my_view AS
SELECT 
  (SELECT count(*) FROM table_a WHERE status = 'active') AS active_count,
  (SELECT count(*) FROM table_a WHERE status = 'active' AND created_at > now() - interval '7 days') AS recent_active_count;

改成CTE写法后：

CREATE VIEW my_view AS
WITH active_records AS (
  -- 只执行一次的子查询，获取所有活跃记录
  SELECT created_at FROM table_a WHERE status = 'active'
)
SELECT
  (SELECT count(*) FROM active_records) AS active_count,
  (SELECT count(*) FROM active_records WHERE created_at > now() - interval '7 days') AS recent_active_count;

这样active_records的结果会被复用，不用重复扫描table_a。

2. 将子查询作为派生表，一次计算多个结果 #

如果你的需求是基于同一个子查询结果计算多个统计值，直接把这个子查询放到FROM子句里，然后在上面一次性算出所有需要的字段，这比多次引用子查询更高效。

比如上面的例子可以改成：

CREATE VIEW my_view AS
SELECT
  count(*) AS active_count,
  -- 用CASE表达式在同一次扫描中计算最近7天的活跃数
  SUM(CASE WHEN created_at > now() - interval '7 days' THEN 1 ELSE 0 END) AS recent_active_count
FROM (
  SELECT created_at FROM table_a WHERE status = 'active'
) AS active_records;

这种方式只需要扫描一次active_records，就能得到两个统计值，效率比两次独立子查询高很多。

3. 使用LATERAL连接（适合关联场景） #

如果你的视图需要关联其他表，并且每个关联行都要执行一次子查询，LATERAL连接可以让你定义一个能引用前面表字段的子查询，并且每个行只计算一次子查询结果，避免重复执行。

比如假设你有table_b，需要对每个table_b的行，计算关联的table_a的活跃统计：

CREATE VIEW my_view AS
SELECT
  b.id AS b_id,
  a_stats.active_count,
  a_stats.recent_active_count
FROM table_b b
LEFT JOIN LATERAL (
  -- 每个b.id只执行一次这个子查询
  SELECT
    count(*) AS active_count,
    SUM(CASE WHEN created_at > now() - interval '7 days' THEN 1 ELSE 0 END) AS recent_active_count
  FROM table_a a
  WHERE a.b_id = b.id AND a.status = 'active'
) AS a_stats ON true;

额外提示 #

PostgreSQL的查询优化器其实很智能，有时候即使你写了重复的子查询，它也可能自动优化成只执行一次。但当子查询逻辑复杂、数据量较大时，手动用上面的方法明确复用结果，能更稳妥地保证查询效率。

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