基于历史列值计算填充新列的表格数据处理问题

当前数据 #

期望输出 #

计算规则 #

• 当当前行column1的值与前一年对应行的column1值一致时，column2取对应值；
• 若当前年份天数少于前一年（如A与B），前一年column1最大值对应的行，其column2取当前年份column1的最大值；
• 若当前年份天数多于前一年（如B与C），前一年column1最大值对应的行，其column2取当前年份column1的最大值。

解决方案 #

可以通过窗口函数结合自关联实现需求，以下是适配大多数SQL数据库的示例代码：

WITH year_stats AS (
    -- 提取各年份的column1最大值及年份标识
    SELECT
        SUBSTRING(date, 7, 4) AS year_num,
        MAX(column1) AS max_col1
    FROM your_table
    GROUP BY SUBSTRING(date, 7, 4)
)
SELECT
    t.*,
    CASE
        -- 规则1：当前column1在前一年存在，直接取对应值
        WHEN EXISTS (
            SELECT 1 FROM your_table t_prev
            WHERE SUBSTRING(t_prev.date, 7, 4) = CAST(SUBSTRING(t.date, 7, 4) AS INT) - 1
              AND t_prev.column1 = t.column1
        ) THEN t.column1
        -- 规则2、3：当前行是年份column1最大值时，取关联年份的最大值
        WHEN t.column1 = (SELECT max_col1 FROM year_stats WHERE year_num = SUBSTRING(t.date,7,4)) THEN
            COALESCE(
                (SELECT max_col1 FROM year_stats WHERE year_num = CAST(SUBSTRING(t.date,7,4) AS INT)-1),
                (SELECT max_col1 FROM year_stats WHERE year_num = CAST(SUBSTRING(t.date,7,4) AS INT)+1)
            )
        ELSE 'etc'
    END AS column2
FROM your_table t
ORDER BY SUBSTRING(date,7,4), column1;

代码说明 #

1. year_stats CTE：提前统计每个年份的column1最大值，避免重复计算；
2. 主查询通过CASE分支实现规则：
   • 先判断当前column1值是否存在于前一年的数据中，符合则直接取值；
   • 若当前行是年份的column1最大值，根据关联年份的最大值填充；
   • 剩余情况输出etc。

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