google-bigquery - 如何在BigQuery中的标准SQL中实现RATIO_TO_REPORT()？

我有一个使用RATIO_TO_REPORT()的旧式SQL查询-它不使用开放访问表，但这是这样的:

SELECT
  Mutation_AA,
  Gene_name,
  CaseCount,
  RATIO_TO_REPORT(CaseCount) OVER (PARTITION BY Gene_name) AS ratio
FROM (
  SELECT
    COUNT(DISTINCT ID_tumour, 50000) AS CaseCount,
    Mutation_AA,
    Gene_name
  FROM
    [isb-cgc:COSMIC.grch38_v79]
  GROUP BY
    Mutation_AA,
    Gene_name )

我正在尝试从旧版SQL迁移到标准SQL(在使用BigQuery之前从未使用过SQL)，因此，技巧将不胜感激!谢谢

最佳答案

只需直接计算比例:

SELECT Mutation_AA,
       Gene_name,
       CaseCount,
       (CaseCount / SUM(CaseCount) OVER (PARTITION BY Gene_name)) AS ratio
. . .

您不需要子查询:

SELECT Mutation_AA, Gene_name,
       COUNT(DISTINCT ID_tumour, 50000) AS CaseCount,
       COUNT(DISTINCT ID_tumour, 50000) / SUM(COUNT(DISTINCT ID_tumour, 50000)) OVER (PARTITION BY Gene_Name) as ratio
FROM [isb-cgc:COSMIC.grch38_v79]
GROUP BY Mutation_AA, Gene_name ;