PostgreSQL按条目数分类统计文档数量的查询改写需求

你可以通过两种高效方式将一行三列的统计结果转换为固定3行2列的格式，以下是具体实现：

方法1：使用CTE + UNION ALL（可读性强） #

先通过CTE复用你的统计子查询，再用UNION ALL将每一列拆分为单独的行：

WITH document_statistics AS (
    -- 这里替换成你已有的一行三列统计子查询
    SELECT
        SUM(CASE WHEN item_count < 10 THEN 1 ELSE 0 END) AS below_10,
        SUM(CASE WHEN item_count BETWEEN 10 AND 20 THEN 1 ELSE 0 END) AS between_10_20,
        SUM(CASE WHEN item_count > 20 THEN 1 ELSE 0 END) AS over_20
    FROM (
        -- 子查询：统计每个文档关联的条目数
        SELECT document_id, COUNT(*) AS item_count
        FROM ITEM
        GROUP BY document_id
    ) AS doc_item_counts
)
SELECT 'BELOW 10 ITEMS' AS category, below_10 AS document_count FROM document_statistics
UNION ALL
SELECT 'BETWEEN 10 AND 20 ITEMS' AS category, between_10_20 AS document_count FROM document_statistics
UNION ALL
SELECT 'OVER 20 ITEMS' AS category, over_20 AS document_count FROM document_statistics;

方法2：使用数组 + unnest（更简洁） #

利用PostgreSQL的unnest函数，将分类名称和统计值分别打包成数组，再拆分为行：

WITH document_statistics AS (
    -- 替换成你已有的一行三列统计子查询
    SELECT
        ARRAY['BELOW 10 ITEMS', 'BETWEEN 10 AND 20 ITEMS', 'OVER 20 ITEMS'] AS categories,
        ARRAY[
            SUM(CASE WHEN item_count < 10 THEN 1 ELSE 0 END),
            SUM(CASE WHEN item_count BETWEEN 10 AND 20 THEN 1 ELSE 0 END),
            SUM(CASE WHEN item_count > 20 THEN 1 ELSE 0 END)
        ] AS counts
    FROM (
        SELECT document_id, COUNT(*) AS item_count
        FROM ITEM
        GROUP BY document_id
    ) AS doc_item_counts
)
SELECT unnest(categories) AS category, unnest(counts) AS document_count
FROM document_statistics;

两种方法都能保证固定返回3行，即使某个分类的文档数量为0也会显示对应行。

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