Oracle 11g中Transactions表两类数据查询需求咨询

表结构与测试数据 #

以下是transactions表的建表语句：

CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2023-01-03 01:26:29', 700 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2023-01-05 04:26:29', 450 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2023-01-08 18:26:29', 1000 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2023-01-09 04:26:29', 200 FROM DUAL UNION ALL
SELECT '6666', 'Betty', TIMESTAMP '2023-03-12 15:26:29', 845 FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2023-03-19 11:00:29', 300 FROM DUAL UNION ALL
SELECT '9999', 'Carol', TIMESTAMP '2023-08-26 01:26:29', 100 FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2023-08-29 02:26:29', 501 FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2023-09-10 03:26:29',  500 FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2023-09-11 08:26:29',  501 FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2023-09-17 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2023-09-30 05:26:29', 700 FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2023-10-01 05:26:29', 200 FROM DUAL;

1. 首次交易7天内总金额超1000的用户交易查询 #

逻辑说明 #

• 先判断每个用户首次交易后7天内的总金额是否超过1000：若达标，返回该用户所有交易；
• 若首次窗口未达标，则找出该用户所有以单笔交易为起点、7天内总金额超1000的交易窗口，仅返回这些窗口内的交易。

SQL实现 #

WITH user_metrics AS (
    SELECT 
        sender_name,
        MIN(transaction_date) AS first_trans,
        SUM(CASE WHEN transaction_date <= MIN(transaction_date) + INTERVAL '7' DAY THEN amount END) AS first_window_sum
    FROM transactions
    GROUP BY sender_name
),
valid_windows AS (
    -- 首次窗口达标用户，返回全量交易
    SELECT 
        sender_name,
        MIN(transaction_date) AS window_start,
        MAX(transaction_date) AS window_end
    FROM transactions t
    JOIN user_metrics um ON t.sender_name = um.sender_name
    WHERE um.first_window_sum > 1000
    GROUP BY t.sender_name
    UNION ALL
    -- 首次窗口未达标用户，筛选符合条件的7天窗口
    SELECT 
        t1.sender_name,
        t1.transaction_date AS window_start,
        t1.transaction_date + INTERVAL '7' DAY AS window_end
    FROM transactions t1
    JOIN user_metrics um ON t1.sender_name = um.sender_name
    WHERE um.first_window_sum <= 1000
    GROUP BY t1.sender_name, t1.transaction_date
    HAVING (
        SELECT SUM(amount) 
        FROM transactions t2 
        WHERE t2.sender_name = t1.sender_name
          AND t2.transaction_date BETWEEN t1.transaction_date AND t1.transaction_date + INTERVAL '7' DAY
    ) > 1000
)
SELECT DISTINCT t.*
FROM transactions t
JOIN valid_windows vw 
    ON t.sender_name = vw.sender_name
    AND t.transaction_date BETWEEN vw.window_start AND vw.window_end
ORDER BY t.sender_name, t.transaction_date;

2. 以周一为周起始的周达标交易查询 #

可以实现，Oracle 11g支持TRUNC(date, 'IW')格式符，该格式将日期截断到对应周的周一（一周的起始日）。

逻辑说明 #

1. 按用户+周一为起始的周分组，计算每周交易总金额；
2. 筛选出周总金额>1000的分组；
3. 关联原表，返回这些分组对应的用户当周所有交易。

SQL实现 #

WITH weekly_totals AS (
    SELECT 
        sender_name,
        TRUNC(transaction_date, 'IW') AS week_start_monday,
        SUM(amount) AS weekly_sum
    FROM transactions
    GROUP BY sender_name, TRUNC(transaction_date, 'IW')
    HAVING SUM(amount) > 1000
)
SELECT t.*
FROM transactions t
JOIN weekly_totals wt 
    ON t.sender_name = wt.sender_name
    AND TRUNC(t.transaction_date, 'IW') = wt.week_start_monday
ORDER BY t.sender_name, t.transaction_date;

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