mysql - 从链接值中查找唯一用户

Question

我的表格中有这种形式的值。

id | val1  | val2 --------------------1  |   e1  |   m12  |   e1  |   m23  |   e2  |   m24  |   e3  |   m15  |   e4  |   m36  |   e5  |   m37  |   e5  |   m48  |   e4  |   m5

From this, I have to recover unique users like this and give them a unique id to identify.

User1 -> (val1 : e1, e2, e3 | val2: m1, m2)

e1 <-> m1, e1 <-> m2, m1 <-> e3, e2 <-> m2 ( <-> means linked).

e1 is connected to m1.

e1 is connected to m2.

m2 is connected to e2.

So e1,m1 are connected to e2.

Similarly, we find e1, e2, e3, m1, m2 all are linked. We need to identify these chains.

---

User2 -> (val1 : e4, e5 | val2: m3, m4, m5)

I have written two queries based on grouping my val1 and then by val2 separately and joining them in code (Java).

I want this to do this directly in MySQL/BigQuery query itself as we are building some reports on this.

Is this possible in a single query? Please help.

Thank you.

Update :

Desired output -

[
 { 
   id : user1,
   val1 : [e1, e2, e3],
   val2 : [m1, m2]
 },
 { 
   id : user2,
   val1 : [e4, e5],
   val2 : [m3, m4, m5]
 }
]

或

id | val1  | val2 | UUID------------------------1  |   e1  |   m1 | u12  |   e1  |   m2 | u13  |   e2  |   m2 | u14  |   e3  |   m1 | u15  |   e4  |   m3 | u26  |   e5  |   m3 | u27  |   e5  |   m4 | u28  |   e4  |   m5 | u2

为了简单起见，假设 val1 和 val2 的值是节点，并且如果存在于同一行中则连接。

表格的行形成图表(user1，user2)，我们需要识别这些图表。

Answer 1

想要加入使用纯 BigQuery(标准 SQL)解决您的任务的选项

先决条件/假设:源数据位于sandbox.temp.id1_id2_pairs
您应该将其替换为您自己的，或者如果您想使用问题中的虚拟数据进行测试 - 您可以如下创建此表(当然将 sandbox.temp 替换为您自己的 project.dataset )

确保设置相应的目标表

注意:您可以在此答案的底部找到所有相应的查询(作为文本)，但现在我用屏幕截图说明我的答案 - 所以所有内容都已呈现 - 查询、结果和使用选项

因此，将分为三个步骤:

第 1 步 - 初始化

在这里，我们只是根据与 id2 的连接对 id1 进行初始分组:

正如您在这里所看到的 - 我们基于通过 id2 的简单一级连接创建了所有 id1 值及其相应连接的列表

输出表为sandbox.temp.groups

第 2 步 - 对迭代进行分组

在每次迭代中，我们都会根据已建立的组来丰富分组。
查询源是上一步的输出表 (sandbox.temp.groups)，目标是覆盖的同一个表 (sandbox.temp.groups)

我们将继续迭代，直到找到的组数与之前的迭代相同

注意:您可以只打开两个 BigQuery Web UI 选项卡(如上所示)，并且无需更改任何代码，只需运行分组，然后一次又一次检查直到迭代收敛

(对于我在先决条件部分中使用的特定数据 - 我进行了三次迭代 - 第一次迭代产生了 5 个用户，第二次迭代产生了 3 个用户，第三次迭代再次产生了 3 个用户 - 这表明我们已经完成了迭代。

当然，在现实生活中 - 迭代次数可能不止三次 - 因此我们需要某种自动化(请参阅答案底部的相​​应部分)。

第 3 步 - 最终分组
当 id1 分组完成后 - 我们可以为 id2 添加最终分组

最终结果现在位于sandbox.temp.users表中

使用的查询(不要忘记根据上述逻辑和屏幕截图设置相应的目标表并在需要时覆盖):

先决条件:

#standardSQL
SELECT 1 id, 'e1' id1, 'm1' id2 UNION ALL
SELECT 2,    'e1',     'm2' UNION ALL
SELECT 3,    'e2',     'm2' UNION ALL
SELECT 4,    'e3',     'm1' UNION ALL
SELECT 5,    'e4',     'm3' UNION ALL
SELECT 6,    'e5',     'm3' UNION ALL
SELECT 7,    'e5',     'm4' UNION ALL
SELECT 8,    'e4',     'm5' UNION ALL
SELECT 9,    'e6',     'm6' UNION ALL
SELECT 9,    'e7',     'm7' UNION ALL
SELECT 9,    'e2',     'm6' UNION ALL
SELECT 888,  'e4',     'm55'   

第 1 步

#standardSQL
WITH `yourTable` AS (select * from `sandbox.temp.id1_id2_pairs`
), x1 AS (SELECT id1, STRING_AGG(id2) id2s FROM `yourTable` GROUP BY id1
), x2 AS (SELECT id2, STRING_AGG(id1) id1s FROM `yourTable` GROUP BY id2 
), x3 AS (
  SELECT id, (SELECT STRING_AGG(i ORDER BY i) FROM (
    SELECT DISTINCT i FROM UNNEST(SPLIT(id1s)) i)) grp
  FROM (
    SELECT x1.id1 id, STRING_AGG((id1s)) id1s FROM x1 CROSS JOIN x2
    WHERE EXISTS (SELECT y FROM UNNEST(SPLIT(id1s)) y WHERE x1.id1 = y)
    GROUP BY id1) 
)
SELECT * FROM x3 

第 2 步 - 分组

#standardSQL
WITH x3 AS (select * from `sandbox.temp.groups`)
SELECT id, (SELECT STRING_AGG(i ORDER BY i) FROM (
  SELECT DISTINCT i FROM UNNEST(SPLIT(grp)) i)) grp
FROM (
  SELECT a.id, STRING_AGG(b.grp) grp FROM x3 a CROSS JOIN x3 b 
  WHERE EXISTS (SELECT y FROM UNNEST(SPLIT(b.grp)) y WHERE a.id = y)
  GROUP BY a.id )   

第 2 步 - 检查

#standardSQL
SELECT COUNT(DISTINCT grp) users FROM `sandbox.temp.groups` 

第3步

#standardSQL
WITH `yourTable` AS (select * from `sandbox.temp.id1_id2_pairs`
), x1 AS (SELECT id1, STRING_AGG(id2) id2s FROM `yourTable` GROUP BY id1 
), x3 as (select * from `sandbox.temp.groups`
), f  AS (SELECT DISTINCT grp FROM x3 ORDER BY grp
)
SELECT ROW_NUMBER() OVER() id, grp id1, 
  (SELECT STRING_AGG(i ORDER BY i) FROM (SELECT DISTINCT i FROM UNNEST(SPLIT(id2)) i)) id2
FROM (
  SELECT grp, STRING_AGG(id2s) id2 FROM f 
  CROSS JOIN x1 WHERE EXISTS (SELECT y FROM UNNEST(SPLIT(f.grp)) y WHERE id1 = y)
  GROUP BY grp)

自动化:
当然，如果迭代收敛得很快，上面的“过程”可以手动执行 - 所以你最终会运行 10-20 次。但在更现实的情况下，您可以使用任何 client 轻松自动化此操作。您的选择