SQL Select语句中部分值为NULL时计算AVG

Question

我一直在努力寻找这个问题的解决方案，所以任何帮助都将非常感谢。

我有两个表，一个叫table_a，一个叫table_b，代表两个可能的客户订单来源。它们都包含一个名为"customer_number"的字段。我正在使用外部连接table_b到table_a。然后，在给定这两张表的情况下，计算平均客户订单量。

Where i struggle表示客户在任意一个表中都有条目，但在两个表中都没有。然后我的平均计算返回NULL。

Table_a:

在线订单总额1350987654321650

可以使用暴力破解方法:

( coalesce(a.[Total Online Orders], 0) + coalesce(b.[Total InStore Orders], 0)) /
nullif(case when a.[Total Online Orders] is not null then 1 else 0 end +
case when b.[Total InStore Orders] is not null then 1 else 0 end, 0)
)
) as [Average Order Size]

对2列的值使用两次COALESCE():

(
coalesce(a.[Total Online Orders], b.[Total InStore Orders]) + 
coalesce(b.[Total InStore Orders], a.[Total Online Orders])
) / 2 as [Average Order Size]

也考虑UNION，它也处理一些其他情况，特别是customer number在每个表中不一定是唯一的:

WITH cte (xtype, [customer number], order_amt) AS (
select 1 AS xtype, [customer number], [Total Online Orders]  FROM table_a UNION ALL
select 2 AS xtype, [customer number], [Total InStore Orders] FROM table_b
)
SELECT [customer number]
, SUM(CASE WHEN xtype = 1 THEN order_amt ELSE 0 END) AS [Total Online Orders]
, SUM(CASE WHEN xtype = 2 THEN order_amt ELSE 0 END) AS [Total InStore Orders]
, AVG(order_amt)                                     AS [Average Order Size]
FROM cte
GROUP BY [customer number]
ORDER BY [customer number]
;