<p>如果您的bucket总是从1开始并且increment=1,那么您可以使用非常简单的<code>mod(row_number, max(bucket))</code>:</p>
<pre class="lang-sql prettyprint-override"><code>select
line, term, course
,1+mod(-1+row_number()over(partition by line order by course),max(bucket)) bucket_n
from t
group by line, term, course
order by line,course;
</code></pre>
<p>示例数据的完整示例:</p>
<pre class="lang-sql prettyprint-override"><code>with t(LINE ,BUCKET ,TERM ,COURSE) as (
select 1001, 1, 'FA18', 'COURSE 101' from dual union all
select 1001, 1, 'SP19', 'COURSE 102' from dual union all
select 1001, 1, 'SP19', 'COURSE 103' from dual union all
select 1001, 1, 'FA19', 'COURSE 104' from dual union all
select 1001, 2, 'FA18', 'COURSE 101' from dual union all
select 1001, 2, 'SP19', 'COURSE 102' from dual union all
select 1001, 2, 'SP19', 'COURSE 103' from dual union all
select 1001, 2, 'FA19', 'COURSE 104' from dual union all
select 2001, 1, 'FA18', 'COURSE 201' from dual union all
select 2001, 1, 'SP19', 'COURSE 202' from dual union all
select 2001, 1, 'FA20', 'COURSE 203' from dual union all
select 2001, 2, 'FA18', 'COURSE 201' from dual union all
select 2001, 2, 'SP19', 'COURSE 202' from dual union all
select 2001, 2, 'FA20', 'COURSE 203' from dual union all
select 2001, 3, 'FA18', 'COURSE 201' from dual union all
select 2001, 3, 'SP19', 'COURSE 202' from dual union all
select 2001, 3, 'FA20', 'COURSE 203' from dual
)
select
line, term, course
,1+mod(-1+row_number()over(partition by line order by course),max(bucket)) bucket_n
from t
group by line, term, course
order by line,course;
</code></pre>
<p>结果:</p>
<pre><code>LINE TERM COURSE BUCKET_N
- - -
1001 FA18 COURSE 101 1
1001 SP19 COURSE 102 2
1001 SP19 COURSE 103 1
1001 FA19 COURSE 104 2
2001 FA18 COURSE 201 1
2001 SP19 COURSE 202 2
2001 FA20 COURSE 203 3
</code></pre>
<p>另一个有趣的变体是聚合存储桶并通过位置<code>mod(rownumber, count(buckets))</code>提取值-与上一个解决方案相反,它适用于任何存储桶:</p>
<pre class="lang-sql prettyprint-override"><code>select
line, term, course
,xmlcast(
xmlelement(
"buckets",
xmlagg(xmlelement("bucket", bucket))
).extract('/buckets/*['||
(1+mod(-1+row_number()over(partition by line order by course),count(bucket)))
||']')
as int) bucket_n_2
from t
group by line, term, course
order by line,course;
</code></pre>
<p>完整测试用例:</p>
<pre class="lang-sql prettyprint-override"><code>with t(LINE ,BUCKET ,TERM ,COURSE) as (
select 1001, 1, 'FA18', 'COURSE 101' from dual union all
select 1001, 1, 'SP19', 'COURSE 102' from dual union all
select 1001, 1, 'SP19', 'COURSE 103' from dual union all
select 1001, 1, 'FA19', 'COURSE 104' from dual union all
select 1001, 2, 'FA18', 'COURSE 101' from dual union all
select 1001, 2, 'SP19', 'COURSE 102' from dual union all
select 1001, 2, 'SP19', 'COURSE 103' from dual union all
select 1001, 2, 'FA19', 'COURSE 104' from dual union all
select 2001, 1, 'FA18', 'COURSE 201' from dual union all
select 2001, 1, 'SP19', 'COURSE 202' from dual union all
select 2001, 1, 'FA20', 'COURSE 203' from dual union all
select 2001, 2, 'FA18', 'COURSE 201' from dual union all
select 2001, 2, 'SP19', 'COURSE 202' from dual union all
select 2001, 2, 'FA20', 'COURSE 203' from dual union all
select 2001, 3, 'FA18', 'COURSE 201' from dual union all
select 2001, 3, 'SP19', 'COURSE 202' from dual union all
select 2001, 3, 'FA20', 'COURSE 203' from dual
)
select
line, term, course
,1+mod(-1+row_number()over(partition by line order by course),max(bucket)) bucket_n
,xmlcast(
xmlelement(
"buckets",
xmlagg(xmlelement("bucket", bucket))
).extract('/buckets/*['||
(1+mod(-1+row_number()over(partition by line order by course),count(bucket)))
||']')
as int) bucket_n_2
from t
group by line, term, course
order by line,course;
</code></pre>
<ul>
<li><code>xmlagg(xmlelement("bucket", bucket))</code>聚合所有存储桶编号</李>
<li><code>extract('/buckets/*[N])</code>-从聚合值中提取N的存储桶</li>
<li><code>(1+mod(-1+row_number()over(partition by line order by course),count(bucket)))</code>-计算第n个存储桶</li>
</ul>
<p>结果:BUCKET_N-先前,BUCKET_N_2-新变体:</p>
<pre><code>LINE TERM COURSE BUCKET_N BUCKET_N_2
1001 FA18 COURSE 101 1 1
1001 SP19 COURSE 102 2 2
1001 SP19 COURSE 103 1 1
1001 FA19 COURSE 104 2 2
2001 FA18 COURSE 201 1 1
2001 SP19 COURSE 202 2 3
2001 FA20 COURSE 203 3 2
</code></pre>