CODEWITHSUNDEEP
sqlgoogle-bigquery
Moushmi Das
Moushmi Das

Reputation: 1

Big query/sql to maintain the same data from next row onwards in the same column itself, got based on some condition in the current row

I am working on one of the usecase where I need to implement one of the logic to get the relevant data .

eg: If you will see the image :

The column = new_TARIFF_ALLOWANCE_DATA is calculated as

case when gift_given!=0 or gift_received!=0 then TARIFF_ALLOWANCE_DATA - gift_given + gift_received else remain same whatver it is as per last row calculation from next row onwards' as new_total_allwance

So basicall I want in the same column based on the condition whatver I got will maintain the same value from the next rown onwards in the same column

enter image description here

I tried with the approach if (current_row = previous_row then previous_row , current_row) but not working.

Upvotes: 0

Views: 129

Answers (1)

Damião Martins
Damião Martins

Reputation: 1849

An approach using navigation functions to get the last populated values from gift_given and gift_taken:

WITH calc_last_gifts AS (
  SELECT 
    *,
    LAST_VALUE(NULLIF(gift_given, 0) IGNORE NULLS) OVER (ORDER BY row_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as last_gift_given,
    LAST_VALUE(NULLIF(gift_taken, 0) IGNORE NULLS) OVER (ORDER BY row_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as last_gift_taken,
  FROM sample
)
SELECT 
  * EXCEPT(last_gift_given, last_gift_taken),
  tarif_allowance_data - IFNULL(last_gift_given, 0) + IFNULL(last_gift_taken, 0) as new_tarif_allowance_data
FROM calc_last_gifts

Output: Expected output

More about LAST_VALUE navigation function: https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#last_value

Upvotes: 1

Related Questions