ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как отсортировать группы ?
12 сообщений из 12, страница 1 из 1
Как отсортировать группы ?
    #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, 19:48
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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:41
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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, 22:59
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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:39
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #39975718
Hibiscus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hibiscus
Гость
xtender,

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

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


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

.....
stax
...
Рейтинг: 0 / 0
03.07.2020, 10:50
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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, 11:43
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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:18
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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:20
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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, 12:56
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #39975884
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Stax,

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

.....
stax
...
Рейтинг: 0 / 0
03.07.2020, 13:13
| Ответить | Цитировать | Написать  
Как отсортировать группы ?
    #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
03.07.2020, 14:32
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как отсортировать группы ?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=42&tid=1881099]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 41ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 36ms
get tp. blocked users:
 1ms
others: 301ms
total:  411ms
созд. / загр.: 411ms
x
x
Закрыть


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