代码之家 › 专栏 › 技术社区 › User1974

将行分类为第一行、最后行或中间行

oracle12c categories case oracle sql

User1974 · 技术社区 · 8 年前

road_events .

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

insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (1,100,'CONSTRUCTION  ',1);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (2,100,'CONSTRUCTION  ',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (3,100,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (4,100,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (5,100,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (6,100,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (7,200,'INSPECTION',0);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (8,200,'CONSTRUCTION  ',1);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (9,200,'INSPECTION',1);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (10,200,'INSPECTION',1);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (11,200,'CONSTRUCTION  ',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (12,200,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (13,200,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (14,200,'INSPECTION',2);
insert into infrastr.road_events (event_id,road_id,event_type,lifecycle_number) values (15,200,'CONSTRUCTION  ',3);

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

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

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

对于每条道路,我想用 lifecycle_name (按此顺序):

将调用上一个(最大)生命周期中的行 current lifecycle

如果每个生命周期有超过1行,则:

将调用第一个(最小)生命周期中的行 original lifecycle (如适用)
past lifecycle (如适用)

它看起来是这样的:

+----------+---------+----------------+------------------+--------------------+
| EVENT_ID | ROAD_ID |   EVENT_TYPE   | LIFECYCLE_NUMBER |   LIFECYCLE_NAME   |
+----------+---------+----------------+------------------+--------------------+
|        1 |     100 | CONSTRUCTION   |                1 | ORIGINAL LIFECYCLE |
|        2 |     100 | CONSTRUCTION   |                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 | CONSTRUCTION   |                1 | PAST LIFECYCLE     |
|        9 |     200 | INSPECTION     |                1 | PAST LIFECYCLE     |
|       10 |     200 | INSPECTION     |                1 | PAST LIFECYCLE     |
|       11 |     200 | CONSTRUCTION   |                2 | PAST LIFECYCLE     |
|       12 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       13 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       14 |     200 | INSPECTION     |                2 | PAST LIFECYCLE     |
|       15 |     200 | CONSTRUCTION   |                3 | CURRENT LIFECYCLE  |
+----------+---------+----------------+------------------+--------------------+

有没有一种方法可以按照描述对行进行简洁分类?

我可以想出一些冗长/繁琐的方法来处理它 case 语句和子查询,但我正在寻找比这更优雅的东西。

2 回复 | 直到 8 年前

Kaushik Nayak 8 年前

另一个没有 CASE 语句是要使用的 decode .

with EVENTS
     AS (SELECT event_id,
                road_id,
                SUBSTR(event_type, 0, 15)  AS event_type,
                lifecycle_number,
                MIN(lifecycle_number)
                  over (
                    PARTITION BY road_id ) min_val,
                MAX(lifecycle_number)
                  over (
                    PARTITION BY road_id ) max_val
         FROM   road_events)
SELECT event_id,
       road_id,
       event_type,
       lifecycle_number,
       DECODE(lifecycle_number, min_val, 'ORIGINAL LIFECYCLE',
                                max_val, 'CURRENT LIFECYCLE',
                                'PAST LIFECYCLE') LIFECYCLE_NAME
FROM   EVENTS
ORDER  BY event_id;

SQL Fiddle

mathguy 8 年前

您可以使用 match_recognize() 简洁高效的解决方案条款:

select event_id, road_id, event_type, lifecycle_number, lifecycle_name
from   road_events
match_recognize(
  partition by road_id
  order     by lifecycle_number
  measures  case classifier() when 'A' then 'ORIGINAL LIFECYCLE'
                              when 'B' then 'PAST LIFECYCLE'
                              else          'CURRENT LIFECYCLE'
            end  as lifecycle_name
  all rows per match
  pattern   ( ^ a+ b* c d* $ )
  define    a as lifecycle_number  = first(lifecycle_number),
            c as lifecycle_number != prev(lifecycle_number),
            d as lifecycle_number  = prev(lifecycle_number)
);

注意:感谢@MT0在早期版本中发现了一个错误-有关详细信息,请参阅下面的注释。