blocks|key|1275787|text|这有点复杂。而不是使用rank()等，而是使用lag()来查看什么时候发生了变化。然后对标志进行累积和。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1275788|select+dept,+date1,
+++++++CASE+WHEN+StartFlag+=+0+THEN+1
++++++++++++ELSE+1%2BStartFlag%2BNVL(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;|code-block|syntax|javascript|1275789|这里是SQLFiddle。|1275790|编辑：|1275791|这是戈登在编辑我自己的答案。糟了。最初的查询是90%25的方式。它确定了人数应该增加的群体，但没有在小组内分配人数。我会在另一个级别的row_number()中这样做，如下所示：|1275792|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;|1275793|因此，总体思路如下。首先，使用lag()确定一个组从何处开始(也就是说，部门从一个日期更改到下一个日期)。然后，通过进行累加和，为它们分配一个“组id”。这些是要列举的记录。最后一步是使用row_number()枚举它们。|1275794|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlfiddle.com/#!4/fc339/3^0|B|6|N|5|0|0|0|2|0|0|0|1T|C|0|0|F|5|2M|C|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|M|3|N|5|6|7|1C|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|1D|8|@$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1G|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1H|8|@$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]]|D|@]|E|$]]|$1|U|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

This is a little complicated. Instead of using <code>rank()</code> or the like, use <code>lag()</code> to see when something changes. Then do a cumulative sum of the flag.

<pre><code>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;
</code></pre>

<a href="http://www.sqlfiddle.com/#!4/fc339/3" rel="nofollow">Here</a> is the SQLFiddle.

EDIT:

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 <code>row_number()</code> as in:

<pre><code>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;
</code></pre>

So, the overall idea is the following. First use <code>lag()</code> 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 <code>row_number()</code>.

blocks|key|2596058|text|这可能是model子句的情况，但不幸的是，与Gordon的查询相比，它在大量行上的性能明显差。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2596059|select+dept,+date1,+rank+from+t1
model+
++dimension+by+(+row_number()+over(order+by+date1)+as+rn+)
++measures(+1+as+rank,+dept,+date1+)+
++rules+(+
++++rank[1]+=+1,
++++rank[rn+>+1]+=+
++++case+dept[cv()]+
++++++when+dept[cv()-1]+then+rank[cv()-1]+%2B+1+
++++++else+1
+++++end
++)|code-block|syntax|javascript|2596060|http://www.sqlfiddle.com/#!4/fc339/132|2596061|entityMap|0|LINK|mutability|MUTABLE|url^0|4|5|0|0|0|12|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|W|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|X|8|@]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|M|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|L]]]]

This could have been a case for <code>model</code> clause, but unfortunately it dramatically underperforms on significant amount of rows compared to Gordon's query.

<pre><code>select dept, date1, rank from t1
model 
 dimension by ( row_number() over(order by date1) as rn )
 measures( 1 as rank, dept, date1 ) 
 rules ( 
 rank[1] = 1,
 rank[rn &gt; 1] = 
 case dept[cv()] 
 when dept[cv()-1] then rank[cv()-1] + 1 
 else 1
 end
 )
</code></pre>

<a href="http://www.sqlfiddle.com/#!4/fc339/132" rel="nofollow">http://www.sqlfiddle.com/#!4/fc339/132</a>

blocks|key|198136|text|办法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|198137|用“行号”和“更改”标志标记每一行|ordered-list-item|198138|计算最后的“rnk”作为“行号”与对应于“已更改”行的前一个最大行号之间的差额。|198139|这与Gordon的回答相似，但用CTE写的，我觉得读起来更容易。|198140|with+cte+as+(
++++select+dept,+date1,
++++++++row_number()+over+(order+by+date1)+as+row,
++++++++case+when+dept+=+(lag(dept)+over+(order+by+date1))+then+0+else+1+end+as+changed
++++from+t1
)
select+dept,+date1,+
++++row+-+max(case+when+changed+=+1+then+row+else+1+end)+over+(order+by+date1)+%2B+1+as+rnk
from+cte
order+by+date1|code-block|syntax|javascript|198141|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Q|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|R|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|T|8|@]|9|@]|A|$L|M]]|$1|N|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|O|$]]

The approach is:

<ol>
<li>Mark each row with a 'row number' and a 'changed' flag </li>
<li>Calculate the final 'rnk' as the difference between the 'row number', and the maximum previous 'row number' that corresponds to a 'changed' row.</li>
</ol>

This is similar to Gordon's answer, but written using a CTE which I find easier to read.

<pre><code>with cte as (
 select dept, date1,
 row_number() over (order by date1) as row,
 case when dept = (lag(dept) over (order by date1)) then 0 else 1 end as changed
 from t1
)
select dept, date1, 
 row - max(case when changed = 1 then row else 1 end) over (order by date1) + 1 as rnk
from cte
order by date1
</code></pre>

I have the following data set.

<pre><code>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;
</code></pre>

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.

<pre><code>SQL&gt; 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
</code></pre>

The desired output is as follows. The last rnk=1 is becuase the Jan-04 record is the first record after the change.

<pre><code> DEPT DATE1 RNK
---------- --------- ----------
 100 01-JAN-13 1
 100 02-JAN-13 2
 200 03-JAN-13 1
 100 04-JAN-13 1 &lt;&lt;&lt;----------
</code></pre>

Any pointers?

Oracle Analytic functions - resetting a windowing clause

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

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

问Oracle分析函数-重置窗口子句
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle分析函数-重置窗口子句EN