PostgreSQL多值提取到不同列 处理重复回答及多痛点拆分问题

1 前置数据清洗（解决去重、优先级、跨月保留需求） #

先对原始数据做排序去重，确保同一分组、同一问题、同一提交月份仅保留最新提交的答案，不同月份的答案全部保留：

WITH cleaned_data AS (
    SELECT 
        -- 替换为实际分组维度，比如用户ID、问卷ID等
        user_id,
        question,
        answer,
        submit_time,
        -- 按分组维度、问题、提交月份分组，取每个组内最新的1条
        ROW_NUMBER() OVER (
            PARTITION BY user_id, question, DATE_TRUNC('month', submit_time)
            ORDER BY submit_time DESC
        ) AS rn
    FROM 你的原始表名
    WHERE rn = 1
),
-- 拆分单单元格多值，可替换为实际使用的分隔符
splited_data AS (
    SELECT 
        user_id,
        question,
        TRIM(UNNEST(STRING_TO_ARRAY(answer, ','))) AS single_answer,
        submit_time
    FROM cleaned_data
)

2 重构JSON聚合逻辑（多值字段存为数组避免覆盖） #

原有聚合逻辑对同key多值会导致后续JSON解析只取最后一个值，需要对soda_painpoints这类多值字段单独做数组聚合：

,aggregated_data AS (
    SELECT 
        user_id,
        -- 单值字段按原有逻辑聚合
        '{' || ARRAY_TO_STRING(ARRAY_AGG('"' || question || '":' || single_answer) 
            FILTER (WHERE question NOT IN ('soda_painpoints')), ',') 
        -- 多值字段聚合为JSON数组
        || CASE WHEN COUNT(*) FILTER (WHERE question = 'soda_painpoints') > 0 
            THEN ',"soda_painpoints":' || JSON_AGG(single_answer) 
                FILTER (WHERE question = 'soda_painpoints')
            ELSE '' END
        || '}' AS key_value
    FROM splited_data
    GROUP BY user_id
)

3 提取扁平化列（最多8个痛点字段） #

通过JSON数组下标提取对应位置的值，下标从0开始，不存在的位置默认返回空，也可自定义默认值：

SELECT 
    user_id,
    CAST(COALESCE(key_value::JSON ->> 'consumption_soda','0') AS INTEGER) AS soda_consumption,
    CAST(COALESCE(key_value::JSON ->> 'consumption_water','0') AS INTEGER) AS water_consumption,
    key_value::JSON -> 'soda_painpoints' ->> 0 AS soda_painpoints_1,
    key_value::JSON -> 'soda_painpoints' ->> 1 AS soda_painpoints_2,
    key_value::JSON -> 'soda_painpoints' ->> 2 AS soda_painpoints_3,
    key_value::JSON -> 'soda_painpoints' ->> 3 AS soda_painpoints_4,
    key_value::JSON -> 'soda_painpoints' ->> 4 AS soda_painpoints_5,
    key_value::JSON -> 'soda_painpoints' ->> 5 AS soda_painpoints_6,
    key_value::JSON -> 'soda_painpoints' ->> 6 AS soda_painpoints_7,
    key_value::JSON -> 'soda_painpoints' ->> 7 AS soda_painpoints_8
FROM aggregated_data

如果需要对痛点值全局去重，可在JSON_AGG时添加DISTINCT关键字即可。

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