添加“计算列".到BigQuery查询，而无需重复计算 [英] Adding a "calculated column" to BigQuery query without repeating the calculations

问题描述

我想在新的第三列中重新使用计算列的值.例如，此查询有效:

I want to resuse value of calculated columns in a new third column. For example, this query works:

select
  countif(cond1) as A,
  countif(cond2) as B,
  countif(cond1)/countif(cond2) as prct_pass
  From 
  Where
  Group By

但是当我尝试使用A，B而不是重复计数时，它不起作用，因为A和B是

But when I try to use A,B instead of repeating the countif, it doesn't work because A and B are invalid:

select
  countif(cond1) as A,
  countif(cond2) as B,
  A/B as prct_pass
  From 
  Where
  Group By

我可以以某种方式使更具可读性的第二版工作吗?这是第一个效率低下的人吗?

Can I somehow make the more readable second version work ? Is this first one inefficient ?

推荐答案

您应该像这样构建子查询(即，双重选择)

You should construct a subquery (i.e. a double select) like

SELECT A, B, A/B as prct_pass 
FROM 
(
SELECT countif(cond1) as A, 
       countif(cond2) as B 
       FROM <yourtable>
)

在两个查询中将处理相同数量的数据.在子查询中，您将只执行2 countif()，以防万一该步骤花费很长时间，然后执行2而不是4的确更为有效.

The same amount of data will be processed in both queries. In the subquery one you will do only 2 countif(), in case that step takes a long time then doing 2 instead of 4 should be more efficient indeed.

看一个使用bigquery公开数据集的示例:

Looking at an example using bigquery public datasets:

SELECT 
countif(homeFinalRuns>3) as A,
countif(awayFinalRuns>3) as B,
countif(homeFinalRuns>3)/countif(awayFinalRuns>3) as division 
FROM `bigquery-public-data.baseball.games_post_wide`  

或

SELECT A, B, A/B as division FROM 
(
SELECT countif(homeFinalRuns>3) as A, 
       countif(awayFinalRuns>3) as B 
       FROM `bigquery-public-data.baseball.games_post_wide`  
)

我们可以看到，一次完成所有操作(没有子查询)实际上要快一些.(对于不同的不等式值，我运行了6次查询，快5次，慢1次)

we can see that doing all in one (without a subquery) is actually slightly faster. (I ran the queries 6 times for different values of the inequality, 5 times was faster and one time slower)

无论如何，效率取决于在特定数据集中计算条件的负担方式.

In any case, the efficiency will depend on how taxing is to compute the condition in your particular dataset.

这篇关于添加“计算列".到BigQuery查询，而无需重复计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！