How can I avoid self join in BigQuery

Question

I have the following table and from there I want to add another column with the previous value:

id	Previous id	Value
30	20	8000
20	10	5000

The output would be as follows:

id	Previous_id	Value	Previous_Value
30	20	8000	5000
20	10	5000	-

I know I could run the self join you can see below, but I want to know if there's a better practice to do so, as this is an operation I will have to run periodically in other similar use cases. I've tried analytic functions but I can't find the solution with them...

SELECT t1.*, t2.Value as Previous_Value
FROM `Table` t1
LEFT JOIN `Table`t2
ON t1.Previous_id = t2.id

Answer 1

The self-join is the right approach because your data is "linked".

If the previous id were strictly the id that comes numerically just before the given id, you could use LAG():

SELECT t.*, LAG(t.Value) OVER (ORDER BY t.id) as Previous_Value
FROM `Table` t;

However, this is probably not the case in your data (although it is the case in your sample data).

In terms of performance, you don't need to worry too much about the JOIN. It is an equijoin, so BigQuery should know how to run it optimally.