SQL 条件先行

Question

我想编写一个查询来标识满足条件的有序集中的“下一个”值。 LEAD/LAG 分析函数在这里似乎不适用，因为要向前看的行数根据条件是可变的(不是固定的)。下面的示例显示了示例表 (tbl) 中的所需结果(gnme 列)，但该解决方案似乎并不理想。希望这里有人可以为此类问题提供更优雅的解决方案。提前致谢。

请注意在本示例中，第 1-3 行如何标识第 4 行中的 nme mike，以及第 6-7 行如何标识 nme < em>michael 在第 8 行。

create table tbl (
  id number
  ,nme varchar(255)
)
;

insert into tbl (id, nme) values (1,'unknown');
insert into tbl (id, nme) values (2,'unknown');
insert into tbl (id, nme) values (3,'unknown');
insert into tbl (id, nme) values (4,'mike');
insert into tbl (id, nme) values (5,'mike');
insert into tbl (id, nme) values (6,'unknown');
insert into tbl (id, nme) values (7,'unknown');
insert into tbl (id, nme) values (8,'michael');
insert into tbl (id, nme) values (9,'michael');
insert into tbl (id, nme) values (10,'michael');
insert into tbl (id, nme) values (11,'unknown');
 
select
  id
  ,nme
  ,CASE WHEN nme = 'unknown' THEN 
          NVL
          (
           (SELECT b.nme 
            FROM tbl b 
            WHERE 
              b.nme <> 'unknown'
              AND a.id < b.id 
            ORDER BY id 
            OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY
           )
           , nme
          ) 
        ELSE nme 
        END AS gnme
FROM
  tbl a
;

+----+---------+---------+
| id | nme     | gnme    |
+----+---------+---------+
| 1  | unknown | mike    |
+----+---------+---------+
| 2  | unknown | mike    |
+----+---------+---------+
| 3  | unknown | mike    |
+----+---------+---------+
| 4  | mike    | mike    |
+----+---------+---------+
| 5  | mike    | mike    |
+----+---------+---------+
| 6  | unknown | michael |
+----+---------+---------+
| 7  | unknown | michael |
+----+---------+---------+
| 8  | michael | michael |
+----+---------+---------+
| 9  | michael | michael |
+----+---------+---------+
| 10 | michael | michael |
+----+---------+---------+
| 11 | unknown | unknown |
+----+---------+---------+

Answer 1

当名称未知时，您需要下一个非未知名称。

Oracle 是为数不多的支持窗口函数 lead() 和 lag() 的ignore nulls 选项的数据库之一。这是一个强大的功能，对您的用例来说非常方便:

select 
   id,
   nme,
   case when nme = 'unknown'
       then lead(nullif(nme,'unknown') ignore nulls, 1, 'unknown') over(order by id)
       else nme
   end gnme
from tbl

lead() 中的case 表达式将值'unknow' 转换为null，然后函数带来下一个非 null 值(如果没有可用值则默认为未知)。