PostgreSQL中基于jsonb实现EAV模式的可行性及变体计数方法问询

这个方案完全可行，而且在不少场景下比传统EAV模式更灵活高效！我来帮你拆解分析，再给你搞定变体数量计算的具体方法：

这种把属性值存在jsonb字段、格式为{"attrId":[values]}的设计，优势很明显：

• 查询更简洁：你提到的SELECT * FROM products p WHERE p.attributes @> "{1:[2]}" AND p.attributes @> "{1:[4]}"这类查询，用传统EAV要关联好几张表，这里单表加jsonb操作符就搞定，可读性和编写效率都高。
• 减少数据冗余：传统EAV每个属性值对应一行数据，而这里一个产品的所有属性存在一条jsonb里，能大幅减少表的行数，降低存储压力。
• 灵活扩展属性：新增属性不需要改表结构，直接往jsonb里加新的attrId键就行，适配快速迭代的业务场景。

不过也有几个要注意的点：

• 要保证属性ID的一致性：最好把attrId映射成固定的枚举值或者关联到属性字典表，避免出现同一个属性用不同ID存储的情况（比如width一会儿用1，一会儿用100）。
• 必须建GIN索引：如果要频繁用@>这类操作符查询，一定要给attributes字段建GIN索引，不然数据量大了查询会慢到离谱。建索引的语句是：

  CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes);
  

• 控制数据格式：可以加个约束确保attributes的结构符合预期，比如每个键都是整数，值都是数组，避免脏数据混入。

针对你给出的示例数据：

• p1: {"width":[1]}
• p2: {"width":[2],"height":[3]}
• p3: {"width":[1]}

要统计每个属性的不同变体以及对应产品数量，可以用PostgreSQL的jsonb内置函数来展开数据再分组统计，具体SQL如下：

1. 统计每个属性值的关联产品数 #

SELECT
  attr_id::INT AS 属性ID,
  value::INT AS 属性值,
  COUNT(DISTINCT product_id) AS 关联产品数量
FROM
  products,
  -- 把jsonb拆成键值对（attrId和对应的数组）
  jsonb_each(attributes) AS attrs(attr_id, value_arr),
  -- 把数组拆成单个值
  jsonb_array_elements_text(value_arr) AS value
GROUP BY
  attr_id, value
ORDER BY
  attr_id, value;

这个查询的输出会是：

（假设width对应的attrId是1，height是2）

2. 统计每个属性的总变体数和覆盖产品数 #

如果想知道每个属性有多少种不同的变体，以及覆盖了多少产品，可以用这个查询：

SELECT
  attr_id::INT AS 属性ID,
  COUNT(DISTINCT value::INT) AS 变体数量,
  COUNT(DISTINCT product_id) AS 覆盖产品数
FROM
  products,
  jsonb_each(attributes) AS attrs(attr_id, value_arr),
  jsonb_array_elements_text(value_arr) AS value
GROUP BY
  attr_id
ORDER BY
  attr_id;

输出会是：

优化建议 #

如果产品数据量很大，上面的展开操作可能有点耗时，可以考虑：

• 定期预统计结果到一张汇总表，比如每天跑一次任务把统计结果存起来，查询直接查汇总表。
• 如果某些属性查询特别频繁，可以把这些属性单独拆成普通字段，和jsonb字段互补使用，兼顾灵活性和性能。

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