Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Приоритет join по длине текстового поля / 14 сообщений из 14, страница 1 из 1
23.11.2020, 16:08
    #40021449
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Допустим, есть такой список сущностей:
itemtitle1Белый 12Красный 13Красный 24Красный Плюс 15Желтый Плюс 1
Мне нужно их сгруппировать по категории. Категория является частью (суффиксом) наименования, при этом есть отдельный справочник категорий:
catgrp1Белый2Красный3Желтый4Красный Плюс
Мне нужно получить такой результат:
itemtitlecat1Белый 112Красный 123Красный 224Красный Плюс 145Желтый Плюс 13
Нужно по совпадению имени сопоставить сущность и категорию, причем выбирать только одну категорию, предпочитая категорию с максимальной длиною текста.

Можно ли задать для соединения такой предикат, чтобы получить только одну строку соответствующей категории без лишних действий (группировок)?
Пока что я делаю примерно так:
Код: plsql
1.
2.
3.
4.
  select id, max(grp) keep (dense_rank last order by length(grp)) as grp
  from items
  left join cats on (upper(grp) = upper(substr(title, 1, length(grp))))
  group by id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 16:58
    #40021462
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Неплохой способ при правильной организации (индекс по cats.grp, NLS_SORT, NLS_COMP сессии соответствует индексу):
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with items (id, title) as( select 1,  'Белый 1'
from dual union all select 2,  'Красный 1'
from dual union all select 3,  'Красный 2'
from dual union all select 4,  'Красный Плюс 1'
from dual union all select 5,  'Желтый Плюс 1'
from dual
), cats (cat, grp) as ( select 1,  'Белый'
from dual union all select 2,  'Красный'
from dual union all select 3,  'Желтый'
from dual union all select 4,  'Красный Плюс'
from dual)
  select id, title, grp, cat
  from items i
  outer apply (select grp, cat from cats c where i.title >= c.grp order by grp desc fetch first 1 row only) x
;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 17:17
    #40021472
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Alibek B.,

я б делал через like

ps
>= чревато если сосем нет соответствия в кратком справочнике

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 17:33
    #40021476
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Alibek B.,

Версия оракла всё так же 10g?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 17:43
    #40021478
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Код: 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.
explain plan for
  select id, title, grp, cat
  from items i
  outer apply (select grp, cat from cats c where i.title >= c.grp order by grp desc fetch first 1 row only) x
;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3714172758

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     5 |   155 |    23  (22)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |                 |     5 |   155 |    23  (22)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | ITEMS           |     5 |    60 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT                |                 |     1 |    19 |    20  (25)| 00:00:01 |
|   4 |    VIEW                      | VW_LAT_6FD14B30 |     1 |    19 |     4  (25)| 00:00:01 |
|   5 |     VIEW                     | VW_LAT_A18161FF |     1 |    19 |     4  (25)| 00:00:01 |
|*  6 |      VIEW                    |                 |     1 |    38 |     4  (25)| 00:00:01 |
|*  7 |       WINDOW SORT PUSHED RANK|                 |     3 |    30 |     4  (25)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL     | CATS            |     3 |    30 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=1)
   7 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("C"."GRP") DESC )<=1)
   8 - filter("I"."TITLE">="C"."GRP")

22 rows selected.

SQL>



И:

Код: 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.
explain plan for
select  i.id,
        i.title,
        (
         select  c.cat
           from  cats c
           where i.title like c.grp || '%'
           order by c.grp desc
           fetch first 1 row only
        ) cat
  from items i
;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2755160334

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     5 |    60 |    13  (16)| 00:00:01 |
|*  1 |  VIEW                    |       |     1 |    26 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|       |     1 |    10 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | CATS  |     1 |    10 |     3   (0)| 00:00:01 |
|   4 |  TABLE ACCESS FULL       | ITEMS |     5 |    60 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("C"."GRP")
              DESC )<=1)
   3 - filter(:B1 LIKE "C"."GRP"||'%')

19 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 19:18
    #40021512
НеофитSQL
НеофитSQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Alibek B.
...
Мне нужно получить такой результат:
itemtitlecat1Белый 112Красный 123Красный 224Красный Плюс 145Желтый Плюс 13

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

Можно ли задать для соединения такой предикат, чтобы получить только одну строку соответствующей категории без лишних действий (группировок)?
Пока что я делаю примерно так:
Код: plsql
1.
2.
3.
4.
  select id, max(grp) keep (dense_rank last order by length(grp)) as grp
  from items
  left join cats on (upper(grp) = upper(substr(title, 1, length(grp))))
  group by id



Попробуйте так:

Код: plsql
1.
2.
3.
4.
select i.*, 
       (select max(cat) keep (dense_rank last order by length(grp)) 
          from cats where instr(i.title,grp) = 1) -- "=1" если категория - префикс наименования
  from items i
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 22:24
    #40021555
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
SY
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
--------
|   0 | SELECT STATEMENT             |                 |     5 |   155 |    23  (22)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |                 |     5 |   155 |    23  (22)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | ITEMS           |     5 |    60 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT                |                 |     1 |    19 |    20  (25)| 00:00:01 |
|   4 |    VIEW                      | VW_LAT_6FD14B30 |     1 |    19 |     4  (25)| 00:00:01 |
|   5 |     VIEW                     | VW_LAT_A18161FF |     1 |    19 |     4  (25)| 00:00:01 |
|*  6 |      VIEW                    |                 |     1 |    38 |     4  (25)| 00:00:01 |
|*  7 |       WINDOW SORT PUSHED RANK|                 |     3 |    30 |     4  (25)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL     | CATS            |     3 |    30 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Соломон, в политбюро не дураки сидят - на Солнце лететь надо ночью ;)
Какие планы на 5 строчках, о чем это было?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 22:58
    #40021561
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Код: 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.
with items (id, title) as( select 1,  'Белый 1'
from dual union all select 2,  'Красный 1'
from dual union all select 3,  'Красный 2'
from dual union all select 4,  'Красный Плюс 1'
from dual union all select 5,  'Желтый Плюс 1'
from dual
), cats (cat, grp) as ( select 1,  'Белый'
from dual union all select 2,  'Красный'
from dual union all select 3,  'Желтый'
from dual union all select 4,  'Красный Плюс'
from dual)
--
select I.id, I.title, C.grp, C.cat
from items i, cats c 
where i.title LIKE c.grp||'%'
  AND NOT EXISTS (
     SELECT * FROM cats X 
     WHERE i.title LIKE X.grp||'%'
       AND c.grp < X.grp)
;

        ID TITLE          GRP                 CAT
---------- -------------- ------------ ----------
         1 Белый 1        Белый                 1
         2 Красный 1      Красный               2
         3 Красный 2      Красный               2
         4 Красный Плюс 1 Красный Плюс          4
         5 Желтый Плюс 1  Желтый                3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.11.2020, 23:59
    #40021573
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Спасибо за советы.
Делал через substr, а не через like, из-за старых привычек, в прикладном десктопном программировании вырезать фрагмент строки в десятки раз быстрее работы с шаблонами или регулярными выражениями. Как я понимаю, напрасно.

Да, версию указать забыл, 10g.
Это к сожалению исключает fetch first.
Но даже замена substr на like наверное улучшит запрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.11.2020, 00:41
    #40021580
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
Alibek B.
Спасибо за советы.
Делал через substr, а не через like, из-за старых привычек, в прикладном десктопном программировании вырезать фрагмент строки в десятки раз быстрее работы с шаблонами или регулярными выражениями. Как я понимаю, напрасно.

Да, версию указать забыл, 10g.
Это к сожалению исключает fetch first.
Но даже замена substr на like наверное улучшит запрос.

Мой вариант должен сработать и на Oracle 10g.
Проверьте на реальных данных.

Конечно, коррелированный запрос - это не слишком эффективно, но работать должно.

Предполагаю, что в плане для реальных таблиц будет Nested Loops, и поможет индекс по TITLE.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.11.2020, 02:16
    #40021599
НеофитSQL
НеофитSQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
SQL*Plus,

Остроумная подстановка неравенства строчек вместо неравенства длины.

Как такое провернуть для case-insrnsitive сравнений? Везде добавить upper()?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.11.2020, 09:50
    #40021641
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
andrey_anonymous

о чем это было?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with items (id, title) as( select 1,  'Белый 1'
from dual union all select 2,  'Красный 1'
from dual union all select 3,  'Красный 2'
from dual union all select 4,  'Красный Плюс 1'
from dual union all select 5,  'Желтый Плюс 1'
from dual union all select 6,  'Красненький 1'
from dual
), cats (cat, grp) as ( select 1,  'Белый'
from dual union all select 2,  'Красный'
from dual union all select 3,  'Желтый'
from dual union all select 4,  'Красный Плюс'
from dual)
  select id, title, grp, cat
  from items i
  outer apply (select grp, cat from cats c where i.title >= c.grp order by grp desc fetch first 1 row only) x


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
ID	TITLE	GRP	CAT
1	Белый 1	Белый	1
2	Красный 1	Красный	2
3	Красный 2	Красный	2
4	Красный Плюс 1	Красный Плюс	4
5	Желтый Плюс 1	Желтый	3
6	Красненький 1	Желтый	3
для ид=6 какой должен быть cat?

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.11.2020, 10:11
    #40021655
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
НеофитSQL
провернуть для case-insrnsitive сравнений

зависит от версии
Код: plsql
1.
select * from dual where dummy like 'x%' collate generic_m_ci;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.11.2020, 10:41
    #40021674
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Приоритет join по длине текстового поля
SQL*Plus
Мой вариант должен сработать и на Oracle 10g.
Проверьте на реальных данных.

Да, отлично работает, быстрее моего.
Правда не пойму, как переделать его на left join, чтобы для несопоставленных сущностей (например "Зеленый 1") в результате была категория null. Но это я наверное уже сам смогу решить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Приоритет join по длине текстового поля / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 442ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880674]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 66ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 299ms
total:  442ms
x
x
Закрыть


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