如何用Snowflake SQL按账户筛选与首行日期差≥30天的行？

需求说明 #

给定包含账户编号、行号和日期的表（示例数据如下），需为每个账户筛选两类行：

1. 该账户日期最早的首行
2. 该账户中第一个与首行日期间隔至少30天的行

示例数据：

Row	Account Number	Date
1	1001	2011-01-10
2	1001	2011-02-01
3	1001	2011-02-20
4	1001	2011-02-22
5	2001	2011-04-11
6	2001	2012-01-01
7	2001	2012-01-30
8	2001	2012-02-09

按需求筛选后，账户1001应选中行1和行3，账户2001应选中行5和行6。

实现方案 #

利用Snowflake窗口函数标记首行、计算日期间隔，再筛选目标行，具体SQL如下：

-- 假设表名为account_transactions，字段对应示例的Row、Account Number、Date
WITH account_data AS (
    SELECT
        *,
        -- 按账户分组、日期排序，标记首行（rn=1）
        ROW_NUMBER() OVER (PARTITION BY account_number ORDER BY transaction_date) AS rn,
        -- 获取每个账户的首行日期，用于计算间隔
        FIRST_VALUE(transaction_date) OVER (PARTITION BY account_number ORDER BY transaction_date) AS first_account_date
    FROM account_transactions
),
candidate_rows AS (
    SELECT
        *,
        -- 计算当前行与首行的日期间隔天数
        DATEDIFF(day, first_account_date, transaction_date) AS days_since_first,
        -- 给每个账户的候选行（首行+间隔≥30天的行）重新排序
        ROW_NUMBER() OVER (PARTITION BY account_number ORDER BY transaction_date) AS candidate_rn
    FROM account_data
    -- 先筛选出首行，以及所有间隔≥30天的行
    WHERE rn = 1 OR DATEDIFF(day, first_account_date, transaction_date) >= 30
)
-- 最终选取首行，以及首个间隔≥30天的行
SELECT row_num, account_number, transaction_date
FROM candidate_rows
WHERE rn = 1 OR (days_since_first >= 30 AND candidate_rn = 2);

代码解释 #

1. account_data CTE：

   • 用ROW_NUMBER()为每个账户的行按日期排序，标记首行（rn=1）
   • 用FIRST_VALUE()提取每个账户的首行日期，避免重复计算

2. candidate_rows CTE：

   • 筛选出首行和所有与首行间隔≥30天的行
   • 用DATEDIFF()计算日期间隔天数
   • 再次用ROW_NUMBER()给候选行按账户分组排序，此时首行是candidate_rn=1，首个符合间隔要求的行是candidate_rn=2

3. 最终查询：

   • 直接选取首行，以及候选行中排序为第2的行（即首个间隔≥30天的行）

验证结果 #

针对示例数据，执行上述SQL后将得到如下结果：

内容的提问来源于stack exchange，提问作者veg2020