Oracle SQL实现7天移动窗口去重交易对手计数及交易标记

需求对齐 #

实现逻辑严格匹配以下规则：

• 统计基准：逐笔交易作为独立锚点
• 窗口范围：锚点交易日期前后各6天，与示例一致（锚点为6月17日时覆盖6月11日-6月23日区间）
• 计数规则：窗口内仅统计单笔金额≥1000€的交易，同一客户与同一交易对手的多笔交易去重计为1个
• 打标规则：窗口内去重交易对手数≥5时，给对应锚点交易打上标识
• 替代原自然周固定分组逻辑，支持滑动窗口逐笔计算

推荐实现（Oracle 12cR2及以上版本，适配大型数据集） #

该写法用窗口函数实现，仅需单次遍历排序后的数据即可完成计算，IO开销最低，千万级数据集性能表现优异。
注意：原字段中Transaction ID、Date为带空格/数据库保留关键字的字段，实际执行时需用双引号包裹。

SELECT 
    Customer_ID,
    Counterparty_ID,
    "Transaction ID",
    Transaction_Amount,
    "Date",
    CASE 
        WHEN COUNT(DISTINCT CASE WHEN Transaction_Amount >= 1000 THEN Counterparty_ID END) 
             OVER (
                PARTITION BY Customer_ID 
                ORDER BY TRUNC("Date") -- 截断时分秒，按自然日对齐窗口
                RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND INTERVAL '6' DAY FOLLOWING
             ) >= 5 
        THEN 'Y' 
        ELSE 'N' 
    END AS risk_flag
FROM your_transaction_table_name; -- 替换为实际交易表名

低版本Oracle兼容写法 #

如果使用12cR2以前的Oracle版本（不支持窗口函数内使用DISTINCT聚合），可使用CTE预过滤+关联统计的写法，性能同样可控：

WITH eligible_txn AS (
    -- 预过滤金额达标交易，减少后续计算的数据量
    SELECT 
        Customer_ID,
        Counterparty_ID,
        "Transaction ID",
        TRUNC("Date") AS txn_date
    FROM your_transaction_table_name
    WHERE Transaction_Amount >= 1000
)
SELECT 
    t.Customer_ID,
    t.Counterparty_ID,
    t."Transaction ID",
    t.Transaction_Amount,
    t."Date",
    CASE WHEN cnt.cp_count >=5 THEN 'Y' ELSE 'N' END AS risk_flag
FROM your_transaction_table_name t
LEFT JOIN (
    SELECT 
        a."Transaction ID",
        COUNT(DISTINCT b.Counterparty_ID) AS cp_count
    FROM your_transaction_table_name a
    INNER JOIN eligible_txn b
        ON a.Customer_ID = b.Customer_ID
        AND b.txn_date BETWEEN TRUNC(a."Date") - 6 AND TRUNC(a."Date") + 6
    GROUP BY a."Transaction ID"
) cnt ON t."Transaction ID" = cnt."Transaction ID";

大表性能优化提示 #

• 建立组合索引(Customer_ID, "Date", Counterparty_ID, Transaction_Amount)，可避免全表扫描，窗口计算直接走索引，性能提升可达数倍
• 如果日期字段带时分秒，必须用TRUNC("Date")截断为自然日格式，否则会因时间精度问题导致窗口范围匹配错误
• 数据量超过千万级时优先选择第一种窗口函数写法，避免自关联带来的重复IO开销

逻辑校验：以6月17日的交易为锚点时，上述代码会自动统计6月11日至6月23日区间内同客户的达标交易，按交易对手去重计数，不会出现自然周分组跨周截断的问题，完全匹配滑动窗口要求。

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