BigQuery - 使用子查询和 OR 语句连接多个条件 [英] BigQuery - Joining on multiple conditions using subqueries and OR statements
问题描述
无论如何要在多个潜在条件下连接两个表吗?
Is there anyway to join two tables on multiple potential conditions?
我目前正在将一些代码从 Postgres 迁移到 Bigquery,在那里我加入了多个潜在值,例如:
I'm currently migrating some code from Postgres to Bigquery where I joined on multiple potential values like:
SELECT
*
FROM
(
SELECT
offer_table.offer_id
,customer_table.customer_name
,customer_table.visit_count
,ROW_NUMBER() OVER (PARTITION BY offer_table.offer_id ORDER BY customer_table.visit_count DESC) AS customer_visit_rank
FROM
offer_table
LEFT JOIN customer_table ON
(
offer_table.customer_id = customer_table.customer_id
OR offer_table.email = customer_table.email
OR offer_table.phone = customer_table.phone
)
) dummy
WHERE
customer_visit_rank = 1
我需要这样做是因为我的报价和客户数据在我们的 ID、电子邮件和电话字段的使用不一致,但都是有效的潜在匹配项.如果多个字段都有效(例如:id 和 email 匹配),则会出现重复的行,我会在使用 ORDER BY 部分进行排名后根据 row_number 列将它们过滤掉.
I needed to this because my offer and customer data had inconsistent usage of our id, email, and phone fields but all were valid potential matches. If multiple fields worked (ex: id and email matched), there would be duplicate rows and I'd filter them out based on the row_number column after ranking using the ORDER BY section.
但是,当我尝试在 BigQuery 中加入多个条件时,我收到此错误消息:
However when I try to join on multiple conditions in BigQuery, I get this error message:
LEFT OUTER JOIN 不能在没有条件是连接两侧的字段相等的情况下使用.
有没有人想出一个解决方案来连接多个值而不是执行上述操作?
Has anyone figured out a solution to join on multiple values instead of doing the above?
推荐答案
你可以写单独的查询,然后使用COALESCE
:
You can write separate queries, then use COALESCE
:
SELECT
*
FROM
(
SELECT
offer_table.offer_id
,COALESCE(c1.customer_name,c2.customer_name,c3.customer_name)
,COALESCE(c1.visit_count,c2.visit_count,c3.visit_count)
,ROW_NUMBER() OVER (PARTITION BY offer_table.offer_id ORDER BY customer_table.visit_count DESC) AS customer_visit_rank
FROM
offer_table
LEFT JOIN customer_table c1
ON offer_table.customer_id = customer_table.customer_id
LEFT JOIN customer_table c2
ON offer_table.email = customer_table.email
LEFT JOIN customer_table c3
ON offer_table.phone = customer_table.phone
)
) AS dummy
WHERE
customer_visit_rank = 1
这篇关于BigQuery - 使用子查询和 OR 语句连接多个条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!