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

因此，如果尝试插入由 Material=B 和 Color=white 和 Temperature =50 专门定义的“新组”(g5)，我想将其标识为重复组 (g2)。

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

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

这与帖子 SQL to find duplicate entries (within a group) 有关，但在这种情况下，查找重复插入的标准是基于元素的数量(而不是它们的性质)。

我真的很感激任何帮助

谢谢

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 函数，这可能很有用。或者您可以构建自己的聚合函数来执行此操作。如果此数据相对静态，您可能希望在插入时预先计算和存储，而不是在每次查询时重新生成。