postgresqlcontiguousltree

how to get last known contiguous value in postgres ltree field?


I have a child table called wbs_numbers. the primary key id is a ltree

A typical example is

id series_id
abc.xyz.00001 1
abc.xyz.00002 1
abc.xyz.00003 1
abc.xyz.00101 1

so the parent table called series. it has a field called last_contigous_max.

given the above example, i want the series of id 1 to have its last contigous max be 3

can always assume that the ltree of wbs is always 3 fragment separated by dot. and the last fragment is always a 5 digit numeric string left padded by zero. can always assume the first child is always ending with 00001 and the theoretical total children of a series will never exceed 9999.

If you think of it as gaps and islands, the wbs_numbers will never start with a gap within a series. it will always start with an island.

meaning to say this is not possible.

id series_id
abc.xyz.00010 1
abc.xyz.00011 1
abc.xyz.00012 1
abc.xyz.00101 1

This is possible

id series_id
abc.xyz.00001 1
abc.xyz.00004 1
abc.xyz.00005 1
abc.xyz.00051 1
abc.xyz.00052 1
abc.xyz.00100 1
abc.xyz.10001 2
abc.xyz.10002 2
abc.xyz.10003 2
abc.xyz.10051 2
abc.xyz.10052 2
abc.xyz.10100 2
abc.xyz.20001 3
abc.xyz.20002 3
abc.xyz.20003 3
abc.xyz.20004 3
abc.xyz.20052 3
abc.xyz.20100 3

so the last max contiguous in this case is

What's the query to calculate the last_contigous_max number for any given series_id?

I also don't mind having another table just to store "islands".

Also, you can safely assume that wbs_number records will never be deleted once created. The id in the wbs_numbers table will never be altered once filled in as well.

Meaning to say islands will only grow and never shrink.


Solution

  • You can carry out your problem following these steps:

    WITH cte AS (
        SELECT *, CAST(RIGHT(id_, 4) AS INTEGER)  AS idval
        FROM tab
    ), ranked AS (
        SELECT *,
               ROW_NUMBER() OVER(PARTITION BY series_id ORDER BY idval) AS rn
        FROM cte
    )
    SELECT series_id, idval
    FROM ranked
    WHERE idval = rn
    ORDER BY ROW_NUMBER() OVER(PARTITION BY series_id ORDER BY idval DESC)
    FETCH FIRST ROWS WITH TIES
    

    Check the demo here.