使用group BY计算组的百分比

Question

我正在对数据集进行GROUP BY和COUNT(*)，我想计算每个组在总数中的百分比。

例如，在这个查询中，我想知道每个状态的计数()比总数(select count()从publicdata:samples中表示多少。出生率):

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

有几种方法可以在SQL中做到这一点，但我还没有找到在Bigquery中做到这一点的方法，有人知道吗?

谢谢!

Answer 1

修改Felipe对标准SQL BigQuery方言而不是Legacy SQL方言的回答如下:

select state, 100*(state_count / total) as pct
from (
  SELECT state, count(*) AS state_count, sum(count(*)) OVER() AS total
  FROM `bigquery-public-data.samples.natality` 
  GROUP by state
) s

标准SQL BigQuery聚合分析函数(又名"窗口函数")的文档在这里:https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

Answer 2

查看最近发布的窗口函数之一ratio_to_report:

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

Answer 3

您可以使用窗口函数来按组获取总数的百分比，而不需要子查询(改进evan_b的解决方案):

SELECT 
   state
   ,count(*) / (sum(count(*)) OVER()) as pct
FROM  
   `bigquery-public-data.samples.natality` 
GROUP BY 
   state

Answer 4

您可以使用一个虚拟值作为键，对总数执行自连接。例如:

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

Answer 5

当使用Johnny V的解决方案时，它为我返回频率。对于计算实际百分比，我发现添加一个简单的*100可以工作:

SELECT
  sex
  ,COUNT(*) / (SUM(COUNT(*))OVER()) * 100 AS percentage
FROM `powerful-hall-355408.comic_characters_wikia.dc_comics` 
GROUP BY sex