110 lines
5.2 KiB
Markdown
110 lines
5.2 KiB
Markdown
---
|
||
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 的使用场景。
|