如何在BigQuery中的标准SQL中实现RATIO_TO_REPORT()?
问题描述:
我有一个使用RATIO_TO_REPORT()遗留的SQL查询 - 它不使用开放式访问表,但是这是什么样子:如何在BigQuery中的标准SQL中实现RATIO_TO_REPORT()?
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),所以提示将非常感谢! THX
答
只需直接计算的比例:
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 ;