Oracle Analytic函数-重置窗口条款 [英] Oracle Analytic functions - resetting a windowing clause
问题描述
我有以下数据集.
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,但这不会给我想要的结果.
MY goal is to create a rank column that resets each time the department changes. The closest column that I can use for "partition by" clause is dept, but that won't give me the desired result.
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记录是更改后的第一条记录.
The desired output is as follows. The last rnk=1 is becuase the Jan-04 record is the first record after the change.
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()
查看何时发生了更改.然后对标志求和.
This is a little complicated. Instead of using rank()
or the like, use lag()
to see when something changes. Then do a cumulative sum of the flag.
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.
Here is the SQLFiddle.
这是戈登在编辑我自己的答案.哎呀.原始查询的查询率为90%.它确定了编号应增加的组,但未在组内分配编号.我可以使用另一个row_number()
级别来做到这一点,例如:
This is Gordon editing my own answer. Oops. The original query was 90% of the way there. It identified the groups where the numbers should increase, but did not assign the numbers within the groups. I would do this with another level of row_number()
as in:
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()
枚举它们.
So, the overall idea is the following. First use lag()
to determine where a group begins (that is, where there is a department change from one date to the next). Then, assign a "group id" to these, by doing a cumulative sum. These are the records that are to be enumerated. The final step is to enumerate them using row_number()
.
这篇关于Oracle Analytic函数-重置窗口条款的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!