Can't query bigquery correctly - Correlated subqueries that reference other tables are not supported

user_5532156 0
Viewed 124

I have table something like this,

+---------+------------+-----------------+-----------------------------+
|   id    | event_name | event_params.key| event_params.value.int_value|
+---------+------------+-----------------+-----------------------------+
|    1    |   click    |      stars      |            12               |
+---------+------------+-----------------+-----------------------------+
|         |            |     level       |             1               |
+---------+------------+-----------------+-----------------------------+
|    5    |   click    |      stars      |            20               |
+---------+------------+-----------------+-----------------------------+
|         |            |     level       |             1               |
+---------+------------+-----------------+-----------------------------+
|    8    |   click    |      stars      |            100              |
+---------+------------+-----------------+-----------------------------+
|         |            |     level       |             2               |
+---------+------------+-----------------+-----------------------------+

I want average of all average of stars in level, so did some thing like this,

SELECT level,
     (SELECT AVG((
        SELECT CAST(d.value.string_value as INT64) 
        FROM UNNEST(event_params)as d 
        WHERE (d.key = "stars"
      ))) as avg_star_at_level 
      FROM `table1`,UNNEST(event_params) as h 
      WHERE event_name = "click" AND (h.key = "level") AND
            h.value.int_value = level)
FROM UNNEST(GENERATE_ARRAY(1,100)) as level

But I get something like this "Correlated subqueries that reference other tables are not supported".

So I tried this,

SELECT level,avg_token_at_level 
FROM UNNEST(GENERATE_ARRAY(1,100)) as level,
     (SELECT AVG((
        SELECT CAST(d.value.string_value as INT64) 
        FROM UNNEST(event_params)as d 
        WHERE (d.key = "stars"
      ))) as avg_star_at_level 
      FROM `table1`,UNNEST(event_params) as h 
      WHERE event_name = "click" AND (h.key = "level") 
      AND h.value.int_value = level)

I got Unrecognized name: level. How could I query correctly and efficiently?

Result I am trying to produce:

+---------+------------+
|  level  |    avg     |
+---------+------------+
|    1    |    16.0    |
+---------+------------+
|    2    |    100.0   |
+---------+------------+
google-bigquery sql
1 Answers

Below is for BigQuery Standard SQL 

#standardSQL
SELECT AVG(avg_star_at_level) avg_all_star FROM (
  SELECT 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'level') level,
    AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'stars')) avg_star_at_level
  FROM `project.dataset.table1` 
  GROUP BY level
)

with result 

Row avg_all_star     
1   58.0
user_52219440
Related