根据记录数据的变化重置行号

问题描述

我有如下数据集

name  date
x     2014-01-01
x     2014-01-02
y     2014-01-03
x     2014-01-04

我正试图得到这个结果

name  date           row_num
x     2014-01-01      1
x     2014-01-02      2
y     2014-01-03      1
x     2014-01-04      1

我尝试运行此查询

select name,
    date,
    row_number () over (partition by name order by date) as row_num
from myTBL

但不幸的是我得到了这个结果

but unfortunately I get this result

name  date           row_num
x     2014-01-01      1
x     2014-01-02      2
y     2014-01-03      1
x     2014-01-04      3

请帮助.

推荐答案

您需要确定一起出现的names组.您可以使用不同的行号来执行此操作.然后，使用grp对row_number()进行分区:

You need to identify the groups of names that occur together. You can do this with a difference of row numbers. Then, use the grp for partitioning the row_number():

select name, date,
       row_number() over (partition by name, grp order by date) as row_num
from (select t.*,
             (row_number() over (order by date) -
              row_number() over (partition by name order by date)
             ) as grp
      from myTBL t
     ) t

为您的示例数据:

name  date         1st row_number   2nd      Grp
x     2014-01-01         1           1        0
x     2014-01-02         2           2        0
y     2014-01-03         3           1        2
x     2014-01-04         4           3        1

这应该使您了解其工作原理.

This should give you an idea of how it works.

这篇关于根据记录数据的变化重置行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！