Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка, альтернативное решение / 25 сообщений из 26, страница 1 из 2
  1  все
25.10.2017, 13:03
    #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:30
    #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, 14:10
    #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:12
    #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:16
    #39541765
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
SQL_boyЭто которая pipelined function ?Именно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 14:19
    #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:26
    #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:33
    #39541789
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
SQL_boyИнтересное решение!!!
Только одно но! Если встречается одинаковое значение атрибута, то оно уже неправильно отработает!

connect by prior id/rowid = id/rowid

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 14:37
    #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:42
    #39541796
SQL_boy
SQL_boy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
Staxconnect by prior id/rowid = id/rowid
.....
stax

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

Решение принимается! :)
Учитывая, что мое решение работает только на 10g, твое универсальное!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 14:53
    #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:56
    #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, 15:00
    #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:02
    #39541817
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
andrey_anonymousДругой вариант - сгенерировать нужное число строк (как максимальную длину строки) и соединить с исходной таблицей.
Еще лучше - опереться на любую имеющуюся таблицу, содержащую более 4000 строк.Ещё лучше лучшего - коррелированный table(), чтобы не генерить лишних строк декартом, чтобы потом с ними не бороться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 15:08
    #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:09
    #39541827
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
andrey_anonymousпригодное к переносу на PGSql.... а в киеве дядько.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 15:11
    #39541830
SQL_boy
SQL_boy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
To Stax ,

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



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

Что за коррелированный table() и lateral?
Модели это которое MODEL DIMENSION BY и т.д.? )))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 20:58
    #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, 21:13
    #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:19
    #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, 22:08
    #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, 23:19
    #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
| Ответить | Цитировать | Написать  
26.10.2017, 15:14
    #39542556
Zahar H.
Zahar H.
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка, альтернативное решение
SY,

согласен, так лучше
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка, альтернативное решение / 25 сообщений из 26, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 533ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1885036]:
 0ms
get settings:
 11ms
get forum list:
 17ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 160ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 82ms
get tp. blocked users:
 2ms
others: 238ms
total:  533ms
x
x
Закрыть


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