用简单的CTE做数据准备：例子和真实场景

用简单的CTE做数据准备：例子和真实场景

你大概已经掌握了CTE的基础，甚至写WITH都快成肌肉记忆了。今天我们来深入一点——看看在实际场景下怎么用CTE做数据准备。想象一下你要写个报表或者复杂SQL：先把“原料”分好——再来煮一锅香喷喷的分析“汤”。

CTE就是做中间步骤的神器：过滤、计数、聚合、算平均值——所有数据准备该做的事都能搞定。你可以把复杂的查询拆成清晰的逻辑块，每块只做一件事：挑出需要的记录、算平均、或者给最终查询准备好数据。这样代码更好读，也不用反复写重复片段，还能避免用临时表（如果你不想用的话）。

用CTE特别适合做报表数据准备、复杂过滤，或者想在后续处理前“清洗”下数据。CTE不只是个技术小技巧，更像是一步步搭建逻辑的策略——让你全程掌控每一步。

准备好了吗？现在直接上例子。

用CTE过滤数据

CTE超适合把你想要的数据从大表里“拎”出来，后面就只用这些数据继续操作。比起写一堆嵌套查询，你先过滤数据，给这步起个名字——后面就能像用普通表一样用它。

假设我们有个students表，存着学生的成绩：

students表

比如你想选出成绩大于85的同学。用CTE写起来就很直观：

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

结果：

这样做哪里爽？

你提前把需要的行挑出来，还给这步起了个名字——excellent_students。现在你可以继续用这个结果：比如和别的表join、再过滤、或者算平均分。整个流程很清晰，尤其是查询很长的时候不会乱。

用CTE聚合数据

现在来看下怎么计数或者算平均值。比如我们有个enrollments表，存着学生选了哪些课。

enrollments表

我们想知道每门课有多少学生选。

查询例子：

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

结果：

这里要注意：

• 我们按course_id分组，统计了每门课的学生数。
• course_enrollments表现在就有了这些信息，可以继续分析用。

为报表做数据准备

如果你要做个多步处理的数据报表，CTE绝对是神器。它能把所有逻辑拆成清楚的块，还不用建一堆临时表。比如你有grades成绩表和students学生信息表。现在要做个报表，只要平均分大于80的学生。

grades表

students表

不用写巨长的嵌套查询，分步来就很舒服：

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

第一步（avg_grades）我们算了每个学生的平均分，只保留分数大于80的。第二步（students_with_grades）把这些数据和students表join，拿到名字和姓。最后SELECT出来的表就能直接放报表里——都算好、过滤好、排好版了。

结果：

这就是CTE的爽点：你可以专注于逻辑和结构，不用管什么临时表的创建和删除。

算复杂指标

有时候你得在一个查询里组合不同的数据。比如我们要算每门课：

1. 学生数量。
2. 课程平均分。

查询例子：

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

常见的坑，别踩！

用CTE的时候，很容易踩几个常见的坑。

第一个——过度物化。如果你写太多CTE，PostgreSQL可能会把它们的结果存成临时表，哪怕只用一次。这样反而让查询变慢了。

第二个坑——过滤条件用错地方。如果你在不同阶段乱加过滤，最后查出来的数据可能不是你想要的。比如有时候会太早把重要数据过滤掉。

所以CTE最好用在数据要经过多步转换的时候——这时候它的优势最明显，能帮你写出清晰、易懂又高效的代码。