SQL中更復雜的Island&Gaps問題-有解無憂

首先，如果已經討論過這個問題，我想道歉，但是經過 4 個小時的搜索，我找不到任何可以幫助我解決問題的東西。

這是這樣的場景：有塔，不同的客戶可以在塔的不同高度安裝不同的設備（如電信塔）。

SQL 中更復雜的 Island & Gaps 問題

這些是事實：

塔有 4 個側面，設備只能安裝在 2 個相對的側面（對于相同的高度）。因此，我們不能將設備安裝在（相同高度）塔的 3 個不同側面而不是靠近的 2 個側面。我希望我解釋得很好。
從塔的總高度開始，每個客戶都有自己的范圍。

要求是確定該塔中每個客戶端的可用性。如果客戶僅在一側安裝了設備，則將其視為部分可用；如果根本沒有設備，則將其視為可用。如果客戶將設備安裝在塔的相對兩側，則該高度將被視為不可用。

SQL 中更復雜的 Island & Gaps 問題

這些將是我們正在處理的資料：

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;

所以，從這組資料開始：

SQL 中更復雜的 Island & Gaps 問題

我需要這種格式：

SQL 中更復雜的 Island & Gaps 問題

有什么方法可以使用 Gap & Island 方法在 Oracle SQL 中實作這一點？如果是，你能解釋一下如何實作嗎？

謝謝！

uj5u.com熱心網友回復：

這是一個填補范圍空白的解決方案。

這并不完全符合預期的結果。
Fe 間隙得到一個可用的“A”代碼。
但這將是你開始的事情。

第一個 CTE 將資料展平，并獲取塔邊的計數。
第二個 CTE 增加了差距。

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_UM | 客戶 | AVLBLT_TYPE | RANGE_FROM | RANGE_TO
:----------- | ------------: | :---------------- | :-------- | :------------ | ---------: | --------:
123456_TWR1 | 50 | 米 | 沒有客戶 | T | 0 | 11
123456_TWR1 | 50 | 米 | 客戶_1 | 一個 | 12 | 12
123456_TWR1 | 50 | 米 | 客戶_1 | 磷 | 13 | 14
123456_TWR1 | 50 | 米 | 客戶_1 | 一個 | 15 | 16
123456_TWR1 | 50 | 米 | 客戶_1 | T | 17 | 18
123456_TWR1 | 50 | 米 | 客戶_1 | T | 19 | 20
123456_TWR1 | 50 | 米 | 客戶_1 | 一個 | 21 | 21
123456_TWR1 | 50 | 米 | 客戶_1 | T | 22 | 23
123456_TWR1 | 50 | 米 | 客戶_2 | 一個 | 24 | 27
123456_TWR1 | 50 | 米 | 客戶_2 | 磷 | 28 | 29
123456_TWR1 | 50 | 米 | 客戶_2 | 一個 | 30 | 30
123456_TWR1 | 50 | 米 | 客戶_2 | T | 31 | 32
123456_TWR1 | 50 | 米 | 客戶_2 | 一個 | 33 | 34
123456_TWR1 | 50 | 米 | 客戶_2 | 磷 | 35 | 36
123456_TWR1 | 50 | 米 | 客戶_2 | T | 37 | 38
123456_TWR1 | 50 | 米 | 客戶_2 | 一個 | 39 | 39
123456_TWR1 | 50 | 米 | 客戶_3 | T | 40 | 41
123456_TWR1 | 50 | 米 | 客戶_3 | 一個 | 42 | 42
123456_TWR1 | 50 | 米 | 客戶_3 | 磷 | 43 | 44
123456_TWR1 | 50 | 米 | 客戶_3 | 一個 | 45 | 45
123456_TWR1 | 50 | 米 | 客戶_3 | T | 46 | 47
123456_TWR1 | 50 | 米 | 客戶_3 | T | 47 | 48
123456_TWR1 | 50 | 米 | 客戶_3 | 一個 | 49 | 50

關于db<>fiddle的演示在這里

第二個 CTE 大量使用第一個 CTE。
為了提高性能，您可以使用第一個查詢填充臨時表。然后將 CTE 中的臨時表與工會一起使用。

uj5u.com熱心網友回復：

如果您有第三范式的資料和四個表中的資料，這可能會更容易：

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, client, range_from, range_to)

但是，可以將其拆分為表中的這些組件并使用：

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

其中，對于您的樣本資料，輸出：

TOWER_NUMBER TOWER_HEIGHT TOWER_HEIGHT_UM 客戶可用性范圍 RANGE_TO

123456_TWR1 50 米空值噸 0 11

123456_TWR1 50 米客戶端_1 噸 12 12

123456_TWR1 50 米客戶端_1 磷 13 14

123456_TWR1 50 米客戶端_1 噸 15 16

123456_TWR1 50 米客戶端_1 噸 21 21

123456_TWR1 50 米客戶端_2 噸 24 27

123456_TWR1 50 米客戶端_2 磷 28 29

123456_TWR1 50 米客戶端_2 噸 30 30

123456_TWR1 50 米客戶端_2 噸 33 34

123456_TWR1 50 米客戶端_2 磷 35 36

123456_TWR1 50 米客戶端_2 噸 39 39

123456_TWR1 50 米客戶_3 噸 42 42

123456_TWR1 50 米客戶_3 磷 43 44

123456_TWR1 50 米客戶_3 噸 45 45

123456_TWR1 50 米客戶_3 噸 49 50

TOWER_NUMBER	TOWER_HEIGHT	TOWER_HEIGHT_UM	客戶	可用性	范圍	RANGE_TO
123456_TWR1	50	米	空值	噸	0	11
123456_TWR1	50	米	客戶端_1	噸	12	12
123456_TWR1	50	米	客戶端_1	磷	13	14
123456_TWR1	50	米	客戶端_1	噸	15	16
123456_TWR1	50	米	客戶端_1	噸	21	21
123456_TWR1	50	米	客戶端_2	噸	24	27
123456_TWR1	50	米	客戶端_2	磷	28	29
123456_TWR1	50	米	客戶端_2	噸	30	30
123456_TWR1	50	米	客戶端_2	噸	33	34
123456_TWR1	50	米	客戶端_2	磷	35	36
123456_TWR1	50	米	客戶端_2	噸	39	39
123456_TWR1	50	米	客戶_3	噸	42	42
123456_TWR1	50	米	客戶_3	磷	43	44
123456_TWR1	50	米	客戶_3	噸	45	45
123456_TWR1	50	米	客戶_3	噸	49	50

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413589.html

標籤：

上一篇：如何為SQL中的所有行找到每組的最大值？

下一篇：如何使用生成系列連接兩個表