Специфическая логика нумерования строк для row_number / rank / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Специфическая логика нумерования строк для row_number / rank

18 сообщений из 18, страница 1 из 1

Специфическая логика нумерования строк для row_number / rank

#39795632

rifle

Гость

Добрый день, хотелось бы узнать, существует ли возможность сделать что-то подобное при помощи row_number или rank или еще чего-то, чтоб пронумеровать данные согласно следующему алгоритму.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

with tmp as (
    select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
    select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
    select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select 
    tmp.*,
    case when flag = 1 then row_number() over(partition by flag order by flag) else null end as rnk1,
    case when flag = 1 then rank() over(partition by flag order by flag) else null end as rnk2
from tmp
order by startdate, username

Actual:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

+-------------+--------------------+--------+--------+--------+
| "USERNAME"  | "STARTDATE"        | "FLAG" | "RNK1" | "RNK2" |
+-------------+--------------------+--------+--------+--------+
| "username1" | 01-APR-19 00:00:00 | 1      | 6      | 1      |
| "username1" | 01-APR-19 01:00:00 | 0      |        |        |
| "username1" | 01-APR-19 02:00:00 | 1      | 4      | 1      |
| "username1" | 01-APR-19 03:00:00 | 1      | 3      | 1      |
| "username1" | 01-APR-19 04:00:00 | 0      |        |        |
| "username1" | 02-APR-19 01:00:00 | 1      | 5      | 1      |
| "username1" | 02-APR-19 02:00:00 | 1      | 1      | 1      |
| "username1" | 02-APR-19 03:00:00 | 1      | 2      | 1      |
| "username1" | 02-APR-19 04:00:00 | 0      |        |        |
| "username1" | 02-APR-19 05:00:00 | 0      |        |        |
+-------------+--------------------+--------+--------+--------+

Expected:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

+-------------+--------------------+--------+--------+--------+
| "USERNAME"  | "STARTDATE"        | "FLAG" | "RNK1" | "RNK2" |
+-------------+--------------------+--------+--------+--------+
| "username1" | 01-APR-19 00:00:00 | 1      | 6      | 1      |
| "username1" | 01-APR-19 01:00:00 | 0      |        |        |
| "username1" | 01-APR-19 02:00:00 | 1      | 4      | 2      |
| "username1" | 01-APR-19 03:00:00 | 1      | 3      | 2      |
| "username1" | 01-APR-19 04:00:00 | 0      |        |        |
| "username1" | 02-APR-19 01:00:00 | 1      | 5      | 3      |
| "username1" | 02-APR-19 02:00:00 | 1      | 1      | 3      |
| "username1" | 02-APR-19 03:00:00 | 1      | 2      | 3      |
| "username1" | 02-APR-19 04:00:00 | 0      |        |        |
| "username1" | 02-APR-19 05:00:00 | 0      |        |        |
+-------------+--------------------+--------+--------+--------+

...

Рейтинг:

0 / 0

03.04.2019, 10:59

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795636

rifle

Гость

Ошибся в одном столбике в Expected:

Expected:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

+-------------+--------------------+--------+--------+--------+
| "USERNAME"  | "STARTDATE"        | "FLAG" | "RNK1" | "RNK2" |
+-------------+--------------------+--------+--------+--------+
| "username1" | 01-APR-19 00:00:00 | 1      | 1      | 1      |
| "username1" | 01-APR-19 01:00:00 | 0      |        |        |
| "username1" | 01-APR-19 02:00:00 | 1      | 2      | 2      |
| "username1" | 01-APR-19 03:00:00 | 1      | 2      | 2      |
| "username1" | 01-APR-19 04:00:00 | 0      |        |        |
| "username1" | 02-APR-19 01:00:00 | 1      | 3      | 3      |
| "username1" | 02-APR-19 02:00:00 | 1      | 3      | 3      |
| "username1" | 02-APR-19 03:00:00 | 1      | 3      | 3      |
| "username1" | 02-APR-19 04:00:00 | 0      |        |        |
| "username1" | 02-APR-19 05:00:00 | 0      |        |        |
+-------------+--------------------+--------+--------+--------+

...

Рейтинг:

0 / 0

03.04.2019, 11:09

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795642

MazoHist

Участник

Сообщения: 999

Рейтинг: 0 / 0

STFF start_of_group и немного модифицировать

...

Рейтинг:

0 / 0

03.04.2019, 11:14

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795656

MazoHist

Участник

Сообщения: 999

Рейтинг: 0 / 0

MazoHistSTFF start_of_group и немного модифицировать

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

SQL> with tmp as (
  2      select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
  3      select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
  4      select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
  5      select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
  6      select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
  7      select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
  8      select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
  9      select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
 10      select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
 11      select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
 12  )
 13  select username
 14       , startdate
 15       , flag
 16       , rnkx rnk1
 17       , rnkx rnk2
 18    from ( select a.*
 19                , case when flag > 0 then sum(sog) over(order by startdate, username) end rnkx
 20            from (select tmp.*
 21                       , case when flag > lag(flag,1,0) over (order by startdate, username) then 1 end sog
 22                    from tmp
 23                  ) a
 24         )
 25   order by startdate, username;

USERNAME  STARTDAT       FLAG       RNK1       RNK2
--------- -------- ---------- ---------- ----------
username1 01.04.19          1          1          1
username1 01.04.19          0
username1 01.04.19          1          2          2
username1 01.04.19          1          2          2
username1 01.04.19          0
username1 02.04.19          1          3          3
username1 02.04.19          1          3          3
username1 02.04.19          1          3          3
username1 02.04.19          0
username1 02.04.19          0

...

Рейтинг:

0 / 0

03.04.2019, 11:36

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795665

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select username, startdate, flag, decode(flag, 1, match_num) as rn
from tmp
match_recognize(
partition by username
order by startdate
measures match_number() AS match_num
all rows per match
pattern (f+ s*)
define f as f.flag = 1, s as s.flag = 0
);

USERNAME  STARTDATE                 FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          1          1
username1 2019-04-01 01:00:00          0           
username1 2019-04-01 02:00:00          1          2
username1 2019-04-01 03:00:00          1          2
username1 2019-04-01 04:00:00          0           
username1 2019-04-02 01:00:00          1          3
username1 2019-04-02 02:00:00          1          3
username1 2019-04-02 03:00:00          1          3
username1 2019-04-02 04:00:00          0           
username1 2019-04-02 05:00:00          0           

10 rows selected.

...

Рейтинг:

0 / 0

03.04.2019, 11:42

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795673

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select username, startdate, flag, decode(flag, 1, count(lg) over(partition by username order by startdate)) as rn
from
 (select tmp.*, case when flag = 1 and lag(flag, 1, 0) over (partition by username order by startdate) = 0 then 1 end lg
  from tmp
 )
order by username, startdate;

USERNAME  STARTDATE                 FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          1          1
username1 2019-04-01 01:00:00          0           
username1 2019-04-01 02:00:00          1          2
username1 2019-04-01 03:00:00          1          2
username1 2019-04-01 04:00:00          0           
username1 2019-04-02 01:00:00          1          3
username1 2019-04-02 02:00:00          1          3
username1 2019-04-02 03:00:00          1          3
username1 2019-04-02 04:00:00          0           
username1 2019-04-02 05:00:00          0           

10 rows selected.

...

Рейтинг:

0 / 0

03.04.2019, 11:48

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795688

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select username, dt, flag, decode(flag, 1, rn) rn
from tmp
model
partition by (username)
dimension by (row_number() over (partition by username order by startdate) r)
measures (startdate as dt, flag, flag as rn)
rules 
(rn[any] = case when cv(r) > 1 then rn[cv() - 1] + case when flag[cv()] = 1 and flag[cv() - 1] = 0 then 1 else 0 end
                else rn[cv()] 
           end
);

USERNAME  DT                        FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          1          1
username1 2019-04-01 01:00:00          0           
username1 2019-04-01 02:00:00          1          2
username1 2019-04-01 03:00:00          1          2
username1 2019-04-01 04:00:00          0           
username1 2019-04-02 01:00:00          1          3
username1 2019-04-02 02:00:00          1          3
username1 2019-04-02 03:00:00          1          3
username1 2019-04-02 04:00:00          0           
username1 2019-04-02 05:00:00          0           

10 rows selected.

...

Рейтинг:

0 / 0

03.04.2019, 12:00

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795692

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

AmKad

Код: plsql

1.
2.

all rows per match
pattern (f+ s*)

есть нюансы.

...

Рейтинг:

0 / 0

03.04.2019, 12:02

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795697

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

-2-AmKad

Код: plsql

1.
2.

all rows per match
pattern (f+ s*)

есть нюансы.

Код: plsql

pattern (s* f+ s*)

...

Рейтинг:

0 / 0

03.04.2019, 12:06

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795703

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

AmKad-2-пропущено...
есть нюансы.

Код: plsql

pattern (s* f+ s*)

Нашел еще один нюанс. Теперь так:

Код: plsql

pattern (s* f* s*)

...

Рейтинг:

0 / 0

03.04.2019, 12:14

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795712

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

В итоге оставляем

Код: plsql

pattern (s* f*)

и тестим на разных вариантах:

Исходные данные

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select username, startdate, flag, decode(flag, 1, match_num) as rn
from tmp
match_recognize(
partition by username
order by startdate
measures match_number() AS match_num
all rows per match
pattern (s* f*)
define f as f.flag = 1, s as s.flag = 0
);

USERNAME  STARTDATE                 FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          1          1
username1 2019-04-01 01:00:00          0           
username1 2019-04-01 02:00:00          1          2
username1 2019-04-01 03:00:00          1          2
username1 2019-04-01 04:00:00          0           
username1 2019-04-02 01:00:00          1          3
username1 2019-04-02 02:00:00          1          3
username1 2019-04-02 03:00:00          1          3
username1 2019-04-02 04:00:00          0           
username1 2019-04-02 05:00:00          0           

10 rows selected.

Первый флаг - нулевой

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select username, startdate, flag, decode(flag, 1, match_num) as rn
from tmp
match_recognize(
partition by username
order by startdate
measures match_number() AS match_num
all rows per match
pattern (s* f*)
define f as f.flag = 1, s as s.flag = 0
);

USERNAME  STARTDATE                 FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          0           
username1 2019-04-01 01:00:00          0           
username1 2019-04-01 02:00:00          1          1
username1 2019-04-01 03:00:00          1          1
username1 2019-04-01 04:00:00          0           
username1 2019-04-02 01:00:00          1          2
username1 2019-04-02 02:00:00          1          2
username1 2019-04-02 03:00:00          1          2
username1 2019-04-02 04:00:00          0           
username1 2019-04-02 05:00:00          0           

10 rows selected.

Все флаги нулевые

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag  from dual
) 
select username, startdate, flag, decode(flag, 1, match_num) as rn
from tmp
match_recognize(
partition by username
order by startdate
measures match_number() AS match_num
all rows per match
pattern (s* f*)
define f as f.flag = 1, s as s.flag = 0
);

USERNAME  STARTDATE                 FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          0           
username1 2019-04-01 01:00:00          0           
username1 2019-04-01 02:00:00          0           
username1 2019-04-01 03:00:00          0           
username1 2019-04-01 04:00:00          0           
username1 2019-04-02 01:00:00          0           
username1 2019-04-02 02:00:00          0           
username1 2019-04-02 03:00:00          0           
username1 2019-04-02 04:00:00          0           
username1 2019-04-02 05:00:00          0           

10 rows selected.

Все флаги выставлены

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag  from dual
) 
select username, startdate, flag, decode(flag, 1, match_num) as rn
from tmp
match_recognize(
partition by username
order by startdate
measures match_number() AS match_num
all rows per match
pattern (s* f*)
define f as f.flag = 1, s as s.flag = 0
);

USERNAME  STARTDATE                 FLAG         RN
--------- ------------------- ---------- ----------
username1 2019-04-01 00:00:00          1          1
username1 2019-04-01 01:00:00          1          1
username1 2019-04-01 02:00:00          1          1
username1 2019-04-01 03:00:00          1          1
username1 2019-04-01 04:00:00          1          1
username1 2019-04-02 01:00:00          1          1
username1 2019-04-02 02:00:00          1          1
username1 2019-04-02 03:00:00          1          1
username1 2019-04-02 04:00:00          1          1
username1 2019-04-02 05:00:00          1          1

10 rows selected.

...

Рейтинг:

0 / 0

03.04.2019, 12:30

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795821

rifle

Гость

Спасибо всем за варианты, почти добился того что я ожидаю, но появилась новая вводная, нужно увеличивать нумератов в случае дополнительного условия

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

with tmp as (
    select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, null as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, null as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, null as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual
) 
select 
    tmp.*,
    dense_rank() over( order by startdate, username, threshold)-
     (case when flag=1 then
     row_number()over(partition by flag, username order by startdate, username) - flag
      else null end) as grp
from tmp
order by 
startdate, username

Actual:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

+-------------+--------------------+--------+-------------+-------+
| "USERNAME"  | "STARTDATE"        | "FLAG" | "THRESHOLD" | "GRP" |
+-------------+--------------------+--------+-------------+-------+
| "username1" | 01-APR-19 00:00:00 | 1      | 1           | 1     |
| "username1" | 01-APR-19 01:00:00 | 0      |             |       |
| "username1" | 01-APR-19 02:00:00 | 1      | 1           | 2     |
| "username1" | 01-APR-19 03:00:00 | 1      |             | 2     |
| "username1" | 01-APR-19 04:00:00 | 0      |             |       |
| "username1" | 01-APR-19 05:00:00 | 0      |             |       |
| "username1" | 02-APR-19 01:00:00 | 1      | 1           | 4     |
| "username1" | 02-APR-19 02:00:00 | 1      |             | 4     |
| "username1" | 02-APR-19 03:00:00 | 1      | 1           | 4     |
| "username1" | 02-APR-19 04:00:00 | 1      |             | 4     |
| "username1" | 02-APR-19 05:00:00 | 0      |             |       |
+-------------+--------------------+--------+-------------+-------+

Expected:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

+-------------+--------------------+--------+-------------+-------+
| "USERNAME"  | "STARTDATE"        | "FLAG" | "THRESHOLD" | "GRP" |
+-------------+--------------------+--------+-------------+-------+
| "username1" | 01-APR-19 00:00:00 | 1      | 1           | 1     |
| "username1" | 01-APR-19 01:00:00 | 0      |             |       |
| "username1" | 01-APR-19 02:00:00 | 1      | 1           | 2     |
| "username1" | 01-APR-19 03:00:00 | 1      |             | 2     |
| "username1" | 01-APR-19 04:00:00 | 0      |             |       |
| "username1" | 01-APR-19 05:00:00 | 0      |             |       |
| "username1" | 02-APR-19 01:00:00 | 1      | 1           | 4     |
| "username1" | 02-APR-19 02:00:00 | 1      |             | 4     |
| "username1" | 02-APR-19 03:00:00 | 1      | 1           | 5     |
| "username1" | 02-APR-19 04:00:00 | 1      |             | 5     |
| "username1" | 02-APR-19 05:00:00 | 0      |             |       |
+-------------+--------------------+--------+-------------+-------+

...

Рейтинг:

0 / 0

03.04.2019, 14:09

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795843

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

rifle,

Почему grp = 3 пропущено? Да и вообще, "огласите весь список пожалуйста" требований.

...

Рейтинг:

0 / 0

03.04.2019, 14:35

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795852

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

AmKadПочему grp = 3 пропущено?Количество "пропущенных" нулей. Полагаю, непрерывность нумерации не требуется.

...

Рейтинг:

0 / 0

03.04.2019, 14:49

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795873

rifle

Гость

AmKadrifle,

Почему grp = 3 пропущено? Да и вообще, "огласите весь список пожалуйста" требований.

Пропущенно неумышленно, просто таким образом сработала функция, сделвав пропуски, для меня не критично. Главная задача иметь уникальный идентификатор для пачки связанных сообщений.

Вроде как удалось решить задачу следующим запросом:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

with tmp as (
    select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, 0 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 0 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, 0 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-01 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, 0 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 0 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 0 as threshold from dual union all
    select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, 0 as threshold from dual
) 
select 
    a.*
    , case when flag > 0 then sum(sog) over(order by startdate, username) end rnkx
from (
    select tmp.*
    , case when threshold = 1 or flag > lag(flag, 1, 0) over (order by startdate, username) then 1 end sog
from tmp
) a
order by startdate, username

...

Рейтинг:

0 / 0

03.04.2019, 15:21

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795878

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

rifle,

Я на stackoverflow тебе уже ответил:

Код: plsql

1.
2.

select tmp.*, decode(flag, 1, count(threshold) over (partition by username order by startdate)) rn
from tmp;

...

Рейтинг:

0 / 0

03.04.2019, 15:27

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795879

-2-

Участник

Сообщения: 15 349

Рейтинг: 0 / 0

rifleГлавная задача иметь уникальный идентификатор для пачки связанных сообщений.Это можно и без подзапросов.

...

Рейтинг:

0 / 0

03.04.2019, 15:29

| Ответить | Цитировать | Написать

Специфическая логика нумерования строк для row_number / rank

#39795880

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

AmKadrifle,

Я на stackoverflow тебе уже ответил:

Код: plsql

1.
2.

select tmp.*, decode(flag, 1, count(threshold) over (partition by username order by startdate)) rn
from tmp;

С учетом, что тут ты вместо null-ов воткнул нули:

Код: plsql

decode(flag, 1, count(decode(threshold, 1, 1)) over (partition by username order by startdate)) rn

...

Рейтинг:

0 / 0

03.04.2019, 15:29

| Ответить | Цитировать | Написать

18 сообщений из 18, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Специфическая логика нумерования строк для row_number / rank

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39795632&tid=1882633]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	44ms
get topic data:	12ms
get forum data:	3ms
get page messages:	54ms
get tp. blocked users:	1ms
others:	301ms

total:	446ms