Cloud Stack Ninja

I have two data sets as below:

First Data Set

ID   Sequence
1    1
1    2
2    1
2    2
2    3
2    4
2    5
2    7
3    3
3    4
4    5
5    6  

Second data set

ID  Sequence  User1   User2  
1   2         abc     abc2
2   2         def     def2
2   4         pqr     pqr2
3   3         mno     mno2
3   3         mnx     mnx2
4   5         xyz     xyz2
6   6         qaz     qaz2

ID and Sequence in both data sets are integer.

I am looking for the output as below:

ID  Sequence  User1  User2
2   3         def    def2
2   5         pqr    pqr2
2   7         pqr    pqr2
3   4         mno    mno2
3   4         mnx    mnx2

Meaning, I want only those ID and Sequence combination from first data set which doesn't exists in second data set. Also, I want only those ID and Sequence for which "first data set".ID = "second data set".ID and "first data set".Sequence > "second data set".Sequence. Also, the User value should replicate "PARTITION BY ID". Meaning, in 2nd data set I have User value "def" for combination of Id=2 and Sequence=2, that value should replicate for Id=2 and Sequence=3 row in output. Similarly, I have User value "pqr" for combination of Id=2 and Sequence=4, that value should replicate for Id=2 and Sequence=5 and Id=2 and Sequence=7 row in output. Also, in case 2nd data set have multiple user values then output should show multiple users (e.g. Id=3 and Sequence=3 have two User1 values mno and mnx. In the output for Id=3 and Sequence=4, I am expecting to see both mno and mnx).

I am trying this query in DB2 and running on top of a large data set, so performance is also important.



Read more here: https://stackoverflow.com/questions/64403533/sql-to-replicate-values-for-similar-partition-by-values-with-high-volume-data

Content Attribution

This content was originally published by Koushik Chandra at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: