如何用T-SQL基于行值差异动态填充计算列？

要实现你需要的动态计算逻辑，核心是利用窗口函数定位每个无值行前后的有效数值，再通过线性插值完成均匀分配，同时处理首尾无值段的填充。以下是具体实现思路和SQL示例：

核心思路 #

1. 确定行顺序：关系型数据库表本身无序，必须先基于实际业务字段（比如日期、自增主键）生成行号，确保后续计算的顺序准确性。
2. 定位边界值：为每个行获取前后最近的非空[No of Days]值，以及这些有效行的位置。
3. 分场景处理：
   • 本身有值的行直接沿用原数值；
   • 首段无值行（第一个有效行之前）填充为第一个有效数值；
   • 末段无值行（最后一个有效行之后）填充为最后一个有效数值；
   • 中间无值行通过线性插值，将前后有效行的差值均匀分配到每一行。

SQL 实现示例 #

假设你的表有一个可排序的字段（比如id或record_date），以下代码以通用SQL语法为例（不同数据库可能有细微语法差异，比如IGNORE NULLS的支持）：

-- 第一步：为每行生成连续行号（替换ORDER BY后的字段为你的实际排序依据）
WITH numbered_rows AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY record_date) AS row_num
    FROM your_table_name
),
-- 第二步：获取每个行的前后有效数值及行号，还有首尾有效数值
boundary_info AS (
    SELECT
        *,
        -- 前一个非空的[No of Days]
        LAG([No of Days]) IGNORE NULLS OVER (ORDER BY row_num) AS prev_valid_days,
        -- 后一个非空的[No of Days]
        LEAD([No of Days]) IGNORE NULLS OVER (ORDER BY row_num) AS next_valid_days,
        -- 前一个有效行的行号
        LAG(row_num) IGNORE NULLS OVER (ORDER BY row_num) AS prev_valid_row,
        -- 后一个有效行的行号
        LEAD(row_num) IGNORE NULLS OVER (ORDER BY row_num) AS next_valid_row,
        -- 表中第一个非空的[No of Days]（处理首段无值）
        FIRST_VALUE([No of Days]) IGNORE NULLS OVER (ORDER BY row_num) AS first_valid_days,
        -- 表中最后一个非空的[No of Days]（处理末段无值）
        LAST_VALUE([No of Days]) IGNORE NULLS OVER (
            ORDER BY row_num 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_valid_days
    FROM numbered_rows
)
-- 第三步：计算最终的[Days Calculated]列
SELECT
    *,
    CASE
        -- 本身有值的行直接返回原数值
        WHEN [No of Days] IS NOT NULL THEN [No of Days]
        -- 首段无值：填充第一个有效数值
        WHEN prev_valid_days IS NULL THEN first_valid_days
        -- 末段无值：填充最后一个有效数值
        WHEN next_valid_days IS NULL THEN last_valid_days
        -- 中间无值：线性插值均匀分配差值
        ELSE 
            prev_valid_days + (next_valid_days - prev_valid_days) * 
            (row_num - prev_valid_row) / (next_valid_row - prev_valid_row)
    END AS [Days Calculated]
FROM boundary_info
ORDER BY row_num;

关键细节说明 #

• 排序字段必须明确：ROW_NUMBER()中的ORDER BY不能省略或用(SELECT NULL)，必须替换为你的表中能确定行顺序的字段（比如日期、自增ID），否则计算结果会混乱。
• 数据库语法兼容：
  • PostgreSQL、Oracle支持LAG/LEAD的IGNORE NULLS；
  • SQL Server不支持该语法，可改用LAST_VALUE([No of Days]) OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)来获取前一个非空值；
• 差值为0的情况：如果前后有效行数值相同，计算结果会自动等于该数值，符合你"沿用数值"的要求；
• 线性插值逻辑：(next_valid_days - prev_valid_days) * (row_num - prev_valid_row) / (next_valid_row - prev_valid_row)确保中间行的数值从prev_valid_days平滑过渡到next_valid_days，差值被均匀分配到每一行的间隔中。

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