BigQuery中的动态条件联接

Question

我试图在BigQuery中编写一个动态条件语句，以便根据特定条件动态地连接表。一个ID有一个或多个条件是可能的。我如何动态地“过滤”一系列ID条件(使用表1、表2和表3)到最后一个表中的一组masterProductIds？

表1- identification_table

​

​

表2- filing_table

​

​

表3-价格

​

​

在这种情况下，我们对ID1、ID2和ID3有三个条件要评估。

在我们也有价格条件的情况下，它应该加入到价格表中，并根据表1中的运算符和值进行筛选。至于产品条件，我不需要在任何表上加入，我只是取表1中的值。

我期待的是我的最终结果。

终表

​

​

我现在所做的是：

select masterProductId, row_number() over (partition by id ) sq
from `filing_table` p 
left join `identification_table` pc
on case when subject = 'brand' then p.brandName when subject='category' then categoryName end = pc.boundaryValue
--on p.brandName = pc.boundaryValue or p.categoryName = boundaryValue
left join `price` pp
on p.code = pp.code
where 1=1
and pc.code = 'Id 2'
--and pp.RRP < safe_cast(pc1.boundaryValue as float64)

这将对所有事物进行整体评估。我想不出如何用set来计算I。

Answer 1

下面是BigQuery标准SQL，并假设表设置如下(在对问题的注释中得到OP的同意)

identification_table

  SELECT 'Id 1' id, 'masterProductId' subject, '=' operator, '1007' value UNION ALL
  SELECT 'Id 1', 'brandName', '=', 'brand p' UNION ALL
  SELECT 'Id 1', 'categoryName', '=', 'category 1' UNION ALL
  SELECT 'Id 2', 'categoryName', '=', 'category 1' UNION ALL
  SELECT 'Id 2', 'price', '<', '130'  UNION ALL
  SELECT 'Id 3', 'categoryName', '=', 'category 3' 

filing_table

  SELECT 11 code, 'category 1' categoryName, 'brand p' brandName, 1001 masterProductId UNION ALL
  SELECT 22, 'category 1', 'brand z', 1002 UNION ALL
  SELECT 33, 'category 2', 'brand c', 1003 UNION ALL
  SELECT 44, 'category 2', 'brand v', 1004 UNION ALL
  SELECT 55, 'category 3', 'brand e', 1005

price

  SELECT 11 code, 3 price UNION ALL
  SELECT 22, 100 UNION ALL
  SELECT 33, 8 UNION ALL
  SELECT 44, 9 UNION ALL
  SELECT 77, 28 

因此，下面从filing_table摘录那些基于identification_table的所有标准的masterProductId。

EXECUTE IMMEDIATE '''
SELECT masterProductId
FROM (
  SELECT f.*, price
  FROM `filing_table` f
  LEFT JOIN `price` p
  USING(code)
)
WHERE ''' || (  
  SELECT STRING_AGG('(' || condition || ')', ' OR ')
  FROM (
    SELECT STRING_AGG(FORMAT('(%s %s %s)', subject, operator, value), ' AND ') condition
    FROM `identification_table`,
    UNNEST([IF(subject IN ('price', 'masterProductId'), value, '"' || value || '"')]) value
    GROUP BY id
  ));

如果要应用于示例数据，如答案输出的顶部，则为

Row masterProductId  
1   1001     
2   1002     
3   1005