求助：基于数值列在partition内生成字符串结果列的SQL实现

场景1：仅标记最早出现的最大值行为MAXIMUM #

如果分区内存在多个相同的最大值，仅将最早出现的那一行标记为MAXIMUM，其余行按相对位置标记BEFORE/AFTER，可以用以下SQL实现：

WITH ranked_data AS (
    SELECT 
        Category,
        Sub_Category,
        Stock_date,
        demand_in_tonnes,
        -- 按Stock_date给分区内每行生成唯一行号
        ROW_NUMBER() OVER (PARTITION BY Category, Sub_Category ORDER BY Stock_date) AS row_num,
        -- 计算分区内的最大demand值
        MAX(demand_in_tonnes) OVER (PARTITION BY Category, Sub_Category) AS max_demand,
        -- 找到最早出现最大值的行号
        MIN(CASE 
            WHEN demand_in_tonnes = MAX(demand_in_tonnes) OVER (PARTITION BY Category, Sub_Category) 
            THEN ROW_NUMBER() OVER (PARTITION BY Category, Sub_Category ORDER BY Stock_date) 
        END) OVER (PARTITION BY Category, Sub_Category) AS max_row_num
    FROM your_table_name
)
SELECT 
    Category,
    Sub_Category,
    Stock_date,
    demand_in_tonnes,
    CASE
        WHEN row_num < max_row_num THEN 'BEFORE'
        WHEN row_num = max_row_num THEN 'MAXIMUM'
        ELSE 'AFTER'
    END AS Result
FROM ranked_data;

场景2：所有最大值行均标记为MAXIMUM #

如果需要将分区内所有demand等于最大值的行都标记为MAXIMUM，第一个最大值之前的行标记BEFORE，第一个最大值之后的行（包括非最大值行）标记AFTER，可以用这个写法：

WITH data_with_flags AS (
    SELECT 
        Category,
        Sub_Category,
        Stock_date,
        demand_in_tonnes,
        -- 分区内的最大demand值
        MAX(demand_in_tonnes) OVER (PARTITION BY Category, Sub_Category) AS max_demand,
        -- 统计当前行及之前是否出现过最大值
        SUM(CASE 
            WHEN demand_in_tonnes = MAX(demand_in_tonnes) OVER (PARTITION BY Category, Sub_Category) 
            THEN 1 ELSE 0 
        END) OVER (
            PARTITION BY Category, Sub_Category 
            ORDER BY Stock_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS max_has_occurred
    FROM your_table_name
)
SELECT 
    Category,
    Sub_Category,
    Stock_date,
    demand_in_tonnes,
    CASE
        WHEN demand_in_tonnes = max_demand THEN 'MAXIMUM'
        WHEN max_has_occurred = 0 THEN 'BEFORE'
        ELSE 'AFTER'
    END AS Result
FROM data_with_flags;

关于之前报错的说明 #

你之前尝试直接在CASE中嵌套窗口帧报错，大概率是因为窗口函数的嵌套逻辑不符合SQL语法规范（多数数据库不支持在CASE分支中直接使用带自定义帧的窗口函数）。上面的解法通过CTE预计算所有需要的中间值（行号、最大demand、最大值出现标记），再用CASE做简单判断，规避了嵌套窗口函数的语法问题，逻辑也更清晰。

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