使用 SQL 判断 refund 并查询买家的第二笔交易

结论

这三个需求都能用条件表达式和窗口函数解决：

• 用 CASE 判断退款时间是否在购买后的 72 小时内。
• 用 ROW_NUMBER() 对每位买家的购买记录排序，再筛选 rn = 2。
• 查询第二个交易时间，同样是取排序后的第二行，不需要使用 MIN() 或 MAX()。

示例数据有一个问题：rtime 非空的记录实际只有 2 条，并非 3 条。buyer_id 为 3 的退款间隔是 1 天，可以处理；buyer_id 为 12 的退款间隔是 5 天，不能处理。因此，按当前数据计算，结果应为“2 笔退款中有 1 笔可以处理”。

示例中的 ptime 和 rtime 都是 DATE 类型。如果业务规则需要精确计算 72 小时，建议改用 TIMESTAMP，否则只能按日期计算。

1. 判断退款能否处理

假设：

• ptime 是购买时间；
• rtime 是退款时间；
• 退款时间不能早于购买时间；
• 购买后恰好 72 小时仍符合条件。

PostgreSQL、Oracle 等支持 INTERVAL 语法的数据库可以这样写：

SELECT
    t.Id AS buyer_id,
    t.ptime,
    t.rtime,
    t.sid,
    t.itemid,
    t.gtv,
    CASE
        WHEN t.rtime IS NULL THEN 0
        WHEN t.rtime >= t.ptime
         AND t.rtime <= t.ptime + INTERVAL '72' HOUR
        THEN 1
        ELSE 0
    END AS refund_processable
FROM transactions AS t;

如果只需要查看发生过退款的交易：

SELECT
    t.Id AS buyer_id,
    t.ptime,
    t.rtime,
    CASE
        WHEN t.rtime >= t.ptime
         AND t.rtime <= t.ptime + INTERVAL '72' HOUR
        THEN 1
        ELSE 0
    END AS refund_processable
FROM transactions AS t
WHERE t.rtime IS NOT NULL;

按示例数据计算，结果如下：

MySQL 中的时间条件需要改为：

t.rtime <= DATE_ADD(t.ptime, INTERVAL 72 HOUR)

SQL Server 可以使用：

t.rtime <= DATEADD(HOUR, 72, t.ptime)

同时连接商品表

如果“transaction items 表”是指交易表与商品表连接后的结果，可以使用 LEFT JOIN：

SELECT
    t.Id AS buyer_id,
    t.ptime,
    t.rtime,
    t.sid,
    t.itemid,
    i.category,
    i.name,
    t.gtv,
    CASE
        WHEN t.rtime IS NULL THEN 0
        WHEN t.rtime >= t.ptime
         AND t.rtime <= t.ptime + INTERVAL '72' HOUR
        THEN 1
        ELSE 0
    END AS refund_processable
FROM transactions AS t
LEFT JOIN Items AS i
    ON i.sid = t.sid
   AND i.itemid = t.itemid;

这里应使用 LEFT JOIN，因为示例中有不少交易商品在 Items 表里找不到对应记录。改用 INNER JOIN 后，这些交易会被过滤掉。

2. 对每位买家的购买记录排名，并取第二次购买

使用 ROW_NUMBER()，按 buyer_id 分组，再按购买时间排序：

WITH ranked_purchases AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY t.Id
            ORDER BY t.ptime
        ) AS purchase_rank
    FROM transactions AS t
)
SELECT
    Id AS buyer_id,
    ptime,
    rtime,
    sid,
    itemid,
    gtv,
    purchase_rank
FROM ranked_purchases
WHERE purchase_rank = 2;

结果中只有 buyer_id 3 的第二次购买：

这里的“忽略退款”通常是指按照购买记录和 ptime 排名，不按 rtime 排名，也不排除后来发生退款的交易。退款并不会改变它曾经是一笔购买的事实。

如果题目想表达的是“排除已经退款的购买”，则要在执行窗口函数前过滤数据：

WITH ranked_purchases AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY t.Id
            ORDER BY t.ptime
        ) AS purchase_rank
    FROM transactions AS t
    WHERE t.rtime IS NULL
)
SELECT *
FROM ranked_purchases
WHERE purchase_rank = 2;

但按这种理解，示例中的 buyer_id 3 只有一笔未退款交易，无法得到预期结果。因此，第一种理解更符合题目给出的预期。

3. 查询每位买家的第二个交易时间

这个需求仍然可以使用窗口函数，只需保留买家和第二次购买时间：

WITH ranked_transactions AS (
    SELECT
        t.Id AS buyer_id,
        t.ptime AS transaction_time,
        ROW_NUMBER() OVER (
            PARTITION BY t.Id
            ORDER BY t.ptime
        ) AS transaction_rank
    FROM transactions AS t
)
SELECT
    buyer_id,
    transaction_time
FROM ranked_transactions
WHERE transaction_rank = 2;

结果如下：

ROW_NUMBER() 会针对每位买家分别从 1 开始编号。即使买家有更多交易，也能选出第二条记录。

注意事项

相同时间的交易需要稳定排序

如果同一买家可能在完全相同的时间产生多笔交易，只按 ptime 排序就无法确定哪一笔是“第二笔”。此时应增加唯一交易编号作为第二个排序条件，例如：

ROW_NUMBER() OVER (
    PARTITION BY buyer_id
    ORDER BY ptime, transaction_id
)

当前表中的 Id 是买家 ID，不能同时作为唯一交易 ID。实际设计表结构时，建议另设主键：

transaction_id BIGINT PRIMARY KEY

ROW_NUMBER() 与 RANK() 的区别

这里更适合使用 ROW_NUMBER()：

• ROW_NUMBER() 为每行分配唯一序号，适合查找第二笔交易。
• RANK() 会为时间相同的记录分配相同排名，后续名次会跳号。
• DENSE_RANK() 也会为时间相同的记录分配相同排名，但后续名次不会跳号。

如果要查找的是“第二个不同的交易时间”，而不是第二条交易记录，可以使用：

DENSE_RANK() OVER (
    PARTITION BY Id
    ORDER BY ptime
)

时间字段应使用合适的数据类型

如果字段只保存：

2020-09-01

数据库无法知道交易当天的具体时间。对于严格的 72 小时退款窗口，更适合保存：

2020-09-01 14:30:00

同时将字段定义为 TIMESTAMP 或数据库对应的日期时间类型，以免混淆“第 3 个自然日”和“实际经过 72 小时”。

备注：内容仅供参考。