如何通过SQL基于同一ID的上一条记录日期差生成状态列？

你需要用**窗口函数LAG()**来匹配同一id分区内的上一条记录日期，再通过日期差判断状态——这正好能补上你现有分区排名逻辑的缺口。

核心SQL代码 #

SELECT
    id,
    date_,
    -- 提取同一id下按日期排序的上一条记录日期
    LAG(date_) OVER (PARTITION BY id ORDER BY date_) AS prev_date,
    -- 根据日期差标记状态，无前置记录时不生成标记
    CASE
        -- 计算日期差（这里以天数为例，不同SQL方言函数有差异，见下方说明）
        WHEN DATEDIFF(date_, prev_date) < 30 THEN 'Active'
        WHEN DATEDIFF(date_, prev_date) BETWEEN 30 AND 60 THEN 'Inactive'
        WHEN DATEDIFF(date_, prev_date) > 60 THEN 'Dormant'
        -- 无前置记录（比如id仅单条数据）时返回空值
        ELSE NULL
    END AS Activity
FROM schema.table
ORDER BY id, date_;

不同SQL方言的日期差函数适配 #

• MySQL/MariaDB：用DATEDIFF(date_, prev_date)返回天数差；按月差判断用TIMESTAMPDIFF(MONTH, prev_date, date_)
• PostgreSQL：天数差用DATE_PART('day', date_ - prev_date)，月差用DATE_PART('month', date_ - prev_date)
• BigQuery：天数差用DATE_DIFF(date_, prev_date, DAY)，月差用DATE_DIFF(date_, prev_date, MONTH)
• SQL Server：天数差用DATEDIFF(day, prev_date, date_)

针对「最后一条记录无需标记」的调整 #

如果这里的「最后一条记录」指的是每个id下的最新记录（按日期排序的最后一条），可以通过增加排名字段来过滤：

WITH ranked_data AS (
    SELECT
        id,
        date_,
        LAG(date_) OVER (PARTITION BY id ORDER BY date_) AS prev_date,
        -- 标记每个id下的最新记录为rn=1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_ DESC) AS rn
    FROM schema.table
)
SELECT
    id,
    date_,
    CASE
        -- 跳过每个id的最新记录
        WHEN rn = 1 THEN NULL
        WHEN DATEDIFF(date_, prev_date) < 30 THEN 'Active'
        WHEN DATEDIFF(date_, prev_date) BETWEEN 30 AND 60 THEN 'Inactive'
        WHEN DATEDIFF(date_, prev_date) > 60 THEN 'Dormant'
        ELSE NULL
    END AS Activity
FROM ranked_data
ORDER BY id, date_;

关键说明 #

• 必须按id分区、按date_排序，否则LAG()无法正确匹配同id的上一条记录
• 当某id只有单条数据时，prev_date会返回null，对应Activity字段为空，符合「无前置记录无需标记」的要求

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