如何将Pandas的fillna均值填充转换为Snowflake SQL语句？

背景 #

正在将Python Pandas数据管道转换为Snowflake中的一系列视图，多数转换操作都容易实现，但部分操作在SQL中实现难度较高，希望找到简便的实现方法。

核心需求 #

用最简洁的SQL语句实现Pandas中df['col'].fillna(df['col'].mean())的功能——即把某列中的空值替换为该列的平均值。

示例说明 #

原DataFrame： #

import pandas as pd
import numpy as np
df = pd.DataFrame({'somenull':[0, 0, 1, 3, 5, np.nan, np.nan]})

Pandas处理后结果： #

somenull  nonull
0       0.0     0.0
1       0.0     0.0
2       1.0     1.0
3       3.0     3.0
4       5.0     5.0
5       NaN     1.8
6       NaN     1.8

在Snowflake SQL中，你可以用COALESCE函数结合窗口函数AVG() OVER()来实现这个需求，这是最简洁的写法：

SELECT
    somenull,
    COALESCE(somenull, AVG(somenull) OVER ()) AS nonull
FROM your_table;

逻辑解释： #

• COALESCE(somenull, ...)：如果somenull不为空，直接返回原值；如果为空，则返回后面计算的平均值。
• AVG(somenull) OVER ()：自动忽略空值，计算整个表中somenull列的平均值，OVER ()表示作用于整个数据集。

验证示例： #

假设Snowflake中的表名为sample_data，数据与示例一致，执行上述SQL会得到和Pandas完全一致的结果：

如果需要按分组计算平均值（类似Pandas中df.groupby('group_col')['col'].fillna(...)的场景），只需在窗口函数中指定分组字段：

SELECT
    group_col,
    somenull,
    COALESCE(somenull, AVG(somenull) OVER (PARTITION BY group_col)) AS nonull
FROM your_table;

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