如何在SQL中按顺序关联借还记录以计算平均归还时长

核心需求是为每条归还记录匹配同用户同物品、15天内、最早未被匹配的借出记录，再计算平均归还时长。以下是通用SQL实现思路（以PostgreSQL为例，其他数据库可做适配）：

步骤1：预处理借还记录 #

先给每个用户、物品的借还记录按时间排序编号，方便后续顺序匹配：

WITH ranked_lends AS (
    SELECT
        user_id,
        item_id,
        lend_num,
        lend_date,
        -- 按用户+物品分组，借出时间升序编号
        ROW_NUMBER() OVER (PARTITION BY user_id, item_id ORDER BY lend_date) AS lend_rank
    FROM lend_records  -- 替换为你的借出表名
),
ranked_returns AS (
    SELECT
        user_id,
        item_id,
        return_num,
        return_date,
        -- 按用户+物品分组，归还时间升序编号
        ROW_NUMBER() OVER (PARTITION BY user_id, item_id ORDER BY return_date) AS return_rank
    FROM return_records  -- 替换为你的归还表名
)

步骤2：一对一匹配符合条件的借还记录 #

使用LATERAL JOIN为每条归还记录找到符合规则的借出记录：

SELECT
    r.user_id,
    -- 计算归还时长（天数）并取平均
    AVG(EXTRACT(DAY FROM r.return_date - l.lend_date)) AS avg_return_days
FROM ranked_returns r
LEFT JOIN LATERAL (
    SELECT *
    FROM ranked_lends l
    WHERE l.user_id = r.user_id
      AND l.item_id = r.item_id
      -- 借出时间不晚于归还时间，且间隔不超过15天
      AND l.lend_date <= r.return_date
      AND EXTRACT(DAY FROM r.return_date - l.lend_date) <= 15
      -- 确保该借出记录未被更早的归还记录匹配
      AND l.lend_rank > COALESCE(
          (SELECT MAX(l2.lend_rank)
           FROM ranked_lends l2
           JOIN ranked_returns r2 
             ON l2.user_id = r2.user_id 
             AND l2.item_id = r2.item_id
           WHERE r2.return_date <= r.return_date
             AND l2.lend_date <= r2.return_date
             AND EXTRACT(DAY FROM r2.return_date - l2.lend_date) <= 15),
          0
      )
    -- 取符合条件的最早借出记录
    ORDER BY l.lend_rank ASC
    LIMIT 1
) l ON true
-- 过滤掉未匹配到借出记录的归还
WHERE l.lend_num IS NOT NULL
GROUP BY r.user_id;

数据库适配说明 #

• SQL Server：将LATERAL JOIN替换为OUTER APPLY
• MySQL 8.0+：支持LATERAL JOIN，语法一致
• Oracle：使用CROSS APPLY替代LATERAL JOIN

关键逻辑解释 #

1. 排序编号：通过ROW_NUMBER()确保借还记录按时间顺序排队，避免乱序匹配
2. 时间范围校验：严格限制借出时间在归还时间前15天内
3. 未匹配校验：通过子查询排除已被更早归还记录匹配的借出，保证一对一不重复
4. 取最早记录：ORDER BY lend_rank ASC LIMIT 1确保拿到符合条件的最早借出

内容的提问来源于stack exchange，提问作者M. Die K. S.