如何在PostgreSQL中实现数据minmax归一化并输出JSON格式

你遇到的报错是因为PostgreSQL不允许在聚合函数（如json_agg）的参数中直接嵌套窗口函数调用，两者计算优先级冲突，必须拆分执行步骤。另外你原写法中直接给json_agg传键值对的方式无法生成预期的JSON对象数组，需要先构造单个对象再聚合。

以下是可直接运行的实现代码：

WITH user_value_preprocess AS (
    SELECT
        user_id,
        date,
        value,
        -- 按用户分组计算min/max，如需全局归一化可删除PARTITION BY user_id
        MIN(value) OVER (PARTITION BY user_id) AS user_min_val,
        MAX(value) OVER (PARTITION BY user_id) AS user_max_val
    FROM user_table
)
SELECT
    user_id,
    COALESCE(
        JSON_AGG(
            JSON_BUILD_OBJECT(
                'date', date,
                'value', value
            )
        ),
        '[]'::JSON
    ) AS values,
    COALESCE(
        JSON_AGG(
            JSON_BUILD_OBJECT(
                'date', date,
                'value', 
                -- 处理分母为0的边界情况，避免用户所有值相同时报错
                CASE 
                    WHEN user_max_val = user_min_val THEN 0::FLOAT
                    ELSE (value - user_min_val)::FLOAT / (user_max_val - user_min_val)::FLOAT
                END
            )
        ),
        '[]'::JSON
    ) AS values_normalized
FROM user_value_preprocess
GROUP BY user_id;

逻辑说明 #

• 先通过CTE预计算每个用户的最小、最大value，将窗口函数计算和后续的分组聚合拆分，避免触发优先级冲突报错
• 用JSON_BUILD_OBJECT构造单条记录的JSON对象，再通过JSON_AGG聚合成数组，得到符合要求的结构
• 增加了分母为0的边界处理，当某个用户所有value值相等时，归一化值统一设为0，避免运行时错误
• 如果需要全局归一化（所有用户的value共用同一个min、max基准），只需要删除窗口函数中的PARTITION BY user_id即可

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