How to get difference between consecutive rows in BigQuery?

Learn how to find difference between two consecutive rows in BigQuery.

Through this article you can now easily understand how one can derive the difference between the Values of the current row and the row preceding it. For an example, if you have a multiple column in a database, one of which is a timestamp.

We have a series of entries in a database, one of which is a timestamp. I'd like to add as a field the difference between the Values of the current row and the row preceding it.

This is the table:

Row No Date Values
1 2012-10-30 10245
2 2012-10-31 25416
3 2012-11-01 84252
4 2012-11-02 21454
5 2012-11-03 54436
6 2012-11-04 49364
7 2012-11-05 42656

This is the result we need:

Row No Date Values Pre-Value Diffrence
1 2012-10-30 10245 -
2 2012-10-31 25416 10245 15171
3 2012-11-01 84252 25416 58836
4 2012-11-02 21454 84252 -62798
5 2012-11-03 54436 21454 32982
6 2012-11-04 49364 54436 -5072
7 2012-11-05 42656 49364 -6708

 

Syntax:-

Lag() over (order by )

For Example:

We get Output as :

Row No Date Values Pre-Value
1 2012-10-30 10245 -
2 2012-10-31 25416 10245
3 2012-11-01 84252 25416
4 2012-11-02 21454 84252
5 2012-11-03 54436 21454
6 2012-11-04 49364 54436
7 2012-11-05 42656 49364

 

Now we want to calculate difference:


we get final out-put as:

Row No Date Values Pre-Value Diffrence
1 2012-10-30 10245 -
2 2012-10-31 25416 10245 15171
3 2012-11-01 84252 25416 58836
4 2012-11-02 21454 84252 -62798
5 2012-11-03 54436 21454 32982
6 2012-11-04 49364 54436 -5072
7 2012-11-05 42656 49364 -6708

Leave a comment