如何基于ID、文档编号及Timestamp筛选符合24小时收费规则的计费请求

针对你遇到的API计费核对需求，核心是准确识别每个文档的计费请求（即24小时窗口期外的首次请求），以下提供两种可直接落地的SQL实现方案，适配主流关系型数据库。

计费规则回顾 #

• 同一文档首次请求计费，启动24小时窗口期
• 窗口期内同文档重复请求不计费
• 窗口期结束后，同文档新请求重新计费并开启新窗口期

方案一：递归CTE逐行处理（直观易理解） #

通过递归方式逐行遍历每个文档的请求序列，跟踪上一次计费的窗口期起始时间，判断当前请求是否需要计费：

WITH RECURSIVE ranked_requests AS (
    -- 按文档分组，给每个请求按时间排序编号
    SELECT
        Id,
        文档编号,
        Timestamp,
        ROW_NUMBER() OVER (PARTITION BY 文档编号 ORDER BY Timestamp) AS rn
    FROM api_requests
),
billed_requests AS (
    -- 初始化：每个文档的第一条请求必然计费
    SELECT
        Id,
        文档编号,
        Timestamp,
        rn,
        TRUE AS is_billed,
        Timestamp AS window_start
    FROM ranked_requests
    WHERE rn = 1
    -- 递归处理后续请求
    UNION ALL
    SELECT
        rr.Id,
        rr.文档编号,
        rr.Timestamp,
        rr.rn,
        -- 判断当前请求是否超出上一次计费窗口的24小时
        CASE WHEN rr.Timestamp > br.window_start + INTERVAL '24 hours' THEN TRUE ELSE FALSE END AS is_billed,
        -- 超出则更新窗口起始时间，否则保持原窗口
        CASE WHEN rr.Timestamp > br.window_start + INTERVAL '24 hours' THEN rr.Timestamp ELSE br.window_start END AS window_start
    FROM ranked_requests rr
    JOIN billed_requests br ON rr.文档编号 = br.文档编号 AND rr.rn = br.rn + 1
)
-- 输出所有请求的计费标记
SELECT
    Id,
    文档编号,
    Timestamp,
    is_billed
FROM billed_requests
ORDER BY 文档编号, Timestamp;

方案二：窗口函数分组统计（性能更优） #

利用窗口函数标记计费窗口的起始点，再通过分组识别每个窗口的首次计费请求：

WITH ranked_requests AS (
    SELECT
        Id,
        文档编号,
        Timestamp,
        -- 标记是否为新计费窗口的起点
        CASE 
            WHEN LAG(Timestamp) OVER (PARTITION BY 文档编号 ORDER BY Timestamp) IS NULL 
                THEN 1 -- 文档的第一条请求
            WHEN Timestamp > LAG(Timestamp) OVER (PARTITION BY 文档编号 ORDER BY Timestamp) + INTERVAL '24 hours'
                THEN 1 -- 超出上一次请求24小时
            ELSE 0
        END AS is_new_window
    FROM api_requests
),
window_groups AS (
    -- 累计求和得到每个计费窗口的唯一ID
    SELECT
        *,
        SUM(is_new_window) OVER (PARTITION BY 文档编号 ORDER BY Timestamp) AS window_group_id
    FROM ranked_requests
)
-- 每个窗口组的第一条请求标记为计费
SELECT
    Id,
    文档编号,
    Timestamp,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY 文档编号, window_group_id ORDER BY Timestamp) = 1 THEN TRUE ELSE FALSE END AS is_billed
FROM window_groups
ORDER BY 文档编号, Timestamp;

数据库适配说明 #

• PostgreSQL：时间间隔写法为INTERVAL '24 hours'
• MySQL：时间间隔写法改为INTERVAL 24 HOUR
• SQL Server：时间间隔写法为DATEADD(HOUR, 24, window_start)

核对计费数 #

统计本地计费请求总数，直接筛选is_billed = TRUE即可：

SELECT COUNT(*) AS local_billed_count
FROM billed_requests -- 或window_groups，对应上述方案
WHERE is_billed = TRUE;

将此结果与API服务商提供的计费请求数对比即可完成核对。

内容的提问来源于stack exchange，提问作者Fábio Elias Reis Ritter