Given a string column col1 containing hierarchical relationships, such as:
id1/id3/id4/id5
id2/id3/id6/id4/id10
id1/id7/id8
id10/id3/id14/id21/id34
how would you get the unique numbers of ids under id3? In this case it’s 7:
[id4, id5, id6, id10, id14, id21, id34]
I have a sketch of a pretty complex solution using arrays by splitting the col1, reversing the result array and getting the cardinality up until the index of ‘John’.
But it looks pretty complicated and was wondering if there is some other quicker "hack".
>Solution :
As a start, you could do this, and then apply your rules to the result this is giving:
SELECT
R,
t2,
array_position(string_to_array(col1,'/'), t2) as P
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY col1) R,
col1
FROM
table1) t1
CROSS JOIN unnest(string_to_array(col1,'/')) t2
results:
| r | t2 | p |
|---|---|---|
| 1 | id10 | 1 |
| 1 | id3 | 2 |
| 1 | id14 | 3 |
| 1 | id21 | 4 |
| 1 | id34 | 5 |
| 2 | id1 | 1 |
| 2 | id3 | 2 |
| 2 | id4 | 3 |
| 2 | id5 | 4 |
| 3 | id1 | 1 |
| 3 | id7 | 2 |
| 3 | id8 | 3 |
| 4 | id2 | 1 |
| 4 | id3 | 2 |
| 4 | id6 | 3 |
| 4 | id4 | 4 |
| 4 | id10 | 5 |
see: DBFIDDLE