CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerywindow-functionsgaps-and-islands
i_am_cris
i_am_cris

Reputation: 627

Running total with several conditions in bigquery

I need to calculate a running total but need to reset the total on a condition (when expected reached = 0 and product_group and product changes). Got help here without the two extra fields: Calculate a running total with a condition in BigQuery I have this table and may use product_group and product as integers or strings as below. 

Date, Product_group, Product, Registrations, Expected Registrations, Expected Reached, Running Total
            2020-03-01,A, Bikes, 5, 4,1, 1
            2020-03-02,A, Bikes, 7, 5,1, 2
            2020-03-03,A, Bikes, 8, 6,1, 3
            2020-03-04,A, Bikes, 2, 5,0, 0
            2020-03-05,A, Bikes, 5, 4,1, 1
            2020-03-06,A, Bikes, 7, 5,1, 2 
            2020-03-04,B, Cars , 2, 5,0, 0
            2020-03-05,B, Cars , 5, 4,1, 1
            2020-03-06,B, Cars , 7, 5,1, 2
            2020-03-07,B, Cars , 8, 6,1, 3 
            2020-03-08,C, Plane, 2, 5,0, 0

Any suggestions how to adapt this query (answer from the other post) that works fine without the two extra fields-

#standardSQL
SELECT * EXCEPT(grp), 
  SUM(Expected_reached) OVER(PARTITION BY grp ORDER BY `date`) Running_Total
FROM (
  SELECT *, COUNTIF(Expected_reached = 0) OVER(ORDER BY `date`) grp 
  FROM `project.dataset.table`
)

The problem is that COUNTIF(Expected_reached = 0) OVER(ORDER BYdate) grp starts over when the product_group or product changes and I get non unique groups so the running total SUM(Expected_reached) OVER(PARTITION BY grp ORDER BYdate) Running_Total doesnt calculate correctly.

Upvotes: 1

Views: 507

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

You just simply need to add PARTITION BY Product_group, Product to both analytic functions 

#standardSQL
SELECT * EXCEPT(grp), 
  SUM(Expected_reached) OVER(PARTITION BY Product_group, Product, grp ORDER BY `date`) Running_Total
FROM (
  SELECT *, COUNTIF(Expected_reached = 0) OVER(PARTITION BY Product_group, Product ORDER BY `date`) grp 
  FROM `project.dataset.table`
)

Upvotes: 1

GMB
GMB

Reputation: 222622

As far as concerns, you just need to add the two additional columns, product_group and product, to the partition clause of the window functions:

select 
    * except(grp), 
    sum(expected_reached) 
        over(partition by grp, product_group, product order by `date`) running_total
from (
    select 
        *, 
        countif(expected_reached = 0) 
            over(partition by product_group, product order by `date`) grp 
    from `project.dataset.table` 
)

Upvotes: 0

Related Questions