percentage per month Bigquery

user_11985999 0
Viewed 115

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
google-bigquery sql
2 Answers

Using the data that you shared as:

WITH data as(
SELECT "2021-01-01" as created_at,"0001" as result UNION ALL
SELECT "2021-01-01","0000" UNION ALL
SELECT "2021-02-01","00001"UNION ALL
SELECT "2021-02-01","0000"UNION ALL
SELECT "2021-02-01","0002"
)

I used a subquery to help you to deal with the month field and then use that field to partition by and then group them by month, and result.

d as (SELECT  CAST(TIMESTAMP_TRUNC(CAST((created_at) AS TIMESTAMP), MONTH) AS DATE) AS month,
 result, created_at
 from DATA
 )
SELECT d.month,
 d.result,
    count(*) * 100.0 / sum(count(1)) over(partition by month) as percentage
FROM d
 
GROUP BY  1, 2
ORDER BY  1

The output is the following:

enter image description here

user_169293580

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 here

Admin1
Related