A more complex Island & Gaps problem in SQL

user_17948417 0
Viewed 201

First of all i would like to apologize if this was already discussed, but after 4 hours of searching I wasn't able to find anything which could help me with my problem.

This is the scenario: there are towers where different clients can have different equipment mounted at different heights on that tower (like the tower in telecom).

example

These are the facts:

  • the tower has 4 sides and the equipment can be mounted (for the same height) only on 2 opposite sides. So we cannot have equipment mounted (at the same height) on 3 different sides of the tower not on 2 sides which are near. I hope I'm explaining ok.

  • each client has its own range from the total height of an tower.

The requirement is to determine what would be the availability for each client in that tower. If an client has an equipment mounted only on one side, it would be considered Partial Available; if has no equipment at all it would be considered Available. If the customer has equipment mounted on bot opposite sides of the tower, that height it will be considered not available.

example

These would be the data we are working on:

create table tower_test
(Tower_Number        VARCHAR2(12),
 Tower_Side          VARCHAR2(1),
 Tower_Height        NUMBER,
 Tower_Height_Um     VARCHAR2(1) default 'm',
 Client              VARCHAR2(25),
 Client_Start_Height NUMBER,
 Range_From          NUMBER,
 Range_To            NUMBER);
 
-- No Client

-- Side A
-- Client 1 
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_1', 12, 17, 18);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_1', 12, 22, 23);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_2', 24, 35, 36);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_3', 40, 40, 41);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'A', 50, 'Client_3', 40, 47, 48);

-- Side B
-- Client 1
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_1', 12, 13, 14);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_1', 12, 19, 20);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_2', 24, 31, 32);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_2', 24, 37, 38);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_3', 40, 43, 44);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'B', 50, 'Client_3', 40, 46, 47);

-- Side C
-- Client 1 
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_1', 12, 17, 18);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_1', 12, 22, 23);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_2', 24, 28, 29);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_3', 40, 40, 41);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'C', 50, 'Client_3', 40, 47, 48);

-- Side D
-- Client 1
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_1', 12, 19, 20);
-- Client 2
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_2', 24, 31, 32);
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_2', 24, 37, 38);
-- Client 3
INSERT INTO tower_test (Tower_Number, Tower_Side, Tower_Height, Client, Client_Start_Height, Range_From, Range_To) VALUES ('123456_TWR1', 'D', 50, 'Client_3', 40, 46, 47);

COMMIT;

SELECT * FROM tower_test;

So, starting from this set of data:

example

I would need this format:

example

Is there any way to get that in Oracle SQL using Gap & Island approach? If yes, can you explain me how to achieve it?

Thanks!

gaps-and-islands oracle sql
2 Answers

Here's a solution that fills up the gaps in the ranges.

It's not exactly as the expected results.
F.e. the gaps get an 'A' code for available.
But it'll be something for you to start with.

The first CTE flattens the data, and gets counts for the tower sides.
The second CTE adds the gaps.

WITH CTE1_FLATTEND AS 
(
    SELECT 
      Tower_Number
    , Client
    , Client_Start_Height
    , Range_From
    , Range_To
    , COUNT(DISTINCT
            CASE
            WHEN Tower_Side IN ('A','C')
            THEN Tower_Side
            END) AS Total_AC
    , COUNT(DISTINCT
            CASE
            WHEN Tower_Side IN ('B','D')
            THEN Tower_Side
            END) AS Total_BD
    , COUNT(DISTINCT Tower_Side) AS Total_ABCD
    , MAX(Tower_Height) AS Tower_Height
    , MAX(Tower_Height_Um) AS Tower_Height_Um
    FROM tower_test
    GROUP BY
      Tower_Number
    , Client
    , Client_Start_Height
    , Range_From
    , Range_To
)
, CTE2_GAPS_ADDED AS
(
    SELECT
      Tower_Number
    , Client
    , Client_Start_Height
    , Client_Start_Height AS Next_Client_Start_Height
    , COALESCE(LAG(Range_To) 
        OVER (PARTITION BY Tower_Number, Client_Start_Height
              ORDER BY Range_From)+1, Client_Start_Height) AS Range_From
    , Range_From-1 AS Range_To
    , 'A' AS Avlblt_Type -- Available Gap
    , Tower_Height
    , Tower_Height_Um
    FROM CTE1_FLATTEND
   
    UNION

    SELECT
      Tower_Number
    , Client
    , Client_Start_Height
    , COALESCE(LEAD(Client_Start_Height) 
        OVER (PARTITION BY Tower_Number
              ORDER BY Range_From), Client_Start_Height) AS Next_Client_Start_Height
    , Range_To+1 AS Range_From
    , COALESCE(LEAD(Range_From) 
        OVER (PARTITION BY Tower_Number
              ORDER BY Range_From)-1, Tower_Height) AS Range_To
    , 'A' AS Avlblt_Type -- Available Gap
    , Tower_Height
    , Tower_Height_Um
    FROM CTE1_FLATTEND
   
    UNION ALL
    
    SELECT
      Tower_Number
    , Client
    , Client_Start_Height
    , Client_Start_Height AS Next_Client_Start_Height
    , Range_From
    , Range_To
    , CASE
      WHEN Total_ABCD = 1 THEN 'P' -- Partial
      WHEN Total_ABCD > 2 THEN 'F' -- Fault
      WHEN Total_AC = 2   THEN 'T' -- Taken A C
      WHEN Total_BD = 2   THEN 'T' -- Taken B D
      ELSE 'E' -- Exception
      END AS Avlblt_Type
    , Tower_Height
    , Tower_Height_Um
    FROM CTE1_FLATTEND
    
    UNION ALL
    
    SELECT
      Tower_Number
    , 'No Client' AS Client
    , 1 AS Client_Start_Height
    , 0 AS Next_Client_Start_Height
    , 0 AS Range_From
    , MIN(Client_Start_Height)-1 AS Range_To
    , 'T' AS Avlblt_Type
    , Tower_Height
    , Tower_Height_Um
    FROM CTE1_FLATTEND
    GROUP BY 
      Tower_Number
    , Tower_Height
    , Tower_Height_Um
) 
SELECT 
  Tower_Number
, Tower_Height
, Tower_Height_Um
, Client
, Avlblt_Type
, Range_From
, Range_To
--, Client_Start_Height
--, Next_Client_Start_Height
FROM CTE2_GAPS_ADDED
WHERE Range_From <= Range_To
  AND NOT (Client_Start_Height < Next_Client_Start_Height
           AND Range_From >= Next_Client_Start_Height)
ORDER BY Tower_Number, Range_From;

TOWER_NUMBER | TOWER_HEIGHT | TOWER_HEIGHT_UM | CLIENT    | AVLBLT_TYPE | RANGE_FROM | RANGE_TO
:----------- | -----------: | :-------------- | :-------- | :---------- | ---------: | -------:
123456_TWR1  |           50 | m               | No Client | T           |          0 |       11
123456_TWR1  |           50 | m               | Client_1  | A           |         12 |       12
123456_TWR1  |           50 | m               | Client_1  | P           |         13 |       14
123456_TWR1  |           50 | m               | Client_1  | A           |         15 |       16
123456_TWR1  |           50 | m               | Client_1  | T           |         17 |       18
123456_TWR1  |           50 | m               | Client_1  | T           |         19 |       20
123456_TWR1  |           50 | m               | Client_1  | A           |         21 |       21
123456_TWR1  |           50 | m               | Client_1  | T           |         22 |       23
123456_TWR1  |           50 | m               | Client_2  | A           |         24 |       27
123456_TWR1  |           50 | m               | Client_2  | P           |         28 |       29
123456_TWR1  |           50 | m               | Client_2  | A           |         30 |       30
123456_TWR1  |           50 | m               | Client_2  | T           |         31 |       32
123456_TWR1  |           50 | m               | Client_2  | A           |         33 |       34
123456_TWR1  |           50 | m               | Client_2  | P           |         35 |       36
123456_TWR1  |           50 | m               | Client_2  | T           |         37 |       38
123456_TWR1  |           50 | m               | Client_2  | A           |         39 |       39
123456_TWR1  |           50 | m               | Client_3  | T           |         40 |       41
123456_TWR1  |           50 | m               | Client_3  | A           |         42 |       42
123456_TWR1  |           50 | m               | Client_3  | P           |         43 |       44
123456_TWR1  |           50 | m               | Client_3  | A           |         45 |       45
123456_TWR1  |           50 | m               | Client_3  | T           |         46 |       47
123456_TWR1  |           50 | m               | Client_3  | T           |         47 |       48
123456_TWR1  |           50 | m               | Client_3  | A           |         49 |       50

Demo on db<>fiddle here

The second CTE uses the first CTE a lot.
To improve performance, you could fill a temp table with the query from the first. Then use the temp table in the CTE with the unions.

user_40034190

This would probably be easier if you had your data in third normal form and the data in four tables:

tower (tower_number, tower_height, tower_height_um)
tower_sides (tower_number, tower_side)
tower_client (tower_number, client, client_start_height, client_end_height)
tower_equipment(tower, tower_side, client, range_from, range_to)

However, it is possible to split it into those components from your table and use:

SELECT tower_number,
       tower_height,
       tower_height_um,
       client,
       CASE availability
       WHEN 0 THEN 'T'
       WHEN 1 THEN 'P'
       END AS availability,
       range_from,
       range_to
FROM   (
SELECT h.tower_number,
       MAX(h.tower_height) AS tower_height,
       MAX(h.tower_height_um) AS tower_height_um,
       h.height,
       MAX(ch.client) AS client,
       COUNT(t.tower_side) AS availability
FROM   (
         -- Generate all the heights and sides
         WITH heights (tower_number, tower_side, height, tower_height, tower_height_um) AS (
           SELECT tower_number,
                  tower_side,
                  0,
                  MAX(tower_height),
                  MAX(tower_height_um) KEEP (DENSE_RANK LAST ORDER BY tower_height)
           FROM   tower_test
           GROUP BY tower_number, tower_side
         UNION ALL
           SELECT tower_number,
                  tower_side,
                  height + 1,
                  tower_height,
                  tower_height_um
           FROM   heights
           WHERE  height < tower_height
         )
         SELECT * FROM heights
       ) h
       LEFT OUTER JOIN (
         -- Include the client heights
         SELECT *
         FROM   (
           SELECT DISTINCT
                  tower_number,
                  client,
                  client_start_height AS start_height,
                  LEAD(client_start_height - 1, 1, tower_height) OVER (
                    PARTITION BY tower_number
                    ORDER BY client_start_height, range_to
                  ) AS end_height
           FROM   tower_test
         )
         WHERE  start_height < end_height
       ) ch
       ON (    h.tower_number = ch.tower_number
           AND h.height BETWEEN ch.start_height AND ch.end_height)
       -- Include the equipment ranges
       LEFT OUTER JOIN tower_test t
       ON (    t.tower_number = h.tower_number
           AND t.tower_side = h.tower_side
           AND h.height BETWEEN t.range_from AND t.range_to)
GROUP BY
      h.tower_number,
      h.height
)
MATCH_RECOGNIZE(
  PARTITION BY tower_number
  ORDER BY height
  MEASURES
    FIRST(tower_height) AS tower_height,
    FIRST(tower_height_um) AS tower_height_um,
    FIRST(client) AS client,
    FIRST(availability) AS availability,
    FIRST(height) AS range_from,
    LAST(height) AS range_to
  ONE ROW PER MATCH
  PATTERN (client_available+)
  DEFINE 
    client_available
      AS (FIRST(client) = client OR FIRST(client) IS NULL AND client IS NULL)
      AND FIRST(availability) = availability
)
WHERE availability < 2

Which, for your sample data, outputs:

TOWER_NUMBER TOWER_HEIGHT TOWER_HEIGHT_UM CLIENT AVAILABILITY RANGE_FROM RANGE_TO
123456_TWR1 50 m null T 0 11
123456_TWR1 50 m Client_1 T 12 12
123456_TWR1 50 m Client_1 P 13 14
123456_TWR1 50 m Client_1 T 15 16
123456_TWR1 50 m Client_1 T 21 21
123456_TWR1 50 m Client_2 T 24 27
123456_TWR1 50 m Client_2 P 28 29
123456_TWR1 50 m Client_2 T 30 30
123456_TWR1 50 m Client_2 T 33 34
123456_TWR1 50 m Client_2 P 35 36
123456_TWR1 50 m Client_2 T 39 39
123456_TWR1 50 m Client_3 T 42 42
123456_TWR1 50 m Client_3 P 43 44
123456_TWR1 50 m Client_3 T 45 45
123456_TWR1 50 m Client_3 T 49 50

db<>fiddle here

user_15092640
Related