时间戳

首页 > 解决方案 > 无法正确查询 bigquery - 不支持引用其他表的相关子查询

sql - 无法正确查询 bigquery - 不支持引用其他表的相关子查询

问题描述

我有这样的桌子，

+---------+------------+-----------------+-----------------------------+
|   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               |
+---------+------------+-----------------+-----------------------------+

我想要所有平均水平的星星的平均值，所以做了这样的事情，

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

但我得到类似“不支持引用其他表的相关子查询”的信息。

所以我尝试了这个，

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)

我得到了无法识别的名字：level。我怎样才能正确有效地查询？

我试图产生的结果：

+---------+------------+
|  level  |    avg     |
+---------+------------+
|    1    |    16.0    |
+---------+------------+
|    2    |    100.0   |
+---------+------------+

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 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
)

结果 

Row avg_all_star     
1   58.0

推荐阅读