查询近7天活跃客户缺失的每日交易记录（含后续状态）

现有一张包含唯一client_id和record_timestamp字段的表，活跃客户每日应存在一条交易记录。但数据源文件偶尔会遗漏1-2位客户1-2天的交易记录，之后这些客户的记录可能恢复或不再出现在文件中。需要每周一执行查询，找出近7天内存在交易记录缺失、且后续记录恢复或未恢复的客户ID，并展示完整的日期序列（缺失日期显示NULL）。

示例数据 #

期望结果 #

尝试过的SQL代码（存在语法与逻辑问题） #

SELECT
    client_id,
    received_date,
    CASE WHEN previous_date IS NULL THEN NULL
    ELSE previous_date END AS Missing_Record
FROM
    (
        SELECT
            client_id,
            CAST (record_timestamp AS DATE) AS received_date,
            LEAD (CAST (record_timestamp AS DATE), 1) OVER (ORDER BY CAST (record_timestamp AS DATE) DESC) AS previous_date
        FROM Client_Table
        WHERE CONVERT (VARCHAR (10),record_timestamp, 1) >= DATEADD (DAY,- 7,CONVERT (DATE,GETDATE ())) 
            AND CONVERT (VARCHAR (10),record_timestamp,1) < DATEADD (DAY,DATEDIFF (DAY,0,GETDATE ()),0)
    --btw last 7 days and yesterday
    )
    X
WHERE previous_date IS NULL
GROUP BY
   client_id,
    received_date,
    previous_date

解决方案 #

要实现需求，核心是生成近7天的完整日期序列，再与每个客户的记录做左连接，从而找出缺失的日期。以下是适用于SQL Server的实现代码：

-- 生成近7天的日期范围（从7天前到昨天）
WITH DateRange AS (
    SELECT DATEADD(DAY, n, DATEADD(DAY, -7, CAST(GETDATE() AS DATE))) AS date_val
    FROM (
        SELECT TOP 7 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) AS Numbers
),
-- 获取每个客户在近7天内的交易记录（去重日期，保留最新的时间戳）
ClientDailyRecords AS (
    SELECT 
        client_id,
        CAST(record_timestamp AS DATE) AS record_date,
        MAX(record_timestamp) AS record_timestamp
    FROM Client_Table
    WHERE CAST(record_timestamp AS DATE) BETWEEN DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) 
                                          AND DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
    GROUP BY client_id, CAST(record_timestamp AS DATE)
),
-- 找出存在缺失记录的客户
ClientsWithMissing AS (
    SELECT DISTINCT client_id
    FROM ClientDailyRecords c
    RIGHT JOIN DateRange d ON c.record_date = d.date_val
    WHERE c.client_id IS NOT NULL
    GROUP BY client_id
    HAVING COUNT(c.record_date) < 7
)
-- 生成最终结果：每个客户的完整日期序列，缺失显示NULL
SELECT 
    cwm.client_id,
    cdr.record_timestamp
FROM ClientsWithMissing cwm
CROSS JOIN DateRange dr
LEFT JOIN ClientDailyRecords cdr 
    ON cwm.client_id = cdr.client_id 
    AND dr.date_val = cdr.record_date
ORDER BY cwm.client_id, dr.date_val;

代码说明 #

1. DateRange：生成近7天的连续日期（从7天前到昨天），确保覆盖需要检查的所有日期。
2. ClientDailyRecords：对每个客户的每日交易记录去重，保留当天最新的时间戳（因为可能存在多条记录，但需求只需要每日一条）。
3. ClientsWithMissing：通过右连接日期范围和客户记录，筛选出近7天内记录数不足7条的客户（即存在缺失）。
4. 最后通过交叉连接每个目标客户与日期范围，再左连接交易记录，得到完整的日期序列，缺失日期对应的record_timestamp显示为NULL。

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