基于分区块与时长最大值的SQL值填充问题求助

需求明确 #

基于包含ID、PERIOD、DURATION (DAYS)、SIZE、COLOR、BLOCK_SIZE、BLOCK_COLOR字段的数据表，生成SIZE2和COLOR2列，规则如下：

• 按BLOCK_SIZE和BLOCK_COLOR划分分区
• 取每个分区内DURATION (DAYS)最大的非空SIZE、COLOR值，填充到该分区所有行

常见错误原因 #

你之前用FIRST_VALUE()实现时结果不符合预期（如BLOCK_SIZE=4分区SIZE2全为NULL），通常是这两个问题导致：

1. 未指定完整窗口范围：默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，仅覆盖当前行及之前的行，无法获取整个分区的目标值
2. 未处理NULL值排序：如果最大DURATION的行SIZE/COLOR为空，未优先选取次大DURATION的非空值

正确写法 #

方法1：窗口函数+NULL值优先排序（通用支持多数数据库） #

直接通过窗口函数覆盖整个分区，同时优先选取非空值：

SELECT 
    ID,
    PERIOD,
    `DURATION (DAYS)`,
    SIZE,
    COLOR,
    BLOCK_SIZE,
    BLOCK_COLOR,
    -- 取分区内DURATION最大的非空SIZE
    FIRST_VALUE(SIZE) OVER (
        PARTITION BY BLOCK_SIZE, BLOCK_COLOR
        ORDER BY `DURATION (DAYS)` DESC,
                 CASE WHEN SIZE IS NOT NULL THEN 0 ELSE 1 END
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS SIZE2,
    -- 取分区内DURATION最大的非空COLOR
    FIRST_VALUE(COLOR) OVER (
        PARTITION BY BLOCK_SIZE, BLOCK_COLOR
        ORDER BY `DURATION (DAYS)` DESC,
                 CASE WHEN COLOR IS NOT NULL THEN 0 ELSE 1 END
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS COLOR2
FROM your_table;

• 排序逻辑：先按DURATION (DAYS)降序，再将非空值排在空值之前，确保优先选取最大DURATION对应的非空值
• 窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：覆盖整个分区，保证所有行都能获取到分区内的目标值

方法2：CTE+关联查询（适合不支持复杂窗口范围的数据库） #

如果你的数据库对窗口函数支持有限，可通过先获取分区最大DURATION，再关联取值：

WITH block_max_duration AS (
    -- 获取每个分区的最大DURATION，若要求必须从非空SIZE/COLOR行取，可加WHERE过滤
    SELECT 
        BLOCK_SIZE,
        BLOCK_COLOR,
        MAX(`DURATION (DAYS)`) AS max_duration
    FROM your_table
    -- WHERE SIZE IS NOT NULL AND COLOR IS NOT NULL
    GROUP BY BLOCK_SIZE, BLOCK_COLOR
),
block_top_values AS (
    -- 获取每个分区最大DURATION对应的非空SIZE/COLOR，若有多个同DURATION行，取第一个非空值
    SELECT DISTINCT
        b.BLOCK_SIZE,
        b.BLOCK_COLOR,
        FIRST_VALUE(t.SIZE) OVER (
            PARTITION BY b.BLOCK_SIZE, b.BLOCK_COLOR
            ORDER BY CASE WHEN t.SIZE IS NOT NULL THEN 0 ELSE 1 END
        ) AS top_size,
        FIRST_VALUE(t.COLOR) OVER (
            PARTITION BY b.BLOCK_SIZE, b.BLOCK_COLOR
            ORDER BY CASE WHEN t.COLOR IS NOT NULL THEN 0 ELSE 1 END
        ) AS top_color
    FROM block_max_duration b
    JOIN your_table t
        ON b.BLOCK_SIZE = t.BLOCK_SIZE
        AND b.BLOCK_COLOR = t.BLOCK_COLOR
        AND t.`DURATION (DAYS)` = b.max_duration
)
-- 关联回原表，填充SIZE2和COLOR2
SELECT 
    t.*,
    bt.top_size AS SIZE2,
    bt.top_color AS COLOR2
FROM your_table t
JOIN block_top_values bt
    ON t.BLOCK_SIZE = bt.BLOCK_SIZE
    AND t.BLOCK_COLOR = bt.BLOCK_COLOR;

验证说明 #

• 针对BLOCK_SIZE=4的分区：若最大DURATION行的SIZE非空，两种写法都会正确取到该值；若该行SIZE为空，则会自动选取同分区内次大DURATION的非空SIZE值
• 可根据你的数据库类型（如MySQL、PostgreSQL、SQL Server）调整语法细节（比如引号、窗口函数支持程度）

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