ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Несколько разноприоритетных условий join для неоднозначных данных
3 сообщений из 3, страница 1 из 1
Несколько разноприоритетных условий join для неоднозначных данных
    #39650407
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Подскажите, как лучше такое реализовать?
Нужно связать между собой два набора данных, для которых условия по связыванию могут зависеть от нескольких факторов.
Есть Клиент. У Клиента есть дочерние Услуги разных Типов. Для новых данных соблюдается ограничение, что у Клиента может быть не больше одной Услуги каждого Типа. Но для старых Клиентов такого ограничения не было и у некоторых Клиентов может быть несколько Услуг одного типа. У Услуг есть набор различных Атрибутов, состав которых зависит от Типа услуги.

Есть два Типа услуг, которые логически увязаны друг с другом на уровне бизнес-процессов. На уровне базы данных услуги можно связать между собой (для этого в БД есть специальная таблица связей), однако в информационной системе для операторов не предусмотрен GUI для этой операции, связь устанавливается с помощью административных инструментов информационной системы.
Необходимо с помощью стороннего скрипта синхронизировать их между собой. При синхронизации услуги сопоставляются между собой по следующему алгоритму (правила применяются в рамках одного Клиента, в порядке их перечисления):
0. Если услуги присутствуют в таблице связей, то всегда используется существующая связь.
1. Если есть ровно одна Услуга с типом Тип1 и ровно одна услуга с типом Тип2, то они связываются (и эта связь добавляется в таблицу связей).
2. У Услуги с типом Тип1 есть Атрибут1. У Услуги с типом Тип2 есть Атрибут2. Если у услуг разных типов совпадают значения Атрибут1 и Атрибут2, то они связываются.
3. У Услуги с типом Тип1 есть атрибут Логин. У Услуги с типом Тип2 есть атрибут ДатаДобавления. Несвязанные услуги упорядочиваются по вышеуказанным атрибутам и связываются по порядковому номеру.
4. Оставшиеся Услуги фиксируются в журнале, ими вручную занимается администратор информационной системы.

Есть выборка для Услуг типа Тип1 примерно следующего вида:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select rank() over(partition by S.CUSTOMER_ID order by S.LOGIN) as "IDX" --порядковый номер Услуги для Клиента
, count(*) over(partition by S.CUSTOMER_ID) as "CNT" --количество Услуг у Клиента
, S.CUSTOMER_ID --идентификатор Клиента
, S.SERVICE_ID --идентификатор Услуги
, S.LOGIN --атрибут Логин
, RR.VALUE --атрибут Атрибут1
...
from SERVICES S
join RADREPLY RR on (RR.SERVICE_ID = S.SERVICE_ID and RR.ATTRIBUTE = 'Framed-IP-Address' and RR.DATE_BEG <= sysdate and (RR.DATE_END is null or RR.DATE_END > sysdate))
where ...



Есть выборка для Услуг типа Тип2 примерно следующего вида:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select rank() over(partition by S.CUSTOMER_ID order by S.CREATE_DATE) as "IDX" --порядковый номер Услуги для Клиента
, count(*) over(partition by S.CUSTOMER_ID) as "CNT" --количество Услуг у Клиента
, S.CUSTOMER_ID --идентификатор Клиента
, S.SERVICE_ID --идентификатор Услуги
, S.LOGIN --атрибут Атрибут2
, S.CREATE_DATE --атрибут ДатаДобавления
, L.SERVICE_ID as "PARENT_ID" --существующая связь с услугами Тип1
...
from BM_TARIFF T
join SERVICES S on (S.TARIFF_ID = T.TARIFF_ID and S.STATUS != -20)
left join SERVICES_LINK L on (L.SERVICE_ID_1 = S.SERVICE_ID and L.SERVICE_ID_2 is null and L.MNEMONIC = 'parent')
where ...



По отдельности эти запросы возвращают правильные результаты.
Как мне их связать между собой?

Как-то так?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with
  SET1 as ( ... ) --услуги Тип1
, SET2 as ( ... ) --услуги Тип2
select *
from SET1
left join SET2
on (
  SET2.PARENT_ID = SET1.SERVICE_ID
  or (SET1.CNT = 1 and SET2.CNT = 1 and SET1.CUSTOMER_ID = SET2.CUSTOMER_ID)
  or (SET1.CUSTOMER_ID = SET2.CUSTOMER_ID and SET1.VALUE = SET2.LOGIN and SET1.CUSTOMER_ID != SET2.CUSTOMER_ID)
  or (SET1.CUSTOMER_ID = SET2.CUSTOMER_ID and SET1.IDX = SET2.IDX and SET1.VALUE != SET2.LOGIN and SET1.CUSTOMER_ID != SET2.CUSTOMER_ID)
)


Или может быть условие соединение описывать через CASE (тогда не нужно будет повторять инвертированные предыдущие условия)?
...
Рейтинг: 0 / 0
26.05.2018, 19:05
| Ответить | Цитировать | Написать  
Несколько разноприоритетных условий join для неоднозначных данных
    #39650438
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
В финальном запросе переделал left на full.
 

Код: 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.
50.
51.
with
  SL as 
  (
    select rank() over(partition by S.CUSTOMER_ID order by S.LOGIN) as "IDX"
    , count(*) over(partition by S.CUSTOMER_ID) as "CNT"
    , S.CUSTOMER_ID as "CUSTOMER_ID"
    , S.SERVICE_ID as "SERVICE_ID"
    , S.LOGIN as "LOGIN"
    , S.STATUS
    , RR.VALUE as "IP"
    from SERVICES S
    left join RADREPLY RR on (RR.SERVICE_ID = S.SERVICE_ID and RR.ATTRIBUTE = 'Framed-IP-Address' and RR.DATE_BEG <= sysdate and (RR.DATE_END is null or RR.DATE_END > sysdate))
    where S.TYPE_ID = 14 and S.STATUS != -20
  ),
  IP as
  (
    select rank() over(partition by S.CUSTOMER_ID order by S.CREATE_DATE) as "IDX"
    , count(*) over(partition by S.CUSTOMER_ID) as "CNT"
    , S.CUSTOMER_ID as "CUSTOMER_ID"
    , S.SERVICE_ID as "SERVICE_ID"
    , S.LOGIN as "IP"
    , S.STATUS
    , L.SERVICE_ID as "PARENT"
    from BM_TARIFF T
    join SERVICES S on (S.TARIFF_ID = T.TARIFF_ID and S.STATUS != -20)
    left join SERVICES_LINK L on (L.SERVICE_ID_1 = S.SERVICE_ID and L.SERVICE_ID_2 is null and L.MNEMONIC = 'parent')
    where T.TYPE_ID = 206
    and T.DATE_WORK_BEG <= sysdate and (T.DATE_WORK_END is null or T.DATE_WORK_END > sysdate)
    and T.NAME like '%ТО IP-адрес%'
  )
select SL.IDX
, SL.CUSTOMER_ID
, SL.SERVICE_ID
, SL.LOGIN
, SL.STATUS
, SL.IP
, IP.SERVICE_ID as IP_SERVICE
, IP.IP as IP_VALUE
, IP.STATUS as IP_STATUS
, IP.PARENT as IP_PARENT
from SL full join IP
  on
  (
     (SL."SERVICE_ID" = IP."PARENT")
  or (SL."CUSTOMER_ID" = IP."CUSTOMER_ID" and SL."CNT" = 1 and IP."CNT" = 1)
  or (SL."CUSTOMER_ID" = IP."CUSTOMER_ID" and SL."IP" = IP."IP")
  or (SL."CUSTOMER_ID" = IP."CUSTOMER_ID" and SL."IP" != IP."IP" and SL."IDX" = IP."IDX")
  )
where (SL.IP is not null or IP.IP is not null)
--and SL.SERVICE_ID is not null
order by SL.CUSTOMER_ID, SL.LOGIN


Работает быстро, результаты возвращает правильные.
Но я не уверен, что связываться данные будут именно в том порядке, который мне требуется (наиболее приоритетна существующая связь, затем остальные критерии).

И еще вопрос, из чистого любопытства — почему-то full join выполняется гораздо быстрее, чем left join (250 мс против нескольких минут). Причем эта разница сохраняется даже если я раскомментирую ограничение в WHERE (которое фактически превращает full join в left join).
Планы разумеется различаются, но на мой взгляд план с left join должен быть быстрее.
 

left join + explain
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
Plan
SELECT STATEMENT  ALL_ROWSCost: 861 821  Bytes: 154 873 675  Cardinality: 315 425  									
	20 FILTER  								
		19 NESTED LOOPS OUTER  Cost: 861 821  Bytes: 154 873 675  Cardinality: 315 425  							
			7 VIEW BILLING. Cost: 3 297  Bytes: 4 075 291  Cardinality: 12 617  						
				6 WINDOW SORT  Cost: 3 297  Bytes: 1 337 402  Cardinality: 12 617  					
					5 NESTED LOOPS OUTER  Cost: 2 990  Bytes: 1 337 402  Cardinality: 12 617  				
						1 TABLE ACCESS FULL TABLE BILLING.SERVICES Cost: 222  Bytes: 996 743  Cardinality: 12 617  			
						4 VIEW SYS. Cost: 0  Bytes: 27  Cardinality: 1  			
							3 TABLE ACCESS BY INDEX ROWID TABLE BILLING.RADREPLY Cost: 4  Bytes: 63  Cardinality: 1  		
								2 INDEX RANGE SCAN INDEX BILLING.RADREPLY_SERVICE_IDX Cost: 2  Cardinality: 8  	
			18 VIEW SYS. Cost: 68  Bytes: 4 200  Cardinality: 25  						
				17 VIEW BILLING. Cost: 68  Bytes: 5 175  Cardinality: 25  					
					16 WINDOW SORT  Cost: 68  Bytes: 3 275  Cardinality: 25  				
						15 NESTED LOOPS OUTER  Cost: 67  Bytes: 3 275  Cardinality: 25  			
							12 NESTED LOOPS  Cost: 67  Bytes: 2 850  Cardinality: 25  		
								9 TABLE ACCESS BY INDEX ROWID TABLE BILLING.BM_TARIFF Cost: 6  Bytes: 67  Cardinality: 1  	
									8 INDEX RANGE SCAN INDEX BILLING.TARIFF_TYPE_IDX Cost: 1  Cardinality: 40  
								11 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES Cost: 61  Bytes: 1 786  Cardinality: 38  	
									10 INDEX RANGE SCAN INDEX BILLING.SERV_TARIFF_IDX Cost: 1  Cardinality: 234  
							14 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES_LINK Cost: 0  Bytes: 17  Cardinality: 1  		
								13 INDEX RANGE SCAN INDEX BILLING.SRV_LINK_MNEMONIC_IDX Cost: 0  Cardinality: 1

full join + explain
Код: plaintext
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.
Plan
SELECT STATEMENT  ALL_ROWSCost: 4 608 138 102  Bytes: 42 809 117 152 424  Cardinality: 57 155 029 576  										
	36 TEMP TABLE TRANSFORMATION  									
		8 LOAD AS SELECT  								
			7 WINDOW SORT  Cost: 3 924  Bytes: 4 352 865  Cardinality: 12 617  							
				6 VIEW SYS. Cost: 2 990  Bytes: 4 352 865  Cardinality: 12 617  						
					5 NESTED LOOPS OUTER  Cost: 2 990  Bytes: 1 286 934  Cardinality: 12 617  					
						1 TABLE ACCESS FULL TABLE BILLING.SERVICES Cost: 222  Bytes: 492 063  Cardinality: 12 617  				
						4 VIEW SYS. Cost: 0  Bytes: 63  Cardinality: 1  				
							3 TABLE ACCESS BY INDEX ROWID TABLE BILLING.RADREPLY Cost: 4  Bytes: 63  Cardinality: 1  			
								2 INDEX RANGE SCAN INDEX BILLING.RADREPLY_SERVICE_IDX Cost: 2  Cardinality: 8  		
		20 LOAD AS SELECT  								
			19 WINDOW SORT  Cost: 68  Bytes: 6 100  Cardinality: 25  							
				18 VIEW SYS. Cost: 67  Bytes: 6 100  Cardinality: 25  						
					17 NESTED LOOPS OUTER  Cost: 67  Bytes: 5 650  Cardinality: 25  					
						14 VIEW SYS. Cost: 67  Bytes: 5 225  Cardinality: 25  				
							13 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES Cost: 61  Bytes: 1 786  Cardinality: 38  			
								12 NESTED LOOPS  Cost: 67  Bytes: 2 850  Cardinality: 25  		
									10 TABLE ACCESS BY INDEX ROWID TABLE BILLING.BM_TARIFF Cost: 6  Bytes: 67  Cardinality: 1  	
										9 INDEX RANGE SCAN INDEX BILLING.TARIFF_TYPE_IDX Cost: 1  Cardinality: 40  
									11 INDEX RANGE SCAN INDEX BILLING.SERV_TARIFF_IDX Cost: 1  Cardinality: 234  	
						16 TABLE ACCESS BY INDEX ROWID TABLE BILLING.SERVICES_LINK Cost: 0  Bytes: 17  Cardinality: 1  				
							15 INDEX RANGE SCAN INDEX BILLING.SRV_LINK_MNEMONIC_IDX Cost: 0  Cardinality: 1  			
		35 VIEW SYS. Cost: 4 608 134 110  Bytes: 42 809 117 152 424  Cardinality: 57 155 029 576  								
			34 UNION-ALL  							
				27 FILTER  						
					26 NESTED LOOPS OUTER  Cost: 4 608 134 110  Bytes: 28 063 119 521 325  Cardinality: 57 155 029 575  					
						22 VIEW BILLING. Cost: 32 534 786  Bytes: 738 442 982 109  Cardinality: 2 286 201 183  				
							21 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B61_DA622CF8 Cost: 32 534 786  Bytes: 434 378 224 770  Cardinality: 2 286 201 183  			
						25 VIEW SYS. Cost: 2  Bytes: 4 200  Cardinality: 25  				
							24 VIEW BILLING. Cost: 2  Bytes: 5 175  Cardinality: 25  			
								23 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B62_DA622CF8 Cost: 2  Bytes: 1 650  Cardinality: 25  		
				33 FILTER  						
					30 FILTER  					
						29 VIEW BILLING. Cost: 2  Bytes: 5 175  Cardinality: 25  				
							28 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B62_DA622CF8 Cost: 2  Bytes: 1 650  Cardinality: 25  			
					32 VIEW BILLING. Cost: 32 534 786  Bytes: 413 802 414 123  Cardinality: 2 286 201 183  					
						31 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6B61_DA622CF8 Cost: 32 534 786  Bytes: 434 378 224 770  Cardinality: 2 286 201 183
...
Рейтинг: 0 / 0
26.05.2018, 20:27
| Ответить | Цитировать | Написать  
Несколько разноприоритетных условий join для неоднозначных данных
    #39650453
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
И еще хотел бы спросить.
С полученными данными мне нужно будет делать несколько разных обработок.
Поэтому я хочу использовать не запрос, а вспомогательную таблицу, которая будет предварительно очищаться, а затем в которую эти данные будут сохраняться.
Не посоветуете, с какими параметрами и атрибутами эту таблицу лучше создавать? Я задал LOGGING, CACHE и NOPARALLEL.
Или лучше просто сделать временную таблицу (global temp)?
...
Рейтинг: 0 / 0
26.05.2018, 21:37
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Несколько разноприоритетных условий join для неоднозначных данных
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=113&tid=1883935]:
 0ms
get settings:
 5ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 49ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 21ms
get tp. blocked users:
 1ms
others: 207ms
total:  306ms
созд. / загр.: 306ms
x
x
Закрыть


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