ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Приоритет join по длине текстового поля
14 сообщений из 14, страница 1 из 1
Приоритет join по длине текстового поля
    #40021449
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Допустим, есть такой список сущностей:
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:08
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021462
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Неплохой способ при правильной организации (индекс по 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, 16:58
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021472
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Alibek B.,

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

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

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

Версия оракла всё так же 10g?
...
Рейтинг: 0 / 0
23.11.2020, 17:33
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021478
Фотография 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.
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, 17:43
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021512
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
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, 19:18
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021555
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
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:24
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021561
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Код: 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, 22:58
| Ответить | Цитировать | Написать  
Приоритет join по длине текстового поля
    #40021573
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Спасибо за советы.
Делал через substr, а не через like, из-за старых привычек, в прикладном десктопном программировании вырезать фрагмент строки в десятки раз быстрее работы с шаблонами или регулярными выражениями. Как я понимаю, напрасно.

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

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

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

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

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

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

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

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

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


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