google-bigquery - Google Bigquery Legacy SQL - 如果没有返回结果，如何返回 null 或零？

Question

假设我有下表

month    region   revenue   
------  -------- ---------- 
 jan     north      100
 feb     north      150
 mar     north      250

我如何查询上表以获得以下结果？:

month    region   revenue   
------  -------- ---------- 
 jan     north      100
 feb     north      150
 mar     north      250
 apr     north       0
 may     north       0
 jun     north       0

0 可以是 null，反之亦然。本质上是尝试将空/空字段添加到我的查询中(在本例中为 apr、may、jun 行)。任何帮助将不胜感激

谢谢

Answer 1

以下内容适用于 BigQuery 旧版 SQL，但请注意 - 强烈建议 BigQuery 团队迁移到 BigQuery Standard SQL

下面的例子应该可以给你一个想法

#legacySQL
SELECT 
  months.month_abr AS month_abr, 
  regions.region AS region, 
  COALESCE(revenues.revenue, 0) revenue
FROM months
CROSS JOIN (
  SELECT region FROM revenues
) regions
LEFT JOIN revenues
ON months.month_abr = revenues.month_abr
AND regions.region = revenues.region
-- ORDER BY regions.region, months.month_number

其中revenues是包含收入数据的原始表，month是包含月份列表的表(或者您可以使用下面示例中的子查询)

您可以使用下面的示例以及问题中的虚拟数据来测试/玩上面的内容

#legacySQL
SELECT 
  months.month_abr AS month_abr, 
  regions.region AS region, 
  COALESCE(revenues.revenue, 0) revenue
FROM (
  SELECT month_number, month_abr FROM 
  (SELECT 1 month_number, 'jan' month_abr),
  (SELECT 2 month_number, 'feb' month_abr),
  (SELECT 3 month_number, 'mar' month_abr),
  (SELECT 4 month_number, 'apr' month_abr),
  (SELECT 5 month_number, 'may' month_abr),
  (SELECT 6 month_number, 'jun' month_abr)  
) AS months
CROSS JOIN (
  SELECT region FROM (
    SELECT region FROM 
    (SELECT 'jan' month_abr, 'north' region, 100 revenue),
    (SELECT 'feb' month_abr, 'north' region, 150 revenue),
    (SELECT 'mar' month_abr, 'north' region, 250 revenue)
  ) GROUP BY region
) regions
LEFT JOIN (
  SELECT month_abr, region, revenue FROM 
  (SELECT 'jan' month_abr, 'north' region, 100 revenue),
  (SELECT 'feb' month_abr, 'north' region, 150 revenue),
  (SELECT 'mar' month_abr, 'north' region, 250 revenue)
) AS revenues
ON months.month_abr = revenues.month_abr
AND regions.region = revenues.region
ORDER BY regions.region, months.month_number

结果如下

Row month_abr   region  revenue  
1   jan         north   100  
2   feb         north   150  
3   mar         north   250  
4   apr         north   0    
5   may         north   0    
6   jun         north   0    

最后 - 下面是 BigQuery Standard SQL 的外观

#standardSQL
WITH regions AS (
  SELECT DISTINCT region FROM revenues
), months AS (
SELECT EXTRACT(MONTH FROM month) month_number,
  LOWER(FORMAT_DATE('%b', month)) month_abr
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2010-01-01', DATE '2010-12-01', INTERVAL 1 MONTH)) month
)
SELECT month_abr, region, COALESCE(revenues.revenue, 0) revenue
FROM months
CROSS JOIN regions
LEFT JOIN revenues
USING(month_abr, region)
ORDER BY region, month_number

您可以使用问题中的虚拟数据进行测试、玩转

#standardSQL
WITH revenues AS (
  SELECT 'jan' month_abr, 'north' region, 100 revenue UNION ALL
  SELECT 'feb', 'north', 150 UNION ALL
  SELECT 'mar', 'north', 250 
), regions AS (
  SELECT DISTINCT region FROM revenues
), months AS (
SELECT EXTRACT(MONTH FROM month) month_number,
  LOWER(FORMAT_DATE('%b', month)) month_abr
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2010-01-01', DATE '2010-12-01', INTERVAL 1 MONTH)) month
)
SELECT month_abr, region, COALESCE(revenues.revenue, 0) revenue
FROM months
CROSS JOIN regions
LEFT JOIN revenues
USING(month_abr, region)
ORDER BY region, month_number

您应该能够将上述内容应用于您的实际用例