如何在BigQuery的标准SQL中编写左连接？
如何在BigQuery的标准SQL中编写左连接？
提问于 2016-12-26 21:07:23
我们有一个可以在BigQuery的Legacy SQL中工作的查询。我们如何用标准SQL编写它，使其正常工作？
SELECT Hour, Average, L.Key AS Key FROM
(SELECT 1 AS Key, *
FROM test.table_L AS L)
LEFT JOIN
(SELECT 1 AS Key, Avg(Total) AS Average
FROM test.table_R) AS R
ON L.Key = R.Key ORDER BY Hour ASC目前它给出的错误是：
Equality is not defined for arguments of type ARRAY<INT64> at [4:74]BigQuery有两种查询模式:遗留SQL和标准SQL。我们已经看过了BigQuery Standard SQL documentation，也只看到了one SO answer on Standard SQL在BigQuery中的连接--但到目前为止，我们还不清楚需要做什么关键的改变。
Table_L看起来像这样：
Row Hour
1 A
2 B
3 CTable_R看起来像这样：
Row Value
1 10
2 20
3 30期望的结果：
Row Hour Average(OfR) Key
1 A 20 1
2 B 20 1
3 C 20 1我们如何重写这个SQL查询以在标准BigQuery中工作？
回答 2
Stack Overflow用户
回答已采纳
发布于 2016-12-27 02:51:06
根据您最近的问题更新和评论-请尝试以下内容
WITH Table_L AS (
SELECT 1 AS Row, 'A' AS Hour UNION ALL
SELECT 2 AS Row, 'B' AS Hour UNION ALL
SELECT 3 AS Row, 'C' AS Hour
),
Table_R AS (
SELECT 1 AS Row, 10 AS Value UNION ALL
SELECT 2 AS Row, 20 AS Value UNION ALL
SELECT 3 AS Row, 30 AS Value
)
SELECT
Row,
Hour,
(SELECT AVG(Value) FROM Table_R) AS AverageOfR,
1 AS Key
FROM Table_L 上面是测试用的
您应该在"production“中运行的查询是
SELECT
Row,
Hour,
(SELECT AVG(Value) FROM Table_R) AS AverageOfR,
1 AS Key
FROM Table_L 如果由于某种原因，您需要加入，请使用下面的交叉加入版本
SELECT
Row,
Hour,
AverageOfR,
1 AS Key
FROM Table_L
CROSS JOIN ((SELECT AVG(Value) AS AverageOfR FROM Table_R))或者下面的包含Key字段的左连接版本(如果Key对你的逻辑真的很重要--不知何故我觉得这是真的)
SELECT
Row,
Hour,
AverageOfR,
L.Key AS Key
FROM (SELECT 1 AS Key, Row, Hour FROM Table_L) AS L
LEFT JOIN ((SELECT 1 AS Key, AVG(Value) AS AverageOfR FROM Table_R)) AS R
ON L.Key = R.KeyStack Overflow用户
发布于 2016-12-26 21:16:21
您的错误消息表明key不是table_L中的列。如果不是，则不要将其包含在查询中。
看起来您只是想要table_R的总数的平均值。你可以这样做：
SELECT l.*, r.average
FROM test.table_L as l CROSS JOIN
(SELECT Avg(Total) as average
FROM test.table_R
) R
ORDER BY l.hour ASC;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41331767
复制相关文章
相似问题
腾讯云开发者
