All geek questions in one place

Calculate group percentage using GROUP BY

I am doing GROUP BY and COUNT (*) in a dataset, and I would like to calculate the percentage of each group compared to the total.

For example, in this query, I would like to know how many count () for each state represents the sum (select count () from publicdata: samples.natality):

SELECT state, count(*) FROM [publicdata:samples.natality] GROUP by state

There are several ways to do this in SQL, but I have not found a way to do this in Bigquery, does anyone know?

Thanks!

+6
source share
2 answers

Check ratio_to_report, one of the recently announced window features:

 SELECT state, ratio * 100 AS percent FROM ( SELECT state, count(*) AS total, RATIO_TO_REPORT(total) OVER() AS ratio FROM [publicdata:samples.natality] GROUP by state ) state percent AL 1.4201828131159113 AK 0.23521048665998198 AZ 1.3332896746620975 AR 0.7709591206172346 CA 10.008298605982642
+12
source

You can do a self-join against the total by using a dummy value as a key. For instance:

 SELECT t1.state AS state, t1.cnt AS cnt, 100 * t1.cnt / t2.total as percent FROM ( SELECT state, COUNT(*) AS cnt, 1 AS key FROM [publicdata:samples.natality] WHERE state is not null GROUP BY state) AS t1 JOIN ( SELECT COUNT(*) AS total, 1 AS key FROM [publicdata:samples.natality]) AS t2 ON t1.key = t2.key ORDER BY percent DESC
+3
source

Source: https://habr.com/ru/post/946569/

More articles:


All Articles