我有一个使用 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 ;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)