There is a table of sending messages M from sender S to one or several recipients R, for example:

M | S | R --+---+-- 1 | 1 | 2 2 | 1 | 1 2 | 1 | 2 3 | 2 | 1 4 | 2 | 1 4 | 2 | 2

Over time, it turned out that the groups of message recipients are often the same, so it is better to separate them into a separate table and associate each message M with a single group of recipients G:

 M | G --+--- 1 | 1 == R:{2} 2 | 2 == R:{1, 2} 3 | 3 == R:{1} 4 | 2 == R:{1, 2}

i.e

 G | R --+--- 1 | 2 2 | 1 2 | 2 3 | 1

Question: Is it possible to convert the first table to the last one using SQL tools? group sets of recipients of R messages in one table excluding repetitions (in the example, messages 2 and 4 were sent to the same recipient group {1, 2})?

  • What is the SQL dialect and how many users are currently in the database (maximum ID) - Mike

1 answer 1

You did not specify the SQL dialect in which this should be done. In different dialects there are different approaches to solve this problem, which will be much more effective than the less comprehensive solution given below.

 select distinct G, R from ( select min(M1) G from ( select M1.M M1,M2.M M2 from mess M1, mess M2, (select M, count(1) cnt,sum(R) rs from mess group by M) C1, (select M, count(1) cnt,sum(R) rs from mess group by M) C2 where M1.R=M2.R and C1.M=M1.M and C2.M=M2.M and C1.cnt=C2.cnt and C1.rs=C2.rs group by M1.M,M2.M having count(1)=max(C1.cnt) ) A group by M2 ) Z, mess M where MM=ZG

SQLFIDDLE

Group IDs are not assigned in a row, but by the ID of the minimum message with such a group. If we take the contents of the subquery Z and, in addition to G, output M2, we will get a list of messages with the ID of the recipient groups, which most likely would be necessary to insert into some other table.

Query logic: For each message we get the number of recipients and the sum of recipient IDs. We glue the table of messages with ourselves and the received quantities according to the condition of equality of recipients and quantities / amounts. If the number of such records in the context of one and any other messages coincides with the total number of recipients of the message, then such messages have the same set of recipients. The minimum message ID is 1 of the pairs equal to each other in the context of message ID 2 and there is a set of necessary groups, although as a result of the request the same group is repeated many times, but the ID is taken as minimal, so that it is the same for all identical messages. It remains to add a message table to it, take unique entries and now we have the required result. Recipient ID ( rs ) sums are not particularly needed; they serve for some optimization of the sample, eliminating part of the sets with the same number.

PS It is written in MySQL in which, as in Oracle, MS SQL, Postgress, instead of the usual JOIN, you can write a comma operator and make join conditions in from. If your DBMS does not support this operator or it is not equivalent to a JOIN, then you will have to replace the commas with a JOIN and transfer the join conditions to ON structures.

  • Thank you very much for such a detailed answer, on the basis of which we managed to convert a working database. In my case, MySQL was also used, but query optimization is not so important because it is a one-time change in structure. - pavel