基于master_id提取90天交互数据及特殊场景的技术问询

一、第一个需求的代码问题及修正 #

你的现有代码存在两个关键问题：

1. 时间单位偏差：DATEADD(MM, -3, ...)是取「3个月前」，但需求是「回溯90天」，3个月和90天并不完全等价（比如2月、31天的月份会导致天数偏差），应该以天为单位计算时间范围。
2. 子查询过滤条件易丢数据：WHERE interaction_time>='2023-01-01'会直接排除该日期之前的记录，但部分master_id的最新交互时间在2023-01-01之后，其90天范围内的记录可能包含早于2023-01-01的数据，这部分会被错误过滤。

修正后的SQL代码 #

关联子查询写法

SELECT s.*
FROM clean_dd s
JOIN (
    SELECT master_id, MAX(interaction_time) AS latest_time
    FROM clean_dd
    GROUP BY master_id
) m ON s.master_id = m.master_id
WHERE s.interaction_time >= DATEADD(day, -90, m.latest_time)

窗口函数写法（避免表关联）

SELECT *
FROM (
    SELECT 
        *,
        MAX(interaction_time) OVER (PARTITION BY master_id) AS latest_time,
        DATEADD(day, -90, MAX(interaction_time) OVER (PARTITION BY master_id)) AS ninety_days_before_latest
    FROM clean_dd
) t
WHERE interaction_time >= ninety_days_before_latest

二、提取交互数据不足90天的客户数据 #

要提取这类客户，需先判断每个master_id的最早交互时间与最新交互时间的间隔是否小于90天，再取出这些客户的所有交互记录：

SELECT s.*
FROM clean_dd s
JOIN (
    SELECT 
        master_id,
        MAX(interaction_time) AS latest_time,
        MIN(interaction_time) AS earliest_time
    FROM clean_dd
    GROUP BY master_id
    WHERE DATEDIFF(day, MIN(interaction_time), MAX(interaction_time)) < 90
) m ON s.master_id = m.master_id

该逻辑覆盖“客户所有交互历史总时长不到90天”的场景，若需同时满足「所有记录在最新时间90天内且整体跨度不足90天」，可结合第一个需求的过滤条件进一步调整。

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