How to self join/group by to get sum?

user_7993977 0
Viewed 282

I have a 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

I wish to get the sum of Score of each ID as long as there is a common value among each of the 3 fields GroupID, Contact and Subject, as well as the common IDs.

For instance,

  • ID 10 is linked to ID 12 as they have the same GroupID.
  • ID 12 is subsequently linked to ID 14 as they have the same Contact.
  • ID 14 is subsequently linked to ID 16 as they have the same Subject.
  • Therefore, the Sum_Score of IDs 10, 12, 14 and 16 = 77 + 62 + 72 + 88 = 299

Output:

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)
pandas aggregate recursion join sql
2 Answers

Schema

create table tablename(ID int,      GroupID int,  Contact int,  Subject int,  Score int);
insert into tablename values(10,     32,      8017,     5,       77);
insert into tablename values(11,     15,      5019,     1,       80);
insert into tablename values(12,     32,      8018,     3,       62);
insert into tablename values(13,     17,      8870,     9,       63);
insert into tablename values(14,     49,      8018,     11,      72);
insert into tablename values(15,     19,      8305,     7 ,      93);
insert into tablename values(16,     22,      8029,     11,      88);

Query #1 (SQL Server)

with cte as (
select id rid,* from tablename 
union all
select cte.rid rid,t.* from tablename t inner join cte on (t.groupid=cte.groupid or t.contact=cte.contact or t.subject=cte.subject) and t.id>cte.id
),
finalcte as(
select  id,groupid,contact,subject,sum(score)over(partition by rid) TotalScore from cte)
select t.id,t.groupid,t.contact,t.subject,SumScore.TotalScore from tablename t 
outer apply (select max(TotalScore)TotalScore from finalcte c where t.id=c.id) SumScore

Query #2 (MySQL v8.0 and PostgreSQL)

  WITH RECURSIVE cte as (
select id rid,id,groupid,contact,subject,score from tablename 
union all
select cte.rid rid,t.id,t.groupid,t.contact,t.subject,t.score from tablename t inner join cte on (t.groupid=cte.groupid or t.contact=cte.contact or t.subject=cte.subject) and t.id>cte.id
),
finalcte as(
select  id,groupid,contact,subject,sum(score)over(partition by rid) TotalScore from cte)

select t.id,t.groupid,t.contact,t.subject,max(TotalScore) from tablename t inner join finalcte c on t.id=c.id
group by t.id,t.groupid,t.contact,t.subject
order by t.id;

Query #3 Oracle

with cte (rid,id,groupid,contact,subject,score) as (
select id rid,id,groupid,contact,subject,score from tablename 
union all
select cte.rid rid,t.id,t.groupid,t.contact,t.subject,t.score from tablename t inner join cte on (t.groupid=cte.groupid or t.contact=cte.contact or t.subject=cte.subject) and t.id>cte.id
),
finalcte as(
select  id,groupid,contact,subject,sum(score)over(partition by rid) TotalScore from cte)

select t.id,t.groupid,t.contact,t.subject,SumScore.TotalScore from tablename t 
outer apply (select max(TotalScore)TotalScore from finalcte c where t.id=c.id) SumScore
order by t.id

Output:

id groupid contact subject max(TotalScore)
10 32 8017 5 299
11 15 5019 1 80
12 32 8018 3 299
13 17 8870 9 63
14 49 8018 11 299
15 19 8305 7 93
16 22 8029 11 299

View on DB Fiddle

user_86516010

This probably can be further optimized but works well , with pandas and 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
user_98406370
Related