BigQuery - 使用子查询和OR语句加入多个条件 [英] BigQuery - Joining on multiple conditions using subqueries and OR statements

问题描述

有没有办法在多种潜在条件下加入两个表？

目前我正在将一些代码从Postgres迁移到Bigquery，我加入了多个潜在的值，如：

 选择
 * 
从
（
 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 
或offer_table.email = customer_table.email 
或offer_table.phone = customer_table.phone 
）
）dummy 
 WHERE 
 customer_visit_rank = 1 
  

<我需要这样做，因为我的报价和客户数据对我们的ID，电子邮件和电话字段的使用不一致，但都是有效的潜在匹配。如果有多个字段有效（例如：id和email匹配），那么会出现重复的行，我会在使用ORDER BY部分进行排名之后，根据row_number列将其过滤掉。

然而，当我尝试在BigQuery中加入多个条件时，我收到以下错误消息：

无法使用LEFT OUTER JOIN条件是连接两边的字段是平等的。

有没有人想出了一个解决方案来连接多个值而不是你可以编写单独的查询，然后使用 COALESCE ：

 选择
 * 
 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_c ount 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 
  

Is there anyway to join two tables on multiple potential conditions?

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

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.

However when I try to join on multiple conditions in BigQuery, I get this error message:

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

Has anyone figured out a solution to join on multiple values instead of doing the above?

解决方案

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屋！