sql - Listagg 两列 + 当共享相同值时将它们分组

Question

我正在研究 Oracle SQL-Developer。

让我们从数据开始，以更好地描述我的问题。我有一个有 2 列的表格

USER
JOB

这 2 者之间的关系是 1,n。因此，1 个用户可以与 1 个或多个作业相关，而 1 个作业可以与 1 个或多个用户相关。

这里是数据示例:

USER  JOB 
  1     A     
  1     B      
  1     C      
  1     D      
  1     E
  2     B
  2     C
  2     F
  2     G
  3     A
  3     B
  3     C
  3     D
  4     I
  4     J
  4     K
  4     L

  

这个想法是一起检索相互关联的值。首先，我想到了可以连接和分组值的 LISTAGG。但我得到了这样的结果:

 USER  JOB 
  1     A, B, C, D, E     
  2     B, C, F, G
  3     A, B, C, D
  4     I, J, K, L

或者

USER        JOB 
  1, 3        A     
  1, 2, 3     B      
  1, 2, 3     C      
  1, 3        D      
  1           E      
  2           F
  2           G
  4           I
  4           J
  4           K
  4           L

所需的输出如下:

USER        JOB
1, 2, 3     A, B, C, D, E, F, G
4           I, J, K, L

这个想法是，如果用户与其他人共享一份工作，那么即使所有工作都不相同，所有工作也会链接在一起。

示例:USER_1 有 2 个作业 A 和 B。USER_2 有 2 个作业 B 和 C。然后是 USER_1 和 USER_2 共享工作 A、B、C。

相反，如果一项工作与另一项工作共享一个用户，那么即使所有用户不相同，所有用户也会链接在一起......

因此，我尝试了自加入，以便检索一个给定用户的所有作业以及给定一个作业的所有用户，以便显示所有可能的共享值，但也许这是错误的方法，因为我仍然被 LISTAGG 阻止。我也考虑过 PIVOT，但还没有尝试过。

我希望我已经说清楚了。大家觉得怎么样？

Answer 1

这对于大型数据集来说效率不高，但您可以使用分层查询来查找每个组所属的组，然后找到每个组中不同的用户/作业，然后聚合:

WITH groups ( grp, "USER", job ) AS (
  SELECT MIN(CONNECT_BY_ROOT("USER")) AS grp,
         "USER",
         JOB
  FROM   table_name
  CONNECT BY NOCYCLE
         PRIOR "USER" = "USER"
  OR     PRIOR JOB = JOB
  GROUP BY "USER", JOB
)
SELECT users,
       jobs
FROM   (
         SELECT grp,
                LISTAGG("USER", ',') WITHIN GROUP (ORDER BY "USER") AS users
         FROM   (
           SELECT DISTINCT grp, "USER"
           FROM   groups
         )
         GROUP BY grp
       ) u
       INNER JOIN
       (
         SELECT grp,
                LISTAGG(JOB, ',') WITHIN GROUP (ORDER BY JOB) AS jobs
         FROM   (
           SELECT DISTINCT grp, job
           FROM   groups
         )
         GROUP BY grp
       ) j
       ON ( u.grp = j.grp );

对于您的示例数据:

CREATE TABLE table_name ( "USER", JOB ) AS
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 1, 'C' FROM DUAL UNION ALL
SELECT 1, 'D' FROM DUAL UNION ALL
SELECT 1, 'E' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 2, 'C' FROM DUAL UNION ALL
SELECT 2, 'F' FROM DUAL UNION ALL
SELECT 2, 'G' FROM DUAL UNION ALL
SELECT 3, 'A' FROM DUAL UNION ALL
SELECT 3, 'B' FROM DUAL UNION ALL
SELECT 3, 'C' FROM DUAL UNION ALL
SELECT 3, 'D' FROM DUAL UNION ALL
SELECT 4, 'I' FROM DUAL UNION ALL
SELECT 4, 'J' FROM DUAL UNION ALL
SELECT 4, 'K' FROM DUAL UNION ALL
SELECT 4, 'L' FROM DUAL;

输出:

USERS	JOBS
1,2,3	A,B,C,D,E,F,G
4	I,J,K,L

db<> fiddle here