將行分類為第一行、最後一行或介於兩者之間

我有一張桌子叫road_events.

 create table road_events 
  (
   event_id number(5,0),
   road_id number(5,0),
   event_type nvarchar2(50),
   lifecycle_number number(5,0)
  );

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (1,100,'RECONSTRUCTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (2,100,'RECONSTRUCTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (3,100,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (4,100,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (5,100,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (6,100,'INSPECTION',2);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (7,200,'INSPECTION',0);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (8,200,'RECONSTRUCTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (9,200,'INSPECTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (10,200,'INSPECTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (11,200,'RECONSTRUCTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (12,200,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (13,200,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (14,200,'INSPECTION',2);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (15,200,'RECONSTRUCTION',3);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (16,300,'RECONSTRUCTION',1);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (17,400,'INSPECTION',0);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (18,500,'RECONSTRUCTION',1);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (19,500,'RECONSTRUCTION',2);

insert into road_events (event_id,road_id,event_type,lifecycle_number) values (20,600,'INSPECTION',0);
insert into road_events (event_id,road_id,event_type,lifecycle_number) values (21,600,'INSPECTION',0);

---

select
   event_id,
   road_id,
   substr(event_type,0,15) as event_type,
   lifecycle_number
from
   road_events
order by
   event_id

 EVENT_ID    ROAD_ID EVENT_TYPE      LIFECYCLE_NUMBER
---------- ---------- --------------- ----------------
        1        100 RECONSTRUCTION                 1
        2        100 RECONSTRUCTION                 2
        3        100 INSPECTION                     2
        4        100 INSPECTION                     2
        5        100 INSPECTION                     2
        6        100 INSPECTION                     2

        7        200 INSPECTION                     0
        8        200 RECONSTRUCTION                 1
        9        200 INSPECTION                     1
       10        200 INSPECTION                     1
       11        200 RECONSTRUCTION                 2
       12        200 INSPECTION                     2
       13        200 INSPECTION                     2
       14        200 INSPECTION                     2
       15        200 RECONSTRUCTION                 3

       16        300 RECONSTRUCTION                 1

       17        400 INSPECTION                     0

       18        500 RECONSTRUCTION                 1
       19        500 RECONSTRUCTION                 2

       20        600 INSPECTION                     0
       21        600 INSPECTION                     0

對於每條道路，我想用lifecycle_name（按此順序）對每個生命週期中的行進行分類：

1. 最後（最大）生命週期中的行將被呼叫current lifecycle
2. 第一個（最小）生命週期中的行將被呼叫original lifecycle（如果適用）
3. 任何其他人都會被呼叫past lifecycle（如果適用）

它看起來像這樣：

+----------+---------+----------------+------------------+--------------------+
| EVENT_ID | ROAD_ID |   EVENT_TYPE   | LIFECYCLE_NUMBER |   LIFECYCLE_NAME   |
+----------+---------+----------------+------------------+--------------------+
|        1 |     100 | RECONSTRUCTION |                1 | ORIGINAL LIFECYCLE |
|        2 |     100 | RECONSTRUCTION |                2 | CURRENT LIFECYCLE  |
|        3 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
|        4 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
|        5 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
|        6 |     100 | INSPECTION     |                2 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|        7 |     200 | INSPECTION     |                0 | ORIGINAL LIFECYCLE |
|        8 |     200 | RECONSTRUCTION |                1 | PAST LIFECYCLE     |
|        9 |     200 | INSPECTION     |                1 | PAST LIFECYCLE     |
|       10 |     200 | INSPECTION     |                1 | PAST LIFECYCLE     |
|       11 |     200 | RECONSTRUCTION |                2 | PAST LIFECYCLE     |
|       12 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       13 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       14 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       15 |     200 | RECONSTRUCTION |                3 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       16 |     300 | RECONSTRUCTION |                1 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       17 |     400 | INSPECTION     |                0 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       18 |     500 | RECONSTRUCTION |                1 | ORIGINAL LIFECYCLE |
|       19 |     500 | RECONSTRUCTION |                2 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+
|       20 |     600 | INSPECTION     |                0 | CURRENT LIFECYCLE  |
|       21 |     600 | INSPECTION     |                0 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+

有沒有辦法在適用的情況下將行簡潔地分類為current lifecycle、original lifecycle和？past lifecycle

我可以想出一些冗長/繁瑣的方法來處理case語句和子查詢，但我正在尋找比這更優雅的東西。

更新：

1. 我已經用更多場景（道路 300-600）更新了這個問題。感謝@markp 考慮到這一點。
2. @markp 還強調了current lifecycletrums original lifecycle。

您可以使用兩個視窗函式，min()並max()使用相同的視窗，在partition by road_id：

select  event_id,
       road_id,
       event_type,
       lifecycle_number,
       case lifecycle_number
           when max(lifecycle_number) over (partition by road_id)
               then 'CURRENT LIFECYCLE'
           when min(lifecycle_number) over (partition by road_id)  
               then 'ORIGINAL LIFECYCLE'
               else 'PAST LIFECYCLE'
       end as LIFECYCLE_NAME
from    road_events re
order by event_id;

您還可以使用first_value()和last_value()視窗函式。

在dbfiddle.uk進行測試

引用自：https://dba.stackexchange.com/questions/191673