Difference between two consecutive rows by ID

问题:

I'm trying to get the difference between two consecutive rows by ID, the two dates are in different columns. I'm thinking it would have to be something with PARTITION BY combined with getting a MAX 2 desc, then grabbing a MAX 1 asc... getting a bit confused by the logic though. Here is a sample.

Before

ID  Start_date  End_date
1   1/1/2017    2/8/2017
1   2/10/2017   3/8/2017
1   3/21/2017   3/29/2017
1   4/11/2017   5/12/2017
2   4/2/2016    4/3/2016
2   4/6/2016    5/11/2016
3   4/25/2016   4/30/2016
3   5/25/2016   6/29/2016
3   7/13/2016   7/15/2016
3   7/18/2016   8/24/2016

AFTER

ID  Start_date  End_date    new
1   1/1/2017    2/8/2017    NULL
1   2/10/2017   3/8/2017    2
1   3/21/2017   3/29/2017   13
1   4/11/2017   5/12/2017   13
2   4/2/2016    4/3/2016    NULL
2   4/6/2016    5/11/2016   3
3   4/25/2016   4/30/2016   NULL
3   5/25/2016   6/29/2016   25
3   7/13/2016   7/15/2016   14
3   7/18/2016   8/24/2016   3

回答1:

Use the lag function to get the previous value and use it in datediff.

select t.*,datediff(day,lag(end_date) over(partition by id order by start_date),start_date) as diff
from tbl t

LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.