如何用SQL在5分钟窗口内获取无重复的24小时前数据

要实现每日每5分钟执行、提取24小时前对应5分钟窗口且不重复抓取的SQL查询，需从时间窗口精确计算和重复抓取规避两个核心点入手，以下是具体实现方案：

1. 时间窗口精确计算 #

根据当前执行时间，计算24小时前对应的5分钟时间区间（比如当前执行时间是2024-05-20 14:07，目标区间则为2024-05-19 14:05 - 2024-05-19 14:10）。通过DATEADD和DATEDIFF可实现时间对齐到5分钟边界：

DECLARE @CurrentTime DATETIME = GETDATE();
-- 计算24小时前的时间，并对齐到最近的5分钟起始点
DECLARE @WindowStart DATETIME = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(HOUR, -24, @CurrentTime)) / 5 * 5, 0);
-- 计算窗口结束时间（起始时间+5分钟）
DECLARE @WindowEnd DATETIME = DATEADD(MINUTE, 5, @WindowStart);

2. 修改查询语句并规避重复抓取 #

假设Qn.Date字段为datetime类型（若为date类型需先修改为datetime，否则无法精确到分钟），结合原查询条件，调整后的完整查询如下：

方案一：无抓取日志表（依赖窗口不重叠） #

若没有记录抓取历史的表，只要保证任务每5分钟准时执行，且时间窗口严格不重叠，即可避免重复抓取：

DECLARE @CurrentTime DATETIME = GETDATE();
DECLARE @WindowStart DATETIME = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(HOUR, -24, @CurrentTime)) / 5 * 5, 0);
DECLARE @WindowEnd DATETIME = DATEADD(MINUTE, 5, @WindowStart);

SELECT
    Qn.Name, Qn.Email, Qn.Price 
FROM
    Quotation Qn
INNER JOIN 
    Product P ON P.ProductId = Qn.ProductId
WHERE
    Qn.ProductId = 10 
    AND Qn.Date >= @WindowStart 
    AND Qn.Date < @WindowEnd  -- 用<避免包含窗口结束点的记录
    AND Qn.Bname = 'CaraVan'

方案二：有抓取日志表（更可靠的去重方式） #

若需更严谨的去重（比如任务可能延迟执行），建议创建一张抓取日志表QuotationFetchLog，记录每次抓取的时间区间：

-- 仅需执行一次：创建日志表
CREATE TABLE QuotationFetchLog (
    FetchId INT IDENTITY(1,1) PRIMARY KEY,
    FetchStart DATETIME NOT NULL,
    FetchEnd DATETIME NOT NULL,
    FetchTime DATETIME DEFAULT GETDATE()
);

查询时排除已抓取过的记录，并在抓取完成后写入日志：

DECLARE @CurrentTime DATETIME = GETDATE();
DECLARE @WindowStart DATETIME = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(HOUR, -24, @CurrentTime)) / 5 * 5, 0);
DECLARE @WindowEnd DATETIME = DATEADD(MINUTE, 5, @WindowStart);

-- 查询未被抓取的目标区间数据
SELECT
    Qn.Name, Qn.Email, Qn.Price 
FROM
    Quotation Qn
INNER JOIN 
    Product P ON P.ProductId = Qn.ProductId
WHERE
    Qn.ProductId = 10 
    AND Qn.Date >= @WindowStart 
    AND Qn.Date < @WindowEnd 
    AND Qn.Bname = 'CaraVan'
    AND NOT EXISTS (
        -- 排除已被抓取过的记录
        SELECT 1 FROM QuotationFetchLog f
        WHERE Qn.Date >= f.FetchStart AND Qn.Date < f.FetchEnd
    );

-- 写入本次抓取日志
INSERT INTO QuotationFetchLog (FetchStart, FetchEnd)
VALUES (@WindowStart, @WindowEnd);

关键说明 #

• 确保Qn.Date字段包含时分秒信息，否则无法精确过滤5分钟窗口
• 任务调度需设置为每日每5分钟执行（比如使用SQL Server Agent、Windows任务计划等工具）
• 方案二更适合生产环境，能避免因任务延迟、重复执行导致的重复抓取问题

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