[!info] related notes

• 课程 MOC：BodySense 数据库工程深化课程 MOC
• 前置：第 2 课
• 知识：SQL JOIN、SQL JOIN、GROUP BY 与聚合查询

BodySense 数据库第 3 课：SQL JOIN、Aggregation、CTE、Window 与 Projection

[!abstract] 本课目标 能不依赖 ORM 写出真实查询，并理解“返回什么”与“怎么执行”是两层问题。

从零建立概念：SQL 是声明“我要什么”，不是逐行写算法

SQL = Structured Query Language。

和 Go 代码一个关键差异：

Go 常写：

第一步做什么
第二步做什么
循环多少次

SQL 更多写：

我想要哪些满足关系条件的数据。

例如：

SELECT id, title
FROM conversations
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

你没有告诉 PostgreSQL：

先从磁盘第几页读
用哪棵索引
是不是先 sort
用几个 buffer

这些由 Query Planner 决定。

所以 SQL 学习要分两层：

Logical Query Semantics
→ 这条 SQL 应该返回什么

Physical Execution Plan
→ PostgreSQL 实际怎么得到它

本课先把第一层学扎实，第 8 课再深入 Planner。

SELECT 的最小结构

SELECT columns
FROM source
WHERE predicate
ORDER BY ...
LIMIT ...;

Projection

SELECT id, title 在关系查询语义中就是一种 Projection：只保留需要的属性。

Selection / Filter

WHERE user_id = $1 表达筛选谓词。

ORDER BY

如果业务依赖顺序，就必须显式声明。

没有 ORDER BY：

数据库不承诺结果按主键、插入时间或物理位置稳定返回。

JOIN 为什么存在

规范化后，不同事实位于不同表。

例如：

conversations
id | user_id | title

messages
id | conversation_id | content

想获得：

某个会话 + 它的消息

就要按关系条件组合：

SELECT c.id, c.title, m.id, m.content
FROM conversations c
JOIN messages m
  ON m.conversation_id = c.id
WHERE c.id = $1;

ON 的本质是：

哪些左侧行和哪些右侧行属于同一组合。

INNER JOIN 与 LEFT JOIN 从集合结果理解

INNER JOIN

只有两边匹配才留下：

Parent A ─ Child 1 → output
Parent B ─ no child → disappears

LEFT JOIN

无论右侧有没有匹配，都保留左侧：

Parent A ─ Child 1 → output
Parent B ─ NULL    → output

这就是“统计没有子记录的父实体为 0”时 LEFT JOIN 常见的原因。

ON vs WHERE：为什么 LEFT JOIN 最容易踩坑

假设：

LEFT JOIN messages m
  ON m.conversation_id = c.id
WHERE m.role = 'user'

没有 message 的 conversation：

m.role = NULL
WHERE NULL = 'user'
→ 不成立
→ parent 被过滤

结果看起来越来越像 INNER JOIN。

如果业务意思是：

保留所有 conversation，但只匹配 role=user 的 message

条件更应该在：

ON m.conversation_id = c.id
AND m.role = 'user'

Aggregation：从多行压成分组统计

常见 aggregate：

COUNT
SUM
AVG
MIN
MAX

GROUP BY

SELECT conversation_id, COUNT(*)
FROM messages
GROUP BY conversation_id;

表示：

先按 conversation_id 分组
→ 每组计算 COUNT

COUNT(*) vs COUNT(column)

COUNT(*)
→ 统计行

COUNT(column)
→ 只统计 column 非 NULL 的行

在 LEFT JOIN 聚合时这点非常关键。

WHERE vs HAVING

WHERE
→ 聚合前过滤明细行

HAVING
→ GROUP BY / aggregate 后过滤分组

例如：

HAVING COUNT(m.id) > 10

CTE：给复杂查询的中间关系命名

CTE = Common Table Expression。

语法：

WITH recent AS (
  SELECT ...
)
SELECT ... FROM recent;

可以把一条复杂查询拆成逻辑阶段。

重要边界：

CTE 首先是查询表达/组织工具，不应该默认理解成“性能优化器”。

最终执行方式仍要看 PostgreSQL 版本和 Query Plan。

Window Function：计算分组信息但不把明细行折叠掉

GROUP BY：

10 rows
→ one row per group

Window：

10 rows
→ still 10 rows
→ each row gets extra computed context

例如每用户最新一条：

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY created_at DESC
) AS rn

再筛：

rn = 1

常见 Window 还有：

LAG / LEAD
SUM(...) OVER
COUNT(...) OVER
RANK / DENSE_RANK

对 BodySense 的时间序列、revision、rollout history 都很有用。

参数化查询：$1 不是字符串拼接

生产 SQL 不应该：

"... WHERE email = '" + userInput + "'"

而应该：

WHERE email = $1

参数由 driver 按协议绑定。

它既减少 SQL Injection 风险，也让 SQL 和数据边界更清晰。

1. JOIN 是关系组合，不是 ORM 魔法

INNER JOIN → 只保留两边能匹配的组合
LEFT JOIN  → 保留左侧，即使右侧没有匹配

ON 定义匹配关系，WHERE 在逻辑上对 join 后结果继续过滤。对 LEFT JOIN 来说，把右表条件从 ON 移到 WHERE 可能把无匹配行过滤掉。

2. Aggregation

典型顺序心智模型：

FROM / JOIN
→ ON
→ WHERE
→ GROUP BY
→ Aggregate
→ HAVING
→ SELECT / ORDER BY / LIMIT

COUNT(*) 统计行；COUNT(column) 忽略 NULL。理解这个差异能避免 LEFT JOIN 统计误差。

3. CTE 与 Window Function

CTE 可以给复杂查询分阶段命名；Window Function 保留明细行的同时计算分组排名、累计值、前后行关系，例如：

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
LAG(value) OVER (...)
COUNT(*) OVER (PARTITION BY ...)

这对 BodySense 的“每用户最新 revision / 时间趋势 / rollout observation”很有价值。

4. Read Projection

API 不需要把每个 table model 原样返回。可以设计 SQL projection：

Normalized write model
→ JOIN / aggregate / latest-row selection
→ API-specific read projection

但 projection 必须明确是否 durable、是否 cacheable、如何重新生成。

Prediction

[!question]

1. LEFT JOIN 后在 WHERE 写 child.status='active'，为什么可能让没有 child 的 parent 消失？
2. 想统计每个用户的会话数量，包括 0 个会话的用户，应该优先从哪张表开始？
3. “每用户取最新一条记录”除了子查询，还可以用什么 SQL 能力？
4. CTE 是否天然意味着更快？
5. SQL 返回完全正确，是否说明性能一定正确？

[!success]- Answer / 解析 1. 无匹配 child 时右侧列为 NULL，WHERE 条件不成立，于是该 parent 被继续过滤；条件放 ON 可以保留 LEFT JOIN 的空匹配。

2. 从 users 等 parent relation 出发 LEFT JOIN child。 从 child 出发无法自然产生“0 条”的父实体。

3. Window Function。 例如 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) 后筛 rn=1。

4. 不天然。 CTE 主要是语义组织工具；真实计划仍由 PostgreSQL planner 决定，应看 EXPLAIN。

5. 不说明。 逻辑结果正确和物理执行成本是两个维度。后面课程会用 EXPLAIN / BUFFERS 验证。

Lab

针对 BodySense conversations + messages 或 body_state_revisions 写三条纯 SQL：

1. 用户最近 20 个会话；
2. 每个会话的消息数，包含 0；
3. 每个用户最新 revision。

L4 Gate

能解释一条至少包含 LEFT JOIN + GROUP BY 或 Window 的查询，并预测把某个过滤条件从 ON 移到 WHERE 后结果如何变化，再用真实 SQL 验证。