SQL查询中如何将缺失Bucket的items_count填充为0？

以包含所有Bucket的all_buckets作为主表，使用LEFT JOIN关联统计数据，再通过COALESCE或IFNULL将缺失的计数转为0，即可得到符合预期的结果。

示例数据 #

WITH
all_buckets AS (
  SELECT
    *
  FROM
    UNNEST([
      'A',
      'B',
      'C',
      'D',
      'E'
    ]) AS buckets
),
test_data AS (
SELECT
  'A' as bucket, 10 as items_count
  UNION ALL    
SELECT
  'C' as bucket, 1 as items_count
  UNION ALL
SELECT  
  'E' as bucket, 100 as items_count
)

无效语句问题分析 #

你之前用CROSS JOIN后加WHERE过滤，本质等同于INNER JOIN，只会保留两边匹配的Bucket，自然拿不到缺失的B、D；若选错主表，LEFT/RIGHT JOIN也无法覆盖全部Bucket。

正确查询语句 #

WITH
all_buckets AS (
  SELECT
    *
  FROM
    UNNEST(['A', 'B', 'C', 'D', 'E']) AS buckets
),
test_data AS (
  SELECT 'A' as bucket, 10 as items_count
  UNION ALL    
  SELECT 'C' as bucket, 1 as items_count
  UNION ALL
  SELECT 'E' as bucket, 100 as items_count
)
SELECT
  ab.buckets AS bucket,
  COALESCE(td.items_count, 0) AS items_count
FROM
  all_buckets ab
LEFT JOIN
  test_data td
ON
  ab.buckets = td.bucket
ORDER BY
  ab.buckets

输出结果 #

A, 10
B, 0
C, 1
D, 0
E, 100

内容的提问来源于stack exchange，提问作者Rod0n