Find the occurrences (count) of all elements at all positions in an array in Postgres?

user_3659361 0
Viewed 431

How can we find the occurrence count to position mapping for elements in an array type in postgres ?

For example:

["A", "B", "C", "D"]
["A", "B", "C", "D"]
["A", "B", "D", "C"]
["A", "D", "C", "B"]

Should yield

Element Position Occurrences
A 1 4
A 2 0
A 3 0
A 4 0
B 1 0
B 2 3
B 3 0
B 4 1
C 1 0
C 2 0
C 3 3
C 4 1
D 1 0
D 2 1
D 3 1
D 4 2

I know about that array_position can get the position (1-based) but I am stumped as to how to achieve what I want ?

SQL Query:

create table test (id integer, priorities varchar(100)[]);
insert into test (id, priorities) values (1, '{"A","B","C","D"}');
insert into test (id, priorities) values (2, '{"A","B","C","D"}');
insert into test (id, priorities) values (3, '{"A","D","C","B"}');
insert into test (id, priorities) values (4, '{"A","C","B","D"}');

Edit #1: I think I can somehow combine unnest with ordinality and group by to achieve this.

postgresql arrays sql
2 Answers

This can be achieved like this

SELECT a.elem, a.nr, count(a.elem) 
FROM   test t, unnest(t.priorities) WITH ORDINALITY a(elem, nr) GROUP BY a.elem, a.nr;
user_36593610

demo:db<>fiddle

The tricky part is to get the zero records.

For that it can be helpful to create a table which provides all possible combinations:

SELECT
    *
FROM (
    SELECT
        generate_series(1, max(cardinality(myarray))) idx   -- 1
    FROM t
) gs
CROSS JOIN (                                                -- 3
    SELECT DISTINCT                                         -- 2
        unnest(myarray) val
    FROM t
) values
  1. Create a series from 1 to the max length of an array. This ensures combinations even for different lengths
  2. Create a table with all elements which could be appear within any array.
  3. CROSS JOIN them.

Now you have a reference table. In the next step you can use the unnest() WITH ORDINALITY you provided in your own solution. This result can be LEFT JOINed on the reference table. So you are creating a table with the expected non-zero records and a number of NULL records:

SELECT
    values.val, gs.idx,
    COUNT(*) FILTER (WHERE s.index IS NOT NULL)                   -- 1
FROM (
    SELECT
        generate_series(1, max(cardinality(myarray))) idx
    FROM t
) gs
CROSS JOIN (
    SELECT DISTINCT
        unnest(myarray) val
    FROM t
) values
LEFT JOIN (
    SELECT
        *
    FROM t,
        unnest(myarray) WITH ORDINALITY as a(val, index)
) s ON s.index = gs.idx AND s.val = values.val
GROUP BY values.val, gs.idx
ORDER BY values.val, gs.idx

Finally you can group this entire jpoin. Using a FILTER clause on COUNT() ensures, that only those records are counted, that are not NULL, which means: Count only those combinations which really appear in the data. For the NULL records, the filter would return 0.

user_39842210
Related