Oracle 11G查询连续5天日累计额超1000的交易数据

需求分析 #

需要筛选出2023-01-01至2023-10-01期间，存在连续5天每日累计交易金额超过1000的交易发起方（sender_name）的所有交易记录；若同一用户有多组连续5天的符合条件区间，需返回该用户的全部交易数据。

实现思路 #

1. 按日聚合交易金额：先对每个用户的交易按日期（天维度）聚合，筛选出每日累计金额超1000的有效日期记录。
2. 识别连续日期区间：通过日期与行号的差值分组，将连续的日期归为同一组，统计每组的连续天数，筛选出连续天数≥5的用户分组。
3. 关联原表获取全量数据：基于筛选出的符合条件的用户，关联原交易表返回其在目标时间范围内的所有交易记录。

完整SQL代码 #

WITH daily_valid AS (
    -- 按用户和日期天维度聚合，筛选每日累计金额超1000的记录
    SELECT
        sender_name,
        TRUNC(transaction_date) AS trans_day,
        SUM(amount) AS daily_total
    FROM Transactions
    WHERE transaction_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                              AND TO_DATE('2023-10-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    GROUP BY sender_name, TRUNC(transaction_date)
    HAVING SUM(amount) > 1000
),
continuous_groups AS (
    -- 识别连续日期组，生成分组键
    SELECT
        sender_name,
        trans_day - ROW_NUMBER() OVER (PARTITION BY sender_name ORDER BY trans_day) AS group_key
    FROM daily_valid
),
qualified_users AS (
    -- 筛选存在连续≥5天分组的用户
    SELECT DISTINCT sender_name
    FROM continuous_groups
    GROUP BY sender_name, group_key
    HAVING COUNT(*) >= 5
)
-- 返回符合条件用户的所有交易数据
SELECT t.*
FROM Transactions t
JOIN qualified_users q ON t.sender_name = q.sender_name
WHERE t.transaction_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                            AND TO_DATE('2023-10-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

代码说明 #

• daily_valid：按用户和日期天维度聚合，只保留每日累计金额超1000的记录，排除无效日期。
• continuous_groups：通过trans_day - ROW_NUMBER()生成分组键，连续的日期会得到相同的分组键（日期每日递增1，行号同步递增1，差值恒定）。
• qualified_users：统计每个分组的天数，筛选出存在连续≥5天分组的用户，去重后得到符合条件的用户列表。
• 最终查询：关联原表，返回这些用户在目标时间范围内的所有交易记录。

示例场景适配 #

• 若需求改为连续超过5天（即≥6天），只需将HAVING COUNT(*) >= 5改为HAVING COUNT(*) >= 6即可，此时会返回如示例中Carol的所有交易行。

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