SQL script within subquery (Big Query)?

Question

Is there a way to have SQL scripts within a subquery ?

For example:

select col_1, col_2, count(*) from (
Declare max_radius INT64;
set max_radius = 250;

select * from my_table
where radius <  max_radius
)
group by col_1,col_2

In my case it is not possible to move the variable declaration outside of the subquery. Thanks !

Gordon Linoff · Accepted Answer · 2021-05-02 14:06:18Z

1

For this particular example, you could use a CTE to define the parameter values:

with params as (
      select 250 as max_radius
     )
select t.col_1, t.col_2, count(*)
from params p cross join
     my_table t
where t.radius < p.max_radius
group by t.col_1, t.col_2;

answered May 2, 2021 at 14:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yairh Over a year ago

Yes, this was my first though but if my_table is huge (and in reality it is) the cross join will have a very poor performance because it will duplicate the params for each row in mytable. And I wanted to workaround that problem by using a variable.

Gordon Linoff Over a year ago

@Yairh . . . The cross join should be trivial because there is only one row.

Collectives™ on Stack Overflow

SQL script within subquery (Big Query)?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related