[SQL] Alle mogelijke unieke combinaties (numeriek)

[Xanthium]

Beste mede-tweakers, na wat zoekwerk kom ik zelf niet op een leuke oplossing, misschien heeft een van jullie een betere aanpak.Ik heb een numerieke kolom (geldwaardes) en ik wil de som van alle unieke combinaties naar een tijdelijke tabel wegschrijven zodat ik deze later kan joinen tegen een andere tabel. Ik kom er niet uit hoe ik de som van alle unieke combinaties van bedragen kan terugkrijgen, iemand een suggestie?

[Dido]

Wat bedoel je precies met de som van alle unieke combinaties?

I k kan combinaties niet zo makkelijk optellen, namelijk.

Geef eens een voorbeeldje? Het klink alsof je het beruchte cartesische prodcut namelijk nuttg zou kunnen gebruiken

[Aloys]

SELECT DISTINCT geldbrag FROM geldbedragen daar neem je een SUM over?

[JJvG]

Zoiets?

INSERT INTO tijdelijketabel (tijdelijke kolom)
VALUES (SELECT SUM (SELECT DISTINCT kolomnaam FROM tabelnaam))

[ Voor 8% gewijzigd door JJvG op 22-06-2012 16:54 ]

[Sabelhout]

Tabel met zichzelf joinen:

select distinct sum(tabel1.geldwaarde+tabel2.geldwaarde)
from tabel tabel1,
tabel tabel2
where tabel1.geldwaarde <> tabel2.geldwaarde

Dit gaat wel een leuk Carthesisch product opleveren. Ik ben er even vanuit gegaan dat je unieke records hebt, anders zul je misschien met rowid's iets moeten knutselen...

Nevermind, je wil alle mogelijke combinaties hebben

[ Voor 20% gewijzigd door Sabelhout op 22-06-2012 16:59 ]

[lier]

Voordat allerhande oplossingen aangedragen worden is het misschien (inderdaad) beter om de TS eerst functioneel beschrijft wat hij wil bereiken en waarom hij daar deze ingeslagen weg voor gekozen heeft.

@TS: heeft het aantal unieke combinaties iets te maken met faculteit?

[Dido]

Dat kan natuurlijk ook nog, dat met "combinatie" wordt bedoeld "waarde" of misschien wel "record"...
Dan wordt optellen al een stuk makkelijker.

[Xanthium]

Dido schreef op vrijdag 22 juni 2012 @ 16:52:
Wat bedoel je precies met de som van alle unieke combinaties?

I k kan combinaties niet zo makkelijk optellen, namelijk.

Geef eens een voorbeeldje? Het klink alsof je het beruchte cartesische prodcut namelijk nuttg zou kunnen gebruiken

Klopt, termen als carthesisch product en knapzak probleem zijn al meerdere keren langsgekomen . Stel ik heb de volgende bedragen in een kolom staan:

20
30
15

Dan zijn de volgende unieke combinaties mogelijk (0 niet meegenomen):

20+30 = 50
20+15 = 35
20+30+15 = 65
30+15 = 55

Volgens mij heb ik nu alle mogelijke gevallen in dit voorbeeld valt dit in SQL te automatiseren?

[ThomasG]

Xanthium schreef op vrijdag 22 juni 2012 @ 16:58:
[...]


Klopt, termen als carthesisch product en knapzak probleem zijn al meerdere keren langsgekomen . Stel ik heb de volgende bedragen in een kolom staan:

20
30
15

Dan zijn de volgende unieke combinaties mogelijk (0 niet meegenomen):

20+30 = 50
20+15 = 35
20+30+15 = 65
30+15 = 55

Volgens mij heb ik nu alle mogelijke gevallen in dit voorbeeld valt dit in SQL te automatiseren?

Dan zou je gebruik moeten maken van recursive queries (Common Table Expressions). Goede documentatie en voorbeelden kun je vinden op de PostgreSQL site, het is gewoon SQL Standaard dus werkt in elke zelf respecteerde SQL database: http://www.postgresql.org/docs/8.4/static/queries-with.html Let wel dat dit een enorme impact kan hebben op de performance, dus wellicht beter te doen is met bijv. Java of een PHP script.

[Xanthium]

ThomasG schreef op vrijdag 22 juni 2012 @ 17:01:
[...]
Dan zou je gebruik moeten maken van recursive queries (Common Table Expressions). Goede documentatie en voorbeelden kun je vinden op de PostgreSQL site, het is gewoon SQL Standaard dus werkt in elke zelf respecteerde SQL database: http://www.postgresql.org/docs/8.4/static/queries-with.html Let wel dat dit een enorme impact kan hebben op de performance, dus wellicht beter te doen is met bijv. Java of een PHP script.

Bedankt ik ga het eens doornemen. Ik heb helaas niet de mogelijkheid om een hogere programmeertaal te gebruiken, het zal voor nu in SQL moeten gebeuren. Eens kijken hoe ver ik kom

[Xanthium]

Ben inmiddels tot het volgende gekomen; een CROSS join die het carthesisch product teruggeeft, maar alleen voor 2 waarden:

code:

with cteAllColumns as (
SELECT [bedrag] AS col
  FROM [DB].[dbo].[Table]
  WHERE idnumber = '1180045176'
)
select c1.col, c2.col, (c1.col + c2.col) AS SUM
    from cteAllColumns c1 
        cross join cteAllColumns c2 
    where c1.col < c2.col
    order by c1.col, c2.col

Resulteert in:


Het is een stapje dichter bij wat ik wil, maar ik moet dus alle mogelijke combinaties berekenen, dus dan zal het ook om veel meer kolommen gaan. Iemand een idee hoe dit is te realiseren?

[NMe]

Met SQL? Niet of in elk geval niet eenvoudig? SQL is hier helemaal niet voor bedoeld, waarom gebruik je er geen scripting- of programmeertaal voor?

[kutagh]

Wat je in feite wilt doen in stappen is dit:
(letters staan voor een getal, combinatie van letters staat voor som van de getallen waar die letters voor staan)

[a] -> [a,b,ab] -> [a,b,c,ab,ac,bc,abc] -> [a,b,c,d,ab,ac,bc,ad,bd,cd,abc,abd,acd,bcd,abcd]

Enzovoorts.
Kijk maar of je hiermee recursief iets kunt opbouwen.

[ Voor 14% gewijzigd door kutagh op 23-06-2012 14:40 ]

[Xanthium]

Dank voor de reacties, ben inmiddels zelf een aardig eind gekomen:

code:

-- Variabelen declareren
DECLARE @aantalDR int
DECLARE @aantalCR int
DECLARE @joinstring varchar(max)
DECLARE @sumstring varchar(max)
DECLARE @columnstring varchar(max)
DECLARE @counter int
DECLARE @sqlcmd varchar(max)
DECLARE @post varchar(20)

SET @post = '0100231706'

-- Bepaal het aantal waarden (records), zet waarde vast in variabele
SET @aantalDR = (SELECT COUNT([bedrag]) FROM [DB].[dbo].[Tabel] WHERE post = @post AND label = 'DR');

-- Maak string voor het aantal te joinen tabellen

SET @counter = 3;
SET @joinstring = 'cross join cteAllColumns c2' + CHAR(13)
WHILE @counter <= @aantalDR
BEGIN
SET @joinstring = @joinstring + ' cross join cteAllColumns c' + CONVERT(varchar,@counter) + CHAR(13)
SET @counter = @counter + 1
END

-- Maak string voor het aantal op te tellen kolommen
SET @counter = 2;
SET @sumstring = 'c1.Waarde'
WHILE @counter <= @aantalDR
BEGIN
SET @sumstring = @sumstring + ' + c' + CONVERT(varchar,@counter) + '.Waarde'
SET @counter = @counter + 1
END


-- Maak een zwevende tabel aan en voer query uit
SET @sqlcmd = '

WITH cteAllColumns as (
SELECT [bedrag] as Waarde
  FROM [DB].[dbo].[Tabel]
  WHERE post = ' + @post + ' AND label = ''DR''
)

-- Bereken carthetisch product over alle waarden
SELECT *,
(
' + @sumstring + '
 ) AS Som
 
    from cteAllColumns c1 
    ' + @joinstring + '
    ORDER BY Som'      
        
EXEC(@sqlcmd)

Toelichting: heb hier dankbaar gebruik gemaakt van Dynamic T-SQL. Het script leest het aantal waarden uit de originele tabel en berekent vervolgens alle mogelijke combinaties die er zijn tussen deze getallen. Nadeel is wel dat bij meer dan 7 waarden mijn pc al langer dan een minuut per run bezig is..

Kan wellicht nog een stuk efficienter dit

[ Voor 10% gewijzigd door Xanthium op 23-06-2012 14:50 ]

[Voutloos]

Insert de resultaten van het cartesisch product in dezelfde tabel en stop wanneer er geen rows meer bijkomen.

Done.

[kutagh]

Vind je het gek dat het lang gaat duren, als je ziet hoe snel het aantal waarden toeneemt?
Even nagerekend met een simpele C# programma en je hebt (2^n)-1 waarden waar n het aantal originele rijen zijn. Dan is SQL ook niet eens bedoeld voor berekeningen...

[Z-Dragon]

Ik word toch wel heel benieuwd waar dit goed voor is.

[NMe]

Ik niet, maar ik ben wel benieuwd waarom de topicstarter eigenlijk steeds weer de vraag negeert waarom dit zo nodig in SQL moet.

[ID-College]

NMe schreef op maandag 25 juni 2012 @ 12:21:
Ik niet, maar ik ben wel benieuwd waarom de topicstarter eigenlijk steeds weer de vraag negeert waarom dit zo nodig in SQL moet.

Ik heb helaas niet de mogelijkheid om een hogere programmeertaal te gebruiken, het zal voor nu in SQL moeten gebeuren

Lack of knowledge denk ik..?

[Xanthium]

Correct

[pedorus]

Xanthium schreef op zaterdag 23 juni 2012 @ 14:48:
Nadeel is wel dat bij meer dan 7 waarden mijn pc al langer dan een minuut per run bezig is..

Kan wellicht nog een stuk efficienter dit

Dat komt omdat je de post van ThomasG niet gesnapt hebt denk ik. Als ik het even uitprobeer is het voor 8 waardes zodanig snel dat je geen tijd kan meten...

SQL:

WITH t(summation, maximum) AS (
    SELECT amount, amount from amounts
  UNION ALL
    SELECT summation + amount, amount from t inner join amounts on amount < maximum
)
SELECT * FROM t;

En dan moet je nog even bedenken of je bijv. select distinct summation from t wil of iets anders.

[NMe]

Xanthium schreef op maandag 25 juni 2012 @ 17:09:
Correct

Dat klinkt dan als een goede reden om die kennis eens op te gaan doen in plaats van terug te grijpen op een techniek die hier eigenlijk niet handig voor is?