ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка, альтернативное решение
25 сообщений из 26, страница 1 из 2
  1  все
Задачка, альтернативное решение
    #39541655
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
Ребята всем привет!

Недавно столкнулся вот с такой задачкой: Есть некоторая таблица(отношение) - A , содержащее N-записей(кортежей). Нужно по одному атрибуту для каждого кортежа(записи) провести операцию(рекурсивную), результатом которой станет новое отношение - B , содержащее N-записей. После этого для каждого атрибута таблицы A следует вывести соответствующие ему данные из таблицы B - Т.е. провести операцию соединения.

Возможно, я не совсем понятно написал, но надеюсь, что пример сделает задачу более понятной:

Дана таблица A :

Id Word1ABC2RU
Код: plsql
1.
2.
3.
4.
5.
6.
With A as 
     (
       select 1 as id, 'ABC' as Word from Dual
       union  
       select 2 as id, 'RU' as Word from Dual
     )



Задача - разобрать значение атрибута Word, таблицы A , посимвольно и вывести для атрибута Word каждый соответствующий ему символ. Результат:

Id Word Letter1ABCA1ABC B1ABC C2RUR2RUU

Решение:

Первое: Для разбора значения посимвольно я решил использовать рекурсивынй(иерархический) запрос. Далее просто передаю значения атрибута в этот запрос и получаю его разбор. Можно было это реализовать на PL/SQL: открываем один курсор по таблице А, второй внутри него, который содержит запрос разбора. Но хотелось все-таки на SQL.
Втрое: нужно чтобы иерархический запрос разбора работал для конкретного атрибута, а не гулял по всем атрибутам таблицы. Поэтому я решил воспользоваться унарным оператором CONNECT_BY_ROOT.

В итоге я пришел вот к такому решению :
Код: plsql
1.
2.
3.
4.
5.
select 
      Word, SubStr(Word,level,1) as Letter  
from A
     connect by Length(Word)>=Level 
            and CONNECT_BY_ROOT id = id



Но, я потом подумал, а что если бы в таблице не было id!? Возможно я смог бы использовать в качестве уникального идентификатора Rowid? Решил попробовать сначала для одной записи так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
With A as 
     (
       select 1 as id, 'ABC' as Word from Dual
       --union  
       --select 2 as id, 'RU' as Word from Dual
     )

select 
      Word, SubStr(Word,level,1) as Letter  
from A
     connect by Length(Word)>=Level 
            and CONNECT_BY_ROOT Rowid = Rowid



Отработало все хорошо! Потом решил для всех записей в таблице:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
With A as 
     (
       select 1 as id, 'ABC' as Word from Dual
       union  
       select 2 as id, 'RU' as Word from Dual
     )

select 
      Word, SubStr(Word,level,1) as Letter  
from A
     connect by Length(Word)>=Level 
            and CONNECT_BY_ROOT Rowid = Rowid


А здесь получил вот такую ошибку:
Код: plsql
1.
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.



Товарищи , есть два вопроса к Вам:

1) Можете предложить альтернативное решение задачи? У меня был Oracle 10g
2) Почему возникла ошибка в последнем примере?
...
Рейтинг: 0 / 0
25.10.2017, 13:03
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541692
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SQL_boyВ итоге я пришел вот к такому решению :
Код: plsql
1.
2.
3.
4.
5.
select 
      Word, SubStr(Word,level,1) as Letter  
from A
     connect by Length(Word)>=Level 
            and CONNECT_BY_ROOT id = id

Эту багу прикрыли в 11g:
Код: plsql
1.
ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition


Лучше всего иметь табличную поточную функцию-генератор строк.
...
Рейтинг: 0 / 0
25.10.2017, 13:30
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541752
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
Никаких rowid
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
With A as 
     (
       select 'ABC' as Word from Dual
       union  
       select 'RU' as Word from Dual
     )

select 
      Word, SubStr(Word,level,1) as Letter  
from A
     connect by  prior word = word 
     and SubStr(Word,level,1) is not null
     and prior dbms_random.value is not null
...
Рейтинг: 0 / 0
25.10.2017, 14:10
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541759
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
ElicЭту багу прикрыли в 11g:
Код: plsql
1.
ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition




А почему багу?
Получается, если на базе сейчас стоит это решение и через какое-то время ее переведу на 11g, то процедура сломается. Да-с, хреново!
ElicЛучше всего иметь табличную поточную функцию-генератор строк.


Это которая pipelined function ?
...
Рейтинг: 0 / 0
25.10.2017, 14:12
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541765
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SQL_boyЭто которая pipelined function ?Именно.
...
Рейтинг: 0 / 0
25.10.2017, 14:16
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541773
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
MazoHistНикаких rowid
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
With A as 
     (
       select 'ABC' as Word from Dual
       union  
       select 'RU' as Word from Dual
     )

select 
      Word, SubStr(Word,level,1) as Letter  
from A
     connect by  prior word = word 
     and SubStr(Word,level,1) is not null
     and prior dbms_random.value is not null



Интересное решение!!!
Только одно но! Если встречается одинаковое значение атрибута, то оно уже неправильно отработает!
...
Рейтинг: 0 / 0
25.10.2017, 14:19
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541777
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
SQL_boyИнтересное решение!!!
Только одно но! Если встречается одинаковое значение атрибута, то оно уже неправильно отработает!
Ожидал вопрос. Делаем неуникальное уникальным.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
With A as 
     (
       select 'ABC' as Word from Dual
       union  all
       select 'ABC' as Word from Dual
     )
, b as (select word, row_number() over (partition by word order by null) rn from a)
select 
      Word, SubStr(Word,level,1) as Letter  
from b
     connect by  prior word = word and  prior rn = rn 
     and SubStr(Word,level,1) is not null
     and prior dbms_random.value is not null


только потом результат тоже надо как-то разделять будет.
...
Рейтинг: 0 / 0
25.10.2017, 14:26
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541789
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SQL_boyИнтересное решение!!!
Только одно но! Если встречается одинаковое значение атрибута, то оно уже неправильно отработает!

connect by prior id/rowid = id/rowid

ps
другой вопрос если dbms_random.value сгенерит одинаковые значения

.....
stax
...
Рейтинг: 0 / 0
25.10.2017, 14:33
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541792
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
StaxSQL_boyИнтересное решение!!!
Только одно но! Если встречается одинаковое значение атрибута, то оно уже неправильно отработает!

connect by prior id/rowid = id/rowid

ps
другой вопрос если dbms_random.value сгенерит одинаковые значения

.....
stax

А какая разница? Он же на not null проверяется
Код: plsql
1.
 and prior dbms_random.value is not null
...
Рейтинг: 0 / 0
25.10.2017, 14:37
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541796
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
Staxconnect by prior id/rowid = id/rowid
.....
stax

Он ругается, если использовать rowid. Ошибка, как в первом моем посте.
...
Рейтинг: 0 / 0
25.10.2017, 14:42
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541805
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
To MazoHist

Решение принимается! :)
Учитывая, что мое решение работает только на 10g, твое универсальное!
...
Рейтинг: 0 / 0
25.10.2017, 14:50
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541807
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SQL_boyStaxconnect by prior id/rowid = id/rowid
.....
stax

Он ругается, если использовать rowid. Ошибка, как в первом моем посте.

Ну нету rowid у сгенерированных таким образом записей - они же не сохранены в таблице.
Сложите в табличку - будет rowid.
Если on-fly - то используйте вместо rowid любой нумератор, хоть rownum.
...
Рейтинг: 0 / 0
25.10.2017, 14:53
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541811
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SQL_boy To MazoHist

Решение принимается!
Вот это:
Код: plsql
1.
and prior dbms_random.value is not null


печальная тема.
Воспользуйтесь лучше рекурсивным with subquery clause.
Другой вариант - сгенерировать нужное число строк (как максимальную длину строки) и соединить с исходной таблицей.
Еще лучше - опереться на любую имеющуюся таблицу, содержащую более 4000 строк.
...
Рейтинг: 0 / 0
25.10.2017, 14:56
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541814
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SQL_boyStaxпропущено...


connect by prior id/rowid = id/rowid

ps
другой вопрос если dbms_random.value сгенерит одинаковые значения

.....
stax

А какая разница? Он же на not null проверяется
Код: plsql
1.
 and prior dbms_random.value is not null



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
  1  select ename,lpad(SubStr(ename,level,1),level,' ') let from emp
  2  where deptno=10
  3  connect by  prior empno = empno
  4       and SubStr(ename,level,1) is not null
  5*      and prior trunc(dbms_random.value(1,5)) is not null
SQL> /
select ename,lpad(SubStr(ename,level,1),level,' ') let from emp
                                                            *
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data




SQL_boyStaxconnect by prior id/rowid = id/rowid
.....
stax

Он ругается, если использовать rowid. Ошибка, как в первом моем посте.

потому что у вью (with) нет ровіда
Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  select ename,lpad(SubStr(ename,level,1),level,' ') let from emp
  2  where deptno=10
  3  connect by  prior rowid = rowid
  4       and SubStr(ename,level,1) is not null
  5*      and prior dbms_random.value(1,5) is not null
SQL> /

ENAME      LET
---------- ----------------------------------------
CLARK      C
CLARK       L
CLARK        A
CLARK         R
CLARK          K
KING       K
KING        I
KING         N
KING          G
MILLER     M
MILLER      I
MILLER       L
MILLER        L
MILLER         E
MILLER          R

15 rows selected.



.....
stax
...
Рейтинг: 0 / 0
25.10.2017, 15:00
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541817
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
andrey_anonymousДругой вариант - сгенерировать нужное число строк (как максимальную длину строки) и соединить с исходной таблицей.
Еще лучше - опереться на любую имеющуюся таблицу, содержащую более 4000 строк.Ещё лучше лучшего - коррелированный table(), чтобы не генерить лишних строк декартом, чтобы потом с ними не бороться.
...
Рейтинг: 0 / 0
25.10.2017, 15:02
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541825
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
ElicЕще лучше - опереться на любую имеющуюся таблицу, содержащую более 4000 строк.Ещё лучше лучшего - коррелированный table(), чтобы не генерить лишних строк декартом, чтобы потом с ними не бороться.[/quot]
Ну дальше пойдут lateral, а там и модели подтянутся :)
От условий зависит.
Вот вчера разговаривал с коллегой, у которого есть задача сделать некое решение, пригодное к переносу на PGSql.
Так рекурсивный with в его условиях - самое оно.
А кто-то, возможно, имеет ввиду какой-нибудь убогий опенсорс, не умеющий деревяшку al all - ему cartesian join с таблицей о 4000 строках показан.
Ну а ежели товарищу именно под oracle - то я бы сделал pipelined и не парил никому мозг.
...
Рейтинг: 0 / 0
25.10.2017, 15:08
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541827
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
andrey_anonymousпригодное к переносу на PGSql.... а в киеве дядько.
...
Рейтинг: 0 / 0
25.10.2017, 15:09
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541830
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
To Stax ,

Я изначально не совсем правильно понял назначение:
Код: plsql
1.
and prior dbms_random.value is not null



поэтому так написал. Сейчас понимаю, что здесь работает все иначе.
...
Рейтинг: 0 / 0
25.10.2017, 15:11
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39541835
SQL_boy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_boy
Гость
Elic, andrey_anonymous

Что за коррелированный table() и lateral?
Модели это которое MODEL DIMENSION BY и т.д.? )))
...
Рейтинг: 0 / 0
25.10.2017, 15:21
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39542047
Zahar H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zahar H.
Гость
SQL_boy,

WITH a as (
SELECT COLUMN_VALUE AS Word
FROM TABLE(sys.odcivarchar2list('ABC', 'RU'))
)
SELECT a.word, SUBSTR(word, b.rn, 1) letter
FROM a, LATERAL(SELECT LEVEL rn FROM dual CONNECT BY LEVEL<=LENGTH(a.word)) b
...
Рейтинг: 0 / 0
25.10.2017, 20:58
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39542049
Zahar H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zahar H.
Гость
One more:

WITH y(rn, word, letter, i) AS (
SELECT ROWNUM rn, COLUMN_VALUE word, SUBSTR(COLUMN_VALUE, 1, 1), 1
FROM TABLE(sys.odcivarchar2list('ABC', 'RU'))
UNION ALL
SELECT rn, word, SUBSTR(word, y.i+1, 1), y.i+1
FROM y
WHERE rn=y.rn
AND SUBSTR(word, y.i+1, 1) IS NOT NULL
)
SELECT word, letter
FROM y
ORDER BY rn, i
...
Рейтинг: 0 / 0
25.10.2017, 21:13
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39542050
Zahar H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zahar H.
Гость
And yet one more:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT a.COLUMN_VALUE word, SUBSTR(a.COLUMN_VALUE, b.COLUMN_VALUE, 1) letter
FROM TABLE(sys.odcivarchar2list('ABC', 'RU')) a, 
     TABLE(CAST(MULTISET(SELECT LEVEL 
                         FROM dual 
                         CONNECT BY LEVEL<=LENGTH(a.COLUMN_VALUE)
                         ) AS sys.odcinumberlist
               )
           ) b
...
Рейтинг: 0 / 0
25.10.2017, 21:19
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39542062
Zahar H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zahar H.
Гость
SQL_boy, the MODEL Clause:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
WITH x AS (
SELECT ROWNUM rn, column_value name
FROM TABLE(sys.odcivarchar2list('ABC', 'RU'))
)
SELECT name, letter
FROM x
MODEL RETURN UPDATED ROWS 
  PARTITION BY (rn)
  DIMENSION BY (0 n)
  MEASURES (' ' LETTER, name)
  RULES ITERATE(1000) UNTIL ITERATION_NUMBER=LENGTH(name[0])-1
  (
     NAME[ITERATION_NUMBER+1]=name[0],
     LETTER[ITERATION_NUMBER+1]=SUBSTR(name[0], ITERATION_NUMBER+1, 1)
  )
...
Рейтинг: 0 / 0
25.10.2017, 22:08
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39542079
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Zahar H.SQL_boy, the MODEL Clause:


Ну и зачем итерации:

Код: 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.
with a as 
     (
       select 1 as id, 'ABC' as Word from Dual
       union  
       select 2 as id, 'RU' as Word from Dual
     )
select  id,
        letter
  from  a
  model
    return updated rows
    partition by(id)
    dimension by(0 d)
    measures(
             word,
             ' ' letter
            )
    rules(
          letter[for d from 1 to length(word[0]) increment 1] = substr(word[0],cv(d),1)
         )
/

        ID LET
---------- ---
         1 A
         1 B
         1 C
         2 R
         2 U

SQL>



SY.
...
Рейтинг: 0 / 0
25.10.2017, 23:19
| Ответить | Цитировать | Написать  
Задачка, альтернативное решение
    #39542556
Zahar H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zahar H.
Гость
SY,

согласен, так лучше
...
Рейтинг: 0 / 0
26.10.2017, 15:14
| Ответить | Цитировать | Написать  
25 сообщений из 26, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка, альтернативное решение
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39541752&tid=1885036]:
 0ms
get settings:
 10ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 189ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 85ms
get tp. blocked users:
 1ms
others: 240ms
total:  564ms
созд. / загр.: 564ms
x
x
Закрыть


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