PostgreSQL聚合JSON列：计算模板响应平均布局的查询需求

表结构说明 #

• template_post表：
  • id：主键
  • data：JSON类型，存储模板的items（项目列表）和rows（行列表）信息
• response_post表：
  • id：主键
  • template_id：外键，关联template_post.id
  • data：JSON类型，记录响应中项目的行放置情况（filled_rows数组，每个元素包含row_id和对应的item_ids数组）

需求规则 #

统计指定模板下所有响应中每个项目在各行的出现次数，将项目放置在出现次数最多的行中；若多个行出现次数相同，则选择最先出现的行（按响应数据中的行出现顺序）。最终生成合并后的布局结果，格式与response_post.data一致。

示例数据 #

template_post表数据 #

response_post表数据 #

预期结果 #

{
  "filled_rows": [
    {
      "row_id": 0,
      "item_ids": [1]
    },
    {
      "row_id": 2,
      "item_ids": [2]
    },
    {
      "row_id": 4,
      "item_ids": [0,3]
    }
  ]
}

解决方案SQL查询 #

WITH item_row_counts AS (
    -- 解析所有响应，拆分出每个项目对应的行，统计次数并记录首次出现顺序
    SELECT
        rp.template_id,
        (item->>'id')::INT AS item_id,
        (fr->>'row_id')::INT AS row_id,
        COUNT(*) AS occurrence_count,
        MIN(rp.id) AS first_response_id,
        MIN(fr_index) AS first_row_index
    FROM response_post rp
    CROSS JOIN LATERAL json_array_elements(rp.data->'filled_rows') WITH ORDINALITY AS frs(fr, fr_index)
    CROSS JOIN LATERAL json_array_elements(fr->'item_ids') AS items(item)
    WHERE rp.template_id = 1  -- 指定目标模板ID
    GROUP BY rp.template_id, (item->>'id')::INT, (fr->>'row_id')::INT
),
ranked_item_rows AS (
    -- 为每个项目的行按优先级排名：次数降序>首次响应ID升序>首次行位置升序
    SELECT
        item_id,
        row_id,
        ROW_NUMBER() OVER (
            PARTITION BY item_id
            ORDER BY occurrence_count DESC, first_response_id ASC, first_row_index ASC
        ) AS rn
    FROM item_row_counts
),
final_item_assignments AS (
    -- 筛选每个项目排名第一的行，聚合同一行的项目ID
    SELECT row_id, ARRAY_AGG(item_id ORDER BY item_id) AS item_ids
    FROM ranked_item_rows
    WHERE rn = 1
    GROUP BY row_id
)
-- 组装成目标JSON格式
SELECT json_build_object(
    'filled_rows',
    json_agg(
        json_build_object('row_id', row_id, 'item_ids', item_ids)
        ORDER BY row_id
    )
) AS average_layout;

查询说明 #

1. item_row_counts：拆分响应数据中的行和项目，统计每个项目在每行的出现次数，同时记录首次出现的响应ID和行位置，用于处理次数相同的歧义场景。
2. ranked_item_rows：为每个项目的所有候选行分配排名，确保出现次数最多、最先出现的行排在首位。
3. final_item_assignments：提取每个项目的最优行，将同一行的项目ID聚合为有序数组。
4. 最后通过JSON函数组装成符合要求的结果格式。

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