ARRAY JOIN场景下将NULL转换为0的SQL实现方案问询

我有一张由Table_A与数组表Table_B关联生成的表，表中存在NULL值，希望将这些NULL替换为0。现有SQL代码使用了带ignore nulls的array_agg且无法移除该参数，需要解决转换问题。

当前表 #

Date  | Sessions | ID          | City
------+----------+-------------+-------------
06-02 | 1        | 107         | Cardiff
      |          | 102         | Paris
06-03 | NULL     | NULL        | NULL
11-12 | 1        | 105         | Amsterdam
      |          | 107         | Cardiff
      |          | 103         | Rome
27-06 | NULL     | NULL        | NULL

期望输出表 #

Date  | Sessions | ID          | City
------+----------+-------------+-------------
06-02 | 1        | 107         | Cardiff
      |          | 102         | Paris
06-03 | 0        | 0           | 0
11-12 | 1        | 105         | Amsterdam
      |          | 107         | Cardiff
      |          | 103         | Rome
27-06 | 0        | 0           | 0

当前SQL代码 #

select date, Sessions,
    array_agg(a.ID ignore nulls) as ID, array_agg(City ignore nulls) as City
  from Table_B b, unnest (ID) as ID_un
  left join Table_A a on ID_un = cast(a.ID as string)
  group by 1, 2
             ...

解决方案 #

要实现需求，我们可以在聚合结果外层增加判断逻辑，同时对Sessions字段直接替换NULL：

SELECT 
  date,
  COALESCE(Sessions, 0) AS Sessions,
  -- 处理ID字段：聚合结果为空时返回[0]，否则保留原结果
  CASE 
    WHEN array_agg(a.ID ignore nulls) = [] THEN [0]
    ELSE array_agg(a.ID ignore nulls) 
  END AS ID,
  -- 处理City字段：聚合结果为空时返回['0']，否则保留原结果（City为字符串类型）
  CASE 
    WHEN array_agg(City ignore nulls) = [] THEN ['0']
    ELSE array_agg(City ignore nulls) 
  END AS City
FROM Table_B b, unnest (ID) as ID_un
LEFT JOIN Table_A a ON ID_un = cast(a.ID as string)
GROUP BY 1, 2

逻辑说明 #

• COALESCE(Sessions, 0)：直接将Sessions中的NULL值替换为0。
• 对于ID和City的聚合结果：因为array_agg(ignore nulls)会过滤掉所有NULL值，当没有匹配到有效数据时会得到空数组，此时我们返回包含0（或字符串'0'）的数组；如果聚合结果不为空，则保留原聚合值。

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