FDE 短课 FDE-14

FDE-14

SQL 实战:JOIN、窗口函数、在客户数据里查问题

  • 入门到进阶
  • 集成
  • Day 14

场景:「为什么沙田那几座总是慢?」

物业报修项目继续(第 7 到 13 课)。历史工单已经导进系统(第 13 课)。周一早会,物业总监看着看板问:「沙田那几座大厦,报修好像总要很久才有人上门。是承办商的问题,还是我们派单的问题?」

没有现成报表。你手上有一个只读数据库账号和三张表:

  • tickets:工单(ticket_id, building_id, category, reported_at, status)
  • assignments:派单记录(ticket_id, contractor_id, assigned_at, arrived_at),一张工单可能被改派多次
  • buildings:大厦(building_id, name, district)

这一课讲清楚:用 SQL 把一句业务疑问变成可核对的数字。现场和面试都常见这种情况:给你几张表,十几分钟内查出答案,并讲清楚口径。

概念

先定口径,再写 SQL

「慢」要先下定义。先和总监确认一句:慢 = 从报修到第一次上门的时间(arrived_at − reported_at),只算已上门的工单,看最近 90 天。口径没定好,SQL 写得再漂亮,结果也会被质疑。

JOIN:最常见的错不在语法,在行数

写法结果常见坑
INNER JOIN两边都有才保留还没派单的工单悄悄消失,平均时长偏好看
LEFT JOIN左表全保留,右边没有就是 NULL在 WHERE 里过滤右表字段,等于变回 INNER JOIN;这类条件要放进 ON
一对多 JOIN左表一行变多行一张工单改派 3 次就被算 3 次,COUNT 和 AVG 全被放大

好习惯:JOIN 前后各数一次 COUNT(*) 和 COUNT(DISTINCT ticket_id),对不上就先停下来查。

窗口函数:组内挑第一条、排名、比较

GROUP BY 会把多行压成一行;窗口函数保留每一行,同时算出这一行在组里的位置或组内统计。

  • ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY arrived_at):每张工单的第几次到场,取 1 就是首次
  • RANK() / DENSE_RANK():排名,两者在并列时的处理不同
  • AVG(x) OVER (PARTITION BY district):每行旁边放上所属区的平均值,方便对比
  • LAG():取上一行的值,例如同一单位上次报修是几天前,用来查重复报修

一条好读的查询

用 CTE(WITH)分步写,每一步都能单独跑、单独核对:

WITH first_visit AS (
  SELECT ticket_id, contractor_id, arrived_at,
         ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY arrived_at) AS rn
  FROM assignments
  WHERE arrived_at IS NOT NULL
),
t AS (
  SELECT tk.ticket_id, b.district, fv.contractor_id,
         EXTRACT(EPOCH FROM fv.arrived_at - tk.reported_at) / 3600.0 AS hours_to_arrive
  FROM tickets tk
  JOIN buildings b    ON b.building_id = tk.building_id
  JOIN first_visit fv ON fv.ticket_id = tk.ticket_id AND fv.rn = 1
  WHERE tk.reported_at >= NOW() - INTERVAL '90 days'
)
SELECT district, contractor_id,
       COUNT(*) AS tickets,
       ROUND(AVG(hours_to_arrive)::numeric, 1) AS avg_hours,
       ROUND((PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY hours_to_arrive))::numeric, 1) AS p90_hours
FROM t
GROUP BY district, contractor_id
ORDER BY p90_hours DESC;

这是 PostgreSQL 写法,其他数据库的时间差和百分位函数名称不同。除了平均值还看 P90:平均值会被大量快单拉低,少数拖了两三天的单只有在长尾里才看得见。

香港落地例子

你交给总监的是一页结果,加上口径说明:

区承办商工单数平均到场(小时)P90(小时)
沙田承办商甲12020.552.0
沙田承办商乙959.822.0
观塘承办商甲14011.226.0

(以上数字为课堂示例数据。)

读法:同一家承办商甲,在观塘不慢,在沙田慢,所以不像是「甲整体差」,更像沙田的派单或人手覆盖出了问题。页面上再补两条:

  • 口径:首次上门、最近 90 天、只含已上门的工单;至今未上门的工单另列数量,它们最需要跟进
  • 下一步建议:按 category 再拆(例如水喉和电力),看是不是某类工种在沙田缺人

安全与权限:用只读账号,最好查只读副本,不在生产主库跑大查询;业主姓名和电话不拉出来,聚合到区和承办商就够;查询文件放进仓库,别人可以重跑出同样的数字。

四类雇主的差异(对照即可):

雇主类型这件事上的侧重点
AI 原生 / 产品公司把常用查询做成看板,或让 LLM 生成 SQL(text-to-SQL);要有固定口径的语义层和只读权限
大厂云与平台数据多在 BigQuery、Redshift 等数仓,按扫描量计费,先限定分区和时间范围
咨询与系统集成口径定义写进交付文档请客户签字,避免月底对数时各说各话
甲方企业内部(如银行、保险)多数只能查脱敏后的数据集市;查询要审批或留审计记录,不能随手导出明细

面试怎么说

题目(公开面经里的常见问法类型):「给你订单表和派送表,查出每个区最慢的承办商」,或「查每个客户最近的一笔订单」。

答题思路:先复述口径(时间范围、算首次还是最近一次、没有记录的怎么处理);点出一对多 JOIN 会重复计数,用 ROW_NUMBER 取每组第一条;用 CTE 分步写;说明你怎么核对(JOIN 前后行数、抽几张单手算);最后讲结论怎么给业务看,而不是只交一条 SQL。

小练习

用 15 分钟,用 SQLite(Python 自带 sqlite3;SQLite 3.25 起支持窗口函数):

  1. 建 tickets 和 assignments 两张表:工单 T1 派了 3 次,T2 派了 1 次,T3 派了 1 次但还没到场(arrived_at 为 NULL)
  2. 直接 JOIN 两张表后 COUNT,看 T1 被算了几次
  3. 用 ROW_NUMBER() 只保留每张工单的首次到场,再数一次
  4. 用一句话写下:T3 该不该算进「平均到场时间」?应该放在报告哪里?

一句话带走

用 SQL 查问题,先定口径;JOIN 前后数行数;一对多用窗口函数取第一条;结果附上口径和没算进去的数据。

自测

  1. 同事写了 tickets LEFT JOIN assignments,然后加上 WHERE assignments.arrived_at >= '2026-08-01',说「用 LEFT JOIN,没派单的工单也算进去了」。哪里不对?怎么改?
  2. 你在一家保险公司的内部团队做 FDE,理赔主管问「哪些理赔员处理得最慢」。和物业例子相比,你在口径和数据使用上会多注意什么?

先用自己的话写两三句,再打开答案。

参考答案

第 1 题:WHERE 里对右表字段加条件,会把右表为 NULL 的行(没派单、没到场)过滤掉,LEFT JOIN 实际变成了 INNER JOIN。要保留没派单的工单,就把条件挪进 ON:LEFT JOIN assignments a ON a.ticket_id = t.ticket_id AND a.arrived_at >= '2026-08-01',或者先在子查询里过滤 assignments,再 LEFT JOIN。另外,一对多的问题还在:改派多次的工单会重复,要先用 ROW_NUMBER 取首次。还要说明一点:没到场的单本来就算不出到场时间,应该单独列出数量,而不是混进平均值。

第 2 题:口径上,先定「慢」从哪里算到哪里(报案、文件齐全还是审批完成),并扣除等客户补文件的时间,否则会怪错人;案件复杂度不同(医疗和车险、金额大小),要分类或分层比较,否则处理难案的人吃亏。数据上,理赔含个人资料和健康资料,按《个人资料(私隐)条例》(PDPO)只用达成目的所需的字段,查只读、脱敏的数据集,结果聚合到理赔员就够,不拉客户明细;把个人排名交给管理层前,先和主管对齐用途,避免这个数字被当成唯一的绩效指标。

系列:上一篇 · 数据格式与转换:JSON/CSV/XML、schema 校验、脏数据处理 · 下一篇 · ETL/数据管道:批 vs 流,增量同步 · 回到索引

在 GitHub 上查看源文