SQL组合多个标识符为重复记录创建一个组ID

Question

我正在处理 Oracle 中的一个问题，我正在努力“优雅地”解决它。

我有一个包含三个不同标识符的数据提取:A、B、C

每个标识符可能出现在多行中，并且每一行可能具有这三个标识符中的一个或多个(即该列已填充​​或为空)。

我希望能够对具有 A、B 或 C 的任意组合的所有记录进行分组，并为它们分配相同的组 ID。

提取显示最终组应该是什么的表:

Rownum | A    | B    | C    | End group
1        p      NULL   NULL   1
2        p      r      NULL   1
3        q      NULL   NULL   2
4        NULL   r      NULL   1
5        NULL   NULL   s      2
6        q      NULL   s      2

我最初的方法是为提取中的每一行分配一个 guid，并为三个标识符创建一个查找表:

GUID | IDENTIFIER | IDENTIFIER TYPE | GROUP | END GROUP
1      p            A                 1       1
2      p            A                 1       1
2      r            B                 2       1
3      q            A                 3       3
4      r            B                 2       1
5      s            C                 4       3
6      q            A                 3       3
6      s            C                 4       3

然后按标识符分组并分配一个组号。然而，这些组需要在可能的情况下进行组合，以提供端组中显示的 View 。

对于这个问题，我能想到的唯一解决方案是使用循环，我宁愿避免使用循环。

任何想法将不胜感激。

尼尔

Answer 1

这确实是一个有趣的问题。不过，我认为我们缺少“群体”的定义。因为在您的示例中 (p,null,null) (row1) 和 (null,r,null) (row4) 不共享公共(public)标识符并且属于同一组我将使用此定义进行分组:

A row belongs to a group if it shares at least one identifier with at least one row of this group.

这意味着我们可以“链接”行。这自然会导致分层解决方案:

SQL> SELECT ID, a, b, c, MIN(grp) grp
  2    FROM (SELECT connect_by_root(id) ID,
  3                 connect_by_root(a) a,
  4                 connect_by_root(b) b,
  5                 connect_by_root(c) c,
  6                 ID grp
  7             FROM a
  8           CONNECT BY NOCYCLE(PRIOR a = a
  9                           OR PRIOR b = b
 10                           OR PRIOR c = c))
 11   GROUP BY ID, a, b, c
 12   ORDER BY ID;

        ID A          B          C                 GRP
---------- ---------- ---------- ---------- ----------
         1 p                                         1
         2 p          r                              1
         3 q                                         3
         4            r                              1
         5                       s                   3
         6 q                     s                   3

6 rows selected

可以执行子查询来理解构造:

SQL> SELECT connect_by_root(id) ID,
  2         connect_by_root(a) a,
  3         connect_by_root(b) b,
  4         connect_by_root(c) c,
  5         substr(sys_connect_by_path(ID, '->'), 3) path,
  6         ID grp
  7    FROM a
  8  CONNECT BY NOCYCLE(a = PRIOR a
  9                  OR b = PRIOR b
 10                  OR c = PRIOR c);

        ID A          B          C          PATH            GRP
---------- ---------- ---------- ---------- -------- ----------
         1 p                                1                 1
         1 p                                1->2              2
         1 p                                1->2->4           4
         2 p          r                     2                 2
         2 p          r                     2->1              1
         2 p          r                     2->4              4
         3 q                                3                 3
         3 q                                3->6              6
         3 q                                3->6->5           5
         4            r                     4                 4
         4            r                     4->2              2
         4            r                     4->2->1           1
         5                       s          5                 5
         5                       s          5->6              6
         5                       s          5->6->3           3
         6 q                     s          6                 6
         6 q                     s          6->3              3
         6 q                     s          6->5              5

18 rows selected