PostgreSQL物化视图如何避免重复计算？能否引用计算列？

核心结论 #

在PostgreSQL的SELECT语句（包括创建物化视图时的查询）中，无法直接在同一层级的SELECT列表里引用已定义的计算列别名。你示例中的average_2写法会触发语法错误，因为tests_taken是当前SELECT刚定义的别名，PostgreSQL的查询解析顺序是先处理FROM/GROUP BY子句，再处理SELECT列表，同一层级的别名还未被解析，因此无法互相引用。

实用替代方法 #

1. 使用CTE（公共表表达式）

先在CTE中计算出所有基础聚合值，再在外层查询中引用这些值进行二次计算，逻辑清晰且避免重复代码：

CREATE MATERIALIZED VIEW student_average AS
WITH student_stats AS (
    SELECT
        student_id,
        COUNT(*) AS tests_taken,
        SUM(score) AS total_score
    FROM student_scores
    GROUP BY student_id
)
SELECT
    student_id,
    tests_taken,
    total_score / tests_taken AS average_score
FROM student_stats;

2. 使用子查询

和CTE逻辑类似，将基础聚合计算放在子查询中，外层引用子查询的结果：

CREATE MATERIALIZED VIEW student_average AS
SELECT
    student_id,
    tests_taken,
    total_score / tests_taken AS average_score
FROM (
    SELECT
        student_id,
        COUNT(*) AS tests_taken,
        SUM(score) AS total_score
    FROM student_scores
    GROUP BY student_id
) AS student_stats;

3. 使用LATERAL子查询

适合需要基于前面列进行更复杂计算的场景，LATERAL允许子查询引用外层查询的列：

CREATE MATERIALIZED VIEW student_average AS
SELECT
    s.student_id,
    s.tests_taken,
    s.total_score / s.tests_taken AS average_score
FROM (
    SELECT
        student_id,
        COUNT(*) AS tests_taken,
        SUM(score) AS total_score
    FROM student_scores
    GROUP BY student_id
) AS s,
LATERAL (
    -- 这里可以基于tests_taken和total_score做更复杂的计算
    SELECT s.total_score / s.tests_taken AS avg
) AS calc;

4. 接受重复表达式（优化器会自动处理）

如果你的聚合表达式比较简单，PostgreSQL的查询优化器会自动识别重复的COUNT(*)或SUM(score)，并只计算一次，不会带来性能损耗。比如你示例中的average_1写法，实际执行时不会重复计算COUNT(*)：

CREATE MATERIALIZED VIEW student_average AS
SELECT
    student_id,
    COUNT(*) AS tests_taken,
    SUM(score) / COUNT(*) AS average_score
FROM student_scores
GROUP BY student_id;

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