How to make a cumulative sum in Bigquery?

Question

Hello. I need to create a Dashboard from a query. I would like to generate this column in the query always adding up to the previous month, ie a cumulative sum.

ex: instead of:

mês 1: 2
mês 2: 3 
mês 3: 5
mês 4: 1

would be:

mês 1: 2
mês 2: 5
mês 3: 10
mês 4: 11

In my case:

SELECT count(id_produto) AS quantidade_ofertas, date_trunc(cast(datahora as date), month) as mes
FROM `TABELA1` 
WHERE DATE(process_time) < CURRENT_DATE()
and cast(datahora as date) < CURRENT_DATE()
group by date_trunc(cast(datahora as date), month)

Being that in this code above, generates a column with values for each month, and not the sum to the previous one (cumulative).

The idea of this query is to generate a Dashboard where I can filter by date without the 'accumulative' that the ferally provides, disturb.

Thank you in advance.

Answer 1

I think for your problem, you can divide it into 2 parts, first using groupby to find the ids in each month, and then using over to make the cumulative sum. Follow an example

with t1 as (
  select '2020-01-01' as data, 0101 as id_produto
  union all
  select '2020-01-01', 0102
  union all
  select '2020-02-01', 0201
  union all
  select '2020-02-01', 0202
  union all
  select '2020-02-01', 0203
  union all
  select '2020-03-01', 0301
  union all
  select '2020-03-01', 0302
  union all
  select '2020-03-01', 0303
),

t2 as (
  select 
    date_trunc(cast(data as date),  month) as data,
    count(id_produto) as id_produto
  from t1 
  group by 1
)

select
  data,
  sum(id_produto) over (order by data rows between unbounded preceding and current row)
from t2
--saida:
Linha   data       f0_  
1       2020-01-01 2
2       2020-02-01 5
3       2020-03-01 8