CODEWITHSUNDEEP
google-bigquery
Sergii
Sergii

Reputation: 21

Bigquery: Calculate balance or rewrite SQL script in Bigquery

Please help. I'm trying to find a solution how to calculate the current balance for each row by having 'in' and 'out' and previous balance in BigQuery: 'Current Balance'= 'Previous Balance'+ 'in' - 'out'

I have:

| id | in |out|
| ---| ---|---|
|1   | 10 |0  |
|2   | 30 |5  |
|3   | 0  |1  |

expected result:

| id| in|out| Balance |
|---|---|---|---------|
|1  | 10|  0|       10|
|2  | 30|  5|       35|
|3  | 0 |  1|       34|

The same issue was raised here: [https://stackoverflow.com/questions/9394418/calculate-balance-with-mysql][1], but provided solution is for MySQL.

If there is a way how to transfer this to BigQuery:

SELECT 
    `table`.`ID`,
    `table`.`In`,
    `table`.`Out`,
    @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
FROM `table`, (SELECT @Balance := 0) AS variableInit
ORDER BY `table`.`ID` ASC

Or Are there any other options for calculating the balance in BigQuery?

Thank you!

Upvotes: 0

Views: 100

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Consider below approach

select * except(initial_balance), 
  initial_balance + sum(t.in - t.out) over win as balance
from your_table t,
unnest([struct(0 as initial_balance)])
window win as (order by id)

if applied to sample data in your question and initial balance = 0 - output is

enter image description here

Upvotes: 2

Mr.Batra
Mr.Batra

Reputation: 833

Try this

SELECT SUM(IN) OVER(ORDER BY ID) - SUM(OUT) OVER (ORDER BY ID) 
FROM YOUR_TABLE

Upvotes: 1

Related Questions