CODEWITHSUNDEEP
google-bigquery
Radek Michna
Radek Michna

Reputation: 509

Google BigQuery; use subselect result in outer select; cross join

I have query that results into one row table and I need to get this result in subsequent computation. Here is non working simplified example (just to depict what I'm trying to achieve):

SELECT amount / (SELECT SUM(amount) FROM [...]) FROM [...]

I tried some nested sub-selects and joins (cross join of the one row table with the other table) but didn't find any working solution. Is there a way to get this working in BigQuery?

Thanks, Radek

EDIT:

ok, I found solution:

select
  t1.x / t2.y as z
from
  (select 1 as k, amount as x from [...] limit 10) as t1
join
  (select 1 as k, sum(amount) as y from [...]) as t2
on
  t1.k = t2.k;

but not sure if this is the best how to do it...

Upvotes: 1

Views: 1971

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

With the recently announced ratio_to_report() window function:

 SELECT RATIO_TO_REPORT(amount) OVER() AS z
 FROM [...]

ratio_to_report takes the amount, and divides it by the sum of all the result rows amounts.

Upvotes: 1

Jordan Tigani
Jordan Tigani

Reputation: 26617

The way you've found (essentially a cross join using a dummy key) is the best way I know of to do this query. We've thought about adding an explicit cross join operator to make it easier to see how to do this, but cross join can get expensive if not done correctly (e.g. if done on two large tables can create n^2 results).

Upvotes: 0

Related Questions