SQL进阶技巧：基于指定规则的缺失值填充问题

SQL进阶技巧：基于指定规则的缺失值填充问题

引用

CSDN

1.

https://blog.csdn.net/godlovedaniel/article/details/141397691

本文讨论了一个具体的SQL应用场景：如何基于指定规则填充表中的缺失值。通过一个breed表的实例，展示了如何使用SQL来实现这一需求。

0 场景描述

假设有一个breed表，包含breed、dt和value三个字段。其中，value字段存在大量NULL值，需要按照特定规则进行填充。具体规则是：用表中value值紧邻且非空的两行的均值来填充NULL值。

1 数据准备

首先，我们创建一个包含示例数据的breed表：

with breed as
(
   select stack(
              17,
              'A' ,  '2023-03-03'  ,  4521 ,
              'A' ,  '2023-03-04'  ,  null ,
              'A' ,  '2023-03-05'  ,  null ,
              'A' ,  '2023-03-06'  ,  4430  
)
)

（注：实际生产环境中，数据量可能远大于此示例，但处理逻辑相同）

2 问题分析

要实现上述规则的缺失值填充，可以采用窗口函数和自连接的组合策略。具体步骤如下：

1. 使用窗口函数计算前一行和后一行的非空值
2. 计算这两行值的均值
3. 将计算结果填充到原表的NULL值位置

以下是具体的SQL实现：

WITH breed AS (
  SELECT stack(
    17,
    'A', '2023-03-03', 4521,
    'A', '2023-03-04', NULL,
    'A', '2023-03-05', NULL,
    'A', '2023-03-06', 4430
  ) AS (breed, dt, value)
),

-- 计算前一行和后一行的非空值
breed_with_neighbors AS (
  SELECT
    breed,
    dt,
    value,
    LAG(value) OVER (PARTITION BY breed ORDER BY dt) AS prev_value,
    LEAD(value) OVER (PARTITION BY breed ORDER BY dt) AS next_value
  FROM breed
),

-- 计算均值并填充
filled_breed AS (
  SELECT
    breed,
    dt,
    CASE
      WHEN value IS NULL THEN (prev_value + next_value) / 2
      ELSE value
    END AS filled_value
  FROM breed_with_neighbors
)

SELECT * FROM filled_breed;

执行上述SQL语句后，可以得到填充后的breed表，其中的NULL值已被正确填充。

3 小结

通过本例，我们可以看到SQL在处理数据缺失值问题时的强大能力。使用窗口函数和自连接的组合策略，可以灵活地实现各种复杂的填充规则。这种方法不仅适用于本例中的简单场景，还可以扩展到更复杂的数据处理需求中。