时间戳

首页 > 解决方案 > Listagg 两列 + 共享相同值时将它们分组

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

问题描述

我正在开发 Oracle SQL-Developer。

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

USER
JOB

这两个之间的关系是1，n。因此，1 个用户可以与 1 个或多个 JOB 相关，1 个 JOB 与 1 个或多个 USER 相关。

这里是数据样本：

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，但还没有尝试过。

我希望我说清楚了。大家怎么看？

标签： sqloraclelistagg

解决方案

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

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;

输出：

用户 工作
1,2,3 A,B,C,D,E,F,G
4 我,J,K,L

db<>在这里摆弄

推荐阅读