Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как отсортировать группы ? / 12 сообщений из 12, страница 1 из 1
02.07.2020, 19:48
    #39975631
Hibiscus
Hibiscus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Добрый вечер.

Есть следующие данные в таблице:
 
Исходные данные
Код: 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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
create table t_test_data(contract_num varchar2(30),priority number(10), acc_priority number(10), account varchar2(30));
--
insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 0', 7, 1, 'Account 140');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 0', 2, 1, 'Account 140');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 14', 2, 1, 'Account 40');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 14', 2, 2, 'Account 140');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 63', 2, 1, 'Account 160');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 63', 7, 1, 'Account 160');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 63', 7, 2, 'Account 180');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 63', 2, 2, 'Account 180');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 63', 2, 3, 'Account 140');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 63', 7, 3, 'Account 140');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 7', 7, 1, 'Account 80');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 7', 2, 1, 'Account 80');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 7', 2, 2, 'Account 180');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 7', 7, 2, 'Account 180');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 7', 2, 3, 'Account 140');

insert into T_TEST_DATA (CONTRACT_NUM, PRIORITY, ACC_PRIORITY, ACCOUNT)
values ('Contract 7', 7, 3, 'Account 140');



Эти данные отправляются во внешний сервис с сортировкой по полям order by CONTRACT_NUM asc, PRIORITY asc, ACC_PRIORITY asc.
Хочется оптимизировать отправку данных - сделать отправку группами.
Единственное препятствие:
- в группах должны быть только уникальные ACCOUNT
- должна сохранятся сортировка в рамках CONTRACT_NUM asc, PRIORITY asc, ACC_PRIORITY asc.

Простое решение:
Код: plsql
1.
2.
3.
4.
select td.*,
       row_number() over(order by td.contract_num, td.priority, td.acc_priority) sending_seq,
       row_number() over (partition by td.account order by td.contract_num, td.priority, td.acc_priority) sending_gorup
  from t_test_data td



Столкнулось с проблемой нарушения sending_seq:

CONTRACT_NUMPRIORITYACC_PRIORITYACCOUNTSENDING_SEQSENDING_GROUPContract 021Account 14011Contract 071Account 14022Contract 1421Account 4031Contract 1422Account 14043Contract 6321Account 16051Contract 6322Account 18061Contract 6323Account 14074Contract 6371Account 16082Contract 6372Account 18092Contract 6373Account 140105Contract 721Account 80111Contract 722Account 180123Contract 723Account 140136Contract 771Account 80142Contract 772Account 180154Contract 773Account 140167

Есть ли приличный(не уходя в PL\SQL) способ создать SENDING_GROUP не противоречащие двум условиям?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.07.2020, 22:41
    #39975691
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Код: 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.
with t as (
           select  td.*,
                   row_number() over (partition by account order by contract_num,priority,acc_priority) sending_group
             from  t_test_data td
          )
select  t.*,
        row_number() over(partition by sending_group order by contract_num,priority,acc_priority) sending_seq
  from  t
/

CONTRACT_NUM                     PRIORITY ACC_PRIORITY ACCOUNT                        SENDING_GROUP SENDING_SEQ
------------------------------ ---------- ------------ ------------------------------ ------------- -----------
Contract 0                              2            1 Account 140                                1           1
Contract 14                             2            1 Account 40                                 1           2
Contract 63                             2            1 Account 160                                1           3
Contract 63                             2            2 Account 180                                1           4
Contract 7                              2            1 Account 80                                 1           5
Contract 0                              7            1 Account 140                                2           1
Contract 63                             7            1 Account 160                                2           2
Contract 63                             7            2 Account 180                                2           3
Contract 7                              7            1 Account 80                                 2           4
Contract 14                             2            2 Account 140                                3           1
Contract 7                              2            2 Account 180                                3           2
Contract 63                             2            3 Account 140                                4           1
Contract 7                              7            2 Account 180                                4           2
Contract 63                             7            3 Account 140                                5           1
Contract 7                              2            3 Account 140                                6           1
Contract 7                              7            3 Account 140                                7           1

16 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.07.2020, 22:59
    #39975695
Hibiscus
Hibiscus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
SY,

Я извиняюсь, возможно я не точно сформулировал, но по sending_seq получаются одинаковые account(- в группах должны быть только уникальные ACCOUNT), а по sending_group нарушается второе условие.

Должно получится что-то подобное:

№CONTRACT_NUMPRIORITYACC_PRIORITYACCOUNTSENDING_GROUP1Contract 021Account 14012Contract 071Account 14023Contract 1421Account 4024Contract 1422Account 14035Contract 6321Account 16036Contract 6322Account 18037Contract 6323Account 14048Contract 6371Account 16049Contract 6372Account 180410Contract 6373Account 140511Contract 721Account 80512Contract 722Account 180513Contract 723Account 140614Contract 771Account 80615Contract 772Account 180616Contract 773Account 1407
Или так:
№CONTRACT_NUMPRIORITYACC_PRIORITYACCOUNTSENDING_GROUP1Contract 021Account 14012Contract 071Account 14023Contract 1421Account 4014Contract 1422Account 14035Contract 6321Account 16016Contract 6322Account 18017Contract 6323Account 14048Contract 6371Account 16049Contract 6372Account 180410Contract 6373Account 140511Contract 721Account 80112Contract 722Account 180213Contract 723Account 140614Contract 771Account 80615Contract 772Account 180616Contract 773Account 1406
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.07.2020, 23:39
    #39975715
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Hibiscus
Я извиняюсь, возможно я не точно сформулировал, но по sending_seq получаются одинаковые account(- в группах должны быть только уникальные ACCOUNT), а по sending_group нарушается второе условие.
У SY как раз как ты показал второй вариант, только смотри на sending_group, а не sending_sequence. Отсортируй его вывод в таком же порядке как у тебя в примере и увидишь, что все сходится.

Вообще, у тебя огромная разница между двумя примерами:
в
 
Должно получится что-то подобное:
Hibiscus
№CONTRACT_NUMPRIORITYACC_PRIORITYACCOUNTSENDING_GROUP1Contract 021Account 14012Contract 071Account 14023Contract 1421Account 4024Contract 1422Account 14035Contract 6321Account 16036Contract 6322Account 18037Contract 6323Account 14048Contract 6371Account 16049Contract 6372Account 180410Contract 6373Account 140511Contract 721Account 80512Contract 722Account 180513Contract 723Account 140614Contract 771Account 80615Contract 772Account 180616Contract 773Account 1407
вот в этом первом используется сквозная сортировка - она намного сложнее - нужно менять номер группы каждый раз как попадается аккаунт из текущей группы.

а в
 
Или так:
Hibiscus

№CONTRACT_NUMPRIORITYACC_PRIORITYACCOUNTSENDING_GROUP1Contract 021Account 14012Contract 071Account 14023Contract 1421Account 4014Contract 1422Account 14035Contract 6321Account 16016Contract 6322Account 18017Contract 6323Account 14048Contract 6371Account 16049Contract 6372Account 180410Contract 6373Account 140511Contract 721Account 80112Contract 722Account 180213Contract 723Account 140614Contract 771Account 80615Contract 772Account 180616Contract 773Account 1406
используется лишь сортировка внутри группы, что SY и дал тебе. Это намного проще
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.07.2020, 23:55
    #39975718
Hibiscus
Hibiscus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
xtender,

Не не, все же я видно плохо написал.
Условие - должна сохранятся сортировка в рамках CONTRACT_NUM asc, PRIORITY asc, ACC_PRIORITY asc.
в рамках каждого договора должна сохранится последовательность отправки по PRIORITY и ACC_PRIORITY, пусть и в разных группах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 10:50
    #39975820
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Hibiscus

Должно получится что-то подобное:


пятница, устал наверное
что-то кроме иерархии + мултисет/(конкатенация в строку) ничего хорошего в голову не приходит

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 11:43
    #39975844
Hibiscus
Hibiscus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Получилось! Видимо во сне пришло :-)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
with t as (
           select  td.*,
                   row_number() over (partition by account order by contract_num,priority,acc_priority) sending_group
             from  t_test_data td
          )
select  t.*,
        max(sending_group) over(partition by contract_num order by priority,acc_priority rows between unbounded preceding and current row) mgr1,
        max(sending_group) over(order by contract_num,priority,acc_priority rows between unbounded preceding and current row) mgr2
  from  t
order by contract_num,priority,acc_priority



Причем оба варианта.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 12:18
    #39975858
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Hibiscus
Получилось!


для
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
Contract 7                              2            1 Account 80                                 1          1          5
Contract 7                              2            2 Account 180                                3          3          5
Contract 7                              2            3 Account 180                                4          4          5
Contract 7                              2            4 Account 180                                5          5          5
Contract 7                              2            6 Account 140                                6          6          6
Contract 7                              7            1 Account 80                                 2          6          6
Contract 7                              7            2 Account 180                                6          6          6
Contract 7                              7            3 Account 140                                7          7          7



группа меняется?

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 12:20
    #39975859
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Hibiscus,

через гланды
Код: 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.
39.
40.
41.
42.
43.
44.
SQL> ed
Wrote file afiedt.buf

  1  with td as (
  2  select t.*
  3   ,row_number() over (partition by contract_num order by priority, acc_priority,account) rn
  4   from T_TEST_DATA t)
  5  , t (CONTRACT_NUM,PRIORITY, ACC_PRIORITY, ACCOUNT,RN,m,sub_gr) as (
  6    select td.*,ANE_CHAR_TABLE(account),1  from td where rn=1
  7  UNION ALL
  8    select td.CONTRACT_NUM,td.PRIORITY, td.ACC_PRIORITY, td.ACCOUNT,td.RN
  9     ,case when ANE_CHAR_TABLE(td.account) submultiset of t.m then ANE_CHAR_TABLE(td.account)
 10              else t.m MULTISET UNION ANE_CHAR_TABLE(td.account) end
 11     ,case when ANE_CHAR_TABLE(td.account) submultiset of t.m then t.sub_gr+1 else t.sub_gr end
 12    from td,t where td.rn=t.rn+1 and td.CONTRACT_NUM=t.CONTRACT_NUM
 13  )
 14  select * from t
 15* order by contract_num,priority, acc_priority,account
SQL> /

CONTRACT_NUM                     PRIORITY ACC_PRIORITY ACCOUNT                                RN     SUB_GR
------------------------------ ---------- ------------ ------------------------------ ---------- ----------
Contract 0                              2            1 Account 140                             1          1
Contract 0                              7            1 Account 140                             2          2
Contract 14                             2            1 Account 40                              1          1
Contract 14                             2            2 Account 140                             2          1
Contract 63                             2            1 Account 160                             1          1
Contract 63                             2            2 Account 180                             2          1
Contract 63                             2            3 Account 140                             3          1
Contract 63                             7            1 Account 160                             4          2
Contract 63                             7            2 Account 180                             5          2
Contract 63                             7            3 Account 140                             6          2
Contract 7                              2            1 Account 80                              1          1
Contract 7                              2            2 Account 180                             2          1
Contract 7                              2            3 Account 180                             3          2
Contract 7                              2            4 Account 180                             4          3
Contract 7                              2            6 Account 140                             5          3
Contract 7                              7            1 Account 80                              6          3
Contract 7                              7            2 Account 180                             7          4
Contract 7                              7            3 Account 140                             8          4

18 rows selected.

SQL>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 12:56
    #39975874
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Hibiscus
Причем оба варианта.
Оба нарушают первое правило, но если это не критично, то можно упростить и убрать дефолтное определение окна "rows between unbounded preceding and current row". В этом решении от него толку нет.

Если же требуется соответствие обоим правилам, то в записимости от того, что понимается по сохранением сортировки

Либо
Код: 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.
SQL> select *
  2    from
  3  (select * from t_test_data
  4  union all select 'Contract 7',  2, 3, 'Account 180' from dual
  5  union all select 'Contract 7',  2, 4, 'Account 180' from dual) td
  6  model
  7  dimension by (contract_num, priority, acc_priority, account)
  8  measures (0 gr)
  9  (
 10    gr[any,any,any,any] order by contract_num, priority, acc_priority, account =
 11    greatest(max(gr)[any,any,any,cv(account)]+1,nvl(max(gr)[any,any,any,any],0))
 12  )
 13  order by 1, 2, 3;

CONTRACT_NUM                     PRIORITY ACC_PRIORITY ACCOUNT                                GR
------------------------------ ---------- ------------ ------------------------------ ----------
Contract 0                              2            1 Account 140                             1
Contract 0                              7            1 Account 140                             2
Contract 14                             2            1 Account 40                              2
Contract 14                             2            2 Account 140                             3
Contract 63                             2            1 Account 160                             3
Contract 63                             2            2 Account 180                             3
Contract 63                             2            3 Account 140                             4
Contract 63                             7            1 Account 160                             4
Contract 63                             7            2 Account 180                             4
Contract 63                             7            3 Account 140                             5
Contract 7                              2            1 Account 80                              5
Contract 7                              2            2 Account 180                             5
Contract 7                              2            3 Account 140                             6
Contract 7                              2            3 Account 180                             6
Contract 7                              2            4 Account 180                             7
Contract 7                              7            1 Account 80                              7
Contract 7                              7            2 Account 180                             8
Contract 7                              7            3 Account 140                             8

18 rows selected.



Либо второй агрегат как-то так (если интересует неубывание по приоритету безотносительно остального)
Код: plsql
1.
max(gr)[any,priority<cv(priority),any,any]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 13:13
    #39975884
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Stax,

не так понял и сделал для partition by contract_num
легко убрать

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2020, 14:32
    #39975928
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как отсортировать группы ?
Кобанчег
Код: plsql
1.
2.
3.
4.
(
  gr[any,any,any,any] order by contract_num, priority, acc_priority, account =
  greatest(max(gr)[any,any,any,cv(account)]+1,nvl(max(gr)[any,any,any,any],0))
)

Если устраивает результат, то можно предварительно получить порядок прохода ord и разнообразие any можно упростить до
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select *
  from
(select * from t_test_data
union all select 'Contract 7',  2, 3, 'Account 180' from dual
union all select 'Contract 7',  2, 4, 'Account 180' from dual) td
model
dimension by (row_number() over (order by contract_num, priority, acc_priority) ord, account)
measures (contract_num, priority, acc_priority, 0 gr)
(
  gr[any,any] order by contract_num, priority, acc_priority, account =
  greatest(max(gr)[any,cv(account)]+1,nvl(max(gr)[cv(ord)-1,any],0))
)
order by 1, 2, 3;

Это будет чуть эффективнее.
Но однопроходный PL/SQL с ассициативным массивом по account и без всяких агрегатов будет эффективнее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как отсортировать группы ? / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 1736ms
Закрыть
start [/forum/search_topic.php?author=%D0%A2%D0%B8%D0%BC%D1%83%D1%80+%D0%91&author_mode=last_topics&do_search=1]:
 0ms
get settings:
 9ms
get forum list:
 12ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 55ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 1561ms
total:  1736ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]