KDnuggets

Practical SQL Tricks Every Data Scientist Should Know

8.5内容质量

TL;DR · AI 摘要

本文介绍了7个实用的SQL技巧，帮助数据科学家更高效地处理复杂分析任务。

核心要点

  • 使用LAG()和LEAD()函数可以计算事件之间的间隔，如交易间隔天数。
  • 窗口函数能有效处理时间序列数据，如平滑噪声数据。
  • 通过CTE和子查询可以实现复杂的客户分层和升级路径分析。

结构提纲

按章节快速跳转。

  1. §引言

    本文介绍了7个实用的SQL技巧，帮助数据科学家更高效地处理复杂分析任务。

  2. ·设置数据集

    使用一个虚构的SaaS公司客户交易表作为示例数据集。

  3. ›使用LAG()函数计算事件间隔

    通过LAG()函数计算客户连续交易之间的天数间隔。

  4. ·窗口函数处理时间序列数据

    窗口函数可用于平滑时间序列数据中的噪声。

  5. ›CTE和子查询实现客户分层

    通过CTE和子查询实现客户按消费层级的分组。

  6. ›追踪客户计划升级路径

    使用窗口函数和CTE追踪客户计划升级路径。

思维导图

用一张图看清主题之间的关系。

查看大纲文本（无障碍 / 无 JS 友好）
  • 实用SQL技巧
    • LAG()和LEAD()函数
      • 计算事件间隔
    • 窗口函数
      • 平滑时间序列数据
    • CTE和子查询
      • 客户分层
      • 追踪计划升级路径

金句 / Highlights

值得收藏与分享的关键句。

#SQL#数据科学#数据分析
打开原文

每位数据科学家都应该掌握的实用 SQL 技巧 - KDnuggets

publ: 2026年6月19日

  • 博客热门文章
  • 主题 人工智能 职业建议 计算机视觉 数据工程 数据科学 语言模型 机器学习 MLOps 自然语言处理 编程 Python SQL
  • 数据集
  • 活动
  • 资源 快速参考指南 推荐 技术简报
  • 广告

加入新闻简报

#header end

/ad_wrapper

每位数据科学家都应该掌握的实用 SQL 技巧

在本文中，我们将介绍一些关键的 SQL 模式和工作流程，使日常的数据分析更加清晰、更快、更容易扩展。

作者：

Bala Priya C

，KDnuggets 贡献编辑和技术内容专家，2026年6月19日发布于

SQL

<div class="addthis_native_toolbox"></div>

# 引言

仅仅关注 SELECT、WHERE 和 GROUP BY 就足以进行基本的聚合，但许多实际的分析任务需要超越简单查询的模式。例如，包括检测连续活动 streak、按消费等级对客户进行分段、平滑嘈杂的时间序列数据，或追踪跨行的计划升级路径。

本文将介绍7种实用的 SQL 模式，这些模式超越了基础内容，专注于解决实际分析问题的技术。

# 设置数据集

我们将使用一个虚构的订阅软件即服务（SaaS）公司的示例客户交易表：

code
CREATE TABLE transactions (
    transaction_id   SERIAL PRIMARY KEY,
    customer_id      INT,
    plan_type        VARCHAR(20),   -- 'starter', 'pro', 'enterprise'
    amount           NUMERIC(10,2),
    status           VARCHAR(20),   -- 'completed', 'refunded', 'failed'
    created_at       TIMESTAMP
);

从2023年9月到2024年6月，7位客户共36笔交易的完整数据集可在 seed.sql 中找到。在继续查询之前，请先运行它。

# 1. 使用 LAG() 计算事件之间的间隔时间

LAG() 和 LEAD() 允许你在不进行自连接的情况下访问前一行或后一行的值。它们特别适用于计算事件之间的间隔，如续订周期、流失信号和重新参与延迟。

任务：计算每位客户连续完成交易之间经过的天数。

code
SELECT
    customer_id,
    created_at,
    LAG(created_at) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
    ) AS previous_transaction_at,
    ROUND(
        EXTRACT(EPOCH FROM (
            created_at - LAG(created_at) OVER (
                PARTITION BY customer_id
                ORDER BY created_at
            )
        )) / 86400
    ) AS days_since_last
FROM transactions
WHERE status = 'completed'
ORDER BY customer_id, created_at;

输出（截取部分）：

code
customer_id |     created_at      | previous_transaction_at | days_since_last
-------------+---------------------+-------------------------+-----------------
        3317 | 2024-01-03 11:02:00 |                         |
        3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00     |              72
        3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00     |              68
        4482 | 2023-09-10 09:00:00 |                         |
        4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00     |              30
        4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00     |              31
        4482 | 2024-01-03 09:14:00 | 2023-11-10 09:14:00     |              54
        4482 | 2024-03-03 08:20:00 | 2024-01-03 09:14:00     |              60
        4482 | 2024-04-03 10:00:00 | 2024-03-03 08:20:00     |              31
        4482 | 2024-05-01 11:00:00 | 2024-04-03 10:00:00     |              28
        ...
        7891 | 2024-02-01 09:00:00 |                         |
        7891 | 2024-04-01 09:00:00 | 2024-02-01 09:00:00     |              60
        7891 | 2024-05-15 09:00:00 | 2024-04-01 09:00:00     |              44
        8810 | 2024-01-05 12:00:00 |                         |
        8810 | 2024-02-05 12:00:00 | 2024-01-05 12:00:00     |              31
        8810 | 2024-04-05 12:00:00 | 2024-02-05 12:00:00     |              60
(29 rows)

每个客户的第一行在两列中始终为 NULL —— 没有可以参考的先前事件。EXTRACT(EPOCH ...) 将时间戳间隔转换为秒；除以 86400 可以得到天数。

LEAD() 的工作方式相同，但它是向前查看而不是向后，这使其在计算下一次续订时间或标记流失前的最后一次交易时非常有用。

# 2. 使用自连接比较同一表中不同行

自连接将同一表中的行相互关联。当你需要比较同一实体在不同时间的两个事件 —— 升级、降级、重新激活或任何前后模式时，这是正确的工具。

任务：查找在任何时候从初级升级到高级（或从高级升级到企业）的客户。

code
SELECT DISTINCT t1.customer_id
FROM transactions t1
JOIN transactions t2
    ON  t1.customer_id = t2.customer_id
    AND t1.plan_type   = 'starter'
    AND t2.plan_type   = 'pro'
    AND t2.created_at  > t1.created_at
WHERE t1.status = 'completed'
  AND t2.status = 'completed'
ORDER BY t1.customer_id;

输出：

code
customer_id
-------------
        4482
        6204
        7891
(3 rows)

表被两次别名（t1, t2），这样每个别名可以代表同一客户在不同时间点的状态。条件 t2.created_at > t1.created_at 强制时间顺序 —— 没有它，你会匹配那些仅仅在任意顺序下拥有这两种计划类型的客户，包括错误的顺序。DISTINCT 会将客户在升级前有多个初级交易的情况合并，否则会产生重复行。

这种结构同样适用于检测降级、查找流失后又回来的客户，或比较任何需要按时间排序的两个状态。

# 3. 使用 ROW_NUMBER() 选择每组的顶部行

当你需要每类别的前 N 行 —— 每个客户的最大交易、每个账户的最新事件、每个群体的首次购买 —— 在公共表表达式（CTE）中使用 ROW_NUMBER() 是标准方法。

任务：获取每位客户单笔金额最高的已完成交易。

code
WITH ranked AS (
    SELECT
        customer_id,
        transaction_id,
        amount,
        plan_type,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC, created_at DESC
        ) AS rn
    FROM transactions
    WHERE status = 'completed'
)
SELECT customer_id, transaction_id, amount, plan_type
FROM ranked
WHERE rn = 1
ORDER BY customer_id;
code
customer_id  | transaction_id  | amount  | plan_type
-------------+----------------+--------+------------
        3317 |             12 |  19.00 | starter
        4482 |              8 | 299.00 | enterprise
        5901 |             19 | 299.00 | enterprise
        6103 |             25 | 299.00 | enterprise
        6204 |             28 |  79.00 | pro
        7891 |             32 |  79.00 | pro
        8810 |             36 |  79.00 | pro
(7 rows)

ROW_NUMBER() 会为每个分区中排序靠前的行分配数字 1。外部查询会筛选出这些行。按 created_at DESC 的次要排序作为平局的决出方式；当两个交易金额相同时，较新的交易会胜出。

如果你希望保留平局而不是决出胜者，可以将 ROW_NUMBER() 替换为 RANK()。RANK() 会为平局的行分配相同的数字，并跳过下一个排名（1, 1, 3），而 DENSE_RANK() 则不会跳过（1, 1, 2）。

# 4. 使用 NTILE(n) 按消费金额对客户进行分组

NTILE(n) 将排序后的行分成 n 个大致相等的组，并为每行分配一个组号。它是进行客户分级、消费四分位数或构建 A/B 分析的客户群体的正确工具，无需硬编码阈值。

任务：根据客户已完成交易的总金额，将客户分为消费四分位数。

code
WITH customer_spend AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spend,
        COUNT(*) AS total_transactions
    FROM transactions
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spend,
    total_transactions,
    NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY total_spend DESC;
code
customer_id | total_spend | total_transactions | spend_quartile
-------------+-------------+--------------------+----------------
        5901 |     1495.00 |                  5 |              4
        6103 |      835.00 |                  5 |              3
        4482 |      653.00 |                  7 |              3
        8810 |      237.00 |                  3 |              2
        6204 |      177.00 |                  3 |              2
        7891 |      177.00 |                  3 |              1
        3317 |       57.00 |                  3 |              1
(7 rows)

第 4 四分位数是消费最高的客户；第 1 四分位数是消费最低的客户。NTILE() 不会硬编码消费阈值，因此当新客户加入时，分组会自动重新调整。这使其比静态的截止值（如 CASE WHEN total_spend > 500）更稳健。

# 5. 使用滚动窗口平滑噪声数据

滚动（或移动）平均值可以平滑月与月之间的波动，使时间序列数据的趋势更容易阅读。使用显式 ROWS BETWEEN 框架的窗口函数可以精确控制要包含的周期数。

任务：计算 3 个月的月收入滚动平均值以平滑噪声。

code
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at)::DATE AS month,
        SUM(amount) AS monthly_revenue
    FROM transactions
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    monthly_revenue,
    ROUND(AVG(monthly_revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS revenue_3mo_avg
FROM monthly
ORDER BY month;
code
month    | monthly_revenue | revenue_3mo_avg
-------------+-----------------+-----------------
 2023-09-01  |           19.00 |           19.00
 2023-10-01  |           19.00 |           19.00
 2023-11-01  |           79.00 |           39.00
 2024-01-01  |          275.00 |          124.33
 2024-02-01  |          476.00 |          276.67
 2024-03-01  |          555.00 |          435.33
 2024-04-01  |          835.00 |          622.00
 2024-05-01  |          775.00 |          721.67
 2024-06-01  |          598.00 |          736.00
(9 rows)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 告诉窗口函数查看当前行和前两行。由于前两行没有历史数据，因此它们分别使用较少的输入，作为1个月和2个月的平均值。

如果你想包括所有具有相同 ORDER BY 值的行（当多行共享一个时间戳时这很有用），可以将 ROWS 替换为 RANGE。如需更长的平滑效果，可以将 2 PRECEDING 改为 5 PRECEDING，以创建一个6个月的窗口。

# 6. 使用 FILTER 进行有条件聚合

FILTER 允许你对特定的聚合应用 WHERE 条件，而无需将查询拆分为多个子查询。结果是在一次数据遍历中实现多个条件聚合。

任务：按月份获取总收入、退款金额和失败交易数量——每个月一行。

code
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed,
    SUM(amount) FILTER (WHERE status = 'refunded')  AS revenue_refunded,
    COUNT(*)    FILTER (WHERE status = 'failed')    AS failed_count
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
code
month             | revenue_completed | revenue_refunded | failed_count
------------------------+-------------------+------------------+--------------
 2023-09-01 00:00:00+00 |             19.00 |                  |            0
 2023-10-01 00:00:00+00 |             19.00 |                  |            0
 2023-11-01 00:00:00+00 |             79.00 |                  |            0
 2024-01-01 00:00:00+00 |            275.00 |                  |            0
 2024-02-01 00:00:00+00 |            476.00 |            79.00 |            1
 2024-03-01 00:00:00+00 |            555.00 |            79.00 |            0
 2024-04-01 00:00:00+00 |            835.00 |           299.00 |            0
 2024-05-01 00:00:00+00 |            775.00 |                  |            1
 2024-06-01 00:00:00+00 |            598.00 |                  |            2
(9 rows)

FILTER 的替代方法是使用三个独立的子查询并进行连接——代码更多，更难阅读，通常也更慢。请注意，当某个月份没有匹配行时，使用 FILTER 的 SUM 会返回 NULL（而不是零），这是准确的：那些月份确实没有退款。如果你更喜欢零，可以使用 COALESCE(..., 0) 包裹。

FILTER 是标准 SQL，并且在 PostgreSQL 和 BigQuery 中有效。在 Snowflake 和一些其他数据库中，应使用 SUM(CASE WHEN status = 'completed' THEN amount END) 代替。

# 7. 使用窗口函数检测连续活动 streak

查找无间断的序列 —— 没有间隔的活跃月份、有交易的连续天数、订阅 streak —— 是 SQL 中较为棘手的问题之一。经典的解决方案使用窗口函数将行分组为 streak，而无需使用递归 CTE。

技巧：为每个客户的活跃月份分配一个连续的行号。如果月份确实是连续的，从月份日期中减去该行号，会为 streak 中的每个月份生成相同的常量值。间隔会打破该常量。

任务：找出每个客户的连续活跃月份（至少有一次完成交易的月份）。

code
WITH monthly_activity AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at)::DATE AS active_month
    FROM transactions
    WHERE status = 'completed'
    GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
with_prev AS (
    SELECT
        customer_id,
        active_month,
        LAG(active_month) OVER (
            PARTITION BY customer_id
            ORDER BY active_month
        ) AS prev_month
    FROM monthly_activity
),
streak_groups AS (
    SELECT
        customer_id,
        active_month,
        SUM(CASE WHEN active_month = prev_month + INTERVAL '1 month' THEN 0 ELSE 1 END)
            OVER (PARTITION BY customer_id ORDER BY active_month) AS streak_id
    FROM with_prev
),
streaks AS (
    SELECT
        customer_id,
        streak_id,
        MIN(active_month) AS streak_start,
        MAX(active_month) AS streak_end,
        COUNT(*) AS streak_length_months
    FROM streak_groups
    GROUP BY customer_id, streak_id
)
SELECT customer_id, streak_start, streak_end, streak_length_months
FROM streaks
ORDER BY customer_id, streak_start;
code
customer_id | streak_start | streak_end | streak_length_months
-------------+--------------+------------+----------------------
        3317 | 2024-01-01   | 2024-01-01 |                    1
        3317 | 2024-03-01   | 2024-03-01 |                    1
        3317 | 2024-05-01   | 2024-05-01 |                    1
        4482 | 2023-09-01   | 2023-11-01 |                    3
        4482 | 2024-01-01   | 2024-01-01 |                    1
        4482 | 2024-03-01   | 2024-05-01 |                    3
        5901 | 2024-02-01   | 2024-06-01 |                    5
        6103 | 2024-01-01   | 2024-04-01 |                    4
        6103 | 2024-06-01   | 2024-06-01 |                    1
        6204 | 2024-01-01   | 2024-01-01 |                    1
        6204 | 2024-03-01   | 2024-03-01 |                    1
        6204 | 2024-05-01   | 2024-05-01 |                    1
        7891 | 2024-02-01   | 2024-02-01 |                    1
        7891 | 2024-04-01   | 2024-05-01 |                    2
        8810 | 2024-01-01   | 2024-02-01 |                    2
        8810 | 2024-04-01   | 2024-04-01 |                    1
(16 rows)

# 快速参考

这些模式在标准 SQL 中有效，无需依赖特定数据库的功能，并且在诸如留存分析、升级漏斗跟踪和收入报告等分析流程中频繁出现。

提示

使用场景

code
LAG()

/

code
LEAD()

事件之间的时间间隔，每个实体的前后对比

自连接

检测状态之间的转换（升级、重新激活）

code
ROW_NUMBER()

每组的前N行，去重

code
NTILE(n)

根据消费/活动水平对客户进行分层

滚动窗口 (

code
ROWS BETWEEN

)

平滑噪声时间序列，移动平均

code
FILTER

在一个查询中进行多个条件聚合

连续 streak 检测

订阅 streak，留存分析，会话间隔

一旦你熟悉了这些，许多通常在 Python 中处理的多步骤数据转换都可以更清晰、更高效地通过一个 SQL 查询来表达。

Bala Priya C 是来自印度的开发人员和技术作家。她喜欢在数学、编程、数据科学和内容创作的交汇点上工作。她的兴趣和专长领域包括 DevOps、数据科学和自然语言处理。她喜欢阅读、写作、编程和咖啡！目前，她正在通过撰写教程、操作指南、观点文章等，学习并与开发人员社区分享她的知识。Bala 还创建了吸引人的资源概述和编程教程。

更多关于此主题的内容

  • 每个数据科学家都应该知道的工具：实用指南
  • 每个 AI 工程师都应该知道的工具：实用指南
  • 每个数据科学家都应该知道的 10 个必备 Pandas 函数
  • 每个数据科学家都应该知道的 10 个 Python 库
  • 每个数据科学家都应该知道的 10 个命令行工具
  • 每个数据科学家都应该知道的 5 个不太为人知的 Python 特性

<hr class="grey-line"><br> <div><h3>我们推荐的 5 个免费课程</h3><br> </div>

Mailchimp for WordPress v4.13.0 - https://wordpress.org/plugins/mailchimp-for-wp/

/ Mailchimp for WordPress 插件

你可以从这里开始编辑。

如果评论已关闭。

<= 上一篇

下一篇 =>

#content end

<script type="text/javascript">kda_sid_write(kda_sid_n);</script>

最新文章

  • 为新手解释损失函数（模型如何知道它们是错误的）实用 SQL 技巧每个数据科学家都应该知道 Python 字典技巧和窍门你应该始终记住高级连接技术：LATERAL 连接、半连接、反连接如何（以及为什么）我构建了一个 AI 助手使用 OpenAI Codex 的 5 个有趣项目

热门文章

  • 将 Claude Code 与本地模型配对
  • 为你的创业点子获得资金的 7 个最佳方式
  • 2026 年成为 LLM 工程师的路线图
  • 廉价本地代理编程：Claude Code + Ollama + Gemma4
  • Anthropic 的 Claude 技能构建完整指南
  • 如何（以及为什么）我构建了一个 AI 助手
  • 使用 Python 中的 sktime 构建时间序列机器学习模型
  • 使用 OpenAI Codex 的 5 个有趣项目
  • 停止在 Pandas 中编写循环：尝试 7 个更快的替代方法
  • 5 个有用的 Python 脚本自动化无聊的 PDF 任务

#content_wrapper end

© 2026

Guiding Tech Media

|

关于

联系

广告

隐私

服务条款

2026 年 6 月 19 日由 bala-priya 发布

blank

不，谢谢！

/.main_wrapper

<script defer type="text/javascript" src="https://s7.addthis.com/js/300/addthis_widget.js#pubid=gpsaddthis"></script>

noptimize

/noptimize