mysql - 如何获取多个日期之间的平均时间

Question

我想做的是根据客户的交易频率对他们进行分类。我记录了他们每次交易的日期，但我无法计算出每个日期之间的平均增量。我实际上想要的是一张显示我的表格:

| User | Average Frequency
|  1   | 15
|  2   | 15
|  3   | 35
...

我当前拥有的数据格式如下:

| User | Transaction Date
|  1   | 2018-01-01
|  1   | 2018-01-15
|  1   | 2018-02-01
|  2   | 2018-06-01
|  2   | 2018-06-18
|  2   | 2018-07-01
|  3   | 2019-01-01
|  3   | 2019-02-05
...

基本上，每个客户都会有多个交易，我想了解如何获取每个日期之间的增量以及增量的平均值。

我知道 datediff 函数及其工作原理，但我不知道如何将它们拆分为事务。我还知道 Looker 等工具中提供了 offset 函数，但我不知道其背后的语法。

谢谢

Answer 1

在 MySQL 8+ 中，您可以使用 LAG获取延迟的交易日期，然后使用DATEDIFF获取两个连续日期之间的差异。然后您可以取这些值的平均值:

SELECT User, AVG(delta) AS `Average Frequency`
FROM (SELECT User, 
             DATEDIFF(`Transaction Date`, LAG(`Transaction Date`) OVER (PARTITION BY User ORDER BY `Transaction Date`)) AS delta
      FROM transactions) t
GROUP BY User

输出:

User    Average Frequency
1       15.5
2       15
3       35

Demo on dbfiddle.com