SQL how to get dynamically determined array entry with string_to_array on a delimited string

Question

Suppose I have a table with the format

user, code, source

with sample data:

p1, a-b-c-d, g1
p2, b-c-d, g1
p4, q-a-b-c-d, g2
p5, b-e-d, g3
p6, q-a-c-d, g2
p7, c-d, g3
p3, a-b-a-a-d-e, g2
p8, a-b-a-a-d-e, g2

I want to write a query where I select all users who have a letter 'b' in their code, and then group by the letter that immediately follows it.

For example the output on the above should be:

count, group

3,c
1,e
2,a

To be clear there will always be a letter that follows 'b' if it exists.

Right now my query looks like:

select count(a.user), (string_to_array(a.code,'-'))[2]
from [table] a
where a.code LIKE 'b'
group by 2

But clearly this doesn't work

Lukasz Szozda · Accepted Answer · 2017-11-08 19:24:09Z

1

You could use unnest with ORDINALITY and LEAD to get next value, then simple GROUP BY:

CREATE TABLE t
AS
SELECT 'p1' "user", 'a-b-c-d' code, 'g1' source
UNION ALL SELECT'p2', 'b-c-d', 'g1'
UNION ALL SELECT'p4', 'q-a-b-c-d', 'g2'
UNION ALL SELECT'p5', 'b-e-d', 'g3'
UNION ALL SELECT'p6', 'q-a-c-d', 'g2'
UNION ALL SELECT'p7', 'c-d', 'g3'
UNION ALL SELECT'p3', 'a-b-a-a-d-e', 'g2'
UNION ALL SELECT'p8', 'a-b-a-a-d-e', 'g2';

Query:

WITH cte AS (
  select  * , LEAD(elem) OVER(PARTITION BY "user", code, source ORDER BY nr) grp
  from t a
  LEFT JOIN LATERAL unnest(string_to_array(a.code,'-')) 
          WITH ORDINALITY AS s(elem, nr) ON TRUE
)
SELECT grp, COUNT(*)
FROM cte
WHERE elem = 'b'
GROUP BY grp;

DBFiddle Demo

answered Nov 8, 2017 at 19:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL how to get dynamically determined array entry with string_to_array on a delimited string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related