Algorithm to get distinct records from cartesian product

Question

I have two tables(say, A and B). My task is to synchronise B with A, i.e. add records to B if present in A but not in B; and delete recoreds from B if present in B but not in A.

A and B can have duplicates records, such that if records are duplicates in A, B should also have duplicates. Sample data in A and B

      **Table A**                              **Table B**
    id    identifier                      id       identifier
    100   capital                         1001     bat
    201   bat                             1002     bat
    202   bat                             1003     bat
                                          5010     keyboard

For this I have fetched records from A and B using outer join, such that my output looks like:

    A.id  B.id   identifier
    100   null    capital
    201   1001    bat
    201   1002    bat   
    201   1003    bat
    202   1001    bat
    202   1002    bat
    202   1003    bat
    null  5010    keyboard

Thus in above case, 100 and 5010 are add and delete candidates respectively, which is easy to figure out.

The issue is to find that 1003 is also a delete candidate. Since 201 and 202 are mapped to 1001 and 1002 respectively.

I can do this in database, by numbering the duplicates in database as done in MYSQL: Avoiding cartesian product of repeating records when self-joining But due to some restrictions, I can only load data in above format using outer join. Therefore I need an algorithm in JAVA to do the above. Thanks in advance.

Because every record after getting inserted into B gets some data associated with it. That's the whole reason of synchronisation. — Mak, Aug 13 '18 at 18:20
Also since you want a Java algorithm it would be good to know what kind of data structure you are using to store these (a list?). — jdehesa, Aug 14 '18 at 10:15
Another question, what if there are more duplicates of an identifier in `A` than in `B`? Should the "extra" duplicates be associated with `null`? — jdehesa, Aug 14 '18 at 10:32

jdehesa · Answer 1 · 2018-08-16T13:06:52.737

I ended up coming up with this algorithm, which is not terribly clean or smart but seems to do the job:

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

class SyncAlgorithm {

    static class JoinResult {
        public final Integer aId;
        public final Integer bId;
        public final String identifier;
        public JoinResult(Integer aId, Integer bId, String identifier) {
            this.aId = aId;
            this.bId = bId;
            this.identifier = identifier;
        }
    }

    public static void main(String[] args) {
        List<JoinResult> table = makeTestTable();
        System.out.println("Initial table:");
        printTable(table);
        System.out.println();

        Iterator<JoinResult> iter = table.iterator();
        // A.id values we have seen
        Map<String, Set<Integer>> aSeen = new HashMap<String, Set<Integer>>();
        // A.id values we have used
        Map<String, Set<Integer>> aUsed = new HashMap<String, Set<Integer>>();
        // B.id values we have seen
        Map<String, Set<Integer>> bUsed = new HashMap<String, Set<Integer>>();
        // Loop over table to remove unnecessary rows
        while (iter.hasNext()) {
            JoinResult row = iter.next();
            // Make sure sets exist for current identifier
            if (!aSeen.containsKey(row.identifier)) {
                aSeen.put(row.identifier, new HashSet<Integer>());
            }
            if (!aUsed.containsKey(row.identifier)) {
                aUsed.put(row.identifier, new HashSet<Integer>());
            }
            if (!bUsed.containsKey(row.identifier)) {
                bUsed.put(row.identifier, new HashSet<Integer>());
            }
            // If there is no match in A remove
            if (row.aId == null) {
                iter.remove();
            // If both A.id and B.id are note null
            } else if (row.bId != null) {
                // Mark A.id as seen
                aSeen.get(row.identifier).add(row.aId);
                // If A.id or B.id were already used discard row
                if (aUsed.get(row.identifier).contains(row.aId) || bUsed.get(row.identifier).contains(row.bId)) {
                    iter.remove();
                // If both ids are new mark them as used and keep the row
                } else {
                    aUsed.get(row.identifier).add(row.aId);
                    bUsed.get(row.identifier).add(row.bId);
                }
            // If A.id is not null but B.id is null save A.id and keep the row
            } else {
                aSeen.get(row.identifier).add(row.aId);
                aUsed.get(row.identifier).add(row.aId);
            }
        }
        // Add A.id values without that have been seen but not used
        for (Map.Entry<String, Set<Integer>> aSeenEntry : aSeen.entrySet())
        {
            Set<Integer> aSeenId = aSeenEntry.getValue();
            aSeenId.removeAll(aUsed.get(aSeenEntry.getKey()));
            for (Integer aId : aSeenId) {
                table.add(new JoinResult(aId, null, aSeenEntry.getKey()));
            }
        }

        System.out.println("Result table:");
        printTable(table);
    }

    static List<JoinResult> makeTestTable() {
        List<JoinResult> table = new ArrayList<JoinResult>();
        table.add(new JoinResult(100, null, "capital"));
        table.add(new JoinResult(201, 1001, "bat"));
        table.add(new JoinResult(201, 1002, "bat"));
        table.add(new JoinResult(201, 1003, "bat"));
        table.add(new JoinResult(202, 1001, "bat"));
        table.add(new JoinResult(202, 1002, "bat"));
        table.add(new JoinResult(202, 1003, "bat"));
        table.add(new JoinResult(null, 5010, "keyboard"));
        table.add(new JoinResult(501, 3001, "foo"));
        table.add(new JoinResult(502, 3001, "foo"));
        return table;
    }

    static void printTable(List<JoinResult> table) {
        System.out.println("A.id    B.id    identifier");
        for (JoinResult row : table) {
            System.out.printf("%-8d%-8d%s\n", row.aId, row.bId, row.identifier);
        }
    }
}

Output:

Initial table:
A.id    B.id    identifier
100     null    capital
201     1001    bat
201     1002    bat
201     1003    bat
202     1001    bat
202     1002    bat
202     1003    bat
null    5010    keyboard
501     3001    foo
502     3001    foo

Result table:
A.id    B.id    identifier
100     null    capital
201     1001    bat
202     1002    bat
501     3001    foo
502     null    foo

score 0 · Answer 2 · answered May 30 '19 at 09:31

Here is how I solved this:

Fetch data from Table A and Table B.
Bucket data for Table A and Table B by the identifier, using :

    Map<String, SameBucketObject>

where key is 'identifier' and SameBucketObject is :

    class SameBucketObject{
      private List<String> idsOfA;
      private List<String> idsOfB;
    // getter, setters, addToList statements  
    }

Basically, I grouped all elements of Table A and Table B by identifier.

In each bucket, check count of elements of A idsOfA and elements of B idsOfB, and if

    sizeOf(idsOfA) < sizeOf(idsOfB) -> add elements with ids in idsOfB List from Table B to Table A
    sizeOf(idsOfA) > sizeOf(idsOfB) -> delete sizeOf(idsOfA) - sizeOf(idsOfB) elements from A from last.
    sizeOf(idsOfA) = sizeOf(idsOfB) -> no action.

This approach does not use extra space

Algorithm to get distinct records from cartesian product

2 Answers2