PostgreSQL窗口函数不支持COUNT(DISTINCT)的解决方案咨询

问题说明 #

在使用递归CTE查询评论层级结构时，尝试通过COUNT(DISTINCT root_id) OVER(PARTITION BY root_id)统计每个根评论的总回复数，遇到语法/功能不支持的报错，需要在不降低性能的前提下解决该问题。

原报错代码 #

WITH RECURSIVE "cte" AS((
        SELECT
            "videos_productvideocomment"."id",
            "videos_productvideocomment"."user_id",
            "videos_productvideocomment"."video_id",
            "videos_productvideocomment"."parent_id",
            "videos_productvideocomment"."text",
            "videos_productvideocomment"."commented_at",
            "videos_productvideocomment"."edited_at",
            "videos_productvideocomment"."created_at",
            "videos_productvideocomment"."updated_at",
            "videos_productvideocomment"."id" AS "root_id"
        FROM
            "videos_productvideocomment"
        WHERE
            (
                "videos_productvideocomment"."parent_id" IS NULL
            AND "videos_productvideocomment"."video_id" = 'f264433c-c0af-49cc-8b40-84453da71b2d'
            )
    ) UNION(
    SELECT
        "videos_productvideocomment"."id",
        "videos_productvideocomment"."user_id",
        "videos_productvideocomment"."video_id",
        "videos_productvideocomment"."parent_id",
        "videos_productvideocomment"."text",
        "videos_productvideocomment"."commented_at",
        "videos_productvideocomment"."edited_at",
        "videos_productvideocomment"."created_at",
        "videos_productvideocomment"."updated_at",
        "cte"."root_id" AS "root_id"
    FROM
        "videos_productvideocomment"
        INNER JOIN
            "cte"
        ON  "videos_productvideocomment"."parent_id" = "cte"."id"
))
SELECT
    *,
    EXISTS(
        SELECT
            (1) AS "a"
        FROM
            "videos_productvideolikecomment" U0
        WHERE
            (
                U0."comment_id" = t."id"
            AND U0."user_id" = '3bd3bc86-0335-481e-9fd2-eb2fb1168f48'
            )
        LIMIT 1
    ) AS "liked"
FROM
    (
        SELECT DISTINCT
            "cte"."id",
            "cte"."created_at",
            "cte"."updated_at",
            "cte"."user_id",
            "cte"."text",
            "cte"."commented_at",
            "cte"."edited_at",
            "cte"."parent_id",
            "cte"."video_id",
            "cte"."root_id" AS "root_id",
            COUNT(DISTINCT "cte"."root_id") OVER(PARTITION BY "cte"."root_id") AS "reply_count", -- 报错行
            COUNT("videos_productvideolikecomment"."id") OVER(PARTITION BY "cte"."id") AS "liked_count"
        FROM
            "cte"
            LEFT OUTER JOIN
                "videos_productvideolikecomment"
            ON  (
                    "cte"."id" = "videos_productvideolikecomment"."comment_id"
                )
    ) t
WHERE
    t."id" = t."root_id"
ORDER BY
    CASE
        WHEN t."user_id" = '3bd3bc86-0335-481e-9fd2-eb2fb1168f48' THEN 0
        ELSE 1
    END ASC,
    "liked_count" DESC

问题根源 #

1. 语法不支持：多数SQL引擎（如MySQL、PostgreSQL < 13）不允许窗口函数中使用COUNT(DISTINCT)。
2. 逻辑错误：即使语法支持，COUNT(DISTINCT root_id) OVER(PARTITION BY root_id)的结果永远是1，因为分区内的root_id完全相同，无法统计出该根评论下的总回复数。

优化解决方案 #

核心思路是提前聚合统计，避免在窗口函数中使用不支持的语法，同时减少重复计算提升性能：

1. 先对递归CTE的结果按root_id分组，统计每个根评论的总回复数（即该root_id下的所有评论条数）。
2. 单独统计每个评论的点赞数，避免关联点赞表后产生重复行导致的DISTINCT开销。
3. 最后将这些统计结果与原CTE数据关联，保留原有逻辑的同时提升性能。

修改后的完整代码：

WITH RECURSIVE "cte" AS((
        SELECT
            "videos_productvideocomment"."id",
            "videos_productvideocomment"."user_id",
            "videos_productvideocomment"."video_id",
            "videos_productvideocomment"."parent_id",
            "videos_productvideocomment"."text",
            "videos_productvideocomment"."commented_at",
            "videos_productvideocomment"."edited_at",
            "videos_productvideocomment"."created_at",
            "videos_productvideocomment"."updated_at",
            "videos_productvideocomment"."id" AS "root_id"
        FROM
            "videos_productvideocomment"
        WHERE
            "videos_productvideocomment"."parent_id" IS NULL
            AND "videos_productvideocomment"."video_id" = 'f264433c-c0af-49cc-8b40-84453da71b2d'
    ) UNION ALL -- 用UNION ALL替代UNION，避免去重开销，递归层级不会产生重复行
    SELECT
        v."id",
        v."user_id",
        v."video_id",
        v."parent_id",
        v."text",
        v."commented_at",
        v."edited_at",
        v."created_at",
        v."updated_at",
        c."root_id"
    FROM
        "videos_productvideocomment" v
        INNER JOIN "cte" c ON v."parent_id" = c."id"
),
-- 统计每个root_id的总回复数（包括根评论自身）
root_reply_counts AS (
    SELECT root_id, COUNT(*) AS reply_count
    FROM cte
    GROUP BY root_id
),
-- 统计每个评论的点赞数
comment_like_counts AS (
    SELECT comment_id, COUNT(*) AS liked_count
    FROM "videos_productvideolikecomment"
    GROUP BY comment_id
)
SELECT
    c.*,
    COALESCE(rrc.reply_count, 1) AS reply_count, -- 根评论至少有自身一条
    COALESCE(clc.liked_count, 0) AS liked_count,
    EXISTS(
        SELECT 1
        FROM "videos_productvideolikecomment" U0
        WHERE U0."comment_id" = c."id"
            AND U0."user_id" = '3bd3bc86-0335-481e-9fd2-eb2fb1168f48'
    ) AS "liked"
FROM cte c
LEFT JOIN root_reply_counts rrc ON c.root_id = rrc.root_id
LEFT JOIN comment_like_counts clc ON c.id = clc.comment_id
WHERE c.id = c.root_id -- 只取根评论
ORDER BY
    CASE WHEN c."user_id" = '3bd3bc86-0335-481e-9fd2-eb2fb1168f48' THEN 0 ELSE 1 END ASC,
    liked_count DESC

性能优化点 #

• 用UNION ALL替代UNION：递归CTE的层级查询不会产生重复行，避免UNION的去重开销。
• 提前聚合统计：将回复数和点赞数的统计独立成CTE，避免关联点赞表后产生大量重复行，无需再用DISTINCT去重。
• 减少窗口函数使用：改用分组聚合+关联的方式，比窗口函数更高效，且兼容所有SQL引擎。

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