我有以下数据集.
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()枚举它们.