计算期间和按日期分组的单独列中的平均值Standard SQL BigQuery

Question

我在BQ中有一个表，看起来像这样：

date          rate 
02/02/22       null
02/01/22       null
01/31/22        1
01/30/22        1.5
01/29/22        0.5

我想创建avg_rate列。我尝试了简单的平均值计算，但因为我有一个逐组语句，它为avg_rate列指定了null。我需要每个rate为null的日期来获取所有不为null的rate的总和，并除以行数(对于那些没有为rate设置null的日期(，并将此数字分配给每个日期。这是我的问题：

SELECT
date,
SUM(rate) / COUNT(*) AS avg_rate
FROM
`my_table`
GROUP BY
1

我得到的输出：

date          avg_rate 
02/02/22       null
02/01/22       null
01/31/22        1
01/30/22        1.5
01/29/22        0.5

所需输出为：

date          avg_rate 
02/02/22        1
02/01/22        1
01/31/22        1
01/30/22        1.5
01/29/22        0.5

Answer 1

您可以使用coalesce返回按日期分组的avg，如果是null，则使用子查询返回列的总平均值：

select date, coalesce(avg(rate), (select avg(rate) from my_table))
from my_table
group by date

Answer 2

假设您有这个：

SELECT *
FROM (
select 1 as i union select 2 union select null
) x;

这将输出：

i
1
2
空