---
id: backend.postgresql.schema-sql
title: 表设计和 SQL：约束、JOIN、CTE、窗口函数、JSONB、UPSERT
type: lesson
domain: backend
stack: [postgresql]
tags: [SQL, 表设计, 约束, JOIN, 窗口函数, JSONB]
level: basic
est_hours: 4
related: [backend.postgresql.index-tx-mvcc, backend.postgresql.interview]
week: [2]
order: 1
---

# 表设计和 SQL：约束、JOIN、CTE、窗口函数、JSONB、UPSERT

## 解释说明

**表设计的基本功**

- **主键**：一般用 `BIGINT GENERATED ALWAYS AS IDENTITY`（自增）或 UUID。对外暴露的 ID 用 UUID 或随机字符串可以避免被遍历。
- **约束是最后一道防线**：`NOT NULL`、`UNIQUE`、`CHECK (amount > 0)`、外键 `REFERENCES`。应用代码有 bug 时，数据库约束能挡住脏数据。
- **范式和反范式**：一般按第三范式拆表（一个事实只存一处）；为了查询性能可以有意冗余（例如订单里冗余商品名称快照），但要清楚冗余字段谁负责更新。
- **时间**：用 `TIMESTAMPTZ`（带时区）存时间，统一存 UTC，展示时再转换。
- **金额**：用 `NUMERIC(12,2)` 或以“分”为单位的整数，**不要用浮点数**。

**常用 SQL**

- `JOIN`：内连接只保留两边都有的行；左连接保留左表全部行，右边没有就是 NULL。
- `GROUP BY` + 聚合函数；`HAVING` 过滤分组后的结果。
- **CTE**（`WITH ... AS`）：把复杂查询拆成几步，可读性好。
- **窗口函数**：`ROW_NUMBER() OVER (PARTITION BY 用户 ORDER BY 时间 DESC)` 不合并行就能做“每组取前 N 条”、排名、累计。
- **UPSERT**：`INSERT ... ON CONFLICT (键) DO UPDATE` 一条语句完成“有则更新、无则插入”，并发下也安全（依赖唯一约束）。
- **RETURNING**：插入、更新后直接返回新值，省一次查询。
- **JSONB**：存半结构化数据（如商品的可变属性），支持索引（GIN）和查询运算符 `->>`、`@>`。结构稳定、经常查询的字段还是建成普通列。

## 案例

电商后台的“每个用户最近 3 笔订单”“按月统计各渠道销售额”“商品扩展属性”都是上面这些语法的典型用途。下面用一个最小的订单库演示。

## 代码示例

```sql
-- 建表：约束写全
CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  channel    TEXT NOT NULL CHECK (channel IN ('web', 'app', 'mini')),
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
  attrs      JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO users (email) VALUES ('a@example.com'), ('b@example.com');
INSERT INTO orders (user_id, channel, amount, attrs, created_at) VALUES
  (1, 'web', 99.00,  '{"coupon": "NEW10"}', now() - interval '3 day'),
  (1, 'app', 25.50,  '{}',                  now() - interval '2 day'),
  (1, 'app', 300.00, '{"gift": true}',      now() - interval '1 day'),
  (1, 'web', 12.00,  '{}',                  now()),
  (2, 'mini', 45.00, '{"coupon": "VIP"}',   now());

-- 1. 每个用户最近 3 笔订单（窗口函数）
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn <= 3;

-- 2. 按渠道统计，只看销售额超过 50 的渠道（GROUP BY + HAVING）
SELECT channel, count(*) AS cnt, sum(amount) AS total
FROM orders GROUP BY channel HAVING sum(amount) > 50 ORDER BY total DESC;

-- 3. CTE：先算每个用户的消费总额，再关联用户表
WITH spend AS (
  SELECT user_id, sum(amount) AS total FROM orders GROUP BY user_id
)
SELECT u.email, COALESCE(s.total, 0) AS total
FROM users u LEFT JOIN spend s ON s.user_id = u.id;

-- 4. JSONB 查询：用了优惠券的订单
SELECT id, attrs->>'coupon' AS coupon FROM orders WHERE attrs ? 'coupon';

-- 5. UPSERT：每日统计表，重复执行不会出错
CREATE TABLE daily_sales (day DATE PRIMARY KEY, total NUMERIC(12, 2) NOT NULL);
INSERT INTO daily_sales (day, total)
SELECT created_at::date, sum(amount) FROM orders GROUP BY 1
ON CONFLICT (day) DO UPDATE SET total = EXCLUDED.total
RETURNING *;
```

运行：`docker run -d --name pg -e POSTGRES_PASSWORD=pg -p 5432:5432 postgres:17`，然后 `psql -h localhost -U postgres` 粘贴执行。

## 易错点

- **`NOT IN` 遇到 NULL**：`x NOT IN (子查询)` 只要子查询结果里有一个 NULL，整个条件就不成立，结果为空；改用 `NOT EXISTS`。
- **`count(列)` 不统计 NULL**，`count(*)` 统计所有行。
- **浮点数存金额**：`0.1 + 0.2` 的精度问题会出现在账目里。
- **LEFT JOIN 后在 WHERE 里过滤右表**：会把左连接变回内连接，右表的条件要写在 `ON` 里。
- **先查再插判断唯一**：并发时两个请求都查不到、都插入成功；用唯一约束 + `ON CONFLICT`。
- **JSONB 什么都往里放**：失去约束和类型检查，查询也难优化。

## 面试常问

见 [PostgreSQL 面试题](interview.md)：范式、`NOT IN` 和 NULL、UPSERT 的并发安全、JSONB 的使用场景。