MySQL lag列数据lag

Question

假设我有以下数据：

CUSTOMER_ID CONTACT_ID  COMM_CREATION_DAY DAYS_SINCE_LAST_CONTACT
10000       28036173295 2014-12-21        0
10000       28365672745 2015-01-29        DATEDIFF('2015-01-29','2014-12-21')
10000       28576719155 2015-02-26        DATEDIFF('2015-02-26','2014-01-29')
38409       28432217395 2015-02-07        0
38409       28565986955 2015-02-25        DATEDIFF('2015-02-25,'2015-02-07')

我试图获取DAYS_SINCE_LAST_CONTACT列，但MySQL中没有LEAD或LAG函数。如何在一条SELECT语句中做到这一点？

我尝试了以下几种方法：

SELECT CUSTOMER_ID, COMM_ID , COMM_CREATION_DAY, PREVIOUS_COMM_CREATION_DAY FROM 
(
select
c.*
, @prev AS PREVIOUS_COMM_CREATION_DAY
, @prev := COMM_CREATION_DAY
FROM contacts c, (select @prev:=NULL) vars
ORDER BY CUSTOMER_ID, c.COMM_CREATION_DAY, c.COMM_ID
) sq
ORDER BY CUSTOMER_ID, COMM_CREATION_DAY

但这给了我：

CUSTOMER_ID CONTACT_ID  COMM_CREATION_DAY LAST_CONTACT_DATE
10000       28036173295 2014-12-21        NULL
10000       28365672745 2015-01-29        2014-12-21
10000       28576719155 2015-02-26        2015-01-29
38409       28432217395 2015-02-07        2015-02-26 (THIS IS INCORRECT!)
38409       28565986955 2015-02-25        2015-02-07

在所有情况下，NULL在最后一列中都不能正确显示。

有什么想法吗？

Answer 1

是的，在mysql中，您需要使用用户定义的变量进行如下计算

select
customer_id,
contact_id,
contact_date,
DAYS_SINCE_LAST_CONTACT 
from(
  select
  customer_id,
  contact_id,
  contact_date,
  @diff:= if(@prev_customer = customer_id,datediff(contact_date,@prev_date),0) as DAYS_SINCE_LAST_CONTACT,
  @prev_customer:=customer_id,
  @prev_date:= contact_date
  from customer c,(select @prev_customer:=0,@prev_date:=null)x
  order by customer_id,contact_date
)x

http://sqlfiddle.com/#!9/6cb9f/2