如何在Oracle查询中确定座位的特定行

wwwo4jvm  于 2023-11-17  发布在  Oracle
关注(0)|答案(3)|浏览(205)

我有要求，如出口座位的航班每排至少有一个座位必须使用（占用）。
例如：出口排座位是4ABC，4DEF，5ABC，5DEF。在4ABC至少有一个座位被使用，4 DEF至少有一个座位应该被使用同样的第5排也。如果4ABC，4DEF，5ABC，5DEF每排至少有一个座位没有使用，那么需要抛出警告。我有查询，返回下面的结果

  1. SELECT FLIGHT_CARRIER,FLIGHT_NUMBER,SEAT_NUMBER,PAX_ID,LOCATION_ATT,flight_date,int_row_pos,availability_attribute
  2.                 FROM SEAT_ALLOC WHERE AIRLINE ='LL'
  3.                 AND FLIGHT_CARRIER = 'LL'
  4.                 AND LOCATION_ATT LIKE  '%E%'
  5.                 AND FLIGHT_NUMBER='7893'
  6.                 AND flight_date=to_date('2021-10-10', 'YYYY-MM-DD');

字符串
结果
Results
现在如何验证每一行来检查availability_attribute是否可用。
我计划使用HashMap来分配这些值。但是是否有任何查询级别可以处理，比如用例。

oracle

3条答案

按热度按时间
wh6knrhe

wh6knrhe1#

样本数据如下：

  1. WITH 
  2.     seat_alloc (FLIGHT_CARRIER, FLIGHT_NUMBER, SEAT_NUMBER, PAX_ID, LOCATION_ATT, INT_ROW_POS, FLIGHT_DATE, AVAILABILITY_ATTRIBUTE) AS 
  3.         (   Select 'LL', 7893, '3D', Null, 'W-B', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  4.             Select 'LL', 7893, '3E', Null, 'B-M', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  5.             Select 'LL', 7893, '3F', Null, 'A-B', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  6.             Select 'LL', 7893, '4A', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  7.             Select 'LL', 7893, '4B', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  8.             Select 'LL', 7893, '4C', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  9.             Select 'LL', 7893, '4D', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  10.             Select 'LL', 7893, '4E', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  11.             Select 'LL', 7893, '4F', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  12.             Select 'LL', 7893, '5A', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  13.             Select 'LL', 7893, '5B', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  14.             Select 'LL', 7893, '5C', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  15.             Select 'LL', 7893, '5D', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  16.             Select 'LL', 7893, '5E', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  17.             Select 'LL', 7893, '5F', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  18.             Select 'LL', 7893, '6A', Null, 'W-B', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  19.             Select 'LL', 7893, '6B', Null, 'B-M', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  20.             Select 'LL', 7893, '6C', Null, 'A-B', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual         
  21.        ),

字符串
.使用退出行状态创建CTE：

  1. exit_rows_stat  AS
  2.       (   Select    FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS, 
  3.                     Case When SubStr(SEAT_NUMBER, -1) IN('A', 'B', 'C') Then 'ABC' 
  4.                          When SubStr(SEAT_NUMBER, -1) IN('D', 'E', 'F') Then 'DEF'
  5.                          When SubStr(SEAT_NUMBER, -1) IN('H', 'J', 'K') Then 'HJK'
  6.                     Else 'XXX' 
  7.                     End "EXIT_BLOCK",
  8.                     Count(*) "BLOCK_SEATS",
  9.                     Count(Case When AVAILABILITY_ATTRIBUTE = 'Occupied' Then 1 End) "OCCUPIED_CNT"
  10.           From      seat_alloc
  11.           Group By  FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS,
  12.                     Case When SubStr(SEAT_NUMBER, -1) IN('A', 'B', 'C') Then 'ABC' 
  13.                          When SubStr(SEAT_NUMBER, -1) IN('D', 'E', 'F') Then 'DEF'
  14.                          When SubStr(SEAT_NUMBER, -1) IN('H', 'J', 'K') Then 'HJK'
  15.                     Else 'XXX' 
  16.                     End
  17.           Having    Max(InStr(LOCATION_ATT, 'E')) > 0
  18.       )
  19. --  
  20. --  FLIGHT_CARRIER FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS EXIT_BLOCK BLOCK_SEATS OCCUPIED_CNT
  21. --  -------------- ------------- --------- ----------- ---------- ----------- ------------
  22. --  LL                      7893 11-OCT-23           4 DEF                  3            3
  23. --  LL                      7893 11-OCT-23           4 ABC                  3            3
  24. --  LL                      7893 11-OCT-23           5 DEF                  3            0
  25. --  LL                      7893 11-OCT-23           5 ABC                  3            0
  26. --

型
要获取警告，请将上述CTE与您的数据连接起来：

  1. --  M a i n    S Q L :
  2. SELECT    s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK,
  3.           Case  When  e.OCCUPIED_CNT = 0 
  4.                 Then  'ALERT *** Exit Row ' || s.INT_ROW_POS || ' Block ' || e.EXIT_BLOCK || ' all seats are empty!' 
  5.           End "WARNING"
  6. FROM      seat_alloc s
  7. LEFT JOIN exit_rows_stat e ON(e.FLIGHT_CARRIER = s.FLIGHT_CARRIER And 
  8.                               e.FLIGHT_NUMBER = s.FLIGHT_NUMBER And 
  9.                               e.FLIGHT_DATE = s.FLIGHT_DATE And 
  10.                               e.INT_ROW_POS = s.INT_ROW_POS)
  11. GROUP BY  s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK,
  12.           Case  When  e.OCCUPIED_CNT = 0 
  13.                 Then  'ALERT *** Exit Row ' || s.INT_ROW_POS || ' Block ' || e.EXIT_BLOCK || ' all seats are empty!' 
  14.           End
  15. ORDER BY  s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK

型
结果应该是...

  1. --  R e s u l t :
  2. --  FLIGHT_CARRIER FLIGHT_NUMBER FLIGHT_DATE INT_ROW_POS EXIT_BLOCK WARNING                                                 
  3. --  -------------- ------------- ----------- ----------- ---------- --------------------------------------------------------
  4. --  LL                      7893 11-OCT-23             3                                                                    
  5. --  LL                      7893 11-OCT-23             4 ABC                                                                
  6. --  LL                      7893 11-OCT-23             4 DEF                                                                
  7. --  LL                      7893 11-OCT-23             5 ABC        ALERT *** Exit Row 5 Block ABC all seats are empty!     
  8. --  LL                      7893 11-OCT-23             5 DEF        ALERT *** Exit Row 5 Block DEF all seats are empty!     
  9. --  LL                      7893 11-OCT-23             6

型
这应该给予你不同的航空公司/航班号/飞机/日期/.的警告，并得到数据集可以进一步处理，以得到你需要的。

UPDATE -退出行的动态块（注解后）

它看起来像是可以得到不同的块和座位的兴趣使用提供的数据.为此，我们将需要一个更大的样本数据集与三个不同的座位设置.

  1. WITH 
  2.     seat_alloc (FLIGHT_CARRIER, FLIGHT_NUMBER, SEAT_NUMBER, PAX_ID, LOCATION_ATT, INT_ROW_POS, FLIGHT_DATE, AVAILABILITY_ATTRIBUTE) AS 
  3.         (   -- FLIGHT 1 - 6 seats - 2 blocks - abc + def - 2 exit rows 
  4.             Select 'LL', 1111, '4A', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  5.             Select 'LL', 1111, '4B', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  6.             Select 'LL', 1111, '4C', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  7.             Select 'LL', 1111, '4D', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  8.             Select 'LL', 1111, '4E', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  9.             Select 'LL', 1111, '4F', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  10.             
  11.             Select 'LL', 1111, '5A', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  12.             Select 'LL', 1111, '5B', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  13.             Select 'LL', 1111, '5C', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  14.             Select 'LL', 1111, '5D', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  15.             Select 'LL', 1111, '5E', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  16.             Select 'LL', 1111, '5F', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  17.             -- FLIGHT 2 - 8 seats - 3 blocks - ab + cdef + gh - 2 exit rows
  18.             Select 'LL', 2222, '8A', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  19.             Select 'LL', 2222, '8B', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  20.             Select 'LL', 2222, '8C', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  21.             Select 'LL', 2222, '8D', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  22.             Select 'LL', 2222, '8E', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  23.             Select 'LL', 2222, '8F', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  24.             Select 'LL', 2222, '8G', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  25.             Select 'LL', 2222, '8H', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  26.             
  27.             Select 'LL', 2222, '9A', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  28.             Select 'LL', 2222, '9B', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  29.             Select 'LL', 2222, '9C', Null, 'A-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  30.             Select 'LL', 2222, '9D', Null, 'A-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  31.             Select 'LL', 2222, '9E', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  32.             Select 'LL', 2222, '9F', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  33.             Select 'LL', 2222, '9G', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  34.             Select 'LL', 2222, '9H', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  35.             -- FLIGHT 3 -  7 seats - 3 blocks - ab + cde + fg - 3 exit rows
  36.             Select 'LL', 3333, '8A', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  37.             Select 'LL', 3333, '8B', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  38.             Select 'LL', 3333, '8C', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  39.             Select 'LL', 3333, '8D', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  40.             Select 'LL', 3333, '8E', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  41.             Select 'LL', 3333, '8F', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  42.             Select 'LL', 3333, '8G', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  43.             
  44.             Select 'LL', 3333, '19A', Null, 'W-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  45.             Select 'LL', 3333, '19B', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  46.             Select 'LL', 3333, '19C', Null, 'A-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  47.             Select 'LL', 3333, '19D', Null, 'A-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  48.             Select 'LL', 3333, '19E', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  49.             Select 'LL', 3333, '19F', Null, 'W-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  50.             Select 'LL', 3333, '19G', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  51.             
  52.             Select 'LL', 3333, '29A', Null, 'W-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  53.             Select 'LL', 3333, '29B', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  54.             Select 'LL', 3333, '29C', Null, 'A-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  55.             Select 'LL', 3333, '29D', Null, 'A-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  56.             Select 'LL', 3333, '29E', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
  57.             Select 'LL', 3333, '29F', Null, 'W-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
  58.             Select 'LL', 3333, '29G', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual 
  59.         ),

型
在这里，我们有三种类型的出口排。第一排有6个座位，我们将分成2个街区，每个街区有3个座位。第二个航班有8个座位排，将分成3个街区，每个街区有2-4-2个座位。第三个航班有7个座位出口排，将分成3个街区，每个街区有2-3-2个座位。下面是cte（exit_rows_stat）来完成：

  1. exit_rows_stat  AS
  2.         (  Select Distinct FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS, 
  3.                         LISTAGG(Case When InStr(LOCATION_ATT, 'E') > 0 Then INT_ROW_POS || SubStr(SEAT_NUMBER, -1) End, '|') WITHIN GROUP (Order By SubStr(SEAT_NUMBER, -1))
  4.                             OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS) "SEATS",
  5.                         Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 3
  6.                              When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 2
  7.                              When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 2
  8.                         End "BLOCK_1",
  9.                         Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 3
  10.                              When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 3
  11.                              When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 4
  12.                         End "BLOCK_2",
  13.                         Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 0
  14.                              When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 2
  15.                              When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 2
  16.                         End "BLOCK_3"
  17.               From      seat_alloc
  18.         ),
  1. /*
  2. FL FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS E SEATS                             BLOCK_1    BLOCK_2    BLOCK_3
  3. -- ------------- --------- ----------- - ------------------------------ ---------- ---------- ----------
  4. LL          1111 11-OCT-23           4 Y 4A|4B|4C|4D|4E|4F                       3          3          0
  5. LL          1111 11-OCT-23           5 Y 5A|5B|5C|5D|5E|5F                       3          3          0
  6. LL          2222 11-OCT-23           8 Y 8A|8B|8C|8D|8E|8F|8G|8H                 2          4          2
  7. LL          2222 11-OCT-23           9 Y 9A|9B|9C|9D|9E|9F|9G|9H                 2          4          2
  8. LL          3333 11-OCT-23           8 Y 8A|8B|8C|8D|8E|8F|8G                    2          3          2
  9. LL          3333 11-OCT-23          19 Y 19A|19B|19C|19D|19E|19F|19G             2          3          2
  10. LL          3333 11-OCT-23          29 Y 29A|29B|29C|29D|29E|29F|29G             2          3          2 */

接下来是另一个cte（exit_row_seat_blocks）来定义块中的座位：

  1. exit_row_seat_blocks AS
  2.     ( Select    FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS, 
  3.                 BLOCK_1, BLOCK_2, BLOCK_3, 
  4.                 '|' || SubStr(SEATS, 1, InStr(SEATS, '|', 1, BLOCK_1) - 1) || '|' "BLOCK_1_SEATS",
  5.                 '|' || Case When BLOCK_3 = 0 Then SubStr(SEATS, InStr(SEATS, '|', 1, BLOCK_1) + 1)
  6.                        Else SubStr(SEATS, InStr(SEATS, '|', 1, BLOCK_1) + 1, InStr(SEATS, '|', 1, BLOCK_1 + BLOCK_2) - InStr(SEATS, '|', 1, BLOCK_1) - 1)
  7.                        End || '|' "BLOCK_2_SEATS", 
  8.                 '|' || Case When BLOCK_3 != 0 Then SubStr(SEATS, InStr(SEATS, '|', 1, BLOCK_1 + BLOCK_2) + 1) End || '|' "BLOCK_3_SEATS"
  9.       From      exit_rows_stat
  10.       Order By  FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS
  11.     )
  1. /*
  2. FL FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS    BLOCK_1    BLOCK_2    BLOCK_3 BLOCK_1_SEATS            BLOCK_2_SEATS            BLOCK_3_SEATS           
  3. -- ------------- --------- ----------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------
  4. LL          1111 11-OCT-23           4          3          3          0 |4A|4B|4C|               |4D|4E|4F|               ||                      
  5. LL          1111 11-OCT-23           5          3          3          0 |5A|5B|5C|               |5D|5E|5F|               ||                      
  6. LL          2222 11-OCT-23           8          2          4          2 |8A|8B|                  |8C|8D|8E|8F|            |8G|8H|                 
  7. LL          2222 11-OCT-23           9          2          4          2 |9A|9B|                  |9C|9D|9E|9F|            |9G|9H|                 
  8. LL          3333 11-OCT-23           8          2          3          2 |8A|8B|                  |8C|8D|8E|               |8F|8G|                 
  9. LL          3333 11-OCT-23          19          2          3          2 |19A|19B|                |19C|19D|19E|            |19F|19G|               
  10. LL          3333 11-OCT-23          29          2          3          2 |29A|29B|                |29C|29D|29E|            |29F|29G|               */

有了这个，我们可以建立每个区块的空座位字符串。如果每个区块的空座位列表等于上面的区块座位列表，那么在警告栏中显示警报。

  1. SELECT  FLIGHT_CARRIER, FLIGHT_NUMBER, PAX_ID, INT_ROW_POS, FLIGHT_DATE, Max(WARNING) "WARNING"
  2. FROM    ( Select  Distinct  a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.SEAT_NUMBER, a.PAX_ID, a.LOCATION_ATT, a.INT_ROW_POS, a.FLIGHT_DATE, a.AVAILABILITY_ATTRIBUTE,
  3.                 CASE WHEN  InStr(b.BLOCK_3_SEATS, a.SEAT_NUMBER) > 0 And 
  4.                     '|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_3_SEATS, '|' || a.SEAT_NUMBER || '|') > 0 
  5.                             Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER) 
  6.                               Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = Replace(b.BLOCK_3_SEATS, '||', '') 
  7.                     THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 3 seats ' || b.BLOCK_3_SEATS || ' are empty!'
  8.                     WHEN  InStr(b.BLOCK_2_SEATS, a.SEAT_NUMBER) > 0 And 
  9.                     '|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_2_SEATS, '|' || a.SEAT_NUMBER || '|') > 0 
  10.                             Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER) 
  11.                               Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = BLOCK_2_SEATS
  12.                     THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 2 seats ' || b.BLOCK_2_SEATS || ' are empty!'
  13.                     WHEN  InStr(b.BLOCK_1_SEATS, a.SEAT_NUMBER) > 0 And 
  14.                     '|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_1_SEATS, '|' || a.SEAT_NUMBER || '|') > 0 
  15.                             Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER) 
  16.                               Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = BLOCK_1_SEATS
  17.                     THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 1 seats ' || b.BLOCK_1_SEATS || ' are empty!' 
  18.                     END "WARNING"
  19.           From      seat_alloc a 
  20.           Left Join exit_row_seat_blocks b ON(b.FLIGHT_CARRIER = a.FLIGHT_CARRIER And
  21.                                               b.FLIGHT_NUMBER = a.FLIGHT_NUMBER And
  22.                                               b.FLIGHT_DATE = a.FLIGHT_DATE And
  23.                                               b.INT_ROW_POS = a.INT_ROW_POS)
  24.         )
  25. GROUP BY   FLIGHT_CARRIER, FLIGHT_NUMBER, PAX_ID, INT_ROW_POS, FLIGHT_DATE
  26. ORDER BY   FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS
  1. /*
  2. FL FLIGHT_NUMBER PAX_ID INT_ROW_POS FLIGHT_DA WARNING                                                         
  3. -- ------------- ------ ----------- --------- ----------------------------------------------------------------
  4. LL          1111                  4 11-OCT-23                                                                 
  5. LL          1111                  5 11-OCT-23 ALERT - Exit Row 5 all block 1 seats |5A|5B|5C| are empty!      
  6. LL          2222                  8 11-OCT-23                                                                 
  7. LL          2222                  9 11-OCT-23 ALERT - Exit Row 9 all block 2 seats |9C|9D|9E|9F| are empty!   
  8. LL          3333                  8 11-OCT-23                                                                 
  9. LL          3333                 19 11-OCT-23 ALERT - Exit Row 19 all block 3 seats |19F|19G| are empty!      
  10. LL          3333                 29 11-OCT-23 ALERT - Exit Row 29 all block 2 seats |29C|29D|29E| are empty!  */
展开查看全部
赞(0）分享  回复(0）举报  2023-11-17
093gszye

093gszye2#

您可以使用分析函数和条件聚合来检查每个座位块中是否至少预订了一个座位：

  1. SELECT FLIGHT_NUMBER,
  2.        SEAT_NUMBER,
  3.        flight_date,
  4.        availability_attribute,
  5.        CASE
  6.        WHEN COUNT(
  7.               CASE
  8.               WHEN availability_attribute = 'Occupied' AND seat_number IN ('4A', '4B', '4C')
  9.               THEN 1
  10.               END
  11.             ) OVER (PARTITION BY flight_number, flight_date) > 0
  12.        AND  COUNT(
  13.               CASE
  14.               WHEN availability_attribute = 'Occupied' AND seat_number IN ('4D', '4E', '4F')
  15.               THEN 1
  16.               END
  17.             ) OVER (PARTITION BY flight_number, flight_date) > 0
  18.        AND  COUNT(
  19.               CASE
  20.               WHEN availability_attribute = 'Occupied' AND seat_number IN ('5A', '5B', '5C')
  21.               THEN 1
  22.               END
  23.             ) OVER (PARTITION BY flight_number, flight_date) > 0
  24.        AND  COUNT(
  25.               CASE
  26.               WHEN availability_attribute = 'Occupied' AND seat_number IN ('5D', '5E', '5F')
  27.               THEN 1
  28.               END
  29.             ) OVER (PARTITION BY flight_number, flight_date) > 0
  30.        THEN 'Has exit seats occupied'
  31.        ELSE 'Exit seat not occupied'
  32.        END AS exit_check
  33. FROM   SEAT_ALLOC;

字符串
如果您需要不同的分组，请更改PARTITION BY子句，并包括您需要的任何其他列和过滤器（它们被删除，因为它们似乎不需要演示必要的技术）。

展开查看全部
赞(0）分享  回复(0）举报  2023-11-17
xlpyo6sf

xlpyo6sf3#

我假设LOCATION_ATT值包含一个'E'（LIKE '%E%'），它将座位标识为在出口排。此外，我们不知道哪些排是现有排，可能有0个或多个出口排。我还基于所有有问题的飞机都有一个单独的过道，两边各有3个座位（ABC和DEF）。
此外，航空公司、乘客和日期值对于这个问题来说是多余的，所以我把它们去掉了。
一些示例数据，包括非退出行值：

  1. WITH seat_alloc (seat_number, location_att, row_number, seat_avail) AS (
  2.     SELECT '4A', 'E-M', 4, 'Occ' FROM dual UNION ALL
  3.     SELECT '4B', 'W-E', 4, 'Vac' FROM dual UNION ALL
  4.     SELECT '4C', 'A-E', 4, 'Occ' FROM dual UNION ALL
  5.     SELECT '4D', 'A-E', 4, 'Vac' FROM dual UNION ALL
  6.     SELECT '4E', 'E-M', 4, 'Vac' FROM dual UNION ALL
  7.     SELECT '4F', 'W-E', 4, 'Occ' FROM dual UNION ALL
  8.     SELECT '5A', 'W-E', 5, 'Vac' FROM dual UNION ALL
  9.     SELECT '5B', 'E-M', 5, 'Occ' FROM dual UNION ALL
  10.     SELECT '5C', 'A-E', 5, 'Vac' FROM dual UNION ALL
  11.     SELECT '5D', 'A-E', 5, 'Vac' FROM dual UNION ALL
  12.     SELECT '5E', 'E-M', 5, 'Vac' FROM dual UNION ALL
  13.     SELECT '5F', 'W-E', 5, 'Vac' FROM dual UNION ALL
  14.     SELECT '6A', 'W-B', 6, 'Vac' FROM dual UNION ALL
  15.     SELECT '6B', 'B-M', 6, 'Vac' FROM dual UNION ALL
  16.     SELECT '6C', 'A-B', 6, 'Vac' FROM dual UNION ALL
  17.     SELECT '6D', 'A-B', 6, 'Vac' FROM dual UNION ALL
  18.     SELECT '6E', 'B-M', 6, 'Vac' FROM dual UNION ALL
  19.     SELECT '6F', 'W-B', 6, 'Vac' FROM dual),

字符串
我们可以看到，在第5排，D、E或F的座位都没有被填满，这意味着应该产生一个警告。我已经将问题分解为一些更小的部分，在这里产生CTE。首先，让我们获得出口排座位：

  1. exit_seats AS (
  2.     SELECT row_number,
  3.            seat_number,
  4.            seat_avail
  5.     FROM   seat_alloc
  6.     WHERE  location_att LIKE '%E%'),

型
然后计算出每个块中有多少个是空的：

  1. exit_unoccupied_ABC AS (
  2.     SELECT row_number,
  3.            COUNT (*) seats_free
  4.     FROM   exit_seats
  5.     WHERE  UPPER (seat_avail) = 'VAC'
  6.     AND    seat_number IN (
  7.         SELECT DISTINCT row_number || 'A' FROM exit_seats UNION ALL
  8.         SELECT DISTINCT row_number || 'B' FROM exit_seats UNION ALL
  9.         SELECT DISTINCT row_number || 'C' FROM exit_seats)
  10.     GROUP BY row_number),
  11. exit_unoccupied_DEF AS (
  12.     SELECT row_number,
  13.            COUNT (*) seats_free
  14.     FROM   exit_seats
  15.     WHERE  UPPER (seat_avail) = 'VAC'
  16.     AND    seat_number IN (
  17.         SELECT DISTINCT row_number || 'D' FROM exit_seats UNION ALL
  18.         SELECT DISTINCT row_number || 'E' FROM exit_seats UNION ALL
  19.         SELECT DISTINCT row_number || 'F' FROM exit_seats)
  20.     GROUP BY row_number)

型
由此，很容易根据需要生成警告：

  1. SELECT DISTINCT es.row_number,
  2.        abc.seats_free AS ABC,
  3.        def.seats_free AS DEF,
  4.        'Warning: empty exit seat block' AS warning_text
  5. FROM   exit_seats es
  6. INNER JOIN exit_unoccupied_ABC abc on es.row_number = abc.row_number
  7. INNER JOIN exit_unoccupied_DEF def on es.row_number = def.row_number
  8. WHERE (abc.seats_free = 3 OR def.seats_free = 3)

型
输出量：

  1. ROW_NUMBER        ABC        DEF WARNING_TEXT
  2. ---------- ---------- ---------- ------------------------------
  3.          5          2          3 Warning: empty exit seat block

型

展开查看全部
赞(0）分享  回复(0）举报  2023-11-17
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com