sql – Oracle Analytic函数 – 重置窗口子句

我有以下数据集. 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

我有以下数据集.

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  <<<----------

有什么指针吗?

解决方法

这有点复杂.而不是使用rank()等,使用lag()来查看某些内容何时发生变化.然后做一个标志的累积和.

select dept,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;

Here是SQLFiddle.

编辑:

这是戈登编辑我自己的答案.哎呀.原始查询是90%的方式.它确定了数字应该增加的组,但没有分配组内的数字.我会用另一个row_number()级别执行此操作,如下所示:

select dept,row_number() over (partition by dept,grp order by date1) as rnk
from (select dept,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()枚举它们.

关于作者: dawei

【声明】:石家庄站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐