如何设置带条件的自动递增列值

Question

我有这样的表：

value nextValue
1     2
2     3
3     20
20    21
21    22
22    23
23    NULL

Value按ASC排序，nextValue为下一行Value。条件nextValue值>10，并计算不同组中的值。

例如，应该有两个组(1,2,3)和(20,21,22,23)，第一组计数为3，第二组计数为4。

我试图用唯一的数字标记每个组，这样我就可以用这些标记的数字进行分组

value nextValue mark
1     2         1
2     3         1
3     20        1
20    21        2
21    22        2
22    23        2
23    NULL      2

但我不知道如何写标记列，当nextValue-value>10时，我需要一个自动记忆变量。我能在蜂巢里实现吗？或者有更好的需求解决方案？

Answer 1

如果我理解正确，可以使用累积和。这个想法是在next_value - value > 10。这标识了组。因此，此查询添加了一个组号：

select t.*,
sum(case when nextvalue > value + 10 then 1 else 0 end) over (order by value desc) as mark
from t
order by value;

您可能不会觉得这个解决方案令人满意，因为编号是按降序排列的。所以，更多的算法解决了这个问题：

select t.*,
(sum(case when nextvalue > value + 10 then 1 else 0 end) over () + 1 -
sum(case when nextvalue > value + 10 then 1 else 0 end) over (order by value desc)
) as mark
from t
order by value;

这里有一个db<gt；不停摆弄

Answer 2

如果value-prev_value>10，然后计算new_group_flag的累计和，得到组号(标记(。最后，您可以使用分析函数或group-by计算组计数(在我的示例中，分析计数用于向您显示包含所有中间计算的完整数据集(。请参阅代码中的注释。

演示：

with your_data as (--use your table instead of this
select stack(10, --the number of tuples generated
1  ,  
2  ,  
3  ,  
20 ,  
21 ,  
22 ,  
23 ,  
40 ,
41 ,
42
) as value
)
select --4. Calculate group count, etc, etc
value, prev_value, new_group_flag, group_number, 
count(*) over(partition by group_number) as group_count
from
(
select --3. Calculate cumulative sum of new group flag to get group number
value, prev_value, new_group_flag, 
sum(new_group_flag) over(order by value rows between unbounded preceding and current row)+1 as group_number
from
(
select --2. calculate new_group_flag
value, prev_value, case when value-prev_value >10 then 1 else 0 end as new_group_flag
from
(
select  --1 Calculate previous value
value, lag(value) over(order by value) prev_value
from your_data
)s
)s
)s

结果：

value  prev_value  new_group_flag  group_number   group_count
1       N           0              1              3
2       1            0              1              3
3       2            0              1              3
20      3            1              2              4
21      20           0              2              4
22      21           0              2              4
23      22           0              2              4
40      23           1              3              3
41      40           0              3              3
42      41           0              3              3

Answer 3

这对我有用它需要"；在无界的前一行和当前行之间的行"；就我而言。

select t.*,
sum(case when nextvalue > value + 10 then 1 else 0 end) over (order by value desc rows between unbounded preceding and current row) as mark
from t
order by value;