percentage per month Bigquery-CodePudding

I am working in Bigquery and I need the percentages for each result for each month, I have the following query but the percentage is calculated with respect to the total, I have tried to add a PARTITION BY in the OVER clause but it does not work.

SELECT CAST(TIMESTAMP_TRUNC(CAST((created_at) AS TIMESTAMP), MONTH) AS DATE) AS `month`,
  result,
     count(*) * 100.0 / sum(count(1)) over() as percentage
FROM table_name

GROUP BY  1,2
ORDER BY  1

month	result	percentage
2021-01	0001	50
2021-01	0000	50
2021-02	00001	33.33
2021-02	0000	33.33
2021-02	0002	33.33

CodePudding user response：

This example is code on dbFiddle SQL server, but according to the documentation google-bigquery has the function COUNT( ~ ) OVER ( PARTITION BY ~ )

create table table_name(month char(7), result int)
insert into table_name values
('2021-01',50),
('2021-01',30),
('2021-01',20),
('2021-02',70),
('2021-02',80);

select 
  month, 
  result, 
  sum(result) over (partition by month) month_total, 
  100 * result / sum(result) over (partition by month) per_cent
from table_name
order by month, result;

month   | result | month_total | per_cent
:------ | -----: | ----------: | -------:
2021-01 |     20 |         100 |       20
2021-01 |     30 |         100 |       30
2021-01 |     50 |         100 |       50
2021-02 |     70 |         150 |       46
2021-02 |     80 |         150 |       53

db<>fiddle