Marico's space

大多数 SQL 教程讲到 SELECT、WHERE 和 GROUP BY 就停了。真正做数据分析需要更多：给患者排名、比较相邻就诊记录、关联多个表、把复杂问题拆成几步来解。这篇文章聊聊四个让这些成为可能的工具：

1. 窗口函数：跨相关行做计算，但不折叠这些行。
2. JOIN：关联表。
3. 公用表表达式（CTE）：给中间结果起名字。
4. 子查询：嵌套在其他查询里的查询。

每个例子都基于同一个医院数据库（PostgreSQL 语法），每个展示的结果都是从实际数据跑出来的。

医院数据集

数据库在 morning schema 下，采用星型设计：一个事实表（visits 就诊记录）和多个维度表。

跑任何查询之前先执行：

set search_path to morning;

数据质量说明。处理数据时发现几个影响分组和关联的问题：诊断名如 Back Pian 和类别 Respiratoey Disease 有拼写错误；Infectious 和 Infectious Disease 是同一类别的两种写法；有个县名前面多了个空格（Kiambu）；诊断 513 到 518 原本缺失，后面用建议的名称补上了。实际工作中最好先清理这些问题，因为 GROUP BY 会把每种写法当成不同的组。

第一部分：窗口函数

什么是窗口函数？

GROUP BY 聚合会把多行折叠成一行。窗口函数则是跨一组相关行（这个"窗口"）做计算，但保留每条原始行。这样就能在同一个结果行里同时显示一次就诊以及它的排名、上次账单或累计总额。

OVER() 子句

每个窗口函数后面都跟着 OVER (...)，用于定义窗口：

function_name(...) OVER ( PARTITION BY column -- 把行分成若干组（可选） ORDER BY column -- 组内排序（可选） frame_clause -- 包含当前行前后的哪些行（可选）
)

• OVER () 里面什么都不写，表示窗口是整个结果集。
• PARTITION BY 为每个分组重启计算，类似于 GROUP BY 但不会折叠行。按 doctor_id 分区，每个医生会有自己的排名。
• ORDER BY 在 OVER 内部决定函数看到行的顺序。排名、LAG/LEAD 和累计计算都依赖它。它和查询末尾的 ORDER BY 是分开的——后者只影响最终输出的排序。
• 窗口框架 指定相对于当前行具体包含哪些行（下面会详细讲）。

1. ROW_NUMBER()

功能： 给分区内的每行分配一个唯一序号 1、2、3……，按 ORDER BY 排序。即使有并列的行也会得到不同的编号。

用途： 给就诊记录编号、选取每个患者最近一次就诊、去重。

医院场景： 给回来就诊超过一次的五位患者按日期顺序编号。

select visit_id, patient_id, visit_date, total_bill_kes, row_number() over (partition by patient_id order by visit_date) as visit_number
from visits
where patient_id in (1, 2, 5, 7, 10)
order by patient_id, visit_date;

效果： 按患者分组，每组内按日期排序并从 1 开始编号。

一个非常常见的模式：每个患者最近一次就诊。 窗口函数不能直接在 WHERE 里过滤，所以要包一层 CTE（第三部分会讲）：

with ranked as ( select visit_id, patient_id, visit_date, row_number() over (partition by patient_id order by visit_date desc, visit_id desc) as rn from visits
)
select visit_id, patient_id, visit_date
from ranked
where rn = 1;

这样返回每个患者恰好一行（共 25 行），就是每位患者最近的一次就诊。

2. RANK() 和 3. DENSE_RANK()（与 ROW_NUMBER 对比）

这三个函数都能排名，但处理并列的方式不同：

医院场景： 按住院天数从长到短给住院患者排名。因为有几位患者住院天数相同，会出现并列。

select visit_id, patient_id, length_of_stay_days as stay, row_number() over (order by length_of_stay_days desc, visit_id) as row_num, rank() over (order by length_of_stay_days desc) as rnk, dense_rank() over (order by length_of_stay_days desc) as dense_rnk
from visits
where admission_type = 'Inpatient'
order by row_num;

结果解读： 1003 和 1016 都住了 3 天，所以 RANK 给两者都排第 4，下一组从第 6 开始（因为 4 和 5 被占用了）。DENSE_RANK 给下一组排第 5，没有空号。ROW_NUMBER 就是简单从 1 数到 9。

怎么选： 需要每组恰好一行（去重）用 ROW_NUMBER；比赛式排名、空号有意义用 RANK；"前 N 档"类问题（如"住院天数最长的三个不同档位"）用 DENSE_RANK。

4. NTILE(n)

功能： 把排好序的行分成大约相等的 n 个桶，告诉每行落在哪个桶里。如果行数不能整除，前面的桶会多一行。

用途： 四分位数、百分位分段、把客户或患者分成等级。

医院场景： 把全部 30 次就诊分成四个账单分位，看看每个分位的账单范围。

with t as ( select visit_id, total_bill_kes, ntile(4) over (order by total_bill_kes) as quartile from visits
)
select quartile, count(*) as visits, min(total_bill_kes) as lowest, max(total_bill_kes) as highest
from t
group by quartile
order by quartile;

30 行没法平分成四组，所以前两个分位各 8 行，后两个各 7 行。最贵的那个分位（账单 28,000 及以上）聚集了医院大部分收入。

5. LAG() 和 6. LEAD()

功能： LAG(col) 返回窗口内上一行的 col 值；LEAD(col) 返回下一行的值。分区内第一行没有上一行，所以 LAG 返回 NULL；最后一行 LEAD 返回 NULL。可选的第二个参数设置偏移量，第三个设置默认值（LAG(col, 2, 0)）。

用途： 把一条记录和相邻记录做对比，比如对比上次就诊的变化、事件间隔、同比增长，不需要自连接。

医院场景： 对于复诊患者，显示账单相比上次的变化，以及下次就诊日期。

select patient_id, visit_id, visit_date, total_bill_kes, lag(total_bill_kes) over w as previous_bill, total_bill_kes - lag(total_bill_kes) over w as bill_change, lead(visit_date) over w as next_visit
from visits
where patient_id in (1, 2, 5, 7, 10)
window w as (partition by patient_id order by visit_date)
order by patient_id, visit_date;

WINDOW w AS (...) 子句给窗口定义起个名字，这样多个函数可以复用。

（患者 2 同理：2500 然后 3800，变化 1300。）患者 10 的账单降了 54,000：第一次是心衰住院 5 天，第二次是胸痛急诊只待了 1 天。每次就诊的账单变化讲出了一个简单的故事，而单纯的 SUM 会把这个信息藏起来。

7. FIRST_VALUE() 和 8. LAST_VALUE()

功能： 返回窗口框架的第一个或最后一个值。这里窗口框架就很重要了，先看下面的警告。

医院场景： 对于 Dr. Faith Chebet（医生 104），对比每次就诊的账单和她第一、最后一次的账单。

select visit_id, visit_date, total_bill_kes, first_value(total_bill_kes) over w as first_bill, last_value(total_bill_kes) over w as last_bill_default, last_value(total_bill_kes) over ( partition by doctor_id order by visit_date, visit_id rows between unbounded preceding and unbounded following ) as last_bill_correct
from visits
where doctor_id = 104
window w as (partition by doctor_id order by visit_date, visit_id)
order by visit_date, visit_id;

坑在这里： 当窗口有 ORDER BY 但没指定显式框架时，默认框架是从分区开头到当前行。所以 LAST_VALUE 用默认框架只会返回当前行自己的值（就是 last_bill_default 列），这几乎永远不是你想要的。把框架扩展到 UNBOUNDED FOLLOWING 才能看到真正的最后一行（last_bill_correct）。FIRST_VALUE 不受影响，因为框架起点始终是分区开头。

9. 聚合窗口函数：SUM、AVG、COUNT、MIN、MAX

熟悉的聚合函数加上 OVER 就变成窗口函数。没有 ORDER BY 时覆盖整个分区，每行都显示同样的值；加了 ORDER BY 就变成累计计算。

医院场景： 显示每个科室的总营收，同时保留每次就诊的明细。用 SELECT DISTINCT 来让每个科室只显示一行。

select distinct department_id, sum(total_bill_kes) over (partition by department_id) as dept_revenue, round(avg(total_bill_kes) over (partition by department_id), 0) as dept_avg_bill, count(*) over (partition by department_id) as visit_count, min(total_bill_kes) over (partition by department_id) as lowest_bill, max(total_bill_kes) over (partition by department_id) as highest_bill
from visits
order by department_id;

不加 DISTINCT 的话，30 行就诊记录每行都会带着自己科室的汇总数据在旁边，很适合用来问"这张账单和科室平均水平比起来怎么样？"这类问题。

累计总量和移动平均 需要 ORDER BY 配合框架。先按天汇总每天的收入，再做累计：

with daily as ( select visit_date, sum(total_bill_kes) as revenue from visits group by visit_date
)
select visit_date, revenue, sum(revenue) over (order by visit_date) as running_total, round(avg(revenue) over (order by visit_date rows between 2 preceding and current row), 0) as moving_avg_3day
from daily
order by visit_date;

15 行结果的前 5 行：

第二天移动平均是 (6000 + 70000) / 2，因为目前只有两行；从第三天开始才真正是当前天加前面两天的平均。

窗口框架深入：ROWS vs RANGE

框架子句的格式是：

{ ROWS | RANGE } BETWEEN <start> AND <end>
-- start/end 可以是：UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING

• ROWS 数的是物理行数。
• RANGE 按值来处理：所有 ORDER BY 值相同的行（同行）被当成一个整体。
• 默认值（当有 ORDER BY 时）是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

医院数据大部分日期有两条就诊记录，这正好能看出两者的区别：

select visit_id, visit_date, total_bill_kes, sum(total_bill_kes) over (order by visit_date) as range_default, sum(total_bill_kes) over (order by visit_date, visit_id rows between unbounded preceding and current row) as rows_running
from visits
where visit_date <= '2026-06-02'
order by visit_date, visit_id;

用 RANGE 时，6 月 1 日的两条就诊是同行，所以每行都包含另一条，显示 6000。用 ROWS 时，累计总量逐行增长。想要真正的逐行累计用 ROWS；想让相同值的行被一起对待用 RANGE。

第二部分：SQL JOIN

JOIN 用条件（通常是外键匹配主键）把两个表的行拼在一起。我们的数据库里 visits.patient_id 指向 patients.patient_id，visits.doctor_id 指向 doctors.doctor_id，以此类推。

INNER JOIN

适用场景： 只要两边都存在的记录，比如把就诊和对应的患者、医生详情关联起来。

场景： 列出每次住院就诊，附带患者姓名和负责医生。

select v.visit_id, p.patient_name, d.doctor_name, v.total_bill_kes
from visits v
inner join patients p on v.patient_id = p.patient_id
inner join doctors d on v.doctor_id = d.doctor_id
where v.admission_type = 'Inpatient'
order by v.visit_id;

9 次住院，现在每条都有可读的名字而不是一堆 ID。注意 JOIN 可以链式调用：这里两个 JOIN 关联了三张表。

LEFT JOIN

适用场景： 需要主表所有行，即使没有匹配也要保留："所有患者，以及他们如果有住院的话对应的住院记录。"

这个数据集里每个患者至少有一次就诊，所以从 patients 左连接到 visits 不会看到 NULL。为了真实演示效果，我们把住院条件放在 ON 子句里：

select count(*) as total_rows, count(v.visit_id) as matched
from patients p
left join visits v on p.patient_id = v.patient_id and v.admission_type = 'Inpatient';

25 个患者都在；其中 9 人有匹配的住院记录，另外 16 人在就诊列得到 NULL。（COUNT(v.visit_id) 忽略 NULL，所以只统计真正匹配的。）

反连接模式： 加上 WHERE v.visit_id IS NULL 就只保留没有匹配的患者，即从未住院的患者：

select p.patient_id, p.patient_name
from patients p
left join visits v on p.patient_id =