sql - 如何 self 加入/分组以获得总和？

Question

我有一张 table :

ID      GroupID  Contact  Subject  Score
10      32       8017      5        77
11      15       5019      1        80
12      32       8018      3        62
13      17       8870      9        63
14      49       8018      11       72
15      19       8305      7        93
16      22       8029      11       88

我希望得到每个 ID 的 Score 的总和，只要 3 个字段 GroupID 中的每一个都有一个共同的值、Contact 和 Subject，以及常用 ID。

例如，

• ID 10 链接到 ID 12，因为它们具有相同的 GroupID。
• ID 12 随后链接到 ID 14，因为它们具有相同的Contact。
• ID 14 随后链接到 ID 16，因为它们具有相同的 Subject。
• 因此，ID 10、12、14 和 16 的 Sum_Score > = 77 + 62 + 72 + 88 = 299

输出:

ID      GroupID  Contact  Subject  Score  Sum_Score   Common_IDs
10      32       8017      5        77      299       (10, 12, 14, 16)
11      15       5019      1        80      80        (11)
12      32       8018      3        62      299       (10, 12, 14, 16)
13      17       8870      9        63      63        (13)
14      49       8018      11       72      299       (10, 12, 14, 16)
15      19       8305      7        93      93        (15)
16      22       8029      11       88      299       (10, 12, 14, 16)

Answer 1

这可能可以进一步优化，但适用于 pandas 和 networkx ;

columns = ['GroupID','Contact','Subject']
G = nx.empty_graph(0, nx.MultiGraph())
for i in range(len(columns)-1):
    G.add_edges_from(zip(df[columns[i]], df[columns[i+1]]))
    
s = pd.Series(nx.connected_components(G)).map(list).explode()
u = df.assign(k=df[columns].stack().map({v:k for k,v in s.items()}).max(level=0))
out = (u.merge(u.groupby('k').agg(Common_IDs=('ID',tuple),Sum_Score=('Score','sum'))
       ,left_on='k',right_index=True,how='left')).drop('k',1)

---

print(out)

   ID  GroupID  Contact  Subject  Score        Common_IDs  Sum_Score
0  10       32     8017        5     77  (10, 12, 14, 16)        299
1  11       15     5019        1     80             (11,)         80
2  12       32     8018        3     62  (10, 12, 14, 16)        299
3  13       17     8870        9     63             (13,)         63
4  14       49     8018       11     72  (10, 12, 14, 16)        299
5  15       19     8305        7     93             (15,)         93
6  16       22     8029       11     88  (10, 12, 14, 16)        299