SQL计算借阅次数间平均间隔天数：方案正确性验证

方案正确性验证 #

你的代码逻辑完全匹配需求：

• 先用ROW_NUMBER()按用户分组、借阅日期排序，生成每位用户的借阅次数排名
• 再通过LEAD()获取同用户的下一次借阅排名与日期，形成相邻借阅的配对
• 最后按排名区间分组计算平均间隔天数，同时过滤掉无后续借阅的记录（即用户的最后一次借阅）

用你提供的测试数据运行，会得到正确结果：

结果说明：

• 首次到第二次借阅的平均：red(124天) + blue(243天) + green(366天)，总和733除以3得到约177.33天
• 第二次到第三次借阅仅red有数据（240天），平均即为240天

与自连接方案的对比 #

自连接方案虽然能实现需求，但存在明显劣势：

• 逻辑更复杂：需要将表自连接，匹配同一用户、排名差1的两条记录，额外处理排名关联逻辑
• 性能更低：大数据量下自连接会生成大量中间结果，而窗口函数（LEAD/LAG）是基于有序数据集的单次遍历，效率远高于自连接
• 代码冗余：自连接需要重复处理排名逻辑，不如窗口函数版本简洁

附自连接实现示例（仅作对比）：

WITH RankedBorrowings AS (
  SELECT
    name,
    date_library_book_borrowed,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY date_library_book_borrowed) AS borrowing_rank
  FROM myt
)
SELECT
  r1.borrowing_rank AS from_rank,
  r2.borrowing_rank AS to_rank,
  AVG(r2.date_library_book_borrowed - r1.date_library_book_borrowed) AS avg_days_between_borrowings
FROM RankedBorrowings r1
JOIN RankedBorrowings r2
  ON r1.name = r2.name
  AND r1.borrowing_rank + 1 = r2.borrowing_rank
GROUP BY r1.borrowing_rank, r2.borrowing_rank
ORDER BY r1.borrowing_rank;

总结 #

你的原方案是正确的，并且是更优选择——相比自连接，代码更简洁、性能更好，完全满足需求。

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