PostgreSQL多表连接记录重复膨胀问题求助（Metabase环境）

记录膨胀的核心原因是一对多/多对多关系连接时产生了笛卡尔积：主表单条记录对应从表多条记录，多次连接后，主记录会被重复N*M次（N、M为各从表的匹配数）。针对你的场景，提供以下几种解决方案：

方案1：仅统计不重复的主记录数 #

如果只是要统计原始checklist_checklistsectionitem关联合规记录的数量，直接用COUNT(DISTINCT)锁定主表唯一ID即可，无需担心后续连接的多对多关系：

SELECT
    COUNT(DISTINCT "public"."checklist_checklistsectionitem"."id")
FROM
    "public"."checklist_checklistsectionitem"
INNER JOIN 
    "public"."checklist_noncompliance" "Checklist Noncompliance" 
         ON "public"."checklist_checklistsectionitem"."id" = "Checklist Noncompliance"."item_id" 
INNER JOIN 
    "public"."checklist_checklistitem" "Checklist Checklistitem - Check List Item" 
         ON "public"."checklist_checklistsectionitem"."check_list_item_id" = "Checklist Checklistitem - Check List Item"."id" 
INNER JOIN
    "public"."checklist_checklistitem_operations" "coltab" 
        ON "coltab"."checklistitem_id" = "Checklist Checklistitem - Check List Item"."id"
INNER JOIN
    "public"."operations_operation" "coltab_2" 
        ON "coltab_2"."id" = "coltab"."operation_id"

方案2：需要展示关联字段，避免主记录重复 #

如果需要获取operations_operation的字段，同时不想主表记录重复，可选择以下两种方式：

方式A：聚合多值字段 #

用STRING_AGG将多对多关联的字段拼接成字符串，按主表唯一字段分组：

SELECT
    "cci"."id" AS section_item_id,
    "cn"."id" AS noncompliance_id,
    "cli"."name" AS checklist_item_name,
    STRING_AGG("oo"."name", ', ') AS operation_names -- 聚合多值字段
FROM
    "public"."checklist_checklistsectionitem" "cci"
INNER JOIN 
    "public"."checklist_noncompliance" "cn" 
         ON "cci"."id" = "cn"."item_id" 
INNER JOIN 
    "public"."checklist_checklistitem" "cli" 
         ON "cci"."check_list_item_id" = "cli"."id" 
INNER JOIN
    "public"."checklist_checklistitem_operations" "cco" 
        ON "cco"."checklistitem_id" = "cli"."id"
INNER JOIN
    "public"."operations_operation" "oo" 
        ON "oo"."id" = "cco"."operation_id"
GROUP BY
    "cci"."id", "cn"."id", "cli"."name" -- 按主表唯一标识分组

方式B：用LATERAL JOIN取单条匹配记录 #

如果只需要每个checklistitem对应的任意一条operation记录，用LATERAL JOIN+LIMIT 1限制匹配数：

SELECT
    "cci"."id" AS section_item_id,
    "cn"."id" AS noncompliance_id,
    "cli"."name" AS checklist_item_name,
    "oo"."id" AS operation_id,
    "oo"."name" AS operation_name
FROM
    "public"."checklist_checklistsectionitem" "cci"
INNER JOIN 
    "public"."checklist_noncompliance" "cn" 
         ON "cci"."id" = "cn"."item_id" 
INNER JOIN 
    "public"."checklist_checklistitem" "cli" 
         ON "cci"."check_list_item_id" = "cli"."id" 
INNER JOIN LATERAL (
    SELECT "id", "name"
    FROM "public"."checklist_checklistitem_operations" "cco"
    INNER JOIN "public"."operations_operation" "oo" ON "oo"."id" = "cco"."operation_id"
    WHERE "cco"."checklistitem_id" = "cli"."id"
    LIMIT 1 -- 仅取第一条匹配记录
) AS "oo" ON TRUE

方案3：提前聚合关联表（适合后续继续连接其他表） #

用CTE提前处理多对多关联的聚合逻辑，再和主表连接，避免后续连接再次触发笛卡尔积：

WITH operation_agg AS (
    SELECT
        "cco"."checklistitem_id",
        STRING_AGG("oo"."name", ', ') AS operation_names,
        ARRAY_AGG("oo"."id") AS operation_ids -- 用数组存储所有关联ID
    FROM "public"."checklist_checklistitem_operations" "cco"
    INNER JOIN "public"."operations_operation" "oo" ON "oo"."id" = "cco"."operation_id"
    GROUP BY "cco"."checklistitem_id"
)
SELECT
    "cci"."id" AS section_item_id,
    "cn"."id" AS noncompliance_id,
    "cli"."name" AS checklist_item_name,
    "oa"."operation_names",
    "oa"."operation_ids"
FROM
    "public"."checklist_checklistsectionitem" "cci"
INNER JOIN 
    "public"."checklist_noncompliance" "cn" 
         ON "cci"."id" = "cn"."item_id" 
INNER JOIN 
    "public"."checklist_checklistitem" "cli" 
         ON "cci"."check_list_item_id" = "cli"."id" 
INNER JOIN operation_agg "oa" ON "oa"."checklistitem_id" = "cli"."id"
-- 后续可继续在此基础上连接其他表，不会触发主记录膨胀

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