mysql - 计算日期之间的平均、最小、最大间隔

Question

我正在尝试使用 SQL 来做到这一点。我有一个包含 transaction_date 的交易表。按日期分组后，我得到了这个列表:

| transaction_date |
|    2019-03-01    |
|    2019-03-04    |
|    2019-03-05    |
|       ...        |

从这 3 个交易日期，我想实现:

• 平均值 = ((4-1) + (5-4))/2 = 2 天(计算每个日期的 DATEDIFF)
• 最少 = 1 天
• 最多 = 3 天

有什么好的语法吗？在我使用 WHILE 迭代所有这些之前。

提前致谢

Answer 1

如果您的mysql版本不支持lag或lead功能。

您可以尝试让列使用子查询来获取下一个日期时间。然后使用 DATEDIFF 获取子查询中的日期间隙。

查询 1:

SELECT avg(diffDt),min(diffDt),MAX(diffDt)
FROM (
  SELECT  DATEDIFF((SELECT transaction_date 
                            FROM  T tt
                            WHERE tt.transaction_date > t1.transaction_date
                            ORDER BY tt.transaction_date 
                            LIMIT 1
                           ),transaction_date) diffDt
  FROM T t1
) t1

<强> Results :

| avg(diffDt) | min(diffDt) | MAX(diffDt) |
|-------------|-------------|-------------|
|           2 |           1 |           3 |

如果你的mysql版本高于8.0，你可以尝试使用LEAD窗口函数代替子查询。

<小时/>

查询#1

SELECT avg(diffDt),min(diffDt),MAX(diffDt)
FROM (
  SELECT  DATEDIFF(LEAD(transaction_date) OVER(ORDER BY transaction_date),transaction_date) diffDt
  FROM T t1
) t1;

| avg(diffDt) | min(diffDt) | MAX(diffDt) |
| ----------- | ----------- | ----------- |
| 2           | 1           | 3           |

<小时/>

View on DB Fiddle