时间戳

首页 > 解决方案 > 根据不在另一个表中的列对有效地创建行

sql - 根据不在另一个表中的列对有效地创建行

问题描述

所以我试图根据用户活动来模拟一个基本的推荐朋友系统。在这个模型中，人们可以加入活动，如果两个人还不是朋友并且碰巧加入了同一个活动，那么他们对彼此的推荐分数就会增加。

我的大多数应用程序都使用 Firebase，但对于这个系统，我正在尝试使用 BigQuery。

我想到的当前系统：

我会用这张桌子来代表友谊。由于它是无向图，因此 A->B 在表中推断 B->A 也将在表中。


+-------+-------+--------------+
| User1 | User2 | TimeFriended |
+-------+-------+--------------+
| abc   | def   |        12345 |
| def   | abc   |        12345 |
| abc   | rft   |         3456 |
| ...   | ...   |          ... |
+-------+-------+--------------+

我还计划像这样存储活动参与：


+------------+-----------+---------------+------------+
| ActivityId | CreatorID | ParticipantID | TimeJoined |
+------------+-----------+---------------+------------+
| abc        | def       | eft           |      21234 |
| ...        | ...       | ...           |        ... |
+------------+---------- +---------------+------------+

最后，假设可能有一个表存储这些推荐朋友的相互活动（不是超级重要，但假设它看起来像：）

+-------+-------+------------+
| User1 | User2 | ActivityID |
+-------+-------+------------+
| abc   | def   | eft        |
| ...   | ...   | ...        |
+-------+-------+------------+

所以这是我要运行的查询：

  1. 获取特定活动的所有参与者。
  2. 对于这些参与者中的每一个，获取不是他们朋友的所有其他参与者
  3. 将 {participant, other non-friend参与者} 的元组添加到“共同活动”表中

因此，显然有几种方法可以做到这一点。我可以使用循环制作一个简单的 BigQuery 脚本，但我不喜欢这样做，因为它会导致大量扫描，而且由于 BigQuery 不使用索引，它不会很好地扩展（就成本而言）。

我也可以使用类似子查询的NOT EXISTS东西，比如类似的东西SELECT ParticipantID from activities WHERE activityID = x AND NOT EXISTS {something to show that there doesn't exist a friend relation}，但是不清楚如何让每个参与者一次性完成这项工作。如果我能找到一个解决方案，它的表扫描与参与者的数量成线性关系，我会很好，但我有预感，即使我以某种方式让它工作，每个 NOT EXISTS 都会导致每个参与者对的完整扫描，导致二次缩放。

加入我可能会做些什么，但我不确定。希望对此有一些想法和指导。我不太习惯 SQL，尤其是像这样的复杂查询。

PS：如果你们中的任何人都想建议另一个无服务器解决方案而不是 BigQuery，请继续 :)

标签： sqlgoogle-cloud-platformgoogle-bigquery

解决方案

推荐阅读