数据科学中 SQL 的典型应用

在数据科学工作流里，SQL 承担着”把明细数据变成可决策指标”的职责：从业务日志中按维度聚合、计算比率与分位数、抽取 Top-N、 识别连续区间。它和 Python/Pandas 不是竞争关系，而是互补关系——SQL 负责在数据库侧完成第一层加工，Pandas/Spark 负责在 分析侧完成建模前的特征组装。本文以一张内容审核日志表 audit_log 为贯穿示例，系统整理数据科学中最常遇到的 SQL 技术点。

示例表结构如下：

字段 含义
biz_type 业务线，如广告、电商、生活服务
dt 日期，格式 YYYY-MM-DD
reviewer_id 审核员编号
decision 审核决策，如 reject / pass
appeal_result 申诉结果，如 win
model_version 模型版本号
handle_seconds 单条审核耗时（秒）

下面的示例查询以 Hive/Spark SQL 为主要方言，并标注 MySQL 8 等常见数据库的差异，代码可在大多数 SQL 引擎上直接调整运行123。

一、按维度分组：日粒度统计与基础聚合

数据科学的第一步通常是回答”总量是多少、按什么维度变化”。GROUP BY 把明细行折叠成维度组合，配合 COUNT、SUM、 AVG 等聚合函数得到汇总指标。

求每个业务线的每日审核量和拒绝率：

SELECT
    biz_type,
    dt,
    COUNT(*) AS audit_cnt,
    SUM(CASE WHEN decision = 'reject' THEN 1 ELSE 0 END) AS reject_cnt,
    SUM(CASE WHEN decision = 'reject' THEN 1 ELSE 0 END) / COUNT(*) AS reject_rate
FROM audit_log
GROUP BY biz_type, dt
ORDER BY biz_type, dt;

要点：

  • GROUP BY 后只能直接选择分组键与聚合结果，其余字段必须包进聚合函数；
  • ORDER BY 放在 GROUP BY 之后，先聚合后排序；
  • 需要过滤分组结果时用 HAVING，而不是 WHERE：WHERE 作用于原始行，HAVING 作用于分组后的聚合值。

二、条件聚合：把布尔事件变成业务指标

真实业务指标很少直接存在表里，多数由”事件是否发生”组合而来。CASE WHEN 配合 SUM 是最常用的条件计数写法：把满足条件的 行计为 1，否则为 0，再求和即为该事件的数量。

计算每个模型版本的”误杀率”，即模型判违规但人审驳回的比例：

SELECT
    model_version,
    SUM(CASE WHEN model_decision = 'reject' AND human_decision = 'pass' THEN 1 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN model_decision = 'reject' THEN 1 ELSE 0 END), 0) AS false_positive_rate
FROM audit_log
GROUP BY model_version;

这里有两个值得单独记住的技术点：

  • 条件聚合：SUM(CASE WHEN ... THEN 1 ELSE 0 END) 等价于按条件过滤后计数，可在一次分组中同时算多个口径；
  • 防除零：NULLIF(denominator, 0) 把分母为 0 的情况转成 NULL，避免”除数为 0”报错。处理比率类指标时应默认加上。

三、窗口函数：移动平均与时间序列

聚合函数会把多行压成一行，窗口函数则保留每一行，同时在其”窗口”内计算。语法核心是 OVER 子句：PARTITION BY 定义分组， ORDER BY 定义窗口内的排序，ROWS BETWEEN 定义滑动范围。

求过去每天审核量的 3 日移动平均：

SELECT
    dt,
    audit_cnt,
    AVG(audit_cnt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM (
    SELECT dt, COUNT(*) AS audit_cnt
    FROM audit_log
    GROUP BY dt
) t
ORDER BY dt;

移动平均在监控看板、趋势平滑、异常检测基线中非常常见，是时间序列分析在 SQL 侧的最小闭环2。

四、排名窗口函数：Top-N 与三者差异

“每个维度里排名前几”是数据科学的高频需求。三个排名函数的行为必须分清2：

函数 行为 示例
ROW_NUMBER() 逐行编号，不并列 1, 2, 3, 4
RANK() 并列跳号 1, 1, 3, 4
DENSE_RANK() 并列不跳号 1, 1, 2, 3

找出每个审核员”被申诉且申诉通过”数量最多的前 3 名：

SELECT reviewer_id, appeal_win_cnt, rk
FROM (
    SELECT
        reviewer_id,
        COUNT(*) AS appeal_win_cnt,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rk
    FROM audit_log
    WHERE appeal_result = 'win'
    GROUP BY reviewer_id
) t
WHERE rk <= 3;

这是一个典型的”先排名后过滤”模式：内层查询完成分组与排名，外层用 WHERE rk <= 3 截取 Top-N。若希望每个业务线内部各自取 前几名，只需在 PARTITION BY 中加入分组键。

五、连续区间问题：连续 N 天超过阈值

“某个业务线是否连续 3 天审核量超过 10000”属于经典的 gap-and-islands（间隙与孤岛）问题：从时间序列中识别出连续满足条件的 片段。两种常用解法如下。

解法一（推荐）：用 LAG 标记新片段起点

WITH daily AS (
    SELECT biz_type, dt, COUNT(*) AS audit_cnt
    FROM audit_log
    GROUP BY biz_type, dt
),
flagged AS (
    SELECT
        biz_type,
        dt,
        audit_cnt,
        CASE
            WHEN audit_cnt > 10000
                 AND LAG(audit_cnt) OVER (PARTITION BY biz_type ORDER BY dt) > 10000
            THEN 0 ELSE 1
        END AS new_segment
    FROM daily
),
segmented AS (
    SELECT
        biz_type,
        dt,
        audit_cnt,
        SUM(new_segment) OVER (PARTITION BY biz_type ORDER BY dt) AS segment_id
    FROM flagged
)
SELECT DISTINCT biz_type
FROM segmented
WHERE audit_cnt > 10000
GROUP BY biz_type, segment_id
HAVING COUNT(*) >= 3;

思路：LAG 取出前一天的审核量，如果当天超阈值且前一天也超阈值，说明仍在同一连续段内，否则开启新段；对段标记做累计求和得到 segment_id，最后统计每段长度是否达到 3。

解法二（技巧型）：行号差法

SELECT DISTINCT biz_type
FROM (
    SELECT
        biz_type,
        dt,
        audit_cnt,
        ROW_NUMBER() OVER (PARTITION BY biz_type ORDER BY dt)
            - DENSE_RANK() OVER (PARTITION BY biz_type, CASE WHEN audit_cnt > 10000 THEN 1 ELSE 0 END ORDER BY dt)
            AS grp
    FROM (
        SELECT biz_type, dt, COUNT(*) AS audit_cnt
        FROM audit_log
        GROUP BY biz_type, dt
    ) a
) b
WHERE audit_cnt > 10000
GROUP BY biz_type, grp
HAVING COUNT(*) >= 3;

当行号与状态组内的 DENSE_RANK 同步递增时差值保持常数，状态翻转时差值跳变，从而把连续段编码为同一个 grp。两种方法等 价，理解其一即可；实际工作中 LAG 版本更易读、更好维护。

六、分布与分位数：P95 审核时长

平均值容易被长尾拉偏，审核时长、响应延迟这类指标更适合看分位数。不同数据库的写法差异明显123：

-- Hive / Spark SQL
SELECT percentile_approx(handle_seconds, 0.95) AS p95_seconds FROM audit_log;

-- 部分方言支持的标准写法
SELECT APPROX_PERCENTILE(handle_seconds, 0.95) AS p95_seconds FROM audit_log;

MySQL 8 没有近似分位数函数，可用 PERCENT_RANK() 或 NTILE 做近似：

SELECT
    handle_seconds,
    PERCENT_RANK() OVER (ORDER BY handle_seconds) AS pct
FROM audit_log
QUALIFY PERCENT_RANK() OVER (ORDER BY handle_seconds) >= 0.95;  -- 仅部分引擎支持 QUALIFY

在 MySQL 中改用子查询套 HAVING 或外层过滤即可。记住结论：分布问题先想分位数，再看均值，且近似算法（如 percentile_approx）在大数据量下是刻意为之，误差可控。

七、SQL 与 Pandas 的等价转换

同一个分析既可以用 SQL 做，也可以用 Pandas 做，理解两者对应关系能显著提升分析效率4。

SQL Pandas
SELECT biz_type, dt, decision FROM ... df[["biz_type", "dt", "decision"]]
WHERE decision = 'reject' df[df["decision"] == "reject"]
GROUP BY biz_type, dt df.groupby(["biz_type", "dt"])
COUNT(*) / AVG(x) .agg(audit_cnt=("x", "count"), m=("x", "mean"))
ORDER BY dt .sort_values("dt")

用 Pandas 复现每日拒绝率：

import pandas as pd

df = pd.read_sql("SELECT biz_type, dt, decision FROM audit_log", conn)
df["is_reject"] = (df["decision"] == "reject").astype(int)
out = df.groupby(["biz_type", "dt"]).agg(
    audit_cnt=("is_reject", "count"),
    reject_rate=("is_reject", "mean"),
).reset_index()

当数据量达到百万级、单机内存装不下时，用 pd.read_csv(..., chunksize=200000) 分块读取、逐块聚合再合并；这类场景更合适的 做法是直接把聚合下推到 SQL 引擎，只把汇总结果拉回本地——这也是”能下推就下推”的工程原则。

八、写 SQL 的工程习惯

  • 先确认口径，再写查询：字段含义、时间范围、去重键、是否包含异常记录，都会改变结果；
  • 用 CTE 组织多步逻辑：WITH daily AS (...), flagged AS (...) 让长查询可读、可调试，也能被优化器复用；
  • 注意方言差异：窗口函数、NULLIF、percentile_approx、QUALIFY 在不同数据库中的支持程度不同，上线前在目标引擎实测；
  • 善用分区裁剪与索引：查询加上 dt 等分区条件，可避免全表扫描，是大型日志表性能的关键；
  • 除零与空值显式处理：比率计算默认套 NULLIF，聚合前想清楚空值的业务含义。

九、知识点地图

把本文内容压缩成一张速查清单：

  • 分组聚合：GROUP BY + COUNT/SUM/AVG + HAVING；
  • 条件聚合：SUM(CASE WHEN ... THEN 1 ELSE 0 END)；
  • 防除零：NULLIF(x, 0)；
  • 窗口框架：OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)；
  • 排名三函数：ROW_NUMBER、RANK、DENSE_RANK 的并列与跳号差异；
  • Top-N：内层排名、外层 WHERE rk <= N；
  • 连续区间：LAG 标记段起点或行号差法；
  • 分位数：percentile_approx / APPROX_PERCENTILE / PERCENT_RANK / NTILE；
  • 语言对应：SQL 聚合 ↔ Pandas groupby().agg()；
  • 工程习惯：CTE、分区裁剪、方言验证、口径先行。

掌握以上十项，就覆盖了数据科学日常分析中最常出现的 SQL 场景：从”每天处理了多少”到”哪些业务线连续异常”，从”哪个模型误杀 最多”到”审核耗时的 P95 是多少”。SQL 的价值不在于语法本身，而在于把业务问题翻译成可重复、可审计、可下推的查询。

参考文献

  1. ISO/IEC 9075:2023, Information technology — Database languages — SQL. ↩ ↩2

  2. MySQL 8.0 Reference Manual, “Window Functions” 与 “Aggregate Functions” 章节. ↩ ↩2 ↩3 ↩4

  3. Apache Hive LanguageManual 与 Spark SQL Guide 中的窗口函数与近似聚合函数说明. ↩ ↩2

  4. pandas documentation, “Group By: split-apply-combine” 与 “IO tools (text, CSV, SQL)”. ↩