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

wwwo4jvm  于 12个月前  发布在  Oracle
关注(0)|答案(3)|浏览(159)

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

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

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

oracle

3条答案

按热度按时间
wh6knrhe

wh6knrhe1#

样本数据如下：

WITH 
    seat_alloc (FLIGHT_CARRIER, FLIGHT_NUMBER, SEAT_NUMBER, PAX_ID, LOCATION_ATT, INT_ROW_POS, FLIGHT_DATE, AVAILABILITY_ATTRIBUTE) AS 
        (   Select 'LL', 7893, '3D', Null, 'W-B', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '3E', Null, 'B-M', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '3F', Null, 'A-B', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '4A', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '4B', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '4C', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '4D', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '4E', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '4F', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '5A', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '5B', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '5C', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '5D', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '5E', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '5F', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '6A', Null, 'W-B', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 7893, '6B', Null, 'B-M', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 7893, '6C', Null, 'A-B', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual         
       ),

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

exit_rows_stat  AS
      (   Select    FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS, 
                    Case When SubStr(SEAT_NUMBER, -1) IN('A', 'B', 'C') Then 'ABC' 
                         When SubStr(SEAT_NUMBER, -1) IN('D', 'E', 'F') Then 'DEF'
                         When SubStr(SEAT_NUMBER, -1) IN('H', 'J', 'K') Then 'HJK'
                    Else 'XXX' 
                    End "EXIT_BLOCK",
                    Count(*) "BLOCK_SEATS",
                    Count(Case When AVAILABILITY_ATTRIBUTE = 'Occupied' Then 1 End) "OCCUPIED_CNT"
          From      seat_alloc
          Group By  FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS,
                    Case When SubStr(SEAT_NUMBER, -1) IN('A', 'B', 'C') Then 'ABC' 
                         When SubStr(SEAT_NUMBER, -1) IN('D', 'E', 'F') Then 'DEF'
                         When SubStr(SEAT_NUMBER, -1) IN('H', 'J', 'K') Then 'HJK'
                    Else 'XXX' 
                    End
          Having    Max(InStr(LOCATION_ATT, 'E')) > 0
      )
--  
--  FLIGHT_CARRIER FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS EXIT_BLOCK BLOCK_SEATS OCCUPIED_CNT
--  -------------- ------------- --------- ----------- ---------- ----------- ------------
--  LL                      7893 11-OCT-23           4 DEF                  3            3
--  LL                      7893 11-OCT-23           4 ABC                  3            3
--  LL                      7893 11-OCT-23           5 DEF                  3            0
--  LL                      7893 11-OCT-23           5 ABC                  3            0
--

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

--  M a i n    S Q L :
SELECT    s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK,
          Case  When  e.OCCUPIED_CNT = 0 
                Then  'ALERT *** Exit Row ' || s.INT_ROW_POS || ' Block ' || e.EXIT_BLOCK || ' all seats are empty!' 
          End "WARNING"
FROM      seat_alloc s
LEFT JOIN exit_rows_stat e ON(e.FLIGHT_CARRIER = s.FLIGHT_CARRIER And 
                              e.FLIGHT_NUMBER = s.FLIGHT_NUMBER And 
                              e.FLIGHT_DATE = s.FLIGHT_DATE And 
                              e.INT_ROW_POS = s.INT_ROW_POS)
GROUP BY  s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK,
          Case  When  e.OCCUPIED_CNT = 0 
                Then  'ALERT *** Exit Row ' || s.INT_ROW_POS || ' Block ' || e.EXIT_BLOCK || ' all seats are empty!' 
          End
ORDER BY  s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK

型
结果应该是...

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

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

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

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

WITH 
    seat_alloc (FLIGHT_CARRIER, FLIGHT_NUMBER, SEAT_NUMBER, PAX_ID, LOCATION_ATT, INT_ROW_POS, FLIGHT_DATE, AVAILABILITY_ATTRIBUTE) AS 
        (   -- FLIGHT 1 - 6 seats - 2 blocks - abc + def - 2 exit rows 
            Select 'LL', 1111, '4A', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 1111, '4B', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 1111, '4C', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 1111, '4D', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 1111, '4E', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 1111, '4F', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            
            Select 'LL', 1111, '5A', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 1111, '5B', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 1111, '5C', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 1111, '5D', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 1111, '5E', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 1111, '5F', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            -- FLIGHT 2 - 8 seats - 3 blocks - ab + cdef + gh - 2 exit rows
            Select 'LL', 2222, '8A', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8B', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8C', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8D', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8E', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8F', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8G', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '8H', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            
            Select 'LL', 2222, '9A', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 2222, '9B', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 2222, '9C', Null, 'A-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 2222, '9D', Null, 'A-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 2222, '9E', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 2222, '9F', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 2222, '9G', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 2222, '9H', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            -- FLIGHT 3 -  7 seats - 3 blocks - ab + cde + fg - 3 exit rows
            Select 'LL', 3333, '8A', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '8B', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '8C', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '8D', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '8E', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '8F', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '8G', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            
            Select 'LL', 3333, '19A', Null, 'W-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '19B', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '19C', Null, 'A-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '19D', Null, 'A-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '19E', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '19F', Null, 'W-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '19G', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            
            Select 'LL', 3333, '29A', Null, 'W-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '29B', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '29C', Null, 'A-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '29D', Null, 'A-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '29E', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
            Select 'LL', 3333, '29F', Null, 'W-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
            Select 'LL', 3333, '29G', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual 
        ),

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

exit_rows_stat  AS
        (  Select Distinct FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS, 
                        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))
                            OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS) "SEATS",
                        Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 3
                             When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 2
                             When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 2
                        End "BLOCK_1",
                        Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 3
                             When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 3
                             When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 4
                        End "BLOCK_2",
                        Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 0
                             When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 2
                             When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 2
                        End "BLOCK_3"
              From      seat_alloc
        ),
/*
FL FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS E SEATS                             BLOCK_1    BLOCK_2    BLOCK_3
-- ------------- --------- ----------- - ------------------------------ ---------- ---------- ----------
LL          1111 11-OCT-23           4 Y 4A|4B|4C|4D|4E|4F                       3          3          0
LL          1111 11-OCT-23           5 Y 5A|5B|5C|5D|5E|5F                       3          3          0
LL          2222 11-OCT-23           8 Y 8A|8B|8C|8D|8E|8F|8G|8H                 2          4          2
LL          2222 11-OCT-23           9 Y 9A|9B|9C|9D|9E|9F|9G|9H                 2          4          2
LL          3333 11-OCT-23           8 Y 8A|8B|8C|8D|8E|8F|8G                    2          3          2
LL          3333 11-OCT-23          19 Y 19A|19B|19C|19D|19E|19F|19G             2          3          2
LL          3333 11-OCT-23          29 Y 29A|29B|29C|29D|29E|29F|29G             2          3          2 */

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

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

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

SELECT  FLIGHT_CARRIER, FLIGHT_NUMBER, PAX_ID, INT_ROW_POS, FLIGHT_DATE, Max(WARNING) "WARNING"
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,
                CASE WHEN  InStr(b.BLOCK_3_SEATS, a.SEAT_NUMBER) > 0 And 
                    '|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_3_SEATS, '|' || a.SEAT_NUMBER || '|') > 0 
                            Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER) 
                              Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = Replace(b.BLOCK_3_SEATS, '||', '') 
                    THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 3 seats ' || b.BLOCK_3_SEATS || ' are empty!'
                    WHEN  InStr(b.BLOCK_2_SEATS, a.SEAT_NUMBER) > 0 And 
                    '|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_2_SEATS, '|' || a.SEAT_NUMBER || '|') > 0 
                            Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER) 
                              Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = BLOCK_2_SEATS
                    THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 2 seats ' || b.BLOCK_2_SEATS || ' are empty!'
                    WHEN  InStr(b.BLOCK_1_SEATS, a.SEAT_NUMBER) > 0 And 
                    '|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_1_SEATS, '|' || a.SEAT_NUMBER || '|') > 0 
                            Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER) 
                              Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = BLOCK_1_SEATS
                    THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 1 seats ' || b.BLOCK_1_SEATS || ' are empty!' 
                    END "WARNING"
          From      seat_alloc a 
          Left Join exit_row_seat_blocks b ON(b.FLIGHT_CARRIER = a.FLIGHT_CARRIER And
                                              b.FLIGHT_NUMBER = a.FLIGHT_NUMBER And
                                              b.FLIGHT_DATE = a.FLIGHT_DATE And
                                              b.INT_ROW_POS = a.INT_ROW_POS)
        )
GROUP BY   FLIGHT_CARRIER, FLIGHT_NUMBER, PAX_ID, INT_ROW_POS, FLIGHT_DATE
ORDER BY   FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS
/*
FL FLIGHT_NUMBER PAX_ID INT_ROW_POS FLIGHT_DA WARNING                                                         
-- ------------- ------ ----------- --------- ----------------------------------------------------------------
LL          1111                  4 11-OCT-23                                                                 
LL          1111                  5 11-OCT-23 ALERT - Exit Row 5 all block 1 seats |5A|5B|5C| are empty!      
LL          2222                  8 11-OCT-23                                                                 
LL          2222                  9 11-OCT-23 ALERT - Exit Row 9 all block 2 seats |9C|9D|9E|9F| are empty!   
LL          3333                  8 11-OCT-23                                                                 
LL          3333                 19 11-OCT-23 ALERT - Exit Row 19 all block 3 seats |19F|19G| are empty!      
LL          3333                 29 11-OCT-23 ALERT - Exit Row 29 all block 2 seats |29C|29D|29E| are empty!  */
赞(0）分享  回复(0）举报  12个月前
093gszye

093gszye2#

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

SELECT FLIGHT_NUMBER,
       SEAT_NUMBER,
       flight_date,
       availability_attribute,
       CASE
       WHEN COUNT(
              CASE
              WHEN availability_attribute = 'Occupied' AND seat_number IN ('4A', '4B', '4C')
              THEN 1
              END
            ) OVER (PARTITION BY flight_number, flight_date) > 0
       AND  COUNT(
              CASE
              WHEN availability_attribute = 'Occupied' AND seat_number IN ('4D', '4E', '4F')
              THEN 1
              END
            ) OVER (PARTITION BY flight_number, flight_date) > 0
       AND  COUNT(
              CASE
              WHEN availability_attribute = 'Occupied' AND seat_number IN ('5A', '5B', '5C')
              THEN 1
              END
            ) OVER (PARTITION BY flight_number, flight_date) > 0
       AND  COUNT(
              CASE
              WHEN availability_attribute = 'Occupied' AND seat_number IN ('5D', '5E', '5F')
              THEN 1
              END
            ) OVER (PARTITION BY flight_number, flight_date) > 0
       THEN 'Has exit seats occupied'
       ELSE 'Exit seat not occupied'
       END AS exit_check
FROM   SEAT_ALLOC;

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

赞(0）分享  回复(0）举报  12个月前
xlpyo6sf

xlpyo6sf3#

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

WITH seat_alloc (seat_number, location_att, row_number, seat_avail) AS (
    SELECT '4A', 'E-M', 4, 'Occ' FROM dual UNION ALL
    SELECT '4B', 'W-E', 4, 'Vac' FROM dual UNION ALL
    SELECT '4C', 'A-E', 4, 'Occ' FROM dual UNION ALL
    SELECT '4D', 'A-E', 4, 'Vac' FROM dual UNION ALL
    SELECT '4E', 'E-M', 4, 'Vac' FROM dual UNION ALL
    SELECT '4F', 'W-E', 4, 'Occ' FROM dual UNION ALL
    SELECT '5A', 'W-E', 5, 'Vac' FROM dual UNION ALL
    SELECT '5B', 'E-M', 5, 'Occ' FROM dual UNION ALL
    SELECT '5C', 'A-E', 5, 'Vac' FROM dual UNION ALL
    SELECT '5D', 'A-E', 5, 'Vac' FROM dual UNION ALL
    SELECT '5E', 'E-M', 5, 'Vac' FROM dual UNION ALL
    SELECT '5F', 'W-E', 5, 'Vac' FROM dual UNION ALL
    SELECT '6A', 'W-B', 6, 'Vac' FROM dual UNION ALL
    SELECT '6B', 'B-M', 6, 'Vac' FROM dual UNION ALL
    SELECT '6C', 'A-B', 6, 'Vac' FROM dual UNION ALL
    SELECT '6D', 'A-B', 6, 'Vac' FROM dual UNION ALL
    SELECT '6E', 'B-M', 6, 'Vac' FROM dual UNION ALL
    SELECT '6F', 'W-B', 6, 'Vac' FROM dual),

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

exit_seats AS (
    SELECT row_number,
           seat_number,
           seat_avail
    FROM   seat_alloc
    WHERE  location_att LIKE '%E%'),

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

exit_unoccupied_ABC AS (
    SELECT row_number,
           COUNT (*) seats_free
    FROM   exit_seats
    WHERE  UPPER (seat_avail) = 'VAC'
    AND    seat_number IN (
        SELECT DISTINCT row_number || 'A' FROM exit_seats UNION ALL
        SELECT DISTINCT row_number || 'B' FROM exit_seats UNION ALL
        SELECT DISTINCT row_number || 'C' FROM exit_seats)
    GROUP BY row_number),
exit_unoccupied_DEF AS (
    SELECT row_number,
           COUNT (*) seats_free
    FROM   exit_seats
    WHERE  UPPER (seat_avail) = 'VAC'
    AND    seat_number IN (
        SELECT DISTINCT row_number || 'D' FROM exit_seats UNION ALL
        SELECT DISTINCT row_number || 'E' FROM exit_seats UNION ALL
        SELECT DISTINCT row_number || 'F' FROM exit_seats)
    GROUP BY row_number)

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

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

型
输出量：

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

型

赞(0）分享  回复(0）举报  12个月前
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

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