SQL:连接时的级联条件
SQL:连接时的级联条件
提问于 2018-06-08 03:22:28
我在SO上发现了一些类似的问题，但没有适用于我的情况。
我在表1中有一个包含数亿行的大型数据集，正在寻找运行以下查询的最有效方法。我正在使用Google BigQuery，但我认为这是一个适用于任何DBMS的通用SQL问题？
我需要将所有者应用于表1中的每一行。我希望加入以下优先级：
1:如果item_id与表2中的标识符匹配
2:如果没有匹配的item_id，请在item_name上尝试匹配
3:如果没有匹配的item_id或item_name，请在item_division上尝试匹配
4:如果没有匹配的item_division，则返回null
表1-数据点：
| id | item_id | item_name | item_division | units | revenue
|----|---------|-----------|---------------|-------|---------
| 1 | xyz | pen | UK | 10 | 100
| 2 | pqr | cat | US | 15 | 120
| 3 | asd | dog | US | 12 | 105
| 4 | xcv | hat | UK | 11 | 140
| 5 | bnm | cow | UK | 14 | 150表2-标识符：
| id | type | code | owner |
|----|---------|-----------|-------|
| 1 | id | xyz | bob |
| 2 | name | cat | dave |
| 3 | division| UK | alice |
| 4 | name | pen | erica |
| 5 | id | xcv | fred | 所需输出：
| id | item_id | item_name | item_division | units | revenue | owner |
|----|---------|-----------|---------------|-------|---------|-------|
| 1 | xyz | pen | UK | 10 | 100 | bob | <- id
| 2 | pqr | cat | US | 15 | 120 | dave | <- code
| 3 | asd | dog | US | 12 | 105 | null | <- none
| 4 | xcv | hat | UK | 11 | 140 | fred | <- id
| 5 | bnm | cow | UK | 14 | 150 | alice | <- division到目前为止，我的尝试包括多次将表加入到自己的表中，我担心这将变得非常低效。
任何帮助都非常感谢。
回答 3
Stack Overflow用户
发布于 2018-06-09 07:35:15
BigQuery标准SQL的另一种选择
#standardSQL
SELECT ARRAY_AGG(a)[OFFSET(0)].*,
ARRAY_AGG(owner
ORDER BY CASE
WHEN type = 'id' THEN 1
WHEN type = 'name' THEN 2
WHEN type = 'division' THEN 3
END
LIMIT 1
)[OFFSET(0)] owner
FROM Datapoints a
JOIN Identifiers b
ON (a.item_id = b.code AND b.type = 'id')
OR (a.item_name = b.code AND b.type = 'name')
OR (a.item_division = b.code AND b.type = 'division')
GROUP BY a.id
ORDER BY a.id 它省略了下面结果中没有所有者的条目(id=3因为没有所有者而退出)
Row id item_id item_name item_division units revenue owner
1 1 xyz pen UK 10 100 bob
2 2 pqr cat US 15 120 dave
3 4 xcv hat UK 11 140 fred
4 5 bnm cow UK 14 150 alice Stack Overflow用户
发布于 2018-06-08 03:46:29
我正在使用以下查询(感谢@Barmar)，但想知道在谷歌BigQuery中是否有更有效的方法：
SELECT a.*, COALESCE(b.owner,c.owner,d.owner) owner FROM datapoints a
LEFT JOIN identifiers b on a.item_id = b.code and b.type = 'id'
LEFT JOIN identifiers c on a.item_name = c.code and c.type = 'name'
LEFT JOIN identifiers d on a.item_division = d.code and d.type = 'division'Stack Overflow用户
发布于 2018-06-08 04:08:25
我不确定现在BigQuery是否优化了像这样的查询--但至少你会写一个强烈提示不要在不需要的时候运行子查询的查询：
#standardSQL
SELECT COALESCE(
null
, (SELECT MIN(payload)
FROM `githubarchive.year.2016`
WHERE actor.login=a.user)
, (SELECT MIN(payload)
FROM `githubarchive.year.2016`
WHERE actor.id = SAFE_CAST(user AS INT64))
)
FROM (SELECT '15229281' user) a
4.2s elapsed, 683 GB processed
{"action":"started"} 例如，下面的查询需要很长时间才能运行，但是BigQuery可以在将来大量优化它的执行(取决于用户需要这样的操作的频率)：
#standardSQL
SELECT COALESCE(
"hello"
, (SELECT MIN(payload)
FROM `githubarchive.year.2016`
WHERE actor.login=a.user)
, (SELECT MIN(payload)
FROM `githubarchive.year.2016`
WHERE actor.id = SAFE_CAST(user AS INT64))
)
FROM (SELECT actor.login user FROM `githubarchive.year.2016` LIMIT 10) a
114.7s elapsed, 683 GB processed
hello
hello
hello
hello
hello
hello
hello
hello
hello
hello页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50748783
复制相关文章
相似问题