Oracle Analytic函数-重置窗口条款

小编典典

Oracle Analytic函数-重置窗口条款

sql

我有以下数据集。

create table t1 (
  dept number,
  date1 date
);

Table created.

insert into t1 values (100, '01-jan-2013');
insert into t1 values (100, '02-jan-2013');
insert into t1 values (200, '03-jan-2013');
insert into t1 values (100, '04-jan-2013');
commit;

我的目标是创建一个等级列，该等级列在每次部门变更时都会重置。我可以用于“ partition by”子句的最接近的列是dept，但这不会给我想要的结果。

SQL> select * from t1;

      DEPT DATE1
---------- ---------
       100 01-JAN-13
       100 02-JAN-13
       200 03-JAN-13
       100 04-JAN-13

select dept,  
       date1,
       rank () Over (partition by dept order by date1) rnk
from t1
order by date1;

      DEPT DATE1            RNK
---------- --------- ----------
       100 01-JAN-13          1
       100 02-JAN-13          2
       200 03-JAN-13          1
       100 04-JAN-13          3

所需的输出如下。最后一次rnk = 1是因为Jan-04记录是更改后的第一条记录。

      DEPT DATE1            RNK
---------- --------- ----------
       100 01-JAN-13          1
       100 02-JAN-13          2
       200 03-JAN-13          1
       100 04-JAN-13          1  <<<----------

有指针吗？

阅读 214

2021-03-17

共1个答案

小编典典

这有点复杂。而不是使用rank()或类似方法，而是使用lag()来查看何时发生了更改。然后对标志进行累加和。

select dept, date1,
       CASE WHEN StartFlag = 0 THEN 1
            ELSE 1+StartFlag+NVL(lag(StartFlag) over (order by date1),0)
       END as rnk
from (select t1.*,
             (case when dept = lag(dept) over (order by date1)
                   then 1
                   else 0
              end) as StartFlag
      from t1
     ) t1
order by date1;

这是SQLFiddle。

编辑：

这是戈登在编辑我自己的答案。哎呀。原始查询的查询率为90％。它确定的群体
，其中的数字应该增加，但并没有在组内分配的编号。我将在另一个层次上执行此操作，row_number()例如：

select dept, date1,
       row_number() over (partition by dept, grp order by date1) as rnk
from (select dept, date1, startflag,
             sum(StartFlag) over (partition by dept order by date1) as grp
      from (select t1.*,
                   (case when dept = lag(dept) over (order by date1)
                         then 0
                         else 1
                    end) as StartFlag
            from t1
           ) t1
     ) t1
order by date1;

因此，总体思路如下。首次用于lag()确定组的开始位置（即，从一个日期到下一个日期的部门发生变化的位置）。然后，通过累加总和为这些分配一个“组ID”。这些是要枚举的记录。最后一步是使用枚举它们row_number()。

2021-03-17