...

SQL, split values of two columns respectively and count members of their intersection

 

In PostgreSQL database’s table T, items_current_order field and items_next_order field contain comma-separated strings having duplicate characters and enclosed by braces:

client_id

order_id

items_current_order

items_next_order

1

905562

4254677

{1,1,1}

{1,1}

2

905562

4254645

{1,2,5,6}

{1,1,6}

3

905562

4254646

{3,3,3,6,6,9,10}

{3,3,6,6}

We need to add a computed column named count to the table. Rule: Split items_current_order field and items_next_order field respectively into a set having duplicate members and count members of their intersection.

client_id

order_id

items_current_order

items_next_order

count

1

905562

4254677

{1,1,1}

{1,1}

2

2

905562

4254645

{1,2,5,6}

{1,1,6}

2

3

905562

4254646

{3,3,3,6,6,9,10}

{3,3,6,6}

4

Write the following SPL code:

A

1

=pg1.query("select * from T")

2

=A1.derive((left(mid(#3,2),-2).split@c() ^ left(mid(#4,2),-2).split@c()).len():count)

derive() function adds a computed column to A1’s table. #3 represents the 3rd field; we can also use name to represent the field. split() function splits each string; @c option means using the comma to separate the string. ^ is the operator for computing intersection.

Source:https://stackoverflow.com/questions/78453352/how-to-get-count-values-array1-in-array2


 
 •  94 View  •