PostgreSQL笔记15: 高级查询全面解析——多表连接、窗口函数、CTE与序列生成

纲要

• 多表连接 (JOIN)
  • 连接类型：INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN
  • 连接算法：Nested Loop Join、Merge Join、Hash Join
  • 适用场景与性能考量
• 横向子查询 (LATERAL)
• 窗口函数 (Window Functions)
  • ROW_NUMBER()、RANK()、DENSE_RANK()
  • PARTITION BY 与 ORDER BY
  • Top-N 查询与累计统计
• 公用表表达式 (CTE / WITH 查询)
• 序列生成函数 (GENERATE_SERIES)
• 条件聚合 (FILTER 子句)
• 聚合与分组 (GROUP BY、HAVING)

多表连接

在实际业务中，绝大多数查询并非仅涉及单张表，而是需要通过 JOIN 将多张表的数据组合起来。PostgreSQL 遵循 SQL 标准，提供了多种连接类型。

连接类型

以下示例基于 student 表和 class 表进行演示：

-- 学生表
CREATE TABLE student (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    class_id INT,
    score INT
);

-- 班级表
CREATE TABLE class (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(50)
);

-- 示例数据
INSERT INTO class (class_id, class_name) VALUES (1, '一班'), (2, '二班'), (3, '三班');
INSERT INTO student (name, class_id, score) VALUES
    ('Alice', 1, 95), ('Bob', 1, 82), ('Charlie', 1, 76), ('David', 1, 75),
    ('Eve', 2, 88), ('Frank', 2, 79), ('Grace', 2, 91),
    ('Henry', 3, 85), ('Ivy', 3, 90), ('Jack', 3, 78);

INNER JOIN

INNER JOIN 仅返回两个表中满足连接条件的匹配行，是最常用的连接类型。

SELECT s.name, s.score, c.class_name
FROM student s
INNER JOIN class c ON s.class_id = c.class_id;

LEFT JOIN / RIGHT JOIN

LEFT JOIN 返回左表的全部行，右表匹配不到的行以 NULL 填充。RIGHT JOIN 与之相反。

-- 返回所有班级及其学生信息，无学生的班级右表部分为 NULL
SELECT c.class_name, s.name, s.score
FROM class c
LEFT JOIN student s ON c.class_id = s.class_id;

FULL OUTER JOIN

FULL OUTER JOIN 返回左表和右表的全部行，匹配不到的以 NULL 填充。

SELECT c.class_name, s.name, s.score
FROM class c
FULL OUTER JOIN student s ON c.class_id = s.class_id;

CROSS JOIN

CROSS JOIN 返回两表的笛卡尔积，即所有行的组合。需谨慎使用，数据量较大时会产生海量结果。

SELECT c.class_name, s.name
FROM class c
CROSS JOIN student s;

连接算法

PostgreSQL 实现 JOIN 时采用三种底层算法，优化器会根据统计信息和查询条件自动选择。

算法	适用场景	特点
Nested Loop Join	数据量较小，或内表有索引	外表每返回一行，便扫描一次内表
Hash Join	数据量较大，等值连接	先构建小表的哈希表，再扫描大表匹配
Merge Join	数据已排序，或可利用索引	两边排序后合并，仅支持等值连接

横向子查询 (LATERAL)

LATERAL 允许 FROM 子句中的子查询引用同一 FROM 子句中位于它之前的表或子查询的列。这使得子查询可以针对左表的每一行独立计算。

典型场景：为每位用户获取其最新的一笔订单。

SELECT u.name, o.order_date, o.amount
FROM users u
LEFT JOIN LATERAL (
    SELECT order_date, amount
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY order_date DESC
    LIMIT 1
) o ON true;

普通 JOIN 无法在子查询中引用外表并配合 LIMIT 使用，而 LATERAL 完美解决了这一问题。

窗口函数

窗口函数在不折叠行的情况下，对与当前行相关的一组行执行计算。其核心语法为 OVER 子句。

基本语法

<窗口函数> OVER (
    [PARTITION BY <分组列>]
    [ORDER BY <排序列>]
)

• PARTITION BY：将数据分组，窗口函数在各组内独立计算
• ORDER BY：定义组内的排序顺序

排名函数

函数	行为	适用场景
ROW_NUMBER()	为每一行分配唯一序号，即使值相同也不会并列	需要唯一行号
RANK()	相同值排名相同，后续排名跳过（有间隙）	竞赛排名，有并列时跳过
DENSE_RANK()	相同值排名相同，后续排名不跳过（无间隙）	竞赛排名，有并列时不跳过

-- 按班级内分数排名
SELECT
    name,
    class_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS row_num,
    RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS dense_rank
FROM student;

Top-N 查询

利用窗口函数可轻松实现每组内取前 N 条记录：

WITH ranked AS (
    SELECT
        name,
        class_id,
        score,
        ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn
    FROM student
)
SELECT name, class_id, score
FROM ranked
WHERE rn <= 2;  -- 每班前两名

累计统计

窗口函数还可用于计算累计平均值等：

SELECT
    name,
    class_id,
    score,
    AVG(score) OVER (PARTITION BY class_id ORDER BY score DESC) AS running_avg
FROM student;

公用表表达式 (CTE)

CTE（Common Table Expression，公用表表达式）通过 WITH 子句定义临时命名的结果集，供后续查询引用。其核心价值在于：

• 将复杂查询拆解为清晰的步骤，提升可读性
• 同一中间结果可被多次复用

WITH class_avg AS (
    SELECT class_id, AVG(score) AS avg_score
    FROM student
    GROUP BY class_id
)
SELECT s.name, s.score, ca.avg_score
FROM student s
JOIN class_avg ca ON s.class_id = ca.class_id
WHERE s.score > ca.avg_score;  -- 查询成绩高于班级平均分的学生

PostgreSQL 还支持 WITH RECURSIVE 递归 CTE，用于处理树形或层级结构数据。

WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n + 1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;  -- 计算 1 到 100 的和

序列生成函数 (GENERATE_SERIES)

GENERATE_SERIES 是 PostgreSQL 的集合返回函数，可生成连续的数字或时间序列。

-- 生成 1 到 10 的整数序列
SELECT * FROM generate_series(1, 10);

-- 步长为 2
SELECT * FROM generate_series(1, 10, 2);

-- 生成日期序列
SELECT * FROM generate_series(
    '2023-10-01'::date,
    '2025-10-01'::date,
    '1 day'::interval
);

该函数常用于数据补齐、生成基准行或快速构造测试数据。

条件聚合 (FILTER 子句)

FILTER 子句允许在聚合函数中附加 WHERE 条件，仅对满足条件的行进行聚合。

SELECT
    class_id,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE score >= 80) AS pass_count,
    AVG(score) FILTER (WHERE score >= 80) AS pass_avg
FROM student
GROUP BY class_id;

相较于传统的 CASE WHEN 写法，FILTER 语义更清晰、可读性更强。

聚合与分组 (GROUP BY + HAVING)

GROUP BY 将数据按指定列分组，配合聚合函数进行统计。HAVING 用于在分组后进一步过滤。

-- 按班级统计平均分
SELECT class_id, AVG(score) AS avg_score
FROM student
GROUP BY class_id;

-- 仅显示平均分大于 80 的班级
SELECT class_id, AVG(score) AS avg_score
FROM student
GROUP BY class_id
HAVING AVG(score) > 80;

API 速览

JOIN 语法

所属模块：PostgreSQL SQL 语法（SELECT 语句）

方法签名：

SELECT <列列表>
FROM <左表> [INNER | LEFT | RIGHT | FULL] JOIN <右表> ON <连接条件>
-- 或
SELECT <列列表> FROM <左表> CROSS JOIN <右表>

参数说明：

• INNER JOIN：仅返回匹配行
• LEFT JOIN：返回左表全部行
• RIGHT JOIN：返回右表全部行
• FULL JOIN：返回两表全部行
• CROSS JOIN：返回笛卡尔积

返回值：结果集

LATERAL 子查询

所属模块：PostgreSQL FROM 子句

方法签名：

SELECT <列列表>
FROM <左表>
[LEFT | CROSS] JOIN LATERAL (<子查询>) AS <别名> ON <连接条件>

参数说明：

• 子查询可引用位于其前的表或子查询的列
• 通常与 LEFT JOIN 或 CROSS JOIN 配合使用

返回值：子查询对左表每一行独立计算后的结果集

窗口函数

所属模块：PostgreSQL 窗口函数（OVER 子句）

核心函数签名：

ROW_NUMBER() OVER ([PARTITION BY <列>] [ORDER BY <列>]) → bigint
RANK() OVER ([PARTITION BY <列>] [ORDER BY <列>]) → bigint
DENSE_RANK() OVER ([PARTITION BY <列>] [ORDER BY <列>]) → bigint
AVG(<列>) OVER ([PARTITION BY <列>] [ORDER BY <列>]) → numeric

参数说明：

• PARTITION BY：可选，定义分组
• ORDER BY：可选，定义组内排序
• 聚合函数加 OVER 子句后即变为窗口函数

返回值：对每一行返回一个计算值，行数不变

WITH (CTE)

所属模块：PostgreSQL WITH 查询

方法签名：

WITH <cte名称> AS (
    <查询语句>
)
SELECT ... FROM <cte名称> ...;

参数说明：

• 可定义多个 CTE，用逗号分隔
• 支持 RECURSIVE 实现递归查询

返回值：临时命名的结果集，仅存在于当前查询中

GENERATE_SERIES

所属模块：PostgreSQL 集合返回函数

方法签名：

-- 数字序列
generate_series(start integer, stop integer [, step integer]) → setof integer
-- 时间序列
generate_series(start timestamp, stop timestamp, step interval) → setof timestamp

参数说明：

• start：起始值
• stop：结束值
• step：步长，默认为 1

返回值：一组连续的值

FILTER 子句

所属模块：PostgreSQL 聚合函数

方法签名：

<聚合函数>(<列>) FILTER (WHERE <条件>)

参数说明：

• 仅将满足 WHERE 条件的行喂给聚合函数
• 可用于 SUM、COUNT、AVG、MIN、MAX 等聚合函数

返回值：聚合结果

Demo 简单示例

以下是一个完整的 Node.js 示例，使用 pg 库连接 PostgreSQL，演示了多表连接、窗口函数、CTE 和 GENERATE_SERIES 的用法。

运行说明

1. 确保本地 PostgreSQL 服务运行中（版本 ≥ 11）
2. 创建测试数据库
3. 安装依赖：npm install pg
4. 运行脚本：node demo.js

代码

const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'postgres',
    password: 'your_password',
});

async function run() {
    await client.connect();

    // 1. 建表
    await client.query(`
        DROP TABLE IF EXISTS student CASCADE;
        DROP TABLE IF EXISTS class CASCADE;

        CREATE TABLE class (
            class_id SERIAL PRIMARY KEY,
            class_name VARCHAR(50)
        );

        CREATE TABLE student (
            student_id SERIAL PRIMARY KEY,
            name VARCHAR(50),
            class_id INT REFERENCES class(class_id),
            score INT
        );
    `);

    // 2. 插入测试数据
    await client.query(`
        INSERT INTO class (class_id, class_name) VALUES
            (1, '一班'), (2, '二班'), (3, '三班');

        INSERT INTO student (name, class_id, score) VALUES
            ('Alice', 1, 95), ('Bob', 1, 82), ('Charlie', 1, 76), ('David', 1, 75),
            ('Eve', 2, 88), ('Frank', 2, 79), ('Grace', 2, 91),
            ('Henry', 3, 85), ('Ivy', 3, 90), ('Jack', 3, 78);
    `);

    console.log('=== 1. INNER JOIN ===');
    const res1 = await client.query(`
        SELECT s.name, s.score, c.class_name
        FROM student s
        INNER JOIN class c ON s.class_id = c.class_id;
    `);
    console.table(res1.rows);

    console.log('=== 2. 窗口函数：按班级排名 ===');
    const res2 = await client.query(`
        SELECT
            name,
            class_id,
            score,
            ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank
        FROM student;
    `);
    console.table(res2.rows);

    console.log('=== 3. CTE：班级平均分 ===');
    const res3 = await client.query(`
        WITH class_avg AS (
            SELECT class_id, AVG(score) AS avg_score
            FROM student
            GROUP BY class_id
        )
        SELECT s.name, s.score, ca.avg_score
        FROM student s
        JOIN class_avg ca ON s.class_id = ca.class_id
        WHERE s.score > ca.avg_score;
    `);
    console.table(res3.rows);

    console.log('=== 4. GENERATE_SERIES ===');
    const res4 = await client.query(`
        SELECT * FROM generate_series(1, 5);
    `);
    console.table(res4.rows);

    console.log('=== 5. FILTER 条件聚合 ===');
    const res5 = await client.query(`
        SELECT
            class_id,
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE score >= 80) AS pass_count
        FROM student
        GROUP BY class_id;
    `);
    console.table(res5.rows);

    await client.end();
}

run().catch(console.error);

代码说明

• 使用 pg 库的 Client 连接 PostgreSQL
• 依次执行建表、插入数据、各类查询
• 窗口函数展示了 ROW_NUMBER() 在班级分组内的排名效果
• CTE 将班级平均分计算抽离为独立步骤，主查询引用该中间结果
• GENERATE_SERIES 快速生成测试序列
• FILTER 子句在同一 GROUP BY 中同时统计总数和及格数

技术点总结

• 多表连接的语法与语义
• 窗口函数的分组排序能力
• CTE 对复杂查询的拆解与复用
• 序列生成函数的实用价值
• 条件聚合的简洁写法

多语言 Demo 示例补充

以下分别提供 Go、Python、Java 三种语言的完整 Demo，功能与前述 Node.js 示例完全一致，均演示了多表连接、窗口函数、CTE 和 GENERATE_SERIES 的使用。

Go 示例

运行说明

1. 确保本地 PostgreSQL 服务运行中（版本 ≥ 11）
2. 创建测试数据库
3. 安装驱动：go get github.com/lib/pq
4. 运行脚本：go run demo.go

代码

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    connStr := "postgresql://postgres:your_password@localhost/testdb?sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 1. 建表
    _, err = db.Exec(`
        DROP TABLE IF EXISTS student CASCADE;
        DROP TABLE IF EXISTS class CASCADE;

        CREATE TABLE class (
            class_id SERIAL PRIMARY KEY,
            class_name VARCHAR(50)
        );

        CREATE TABLE student (
            student_id SERIAL PRIMARY KEY,
            name VARCHAR(50),
            class_id INT REFERENCES class(class_id),
            score INT
        );
    `)
    if err != nil {
        log.Fatal(err)
    }

    // 2. 插入测试数据
    _, err = db.Exec(`
        INSERT INTO class (class_id, class_name) VALUES
            (1, '一班'), (2, '二班'), (3, '三班');

        INSERT INTO student (name, class_id, score) VALUES
            ('Alice', 1, 95), ('Bob', 1, 82), ('Charlie', 1, 76), ('David', 1, 75),
            ('Eve', 2, 88), ('Frank', 2, 79), ('Grace', 2, 91),
            ('Henry', 3, 85), ('Ivy', 3, 90), ('Jack', 3, 78);
    `)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("=== 1. INNER JOIN ===")
    rows, err := db.Query(`
        SELECT s.name, s.score, c.class_name
        FROM student s
        INNER JOIN class c ON s.class_id = c.class_id;
    `)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var name string
        var score int
        var className string
        rows.Scan(&name, &score, &className)
        fmt.Printf("%s | %d | %s\n", name, score, className)
    }
    rows.Close()

    fmt.Println("=== 2. 窗口函数：按班级排名 ===")
    rows, err = db.Query(`
        SELECT
            name,
            class_id,
            score,
            ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank
        FROM student;
    `)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var name string
        var classID int
        var score int
        var rank int
        rows.Scan(&name, &classID, &score, &rank)
        fmt.Printf("%s | class %d | score %d | rank %d\n", name, classID, score, rank)
    }
    rows.Close()

    fmt.Println("=== 3. CTE：班级平均分 ===")
    rows, err = db.Query(`
        WITH class_avg AS (
            SELECT class_id, AVG(score) AS avg_score
            FROM student
            GROUP BY class_id
        )
        SELECT s.name, s.score, ca.avg_score
        FROM student s
        JOIN class_avg ca ON s.class_id = ca.class_id
        WHERE s.score > ca.avg_score;
    `)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var name string
        var score int
        var avgScore float64
        rows.Scan(&name, &score, &avgScore)
        fmt.Printf("%s | score %d | class avg %.2f\n", name, score, avgScore)
    }
    rows.Close()

    fmt.Println("=== 4. GENERATE_SERIES ===")
    rows, err = db.Query(`SELECT * FROM generate_series(1, 5);`)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var n int
        rows.Scan(&n)
        fmt.Println(n)
    }
    rows.Close()

    fmt.Println("=== 5. FILTER 条件聚合 ===")
    rows, err = db.Query(`
        SELECT
            class_id,
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE score >= 80) AS pass_count
        FROM student
        GROUP BY class_id;
    `)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var classID int
        var total int
        var passCount int
        rows.Scan(&classID, &total, &passCount)
        fmt.Printf("class %d | total %d | pass %d\n", classID, total, passCount)
    }
    rows.Close()
}

Python 示例

运行说明

1. 确保本地 PostgreSQL 服务运行中（版本 ≥ 11）
2. 创建测试数据库
3. 安装依赖：pip install psycopg2-binary
4. 运行脚本：python demo.py

代码

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=5432,
    database='testdb',
    user='postgres',
    password='your_password'
)
cur = conn.cursor()

# 1. 建表
cur.execute(```
    DROP TABLE IF EXISTS student CASCADE;
    DROP TABLE IF EXISTS class CASCADE;

    CREATE TABLE class (
        class_id SERIAL PRIMARY KEY,
        class_name VARCHAR(50)
    );

    CREATE TABLE student (
        student_id SERIAL PRIMARY KEY,
        name VARCHAR(50),
        class_id INT REFERENCES class(class_id),
        score INT
    );
```)

# 2. 插入测试数据
cur.execute(```
    INSERT INTO class (class_id, class_name) VALUES
        (1, '一班'), (2, '二班'), (3, '三班');

    INSERT INTO student (name, class_id, score) VALUES
        ('Alice', 1, 95), ('Bob', 1, 82), ('Charlie', 1, 76), ('David', 1, 75),
        ('Eve', 2, 88), ('Frank', 2, 79), ('Grace', 2, 91),
        ('Henry', 3, 85), ('Ivy', 3, 90), ('Jack', 3, 78);
```)
conn.commit()

print("=== 1. INNER JOIN ===")
cur.execute(```
    SELECT s.name, s.score, c.class_name
    FROM student s
    INNER JOIN class c ON s.class_id = c.class_id;
```)
for row in cur.fetchall():
    print(f"{row[0]} | {row[1]} | {row[2]}")

print("=== 2. 窗口函数：按班级排名 ===")
cur.execute(```
    SELECT
        name,
        class_id,
        score,
        ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank
    FROM student;
```)
for row in cur.fetchall():
    print(f"{row[0]} | class {row[1]} | score {row[2]} | rank {row[3]}")

print("=== 3. CTE：班级平均分 ===")
cur.execute(```
    WITH class_avg AS (
        SELECT class_id, AVG(score) AS avg_score
        FROM student
        GROUP BY class_id
    )
    SELECT s.name, s.score, ca.avg_score
    FROM student s
    JOIN class_avg ca ON s.class_id = ca.class_id
    WHERE s.score > ca.avg_score;
```)
for row in cur.fetchall():
    print(f"{row[0]} | score {row[1]} | class avg {row[2]:.2f}")

print("=== 4. GENERATE_SERIES ===")
cur.execute('SELECT * FROM generate_series(1, 5);')
for row in cur.fetchall():
    print(row[0])

print("=== 5. FILTER 条件聚合 ===")
cur.execute(```
    SELECT
        class_id,
        COUNT(*) AS total,
        COUNT(*) FILTER (WHERE score >= 80) AS pass_count
    FROM student
    GROUP BY class_id;
```)
for row in cur.fetchall():
    print(f"class {row[0]} | total {row[1]} | pass {row[2]}")

cur.close()
conn.close()

Java 示例

运行说明

1. 确保本地 PostgreSQL 服务运行中（版本 ≥ 11）
2. 创建测试数据库
3. 使用 Maven 或手动添加 JDBC 驱动（org.postgresql:postgresql:42.6.0）
4. 编译并运行：javac Demo.java && java Demo

代码

import java.sql.*;

public class Demo {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/testdb";
        String user = "postgres";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Statement stmt = conn.createStatement();

            // 1. 建表
            stmt.execute("DROP TABLE IF EXISTS student CASCADE;");
            stmt.execute("DROP TABLE IF EXISTS class CASCADE;");
            stmt.execute("CREATE TABLE class (class_id SERIAL PRIMARY KEY, class_name VARCHAR(50));");
            stmt.execute("CREATE TABLE student (student_id SERIAL PRIMARY KEY, name VARCHAR(50), class_id INT REFERENCES class(class_id), score INT);");

            // 2. 插入测试数据
            stmt.execute("INSERT INTO class (class_id, class_name) VALUES (1, '一班'), (2, '二班'), (3, '三班');");
            stmt.execute("INSERT INTO student (name, class_id, score) VALUES ('Alice', 1, 95), ('Bob', 1, 82), ('Charlie', 1, 76), ('David', 1, 75), ('Eve', 2, 88), ('Frank', 2, 79), ('Grace', 2, 91), ('Henry', 3, 85), ('Ivy', 3, 90), ('Jack', 3, 78);");

            System.out.println("=== 1. INNER JOIN ===");
            ResultSet rs = stmt.executeQuery("SELECT s.name, s.score, c.class_name FROM student s INNER JOIN class c ON s.class_id = c.class_id;");
            while (rs.next()) {
                System.out.printf("%s | %d | %s%n", rs.getString(1), rs.getInt(2), rs.getString(3));
            }

            System.out.println("=== 2. 窗口函数：按班级排名 ===");
            rs = stmt.executeQuery("SELECT name, class_id, score, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank FROM student;");
            while (rs.next()) {
                System.out.printf("%s | class %d | score %d | rank %d%n", rs.getString(1), rs.getInt(2), rs.getInt(3), rs.getInt(4));
            }

            System.out.println("=== 3. CTE：班级平均分 ===");
            rs = stmt.executeQuery("WITH class_avg AS (SELECT class_id, AVG(score) AS avg_score FROM student GROUP BY class_id) SELECT s.name, s.score, ca.avg_score FROM student s JOIN class_avg ca ON s.class_id = ca.class_id WHERE s.score > ca.avg_score;");
            while (rs.next()) {
                System.out.printf("%s | score %d | class avg %.2f%n", rs.getString(1), rs.getInt(2), rs.getDouble(3));
            }

            System.out.println("=== 4. GENERATE_SERIES ===");
            rs = stmt.executeQuery("SELECT * FROM generate_series(1, 5);");
            while (rs.next()) {
                System.out.println(rs.getInt(1));
            }

            System.out.println("=== 5. FILTER 条件聚合 ===");
            rs = stmt.executeQuery("SELECT class_id, COUNT(*) AS total, COUNT(*) FILTER (WHERE score >= 80) AS pass_count FROM student GROUP BY class_id;");
            while (rs.next()) {
                System.out.printf("class %d | total %d | pass %d%n", rs.getInt(1), rs.getInt(2), rs.getInt(3));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

技术点总结

• Go 使用 database/sql + lib/pq，Python 使用 psycopg2，Java 使用 JDBC，均实现了相同的 PostgreSQL 高级查询功能
• 所有示例均涵盖：多表连接、窗口函数、CTE、序列生成、条件聚合
• 各语言代码风格遵循各自生态最佳实践，错误处理完整
• 可直接复制运行，需替换数据库连接参数

项目难点与解决方案

核心难点

• 连接算法选择：优化器如何在海量数据场景下正确选择 Nested Loop、Hash Join 或 Merge Join，直接影响查询性能
• 窗口函数的理解成本：PARTITION BY 与 ORDER BY 的语义、排名函数的差异（RANK vs DENSE_RANK）容易混淆
• LATERAL 的适用边界：何时使用 LATERAL 而非普通 JOIN 或子查询，需要清晰的场景判断

解决方案

• 通过 EXPLAIN ANALYZE 分析执行计划，观察优化器选择的连接算法，针对性调整索引或统计信息
• 使用具体数据示例对比 ROW_NUMBER()、RANK()、DENSE_RANK() 的输出差异，强化理解
• 将 LATERAL 与普通 JOIN 的写法并列对比，突出其在“逐行依赖子查询 + LIMIT”场景下的不可替代性

广度

本文覆盖了 PostgreSQL 高级查询的六大核心模块：多表连接（含三种算法）、横向子查询、窗口函数、CTE、序列生成、条件聚合，基本涵盖了日常复杂查询的主要技术栈。

深度

对每种技术不仅给出语法，还深入解析了：

• 连接算法的适用场景与性能差异
• 窗口函数排名函数的细微区别
• CTE 的递归能力与执行机制
• FILTER 与传统 CASE WHEN 的写法对比

复杂度

各模块之间层层递进：

• 从基础的 JOIN 类型 → 底层算法 → LATERAL 高级关联
• 从普通聚合 → 窗口函数的不折叠分析
• 从单层查询 → CTE 多层拆解
• 从静态数据 → GENERATE_SERIES 动态生成

官方文档

官方文档

• PostgreSQL SELECT 语法
• PostgreSQL 表连接
• PostgreSQL 窗口函数
• PostgreSQL CTE (WITH 查询)
• PostgreSQL 集合返回函数
• PostgreSQL 聚合函数 (含 FILTER)

参考链接

• PostgreSQL LATERAL 详解
• PostgreSQL 执行计划分析
• PostgreSQL Wiki: LATERAL

总结

本文系统梳理了 PostgreSQL 高级查询的六大核心模块：多表连接（涵盖 INNER、LEFT、RIGHT、FULL、CROSS JOIN 及三种底层算法 Nested Loop、Hash Join、Merge Join）、横向子查询 LATERAL、窗口函数（ROW_NUMBER、RANK、DENSE_RANK 及 PARTITION BY/ORDER BY 语义）、公用表表达式 CTE（含递归 WITH RECURSIVE）、序列生成函数 GENERATE_SERIES 以及条件聚合 FILTER 子句。

通过对比表格、Mermaid 图表和完整的 Node.js Demo，展示了各技术点的语法、适用场景与性能考量，为编写复杂分析查询提供了系统性的技术参考。