[SQL] rows naar columns & volgorde in alle vormen

[Verwijderd]

Hoi,
een vraag het volgende is me nu even aan het puzzelen...

Stel ik heb een tabel met 1 column en de tabel heeft 5 records. De records bevatten de waarden A, B, C, D & E.

Ik wil (in PL/SQL) een oplossing bouwen die mij alle mogelijke combinaties terug geeft.

De Meest ideale situatie zou mij 5 columns terug geven * het aantal records met elk een andere combinatie. Het resultaat zou dus moeten zijn:

A B C D E
B C D E A
C D E A B
E A C B D
etc etc

De vraag is dus in twee delen:

(1) Hoe krijg ik de resultaten van records naar columns. Misschien is dit voor een vast aantal resultaten nog redelijk "hard" te programmeren, stel we hebben er 5. Wat zou de beste aanpak zijn als het aantal terugkomende waarden onbekend is enorm groot kan worden? Meest ideaal is uiteraard een oplossing waarin we geen rekening houden met een vast aantal terugkomende resultaten maar als het niet anders kan dan zal ik daar mee moeten leven ;-)

(2) Hoe krijg ik deze set van records zo ver dat ze elke mogelijke combinatie gaan vormen?

Ik werk op een Oracle database en wil eigenlijk het complete probleem in SQL oplossen zonder een andere externe taal er bij te hoeven gebruiken.

Iemand een goede tip hoe dit probleem aan te pakken?


Johan.

[Niemand_Anders]

Reactie op punt 2. Kijk en daar zit nou eigenlijk je probleem. DATAbases zijn hiervoor helemaal niet bedoeld. Databases kunnen wel simpele bewerkingen uitvoeren, maar hiervoor heb je een algoritme nodig, en dat hoort niet in een database thuis.

Maar waarom maak je er geen Java Stored Procedure van. Enkele voorbeeld van hoe dat werkt kun je vinden

De code draait dan in Oracle en kan daarna door elke andere taal op dezelfde manier worden benaderd zoals een PL/SQL stored procedure.

[P.O. Box]

voor punt 1: niet elke database engine support het, maar zoek bijvoorbeeld eens op pivot-tables

[Verwijderd]

Niemand_Anders schreef op woensdag 23 april 2008 @ 09:56:
Reactie op punt 2. Kijk en daar zit nou eigenlijk je probleem. DATAbases zijn hiervoor helemaal niet bedoeld. Databases kunnen wel simpele bewerkingen uitvoeren, maar hiervoor heb je een algoritme nodig, en dat hoort niet in een database thuis.

Maar waarom maak je er geen Java Stored Procedure van. Enkele voorbeeld van hoe dat werkt kun je vinden

De code draait dan in Oracle en kan daarna door elke andere taal op dezelfde manier worden benaderd zoals een PL/SQL stored procedure.

Correct,
echter.... met PL/SQL zou je dat kunnen oplossen in de database... ipv java wil ik een PL/SQL stored procedure gebruiken... en ben dus op zoek naar een snelle en goede manier om dit in PL/SQL te doen. ;-)

[Verwijderd]

Niemand_Anders schreef op woensdag 23 april 2008 @ 09:56:
Reactie op punt 2. Kijk en daar zit nou eigenlijk je probleem. DATAbases zijn hiervoor helemaal niet bedoeld. Databases kunnen wel simpele bewerkingen uitvoeren, maar hiervoor heb je een algoritme nodig, en dat hoort niet in een database thuis.

Ach het is maar hoe je er tegenaan kijkt: Je wilt je data op een bepaalde geordende ontsluiten en dat is een kerntaak van een database. De scheidingslijn is niet zo zwart-wit.

En uiteraard kun je dit prima met PL/SQL doen. Dus wat betreft vraag 1:
"Hoe krijg ik de resultaten van records naar columns"

Er zijn meerdere wegen naar Rome. Je kunt bijvoorbeeld gebruik maken van hierarchical queries en dan middels sys_connect_by_path alles 1 kolom gooien. Maar aangezien je met pl/sql werkt kun je ook prima buffers en temptables opbouwen.

[JaQ]

Verwijderd schreef op woensdag 23 april 2008 @ 09:33:
De vraag is dus in twee delen:

(1) Hoe krijg ik de resultaten van records naar columns. Misschien is dit voor een vast aantal resultaten nog redelijk "hard" te programmeren, stel we hebben er 5. Wat zou de beste aanpak zijn als het aantal terugkomende waarden onbekend is enorm groot kan worden? Meest ideaal is uiteraard een oplossing waarin we geen rekening houden met een vast aantal terugkomende resultaten maar als het niet anders kan dan zal ik daar mee moeten leven ;-)

Onderdeel van het pivot principe is juist dat je het aantal kolommnen in je output kent. ...

edit: mmm.. blijkbaar zijn er mensen die er anders over denken...

Verwijderd schreef op woensdag 23 april 2008 @ 09:33:

(2) Hoe krijg ik deze set van records zo ver dat ze elke mogelijke combinatie gaan vormen?

Zoek maar eens op asktom.oracle.com naar pivot query.

[ Voor 12% gewijzigd door JaQ op 23-04-2008 18:54 ]

[MrBucket]

Je zoekt denk ik naar een algoritme voor permutaties?

Zie o.a. hier: http://web.usna.navy.mil/~wdj/book/node156.html
Alhoewel er geen (pseudo)code wordt gegeven, lijkt het me niet moeilijk om dit als een recursieve functie te implementeren.

[Verwijderd]

2 tijdelijke tabellen aanmaken en die cross-joinen?

Waarom zou je dit uberhaupt met een DB willen doen?

Waarom sla je strings op?

[Confusion]

Verwijderd schreef op woensdag 23 april 2008 @ 09:33:
[..]

Blijft dit probleem beperkt tot 5 rijen? Met 5 is het nog te doen, met 10 wordt het al een belachelijke hoop data die je terug gaat sturen en met 12 of meer kan je het gewoon wel schudden.

[ Voor 3% gewijzigd door Confusion op 23-04-2008 22:19 ]

[Verwijderd]

Confusion schreef op woensdag 23 april 2008 @ 22:18:
[...]

Blijft dit probleem beperkt tot 5 rijen? Met 5 is het nog te doen, met 10 wordt het al een belachelijke hoop data die je terug gaat sturen en met 12 of meer kan je het gewoon wel schudden.

Sorry sorry,
ik heb dit topic heel even moeten laten liggen omdat ik met wat andere "nood"-zaken bezig was... klopt dit zal heel veel data gaan opleveren want neen het blijft niet beperkt tot 5, ja het kunnen er 10 worden en ja ook mogelijk >12.

Laten we even het geheugen en CPU gedeelte niet als bottleneck nemen want dat zal niet echt een probleem gaan vormen als we rond de 12 of <15 blijven....

Het gaat hier meer om de manier van aanpak hoe we dit in PL/SQL gaan oplossen. Ik heb net al wat dingen gelezen en zal hier even op verder gaan... maar laat de gedachten rustig komen op dit topic.... :-)

Groet,
Johan Louwers

[Verwijderd]

MrBucket schreef op woensdag 23 april 2008 @ 19:05:
Je zoekt denk ik naar een algoritme voor permutaties?

Zie o.a. hier: http://web.usna.navy.mil/~wdj/book/node156.html
Alhoewel er geen (pseudo)code wordt gegeven, lijkt het me niet moeilijk om dit als een recursieve functie te implementeren.

kijk, dat is dus wat ik bedoel... :-) (b) ``weave" in a 3 as follows zou dus een uitkomst moeten zijn zoals ik hem zou willen zien... nu alleen nog even uitdokteren hoe we dat in PL/SQL voor elkaar kunnen krijgen.

Groet,
Johan Louwers.

[Verwijderd]

MrBucket schreef op woensdag 23 april 2008 @ 19:05:
Je zoekt denk ik naar een algoritme voor permutaties?

Zie o.a. hier: http://web.usna.navy.mil/~wdj/book/node156.html
Alhoewel er geen (pseudo)code wordt gegeven, lijkt het me niet moeilijk om dit als een recursieve functie te implementeren.

En een artikel op de oracle website heeft me al een stukje verder geholpen... ik denk dat ik even wat verder gaan testen aan de hand van dit voorbeeldje:

Generate Permutations for Characters or Numbers

This tip comes from Frank Zhou, a Senior Software Developer in Braintree, MA.


This query generates permutations for X number of unique characters or numbers.


SQL> variable NumDiffChars number
SQL> variable LengthOfPermutations number
SQL> exec :NumDiffChars := &numOfChars
Enter value for numofchars: 4

SQL> exec :LengthOfPermutations := &lengthofPermutations
Enter value for lengthofpermutations: 4

PL/SQL procedure successfully completed.

SQL> select permutations
2 from
3 (select replace (sys_connect_by_path( n, ',' ) , ',' ) permutations
4 from
5 (select chr(ascii('A')+level-1) n from dual connect by level <=:NumDiffChars ) YourTable
6 connect by nocycle n != prior n
7 )
8 where length(permutations) = :LengthOfPermutations;

PERMUTATIONS
-----------------------------------------------------
ABCD
ABDC
ACBD
ACDB
ADBC
ADCB
BACD
BADC
BCAD
BCDA
BDAC
BDCA
CABD
CADB
CBAD
CBDA
CDAB
CDBA
DABC
DACB
DBAC
DBCA
DCAB
DCBA

24 rows selected.

SQL> spool off

[pedorus]

Verwijderd schreef op zondag 27 april 2008 @ 20:55:
Laten we even het geheugen en CPU gedeelte niet als bottleneck nemen want dat zal niet echt een probleem gaan vormen als we rond de 12 of <15 blijven....

Ehm 14!=87.178.291.200 records; ik verwacht dan toch wat problemen eigenlijk...

Niet-specifiek SQL voorbeeldje met permutaties:

SQL:

create table teams(id varchar(1));
insert into teams values ("A"),("B"),("C");

select t1.id,t2.id,t3.id from teams t1,teams t2,teams t3 
where t2.id not in (t1.id) and t3.id not in (t1.id,t2.id)
order by t1.id,t2.id,t3.id;

[Confusion]

Verwijderd schreef op zondag 27 april 2008 @ 20:55:
Laten we even het geheugen en CPU gedeelte niet als bottleneck nemen want dat zal niet echt een probleem gaan vormen als we rond de 12 of <15 blijven....

Bij 10 rijen zit je op 3.6 miljoen resultaten. Laten we optimistische zeggen dat je rijen uit 10 velden van ieder 1 byte bestaan en dat er dus 36 MB overgezonden moet worden (of tegen meer data gejoined moet worden). Voor 12 rijen kan je dat nog eens met 132 vermenigvuldigen. Vermenigvuldig dat resultaat nog eens met 182 en je zit aan

pedorus schreef op zondag 27 april 2008 @ 21:42:
Ehm 14!=87.178.291.200 records; ik verwacht dan toch wat problemen eigenlijk...