如何在BigQuery中的标准SQL中实现RATIO_TO_REPORT（）？ [英] how to implement RATIO_TO_REPORT() in standard SQL in BigQuery?

问题描述

我有一个使用RATIO_TO_REPORT（）的遗留SQL查询 - 它不使用开放式访问表，但这是它的样子：

 SELECT 
 Mutation_AA，
 Gene_name，
 CaseCount，
 RATIO_TO_REPORT（CaseCount）OVER（PARTITION BY Gene_name）AS比例
 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比率
。 。 。 
  

您不需要子查询：

  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_TO_REPORT（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！