sql - 如何在 BigQuery 中进行分区外连接

Question

我想在 BigQuery 中实现分区外连接。举一个具体的例子，我想实现分区外连接作为这里接受的答案:https://dba.stackexchange.com/questions/227069/what-is-a-partitioned-outer-join

我知道有很多关于这个主题的讨论，但我无法让它在 BigQuery 下工作。我在左表后面添加了 partition by date，语法如下:

select * from ( 
 select '2019-01-17' as date, 'London' as location, 11 as qty   
 union all   
 select '2019-01-15' as date, 'London' as location, 10 as qty   
 union all   
 select '2019-01-16' as date, 'Paris' as location, 20 as qty   
 union all   
 select '2019-01-17' as date, 'Boston' as location, 31 as qty   
 union all   
 select '2019-01-16' as date, 'Boston' as location, 30 as qty 
) as sales partition by (date)  
right join 
(   
 select 'London' as location   
 union all   
 select 'Paris' as location   
 union all   
 select 'Boston' as location   
) 
as loc 
using (location)

我要找的目标结果是:

date      qty    location  
15-JAN-19  NULL  Boston  
15-JAN-19  10    London  
15-JAN-19  NULL  Paris   
16-JAN-19  30    Boston  
16-JAN-19  NULL  London  
16-JAN-19  20    Paris   
17-JAN-19  31    Boston  
17-JAN-19  11    London  
17-JAN-19  NULL  Paris 

但我收到以下错误:语法错误:[11:12] 处的意外关键字分区

如何在 BigQuery 中实现它？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT `date`, qty, location 
FROM (SELECT DISTINCT `date` FROM sales)
CROSS JOIN loc 
LEFT JOIN sales
USING (`date`, location)

您可以使用问题中的样本数据进行测试，如以下示例所示

#standardSQL
WITH sales AS (
 SELECT '2019-01-17' AS `date`, 'London' AS location, 11 AS qty UNION ALL   
 SELECT '2019-01-15', 'London', 10 UNION ALL   
 SELECT '2019-01-16', 'Paris', 20 UNION ALL   
 SELECT '2019-01-17', 'Boston', 31 UNION ALL   
 SELECT '2019-01-16', 'Boston', 30 
), loc AS (   
 SELECT 'London' AS location UNION ALL   
 SELECT 'Paris' UNION ALL   
 SELECT 'Boston' 
) 
SELECT `date`, qty, location 
FROM (SELECT DISTINCT `date` FROM sales)
CROSS JOIN loc 
LEFT JOIN sales
USING (`date`, location)
-- ORDER BY `date`, location    

结果如下

Row date        qty     location     
1   2019-01-15  null    Boston   
2   2019-01-15  10      London   
3   2019-01-15  null    Paris    
4   2019-01-16  30      Boston   
5   2019-01-16  null    London   
6   2019-01-16  20      Paris    
7   2019-01-17  31      Boston   
8   2019-01-17  11      London   
9   2019-01-17  null    Paris    

如果您需要日期为 15-JAN-19 格式 - 您在下面

#standardSQL
SELECT FORMAT_DATE('%d-%b-%y', CAST(`date` AS DATE)) AS `date`, qty, location 
FROM (SELECT DISTINCT `date` FROM sales)
CROSS JOIN loc 
LEFT JOIN sales
USING (`date`, location)

所以结果会是

Row date        qty     location     
1   15-Jan-19   null    Boston   
2   15-Jan-19   10      London   
3   15-Jan-19   null    Paris    
4   16-Jan-19   30      Boston   
5   16-Jan-19   null    London   
6   16-Jan-19   20      Paris    
7   17-Jan-19   31      Boston   
8   17-Jan-19   11      London   
9   17-Jan-19   null    Paris