sql - 大查询 : Divided by sum of values in column to find the ratio

Question

我有一个简单的表，有两列 Bin_name (int) 和 Count_in_this_bin (int)

我想将其转换为每个 bin 与所有 bin 中总计数的比率。

我在 Google BigQuery 中使用了以下查询:

SELECT count_in_bin/(SELECT SUM(count_in_bin) FROM [table])
FROM [table]

然后我得到

error:Query Failed Error: Subselect not allowed in SELECT clause

现在谁能告诉我在 BigQuery 中进行这种简单划分的正确方法？

Answer 1

BigQuery 旧版 SQL

#legacySQL
SELECT 
  count_in_bin, 
  RATIO_TO_REPORT(count_in_bin) OVER() AS ratio
FROM [project:dataset.table]

BigQuery 标准 SQL

#standardSQL
SELECT 
  count_in_bin, 
  count_in_bin / total AS ratio
FROM `project.dataset.table`, 
(SELECT SUM(count_in_bin) total FROM `project.dataset.table`)