sql - Oracle 按组划分为基于日期的序列

Question

我正在尝试使用 PARTITION BY OVER 按特定列对行进行“分组”。我有点了解 PARTITION 的使用，但是我想按日期“阻止”分区。例如，如果我们有

|col1|col2       |
| A  |01/JAN/2012|
| A  |01/FEB/2012|
| B  |01/MAR/2012|
| B  |01/APR/2012|
| A  |01/MAY/2012|

我想按 col1 进行分区，但我希望最后一个 A 与前两个“不同”，因为它按日期由“B”行分隔。

如果我使用；

SELECT ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS RNUM, a.* 
FROM table1 a;

它会屈服；

|RNUM|col1|col2       |
|   1| A  |01/JAN/2012|
|   2| A  |01/FEB/2012|
|   3| A  |01/MAY/2012|
|   1| B  |01/MAR/2012|
|   2| B  |01/APR/2012|

但我真正想要的是；

|RNUM|col1|col2       |
|   1| A  |01/JAN/2012|
|   2| A  |01/FEB/2012|
|   1| B  |01/MAR/2012|
|   2| B  |01/APR/2012|
|   1| A  |01/MAY/2012|

这可能使用 PARTITION BY OVER 吗？目前，我已经退回到使用游标来解析数据并分配一个组 ID，这样我就可以将“A”的两个序列分开，但这非常慢。

谢谢，

标记。

Answer 1

这可以通过一些分析来实现:

select col1, col2, row_number() over (partition by grp order by col2) rnum
  from (select col1, col2, max(grp) over(order by col2) grp
          from (select col1, col2, 
                       case 
                         when lag(col1) over (order by col2) != col1
                         then
                           row_number() over (order by col2)
                         when row_number() over(order by col2) = 1 
                         then
                           1
                       end grp
                  from data));

即:

首先获取 col1 按 col2 日期更改顺序的边界:

SQL> select col1, col2,
  2         case
  3           when lag(col1) over (order by col2) != col1
  4           then
  5             row_number() over (order by col2)
  6           when row_number() over(order by col2) = 1
  7           then
  8             1
  9         end grp
 10    from data;

C COL2             GRP
- --------- ----------
A 01-JAN-12          1
A 01-FEB-12
B 01-MAR-12          3
B 01-APR-12
A 01-MAY-12          5

然后我们可以填充这些空值:

SQL> select col1, col2, max(grp) over(order by col2) grp
  2    from (select col1, col2,
  3                  case
  4                    when lag(col1) over (order by col2) != col1
  5                    then
  6                      row_number() over (order by col2)
  7                    when row_number() over(order by col2) = 1
  8                    then
  9                      1
 10                  end grp
 11            from data);

C COL2             GRP
- --------- ----------
A 01-JAN-12          1
A 01-FEB-12          1
B 01-MAR-12          3
B 01-APR-12          3
A 01-MAY-12          5

然后是通过按 col2 排序并在 grp 上分区来分配 row_number() 的情况

fiddle :http://sqlfiddle.com/#!4/4818c/1