How to find difference between the integers ignoring null values

user_11474083 0
Viewed 70

Data looks like this

Row CurrentPrice    PriceChangeDate  PreviousPrice
1   7.9              25/03/2022      null
2   null             25/03/2022      null
3   7.9              24/03/2022      null
4   8                01/03/2022      8.5
5   8.5              24/02/2022      8

Expected Output

Row CurrentPrice    PriceChangeDate  PreviousPrice  Difference
1   7.9             25/03/2022       7.9            0
2   7.9             24/03/2022       8              0.1
3   8               01/03/2022       8.5            0.5
4   8.5             24/02/2022       8              -0.5

I am not able to find a way to get the expected data.

google-bigquery sql
2 Answers

Consider below approach

select *, round(PreviousPrice - CurrentPrice, 2) as Difference
from (
  select * except(PreviousPrice), 
    ifnull(PreviousPrice, lag(CurrentPrice) over(order by PriceChangeDate)) as PreviousPrice
  from your_table
  where not CurrentPrice is null
)

if applied to sample data in y our question - output is

enter image description here

user_52219440

For your requirement, you can use IFNULL conditional expressions to ignore the null values. You can try the code below where you can SAFE_CAST the values to float inside IFNULL conditional expressions.

 SELECT * EXCEPT(PreviousPrice),
 ifnull(SAFE_CAST(PreviousPrice AS FLOAT64),0) AS PreviousPrice,
 ROUND(ifnull(SAFE_CAST(PreviousPrice AS FLOAT64),0) - ifnull(SAFE_CAST(CurrentPrice AS FLOAT64),0),2) AS Difference
 FROM (
 SELECT * EXCEPT(ROW,PreviousPrice),
   ifnull(PreviousPrice,
   LAG(CurrentPrice) OVER(ORDER BY PriceChangeDate)) AS PreviousPrice
 FROM `project.dataset.pricechange`
 WHERE NOT CurrentPrice = 'null'
 ORDER BY PriceChangeDate DESC )

Output Table

enter image description here

user_175702980
Related