本文介绍了如何在BigQuery中的标准SQL中实现RATIO_TO_REPORT()?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个使用RATIO_TO_REPORT()的遗留SQL查询 - 它不使用开放式访问表,但这是它的样子:
我试图迁移从传统SQL到标准SQL(在使用BigQuery之前从未使用SQL),所以提示将非常感谢!只需直接计算比例即可: 解决方案 SELECT Mutation_AA, Gene_name, CaseCount,(CaseCount / SUM(CaseCount)OVER(PARTITION BY Gene_name))AS比率。 。 。您不需要子查询:
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)作为比例 FROM [isb-cgc:COSMIC.grch38_v79] GROUP BY Mutation_AA,Gene_name;
I have a legacy SQL query that uses RATIO_TO_REPORT() -- it doesn't use open-access tables, but this is what it looks like:
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 )I'm trying to migrate from legacy SQL to standard SQL (never having used SQL prior to using BigQuery), so tips would be much appreciated! thx
解决方案Just directly calculate the ratio:
SELECT Mutation_AA, Gene_name, CaseCount, (CaseCount / SUM(CaseCount) OVER (PARTITION BY Gene_name)) AS ratio . . .You don't need the subquery:
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 ;
更多推荐
如何在BigQuery中的标准SQL中实现RATIO
发布评论