SQL：查找组内的重复条目（元素数量可变）

Question

我有一个由 group_id (PK) 和其他一些字段定义的组表 (GROUPS)。 每个组可以由可变数量的元素及其值组成。该组组合存储在第二个表（GROUP_COMPOSITION）中，该表具有 PK 字段（计数器）、group_id 字段、元素名称字段和元素名称值字段。

例如：

Table of groups:
groupId
g1
g2

Table of Group composition:

PK     groupID       Element_Name    Element_Value
1      g1            Material        A
2      g1            Temperature     37
3      g2            Color           white
4      g2            Temperature     50
5      g2            Material        B          
6      g3            Material        C
7      g4            Color           Red

因此，如果尝试插入由材料=B、颜色=白色和温度=50专门定义的“新组”（g5），我想将其标识为重复组（g2）。

我想防止在第二个表中重复插入“组组成”，其中组组成由元素总数及其值定义。

我正在考虑对要插入的所有可能元素进行 INTERSECT 查询，但不确定这是否是最佳方法。

这与帖子 SQL to find重复条目（在组内）相关，但在这种情况下，查找重复插入的标准是基于元素的数量（而不是其性质）。

我真的很感激任何帮助

谢谢

Answer 1

我想我会构建一个函数并走交叉路口路线。我想您还可以构建一个字符串来将每个组与表示所有组数据的值关联起来。然后将相同的函数应用于您的候选组并检查元素是否匹配。下面是一个可能聚合的 Postgresql 示例：

SELECT g.groupid, array_to_string(g.element_array, ',') elements
  FROM (SELECT o.groupid, array_agg(o.element_name ||'='|| o.element_value) AS element_array
      FROM (SELECT groupid, element_name, element_value
              FROM composition
              ORDER BY 1, 2) o
      GROUP BY groupid) g
  ORDER BY groupid


 groupid |          elements
---------+---------------------------------------
 g1      | Material=A,Temperature=37
 g2      | Color=white,Material=B,Temperature=50
 g3      | Material=C
 g4      | Color=Red

内部排序是为了确保它生成一致。似乎 Oracle 11gR2 有一个用于字符串连接的 LISTAGG 函数，这可能很有用。或者您可以构建自己的聚合函数来执行此操作。如果此数据相对静态，您可能希望在插入时预先计算和存储，而不是在每次查询时重新生成。

Answer 2

既然你的数据似乎表明这些群体的财产范围相当稳定，为什么不让他们成为一等公民并将他们提升为专栏呢？

groupid | Material | Temperature | Color
--------+----------+-------------+-----------
g1      | A        | 37          | <null>
g2      | B        | 50          | White
g3      | C        | <null>      | <null>
g4      | <null>   | <null>      | Red

如果您愿意的话，根据需要动态添加列并不会太难，但我有点好奇您想如何将组定义为“重复”？每当新组的属性在现有组中至少有一个对应的属性时？如果是这样，查询将非常简单：

select groupid from table where
Material=B and
Color=White

如果有任何返回的行，那么您已经拥有一个至少具有这些属性的组。