Can I define an alias in the inner query and pass on to the outer query?

user_12065308 0
Viewed 114

Since the inner query executes first, I thought I could just define an alias for the table and pass it on to the outer query if it's the very same table. So I tried the below.

SELECT
  station_id,
  num_bikes_available,
  (SELECT
     AVG(num_bikes_available)
   FROM
     bigquery-public-data.new_yourk.citibike_stations AS stations
  ) AS avg_num_bikes_avlb
FROM
  stations

Unfortunately it won't recognize the alias stations. Is there a way to avoid typing table names repetitively in this case?

google-bigquery alias sql
1 Answers

No. The right way to do what you want uses a window function:

SELECT station_id, num_bikes_available,
       AVG(num_bikes_available) OVER () AS avg_num_bikes_avlb
FROM `bigquery-public-data.new_yourk.citibike_stations` stations;

A table alias represents a particular instance of a table reference. It cannot be used as another instance of reading the table. In any case, a correlated subquery is unnecessary and more expensive. The window function is the best method for this calculation.

user_11440350
Related