mysql - 使用 MYSQL 计算同一列与组中的许多日期之间的时间

Question

我有一个数据集，其中包含客户编号和订单日期，我需要做的是计算每个客户的每个订单之间的天数，例如：

所以我需要知道每个客户的每个订单之间的天数。谢谢您的帮助 ：）

标签： mysqlsqldatetimedatediff

Answer 1

这是你将如何做到这一点，你可以使用dense_rank()

MySQL 8.0

select t1.id, t1.order_date
            , ifnull(
                datediff(cast(t1.order_date as datetime), cast(t2.order_date as datetime))
                ,0) as intrval
    from (
            select dense_rank() over (partition by id order by cast(order_date as datetime) asc) as rnk
                , order_date, id 
            from table1) as t1
    left join 
     (
            select dense_rank() over (partition by id order by cast(order_date as datetime) asc) as rnk
                , order_date, id 
            from table1) as t2
    on t1.id = t2.id and t2.rnk+1 = t1.rnk

输出：