Oracle SQL需求：筛选30天内交易总额超200万的客户及对应交易记录

需求说明 #

需要筛选出满足以下条件的cust_id及其对应交易记录：

• 同一客户针对同一PRD_ID同时存在买入（BUY）和卖出（SELL）交易
• 两类交易的总AMT超过200万
• 买入和卖出交易的DATE间隔不超过30天

示例说明 #

• 客户ABCD于2021/10/01买入AAA，2021/10/05卖出AAA，时间间隔4天，总金额超200万，属于目标结果；
• 客户QRST于2021/10/07多次买入AAA并当日卖出，总金额不足200万，不属于目标结果；
• 客户ZZYY于2021/10/08买入CCC，2021/12/07卖出CCC，总金额超200万但时间间隔超过30天，不属于目标结果。

原始交易数据 #

期望输出结果 #

解决方案SQL代码 #

我们可以通过CTE先聚合符合条件的客户-产品组合，再关联回原始表获取具体交易记录，代码如下：

WITH valid_cust_prd AS (
    SELECT 
        cust_id,
        prd_id,
        -- 计算总交易金额（注意处理带逗号的金额格式）
        SUM(TO_NUMBER(REPLACE(amt, ',', ''))) AS total_amt,
        -- 计算买卖交易的最小日期差
        ABS(
            MIN(CASE WHEN tran = 'BUY' THEN TO_DATE(date, 'YYYY/MM/DD') END) - 
            MIN(CASE WHEN tran = 'SELL' THEN TO_DATE(date, 'YYYY/MM/DD') END)
        ) AS day_diff
    FROM 
        transactions -- 替换为你的实际表名
    WHERE 
        tran IN ('BUY', 'SELL')
    GROUP BY 
        cust_id, prd_id
    HAVING 
        total_amt > 2000000
        -- 确保同时存在买卖交易
        AND COUNT(DISTINCT tran) = 2
        AND day_diff <= 30
)
SELECT 
    t.cust_id,
    t.amt,
    t.date,
    t.prd_id
FROM 
    transactions t
JOIN 
    valid_cust_prd vcp 
    ON t.cust_id = vcp.cust_id AND t.prd_id = vcp.prd_id
WHERE 
    t.tran IN ('BUY', 'SELL')
ORDER BY 
    t.cust_id, t.date;

代码说明 #

1. CTE部分：按客户+产品维度分组，先把带逗号的金额转为数字计算总和，再通过CASE语句分别提取买卖的最早日期并计算间隔，最后用HAVING过滤出符合所有条件的组合。
2. 主查询：将原始交易表和筛选后的有效组合关联，取出对应交易记录，得到最终结果。

如果你的DATE字段已经是日期类型，可以去掉TO_DATE转换；如果AMT是纯数字类型，也可以去掉REPLACE和TO_NUMBER转换。

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