计算与MySQL中每个唯一ID的上一行的日期差 [英] Calculate date difference from previous row of each unique ID in MySQL

问题描述

我是一名SQL初学者，正在学习查询的绳索.我正在尝试查找同一位客户的两次购买之间的日期差异.我有一个看起来像这样的数据集:

I am a SQL beginner and am learning the ropes of querying. I'm trying to find the date difference between purchases by the same customer. I have a dataset that looks like this:

ID | Purchase_Date
==================
1  | 08/10/2017
------------------
1  | 08/11/2017
------------------
1  | 08/17/2017
------------------
2  | 08/09/2017
------------------
3  | 08/08/2017
------------------
3  | 08/10/2017

我希望有一列显示每次唯一客户购买的天数差异，以便输出看起来像这样:

I want to have a column that shows the difference in days for each unique customer purchase, so that the output will look like this:

ID | Purchase_Date | Difference
===============================
1  | 08/10/2017    | NULL
-------------------------------
1  | 08/11/2017    | 1
-------------------------------
1  | 08/17/2017    | 6
-------------------------------
2  | 08/09/2017    | NULL
-------------------------------
3  | 08/08/2017    | NULL
-------------------------------
3  | 08/10/2017    | 2

使用MySQL查询解决此问题的最佳方法是什么?

What would be the best way to go about this using a MySQL query?

推荐答案

在MySQL中这很棘手.了解您是否是开始的最佳方法可能是相关的子查询方法:

This is rather tricky in MySQL. Probably the best way to learn if you are a beginning is the correlated subquery method:

select t.*, datediff(purchase_date, prev_purchase_date) as diff
from (select t.*,
             (select t2.purchase_date
              from t t2
              where t2.id = t.id and
                    t2.purchase_date < t.purchase_date
              order by t2.purchase_date desc
              limit 1
             ) as prev_purchase_date
      from t
     ) t;

如果在(id, purchase_date)上有索引，性能应该没问题.

Performance should be okay if you have an index on (id, purchase_date).

这篇关于计算与MySQL中每个唯一ID的上一行的日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！