SQL Window Functions Beyond Basics: Solving Real Business Problems

#引言

你们中的大多数人使用 SQL 窗口函数，但你们只是略知皮毛——在这里用一个 ROW_NUMBER()，在那里用一个 SUM() OVER()。窗口函数的真正潜力在于解决更复杂的问题时才得以展现。我将带领你们通过四个模式，展示窗口函数在实际应用中的强大功能。

这些例子都是真实的面试题，你可以在 [StrataScratch](https://www.stratascratch.com/) 上练习。

#累计总计

计算累计总计是窗口函数在商业中最常见的用途之一。财务人员特别喜欢它！它用于跟踪每月的累计收入，然后轻松地与年度收入目标进行比较。

使这成为一个窗口函数问题的原因是，通常你应该在同一个输出中包含每个周期的值和累积总数。你不能使用 GROUP BY 和 SUM()，因为那会将单独的行合并在一起。所以，显而易见的解决方案是使用窗口函数，比如 SUM() OVER()。

#### //示例：计算收入随时间的变化

这个亚马逊的问题原本要求你计算三个月的滚动平均值。然而，我们将忽略那部分，而是计算每个月的累计收入。

数据：这里是 amazon_purchases 表的预览。

| user_id | created_at | purchase_amt | | --- | --- | --- | | 10 | 2020-01-01 | 3742 | | 11 | 2020-01-04 | 1290 | | 12 | 2020-01-07 | 4249 | | ... | ... | ... | | 109 | 2020-10-24 | 1749 |

代码：内部查询将日期转换为 YYYY-MM 格式，使用 TO_CHAR() 并按月汇总收入，过滤掉购买金额大于零的返回。

外部查询在这些每月总计上应用窗口函数。我故意没有在 OVER() 中指定明确的框架子句，所以窗口函数默认使用 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着窗口函数包括所有在当前行之前的行，即月份。换句话说，累计求和是：所有之前的月份加上当前月份。不出所料，这正是累计求和的教科书定义。

SELECT t.month,
       t.monthly_revenue,
       SUM(t.monthly_revenue) OVER(ORDER BY t.month) AS cumulative_revenue
FROM (
    SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
        SUM(purchase_amt) AS monthly_revenue
    FROM amazon_purchases
    WHERE purchase_amt > 0
    GROUP BY TO_CHAR(created_at::date, 'YYYY-MM')
    ORDER BY TO_CHAR(created_at::date, 'YYYY-MM')
) t
ORDER BY t.month ASC;

输出：

| month | monthly_revenue | cumulative_revenue | | --- | --- | --- | | 2020-01 | 26292 | 26292 | | 2020-02 | 20695 | 46987 | | 2020-03 | 29620 | 76607 | | ... | ... | ... | | 2020-10 | 15310 | 239869 |

#间隙与岛屿（会话化）

这个模式也涉及顺序数据，就像累计总计一样，但使用不同的窗口函数。

岛屿是一系列具有相同条件的连续行，例如连续每天登录。间隙是岛屿之间的空间。

这种模式最常见的实际应用之一是会话化——将原始事件流分组为会话。会话通常定义为来自同一用户的事件序列，其中连续事件之间的间隙不超过某个超时时间（网络分析的标准是30分钟）。

会话化在产品和数据工程中广泛使用。无论何时你需要将原始事件流分组为有意义的活动单元，都会用到它。

在 SQL 中的经典检测方法分为两步：

• 使用 LAG() 或 LEAD() — 比较每一行与其前一行或后一行，并标记新序列的开始。

• 使用 SUM(flag) OVER (PARTITION BY user ORDER BY date) — 累积标志以生成序列ID，在序列内部保持不变，在边界处增量。

#### //示例：查找用户连续登录天数

来自 LinkedIn 和 Meta 面试的问题要求你找出截至2022年8月10日，平台访问连续天数最长的前三位用户。你应该输出所有具有前三大连续天数的用户，如果有多位用户具有相同的连续天数。

数据：表是 user_streaks。

| user_id | date_visited | | --- | --- | | u001 | 2022-08-01 | | u001 | 2022-08-01 | | u004 | 2022-08-01 | | ... | ... | | u005 | 2022-08-11 |

代码：查询较长，但通过CTE（Common Table Expressions）结构良好，易于理解。

窗口函数进阶：实战案例解析

用户活跃度分析

在用户活跃度分析中，我们经常需要计算用户的连续登录天数，并找出每个用户的最长连续登录 streak。此外，我们可能还需要找出所有用户中前三大最长的 streak。

#### 示例问题

假设我们有一个表 user_streaks，其中记录了用户的登录日期。我们需要找出每个用户的最长连续登录天数，并且列出所有用户中前三大最长的 streak。

#### 解决方案

为了解决这个问题，我们需要进行以下步骤：

1. 去除重复记录：确保每个用户的每天只有一条登录记录。
2. 标记新的 streak：使用 LAG() 函数来确定用户是否在连续登录。
3. 分配 streak ID：使用 SUM() 窗口函数来为每个 streak 分配一个唯一的 ID。
4. 计算 streak 长度：计算每个 streak 的持续天数。
5. 找出每个用户的最长 streak：对于每个用户，找出其最长的 streak。
6. 排名并选出前三大 streak：对所有用户的最长 streak 进行排名，并选出前三大 streak。

#### SQL 查询

WITH unique_visits AS (
    SELECT DISTINCT user_id, date_visited
    FROM   user_streaks
    WHERE  date_visited <= DATE '2022-08-10'
),
streak_flags AS (
    SELECT *,
           CASE
               WHEN date_visited - LAG(date_visited) OVER (PARTITION BY user_id ORDER BY date_visited) = 1
               THEN 0
               ELSE 1
           END AS new_streak
    FROM   unique_visits
),
streak_ids AS (
    SELECT *,
           SUM(new_streak) OVER (PARTITION BY user_id ORDER BY date_visited) AS streak_id
    FROM   streak_flags
),
streak_lengths AS (
    SELECT user_id,
           streak_id,
           COUNT(*) AS streak_length
    FROM   streak_ids
    GROUP  BY user_id, streak_id
),
longest_per_user AS (
    SELECT user_id,
           MAX(streak_length) AS streak_length
    FROM   streak_lengths
    GROUP  BY user_id
),
ranked_lengths AS (
    SELECT DISTINCT
           streak_length,
           DENSE_RANK() OVER (ORDER BY streak_length DESC) AS len_rank
    FROM   longest_per_user
),
top_lengths AS (
    SELECT streak_length
    FROM   ranked_lengths
    WHERE  len_rank <= 3
)
SELECT u.user_id,
       u.streak_length
FROM   longest_per_user u
JOIN   top_lengths       t USING (streak_length)
ORDER  BY u.streak_length DESC, u.user_id;

#### 输出结果

| user_id | streak_length | |---------|---------------| | u004 | 10 | | u005 | 10 | | u003 | 5 | | u001 | 4 | | u006 | 4 |

群组分析

群组分析是保留分析的基础，它回答了用户在初始事件后回来的频率。关键在于找到用户活动历史中的群组锚点，以便可以衡量所有后续活动。

在 SQL 中，群组分析主要通过三种窗口函数方法实现：

1. 最小时间戳：使用 MIN(event_time) OVER (PARTITION BY user_id) 来找到每个用户的第一个事件时间。
2. 第一个值：使用 FIRST_VALUE(attribute) OVER (PARTITION BY user_id ORDER BY event_time) 来获取用户第一个事件的特定属性，如第一个商户或第一个产品类别。
3. 行号排序：使用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) = 1 来隔离每个用户的第一个事件，然后将其与完整的历史记录进行连接。

#### 示例问题

假设我们有一个 DoorDash 的订单数据表，需要计算每个商户收到的订单数量以及来自首次下单用户的订单数量，并排除没有收到任何订单的商户。

#### 解决方案

1. 确定首次订单：使用 FIRST_VALUE() 窗口函数来标记每个用户的首次订单。
2. 连接首次订单信息：将首次订单信息与完整的订单历史记录进行连接。
3. 计数和聚合：计算每个商户的总订单数和来自首次下单用户的订单数。

#### SQL 查询

WITH first_orders AS (
    SELECT 
        customer_id,
        FIRST_VALUE(merchant_id) OVER (PARTITION BY customer_id ORDER BY order_timestamp) AS first_merchant
    FROM 
        order_details
),
merchant_orders AS (
    SELECT 
        o.merchant_id,
        COUNT(DISTINCT o.customer_id) AS total_customers,
        COUNT(DISTINCT CASE WHEN f.first_merchant = o.merchant_id THEN o.customer_id END) AS first_time_customers,
        COUNT(o.id) AS total_orders
    FROM 
        order_details o
    LEFT JOIN 
        first_orders f ON o.customer_id = f.customer_id
    GROUP BY 
        o.merchant_id
)
SELECT 
    m.name,
    mo.total_orders,
    mo.first_time_orders
FROM 
    merchant_orders mo
JOIN 
    merchant_details m ON mo.merchant_id = m.id
WHERE 
    mo.total_orders > 0
ORDER BY 
    mo.first_time_orders DESC;

#### 解释

1. first_orders CTE：使用 FIRST_VALUE() 函数来获取每个用户的首次订单的商户 ID。
2. merchant_orders CTE：连接 order_details 和 first_orders，计算每个商户的总订单数和来自首次下单用户的订单数。
3. 最终 SELECT：连接 merchant_details 表以获取商户名称，并按首次下单用户的订单数降序排列结果。

通过这些步骤，我们可以有效地进行群组分析，从而更好地理解用户行为和商户表现。

百分位数和排名分析

聚合函数告诉你平均值。基于窗口的排名函数告诉你分布情况，而分布是业务问题中有趣的部分所在。第90百分位的订单价值异常高，是否表明少数大买家正在扭曲收入？底部25%的销售代表是接近中位数还是远低于中位数？

NTILE(n)将行分为n个大致相等的桶。PERCENT_RANK()将每行的排名表示为0到1之间的值。CUME_DIST()告诉你有多少行的值小于或等于当前行的值。PERCENTILE_CONT()计算给定百分位阈值的实际值——当你希望根据动态截止值进行过滤而不是在结果集中排名时，这非常有用。

示例：识别顶级百分位欺诈

这里有一个来自Google和Netflix的例子。他们希望你识别每个州中最可疑的索赔。假设每个州的前5%的索赔可能是欺诈性的。

数据： 表名为fraud_score。

| policy_num | state | claim_cost | fraud_score | | --- | --- | --- | --- | | ABCD1001 | CA | 4113 | 0.61 | | ABCD1002 | CA | 3946 | 0.16 | | ABCD1003 | CA | 4335 | 0.01 | | ... | ... | ... | ... | | ABCD1400 | TX | 3922 | 0.59 |

代码： 在代码中，PERCENTILE_CONT(0.95)计算每个州欺诈分数的第95百分位的插值值。

在下面的SELECT语句中，CTE与原始表连接，以便每个索赔可以与其所在州的阈值进行比较。达到或超过该值的索赔被选中。

WITH state_percentiles AS (
    SELECT state,
           PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY fraud_score) AS p95
    FROM fraud_score
    GROUP BY state)
SELECT f.policy_num,
       f.state,
       f.claim_cost,
       f.fraud_score
FROM fraud_score f
JOIN state_percentiles sp
ON f.state = sp.state
WHERE f.fraud_score >= sp.p95;

输出：

| policy_num | state | claim_cost | fraud_score | | --- | --- | --- | --- | | ABCD1016 | CA | 1639 | 0.96 | | ABCD1021 | CA | 4898 | 0.95 | | ABCD1027 | CA | 2663 | 0.99 | | ... | ... | ... | ... | | ABCD1398 | TX | 3191 | 0.98 |

结论

这四种模式共享一个共同的理念：在数据库中完成工作，尽可能在一次通过中使用窗口规范的全部表达能力。

窗口函数真正强大的地方不在于任何单一函数本身，而在于它们的可组合性：你可以链接CTE，应用多个窗口函数在同一个SELECT中，并构建复杂的分析逻辑，几乎就像业务问题的描述本身。

Nate Rosidi是一位数据科学家和产品策略师，也是StrataScratch的创始人，该平台帮助数据科学家通过真实的面试问题为来自顶级公司的面试做准备。Nate还是一位兼职教授，教授数据分析，并在博客上撰写关于职业市场最新趋势、提供面试建议、分享数据科学项目以及涵盖所有SQL内容的文章。