# 第 5 章 配套代码

> 演示 PG 高级查询：JOIN 全家桶、子查询、CTE / 递归 CTE、窗口函数、UPSERT、LATERAL JOIN。配套表统一以 `ch5_` 前缀，避免与其他章节冲突。

## 准备工作

1. 跑初始化脚本：
   ```bash
   psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql
   ```
   会建出 7 张表（`ch5_users / ch5_products / ch5_orders / ch5_order_items / ch5_employees / ch5_orders_archive / ch5_monthly_sales`）并灌好 1000 行订单 / 24 个月销售 / 16 人组织树。
2. 安装依赖：
   ```bash
   pip install "psycopg[binary]>=3.1"
   ```
3. （可选）通过环境变量覆盖默认连接：
   ```bash
   export PG_DSN="host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
   ```

## 脚本一览（推荐运行顺序）

| 脚本 | 一句话说明 | 关键 PG 特性 |
|------|------------|--------------|
| `01_join_subquery.py` | 4 种 JOIN + EXISTS / IN / JOIN 计划对比 + `NOT IN` NULL 陷阱 | `LEFT/RIGHT/FULL/CROSS JOIN` / 半连接 / `NOT EXISTS` |
| `02_recursive_cte.py` | 员工树向下展开 + 向上找上级 + `CYCLE` 防环（PG 14+） | `WITH RECURSIVE` / `CYCLE … SET … USING …` |
| `03_window_functions.py` | 类目排名 + 移动平均 + 同比 + `DISTINCT ON` 取每用户最新一笔 | `ROW_NUMBER/RANK/DENSE_RANK/NTILE` / `LAG` / `ROWS BETWEEN …` |
| `04_upsert.py` | `ON CONFLICT DO NOTHING/UPDATE` + 部分唯一索引 + 批量 UPSERT | `EXCLUDED` / `ON CONFLICT (col) WHERE …` / 部分唯一索引 |
| `05_lateral_join.py` | 每用户最近 N 笔订单（LATERAL vs 窗口函数）+ EXPLAIN 对比 | `LEFT JOIN LATERAL` / `(user_id, created_at DESC)` 索引 |

## 预期输出

`05_lateral_join.py` 跑完会看到 LATERAL 与窗口函数的执行计划差异：

```
A. LATERAL：每个用户最近 3 笔订单
  uid | name      | order_id | amount  | created_at
  ----+-----------+----------+---------+------------
   1  | user_1    |   902    | 1820.34 | 2026-04-15 …
   1  | user_1    |   771    | 1244.10 | 2026-04-12 …
   …

C. 两种写法的执行计划对比
  LATERAL   cost=    35.42  actual=   2.184ms  rows=15
  Window    cost=   168.91  actual=  18.347ms  rows=15
```

## 常见报错

- `connection refused` → PG 没起 / 端口不对
- `relation "ch5_orders" does not exist` → 没跑 `../init.sql`
- `column "rn" does not exist` → 把窗口函数 `ROW_NUMBER() OVER …` 直接用在 `WHERE` 里了，须用子查询 / CTE 包一层
- `duplicate key value violates unique constraint "ch5_users_email_key"` → 04_upsert.py 跑过半被中断后再跑，先 `TRUNCATE ch5_up_users` 或重跑 `init.sql`