PostgreSQL 15中如何设计存储多分类聚合计数的产品计数表

现有环境与数据 #

Category表结构及数据 #

待导入的Excel数据结构 #

需求核心 #

创建product_count表存储年度分类销售计数，主键需包含year和分类（或聚合分类组），避免使用繁琐的中间关联表，同时保留分类与计数的直接关联。

最优方案推荐：使用数组存储分类ID #

PostgreSQL原生支持数组类型，能完美适配聚合分类的存储需求，同时满足主键约束，无需额外中间表。

1. 表结构设计 #

CREATE TABLE product_count (
    year INT NOT NULL,
    category_ids TEXT[] NOT NULL,
    count INT NOT NULL,
    -- 复合主键：年份+分类ID数组
    PRIMARY KEY (year, category_ids),
    -- 可选约束：确保数组中所有分类ID都存在于Category表
    CHECK (
        NOT EXISTS (
            SELECT 1 
            FROM unnest(category_ids) AS c(id)
            WHERE id NOT IN (SELECT id FROM category)
        )
    )
);

2. 数据导入处理 #

将Excel中逗号分隔的分类字符串转换为数组，注意处理字符串中的空格：

-- 示例：导入2020年C2、C3的合计记录
INSERT INTO product_count (year, category_ids, count)
VALUES (2020, string_to_array('C2, C3', ', '), 100);

-- 导入单个分类的记录
INSERT INTO product_count (year, category_ids, count)
VALUES (2020, ARRAY['C1'], 150);

3. 常用查询示例 #

• 查询包含指定分类的所有分组计数：

SELECT year, category_ids, count
FROM product_count
WHERE 'C2' = ANY(category_ids);

• 展开聚合分类为单个分类（仅用于展示，原计数为合计值不可拆分）：

SELECT year, unnest(category_ids) AS category_id, count
FROM product_count;

方案优势 #

• 直接关联分类与计数，无中间表冗余
• 利用PostgreSQL数组的原生支持，查询、维护便捷
• 通过CHECK约束确保分类ID的合法性，避免脏数据
• 复合主键天然保证同一年份下同一分类组的计数唯一

备选方案说明 #

方案二：使用JSONB存储分类组 #

适合需要额外存储分组描述等扩展属性的场景，但性能略低于数组：

CREATE TABLE product_count (
    year INT NOT NULL,
    category_group JSONB NOT NULL,
    count INT NOT NULL,
    PRIMARY KEY (year, category_group)
);

方案三：直接存储原始分类字符串（不推荐） #

简单但存在格式风险（如空格、分类顺序差异会导致主键重复或不一致），且无法高效关联Category表：

CREATE TABLE product_count (
    year INT NOT NULL,
    category_str TEXT NOT NULL,
    count INT NOT NULL,
    PRIMARY KEY (year, category_str)
);

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