I have a table:
CREATE TABLE T (
order_id Integer PRIMARY KEY,
items_current_order Integer[],
items_next_order Integer[]
);
INSERT INTO T(order_id, items_current_order, items_next_order) VALUES (4254677, '{1, 1, 1}', '{1, 1}');
INSERT INTO T(order_id, items_current_order, items_next_order) VALUES (4254645, '{1,2,5,6}', '{1,1,6}');
I need to count the number of elements of items_next_order included in items_current_order.
The result that I expect:
client_id | order_id | items_current_order | items_next_order | count |
---|---|---|---|---|
905562 | 4254677 | {1, 1, 1} | {1, 1} | 2 |
905562 | 4254645 | {1, 2, 5, 6} | {1, 1, 6} | 2 |
For example, for order_id = 4254645, the result (count) should be 2, since only 2 of the values items_next_order (1, 6) are included in the items_current_order.
The second {1} from items_next_order is not included because there is only one in the items_current_order.
I’m trying something like that:
select
order_id
, items_current_order
, items_next_order
, (SELECT
COUNT(case when arr=matches then matches end)
FROM unnest(items_current_order) arr cross join unnest(items_next_order) matches
where arr is not null and matches is not null )
from T
4
Answers
I’ll try to break down this problem for you:
Case 1 – A = {1, 1}; B = {1,}, the count will be 1.
Case 2 – A = {1,}; B = {1,}, the count will be 1.
Case 3 – A = {1,}; B = {1,1}, the count will be 1.
From case 1 and case 2, you can see that one of the arrays becomes the "limiting reagent".
So, I propose a solution that gets the minimum of the count of every integer in A and B and adds it up.
The SQL code might look something like this:
output:
see: DBFIDDLE
Now i just need to explain why a count 1 for order 4254677 should be correct…. (but I cannot do so…. 🤔😢)
You can check not only item(current_order)=item(next_order). Necessary check count of equal items and take min value.
See example.
Internal output for clarity
To count the number of elements in the items_next_order array that are also present in the items_current_order array for each row in the table, you can use the following SQL query:
SELECT order_id,
(SELECT COUNT(*) FROM unnest(items_next_order) AS i
WHERE i = ANY(items_current_order)) AS count_matching_items
FROM T;
This query uses the unnest function to expand the items_next_order array into individual elements and then checks if each element is present in the items_current_order array using the = ANY() operator. The COUNT(*) function is used to count the number of matching items.
You can use this query to get the count of matching items for each order_id in your table T.