CODEWITHSUNDEEP
sqlselectgoogle-bigquery
lili
lili

Reputation: 81

How to retrieve difference in value between a value of the current and previous rows without using temp table?

I want to retrieve difference in Counter column(below is a sample data) between a value of the current and previous rows.

enter image description here

So the 1st column will be Counter and 2nd column will be difference of current row Counter and previous Counter. Here is an example:

enter image description here

I was thinking to write something like:

select 
  id,
  counter - LAG(counter, 1) OVER (ORDER BY counter)
from table

Here is what is the result of my query: enter image description here I am using Google Bigquery.

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If your counter is never decreasing and you want the results per id, then you are on the right track. I think you want:

select id, counter,
       counter - lag(counter) over (partition by id order by counter) as diff
from table
order by id, counter;

Note: This includes the id as the first column because it seems relevant.

Upvotes: 1

Related Questions