PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get count values array1 in array2

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
Tags:

4

Answers


  1. 0

    I’ll try to break down this problem for you:

    1. For a number to be counted, it needs to appear in both the first array, A and the second array, B.
    2. For any integer in A or B, the final count for that particular integer is the minimum of its count in A and B. To put this into perspective:

    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:

    select order_id, items_current_order, items_next_order, (
    with a as (
        select num, count(*) as a_count from unnest(items_current_order) num group by num
    ), b as (
        select num, count(*) as b_count from unnest(items_next_order) num group by num
    ) select sum(least(a_count, b_count))
    from b
    inner join a on a.num = b.num
) from t;
    Login or Signup to reply.
  2. 0 
    select 
  T.order_id
  , items_current_order
  , items_next_order
  , COUNT(CASE WHEN arr.arr=matches.arr THEN 1 END)
from T
INNER JOIN (SELECT DISTINCT order_id, arr.*
            FROM T
            CROSS JOIN unnest(T.items_current_order) arr) arr on arr.ordeR_id=T.order_id
INNER JOIN (SELECT DISTINCT order_id, arr.*
            FROM T
            CROSS JOIN unnest(T.items_next_order) arr)  matches on matches.ordeR_id=T.order_id
WHERE arr.arr=matches.arr
GROUP BY
  T.order_id
  , items_current_order
  , items_next_order

    output:

    order_id items_current_order items_next_order count
    4254645 {1,2,5,6} {1,1,6} 2
    4254677 {1,1,1} {1,1} 1

    see: DBFIDDLE

    Now i just need to explain why a count 1 for order 4254677 should be correct…. (but I cannot do so…. 🤔😢)

    Login or Signup to reply.
  3. 0

    You can check not only item(current_order)=item(next_order). Necessary check count of equal items and take min value.
    See example.

    select order_id, items_current_order, items_next_order
  ,(select count(*) 
    from
     (
      select ci,row_number()over(partition by ci order by ci)cin 
      from unnest(t.items_current_order) ci
     )co
    inner join (
      select ni,row_number()over(partition by ni order by ni)nin 
      from unnest(t.items_next_order) ni
     )cn on ci=ni and cin=nin
  ) as cnt
from T

    Internal output for clarity

    order_id items_current_order items_next_order ci cin ni nin
    4254677 {1,1,1} {1,1} 1 1 1 1
    4254677 {1,1,1} {1,1} 1 2 1 2
    4254645 {1,2,5,6} {1,1,6} 1 1 1 1
    4254645 {1,2,5,6} {1,1,6} 6 1 6 1
    Login or Signup to reply.
  4. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search