SQL条件展望

Question

我想编写一个查询，它标识满足条件的有序集中的"next“值。在这里，铅/滞后分析函数似乎不适用，因为根据条件，向前看的行数是可变的(而不是固定的)。下面的示例显示了示例表(tbl)所需的结果(列gnme)，但解决方案似乎并不理想。希望这里的人能有一个更好的解决方案。提前谢谢。

请注意本示例中的第1-3行如何标识第4行中的nme迈克，第6-7行如何标识第8行中的nme迈克尔。

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是支持窗口函数ignore nulls选项lead()和lag()的罕见数据库之一。这是一个功能强大的特性，对于您的用例来说很方便：

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

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

Answer 2

您可以使用first_value解析函数：

select
  id
  ,nme
  ,nvl(
        first_value(nullif(nme,'unknown') ignore nulls)over(order by id ROWS between current row and unbounded following) 
        ,'unknown')
        AS gnme
FROM
  tbl a
;

比较的完整示例：

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
    ,nvl(
        first_value(nullif(nme,'unknown') ignore nulls)over(order by id ROWS between current row and unbounded following) 
        ,'unknown')
        AS gnme_2
FROM
  tbl a
;

结果：

        ID NME        GNME       GNME_2
---------- ---------- ---------- ----------
         1 unknown    mike       mike
         2 unknown    mike       mike
         3 unknown    mike       mike
         4 mike       mike       mike
         5 mike       mike       mike
         6 unknown    michael    michael
         7 unknown    michael    michael
         8 michael    michael    michael
         9 michael    michael    michael
        10 michael    michael    michael
        11 unknown    unknown    unknown

11 rows selected.

Answer 3

您也可以只使用LAST_VALUE()和忽略NULLS：

WITH
-- your input
tbl(id,nme) AS (
          SELECT 1,'unknown'
UNION ALL SELECT 2,'unknown'
UNION ALL SELECT 3,'unknown'
UNION ALL SELECT 4,'mike'
UNION ALL SELECT 5,'mike'
UNION ALL SELECT 6,'unknown'
UNION ALL SELECT 7,'unknown'
UNION ALL SELECT 8,'michael'
UNION ALL SELECT 9,'michael'
UNION ALL SELECT 10,'michael'
UNION ALL SELECT 11,'unknown'
)
SELECT
  *
, NVL(
    LAST_VALUE(NULLIF(nme,'unknown') IGNORE NULLS) OVER(
      ORDER BY id DESC
    )
  , 'unknown'
  ) AS gnme
FROM tbl
ORDER BY id;
-- out  id |   nme   |  gnme   
-- out ----+---------+---------
-- out   1 | unknown | mike
-- out   2 | unknown | mike
-- out   3 | unknown | mike
-- out   4 | mike    | mike
-- out   5 | mike    | mike
-- out   6 | unknown | michael
-- out   7 | unknown | michael
-- out   8 | michael | michael
-- out   9 | michael | michael
-- out  10 | michael | michael
-- out  11 | unknown | unknown