Oracle разбит по группам на последовательность дат

Я пытаюсь использовать 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? Сейчас я снова использовал курсор для анализа данных и назначения идентификатора группы, чтобы разделить две последовательности "А", но это довольно медленно.

Спасибо,

Отметка.

Источник

4 ответа

Решение

Сначала вы должны найти GROUP_ID для каждой записи, чтобы отсортировать все похожие COL1 по разным группам, если между ними есть разрыв. А затем используйте этот GROUP_ID в операторе OVER с COL1:

SQLFiddle demo

SELECT ROW_NUMBER() OVER (PARTITION BY Group_id,col1 ORDER BY col2) AS RNUM, a3.* 
FROM 
(
select a1.*,
      (select count(*) from t a2 where 
       a2.col1<>a1.col1 
       AND  
       a2.col2<a1.col2) as GROUP_ID
from t a1
) a3

order by col2
Источник

Это возможно с помощью пары аналитик:

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

тогда это случай присвоения row_number() по заказу col2 и разделение на grp

скрипка: http://sqlfiddle.com/

Источник

Вам не нужен раздел. Вам необходимо преобразовать даты в формат ДД / ММ / ГГГГ и заказать их. Или, если необходимо, то вы можете разделить на части MM, что дает вам 01,02,03... и может быть разделено и легко преобразовано в число при необходимости. Но вам не нужно все это... Не усложняйте ваши запросы. Всегда будь проще. Внешний запрос предназначен только для переформатирования ваших дат обратно в формат DD/MON/YYYY:

SELECT val, to_char(to_date(dt, 'DD/MM/YYYY'), 'DD/MON/YYYY') formatted_date 
  FROM
( -- Format your date to DD/MM/YYYY and order by it --
SELECT 'A' val, to_char(to_date('01/JAN/2012'), 'DD/MM/YYYY') dt FROM dual  
 UNION
SELECT 'A', to_char(to_date('01/FEB/2012'), 'DD/MM/YYYY') FROM dual  
 UNION
SELECT 'B',to_char(to_date('01/MAR/2012'), 'DD/MM/YYYY') FROM dual  
 UNION
SELECT 'B',to_char(to_date('01/APR/2012'), 'DD/MM/YYYY') FROM dual  
 UNION
SELECT 'A',to_char(to_date('01/MAY/2012'), 'DD/MM/YYYY') FROM dual  
ORDER BY 2
)
/

Ваши даты упорядочены так, как вы хотели, чтобы:

VAL FORMATTED_DATE
-------------------
A   01/JAN/2012
A   01/FEB/2012
B   01/MAR/2012
B   01/APR/2012
A   01/MAY/2012
Источник

Посмотрите мой подход ниже, это похоже на ответ Dazzal, немного другая логика:

SQL FIDDLE

Шаг 1:

--find the swhitches to new groups
select col1, col2, 
    case when nvl(lag(col1) over (order by col2),sysdate) <> col1 then 1 end as new_grp
  from data;

COL1    COL2        NEW_GRP
A   January, 01 2012    1
A   February, 01 2012   (null)
B   March, 01 2012      1
B   April, 01 2012      (null)
A   May, 01 2012        1

Шаг 2:

--identify/mark the groups

select col1, col2, sum(new_grp) over (order by col2) as grp
from(
  select col1, col2, 
    case when nvl(lag(col1) over (order by col2),sysdate) <> col1 then 1 end as new_grp
  from data)
  ;

COL1    COL2        NEW_GRP
A   January, 01 2012    1
A   February, 01 2012   1
B   March, 01 2012      2
B   April, 01 2012      2
A   May, 01 2012        3

Шаг 3:

--find the row_number within group
select col1, col2, row_number() over(partition by grp order by col2) rn
from(
  select col1, col2, sum(new_grp) over (order by col2) as grp
  from(
    select col1, col2, 
      case when nvl(lag(col1) over (order by col2),sysdate) <> col1 then 1 end as new_grp
    from data
      )
  );

COL1    COL2        NEW_GRP
A   January, 01 2012    1
A   February, 01 2012   2
B   March, 01 2012      1
B   April, 01 2012      2
A   May, 01 2012        1
Источник
Другие вопросы по тегам