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

Question

我正在尝试使用SQL来完成这个任务。我有一个包含transaction_date的事务表。按日期分组后，我得到了以下列表：

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

从这三个交易日期，我想达到：

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

有好的语法吗？在我用时间迭代它们之前。

提前感谢

Answer 1

如果您的mysql版本不支持lag或lead函数。

您可以尝试使列使用子查询获取下一个DateTime。然后使用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

结果

| 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           |

关于DB Fiddle的看法