[SQL] Opbouwen van tabellen voor grote selecties

[Uhmmie]

Uitleg van het probleem.

Er zijn 3 machines, namelijk 1, 2 en 3.

Iedere machine heeft de mogelijkheid om geld uit te keren. Echter zal hij de muntjes met de minste waarde als eerste uitkeren (dus eerst die van 1ct tot deze op zijn, vervolgens de muntjes van 5ct tot deze op zijn en tenslotte die van 10ct).

Je vraagt aan een machine niet een bedrag maar het aantal muntjes. (Dus geeft mij 10 muntjes, waarna de machine dus zoals hier boven staat beschreven jou zijn 10 laagste muntjes zal uitkeren).

tijdens een reset zal een machine terug keren naar een bepaalde begin waarde.
Deze waarde vind je terug in de onderstaande tabel.

code:

Tabel: restant_overzicht
<machine_id> | <aantal> | <Muntwaarde>
--------------------------------------
           1 |       10 |         1ct
           1 |       10 |         5ct
           1 |        5 |        10ct
           2 |       25 |         5ct
           3 |       10 |         1ct
           3 |       15 |        10ct

Dit houdt dus in dat:
* Machine 1 na een reset 10 muntjes van 1ct, 10 muntjes van 5ct en 5 muntjes van 10ct heeft.
* Machine 2 na een reset 25 muntjes van 5 ct heeft.
* Machine 3 na een reset 10 muntjes van 1ct en 15 muntjes van 10ct heeft.

Naast deze tabel die de begin situatie bijhoudt is er ook nog een tabel die bijhoudt hoeveel muntjes er zijn uitgegeven sinds de laatste reset.

code:

Tabel: uitgegeven_overzicht
<machine_id> | <reeds_uitgeggeven>
----------------------------------
           1 |                13
           2 |                20
           3 |                 7

In het bovenstaande voorbeeld zie je dus dat:
* machine 1 reeds 13 muntjes heeft uitgegeven (dit zou dus 10x1ct en 3x5ct zijn geweest).
* machine 2 reeds 20 muntjes heeft uitgegeven (20x5ct volgens de bovenstaande tabellen).
* machine 3 reeds 7 muntjes (7x1ct volgens de bovenstaande tabellen).


De vraag
Met het bovenstaande ingedachte wil ik nu een query opstellen die de volgende vraag stelt (als het ware).
Geef mij van elke machine X muntjes. Ik wil vervolgens dus een overzicht terug die mij vertelt welke muntjes ik dan van iedere machine zou ontvangen..

Voorbeeld (op basis van bovenstaande tabellen).

Vraag: Geeft mij 2 muntjes.

Antwoord:

code:

<machine_id> | <aantal> | <Muntwaarde>
--------------------------------------
           1 |        2 |         5ct
           2 |        2 |         5ct
           3 |        2 |         1ct

niks bijzonders.. Iedere machine geeft mij gewoon 2 muntjes van de laagste waarde die hij nog bezit.


Stel dat ik nu ipv 2 muntjes om 5 muntjes had gevraagd.

Antwoord:

code:

<machine_id> | <aantal> | <Muntwaarde>
--------------------------------------
           1 |        5 |         5ct
           2 |        5 |         5ct
           3 |        3 |         1ct
           3 |        2 |        10ct

Zoals je zie geven machine 1 en 2 mij gewoon 5 muntjes van de laagste waarde. Machine 3 geeft mij eerst 3x1ct waarna hij ook nog 2x10ct geeft omdat hij niet meer genoeg muntjes van 1ct had.


Stel dat ik nu ipv 2 muntjes om 10 muntjes had gevraagd.

Antwoord:

code:

<machine_id> | <aantal> | <Muntwaarde>
--------------------------------------
           1 |        7 |         5ct
           1 |        3 |        10ct
           3 |        3 |         1ct
           3 |        7 |        10ct

In dit geval zou machine 1 mij eerst zijn laatste 7 muntjes van 5ct geven en vervolgens nog 3x10ct.
Machine 2 keert niks uit aangezien hij/zij niet meer voldoende muntjes heeft.
En machine 3 zal mij opnieuw eerst de 3 laatste muntjes van 1ct geven en vervolgens nog 7 van 10ct.


Het probleem
Ik krijg het dus niet voor elkaar om het bovenstaande probleem aan de hand van een SQL-statement op te lossen.

Natuurlijk kan ik alles selecteren en dan met PHP aan de gang gaan, maar stel dan het nu in eens niet 3 maar een miljoen apparaten zijn, die ieder duizende verschillende muntjes hebben. Dan wil je echt niet alles eerst naar PHP overhalen.

uit eindelijk zou ik eigenlijk via SQL willen berekenen welke machine mij het hoogste bedrag geeft als ik om X muntjes vraag (en welk bedrag dat dan is).

Iemand enige tips hoe ik dit voor elkaar zou kunnen krijgen? Tips hoe ik mijn tabellen het beste op kan zetten, etc, etc..

Alle tips zijn welkom en als je vragen hebt stel ze me dan gerust.

[ Voor 34% gewijzigd door Uhmmie op 11-09-2008 18:04 ]

[moozzuzz]

Is het mogelijk om in de tabel uitgegeven per machine uitgegeven_overzicht
bij te houden hoeveel van welke muntjes er uit gegeven zijn (ipv het aantal muntjes)? Of wordt deze tabel ook gereset bij de reset?

[Uhmmie]

Ik zou die tabel kunnen veranderen naar

code:

Tabel: uitgegeven_overzicht
<machine_id> | <reeds_uitgeggeven> | <muntwaarde> 
-------------------------------------------------
           1 |                  10 |           1
           1 |                   3 |           5
           2 |                  20 |           5
           3 |                   7 |           1

Zodat je per munt waarde ziet hoeveel er zijn uitgegeven.

Of bijvoorbeeld bij kunnen houden hoeveel er nog over zijn per muntwaarde.

code:

Tabel: restant_overzicht
<machine_id> | <restant>| <Muntwaarde>
--------------------------------------
           1 |        0 |         1ct
           1 |        7 |         5ct
           1 |        5 |        10ct
           2 |        5 |         5ct
           3 |        3 |         1ct
           3 |       15 |        10ct

Dan nog vind ik het alleen zeer moeilijk om er achter te komen hoe ik via een SQL statement in de bovenstaande tabellen kan bepalen welke muntjes ik terug zou krijgen als ik om bijvoorbeeld 10 muntjes zou vragen per machine.

Of nog complexer welke machine mij het hoogste bedrag zou geven als ik om 10 muntjes zou vragen.

Waar het dus eigenlijk op neer komt is dat ik in 1 (of meerdere) tabellen vast leg wat mijn begin situatie is, waarheen ik steeds terug kan keren.

Vervolgens moet ik in een andere tabel bij kunnen houden wat er is veranderd (of wat de huidige situatie is).

Het uit eindelijk doel komt neer op het volgende..

Aan de hand van de eerste tabel creeer ik een begin situatie..

Vervolgens wil ik kunnen vragen welke machine mij het hoogste bedrag geeft als ik om X muntjes vraag. Als het ware wil ik dan deze muntjes uit die machine pakken.

Vervolgens wil ik registreren dat er dus uit die machine X muntjes zijn uitgegeven.

Vervolgens wil ik weer de vraag kunnen stellen welke machine mij vanuit de nieuwe situatie het hoogste bedrag geeft als ik X muntjes wil hebben.

Opnieuw wil ik dan weer registreren dat die muntjes zijn vergeven.

enz.

enz.

Tot ik bepaal dat ik terug wil naar de begin situatie (zeg maar een nieuwe dag), waarna ik weer het hele proces opnieuw wil beginnen.

Hoe het geregistreerd wordt maakt mij niet zoveel uit. Zolang ik maar een begin situatie heb, waarheen ik keer op keer terug kan keren.

Ik registreer hoe de huidige situatie is.

En hoe ik via een zo min mogelijk rekenwerk kan bepalen welke machine mij in de huidige situatie het hoogste bedrag uitkeert als ik om een X aantal muntjes vraag.

[ Voor 12% gewijzigd door Uhmmie op 11-09-2008 18:11 ]

[pedorus]

Waarom gebruik je niet de gepivotte versie van die tabel? Dus restant_muntjes(machine_id,aantal1ct,aantal5ct,aantal10ct)?

[Uhmmie]

pedorus schreef op donderdag 11 september 2008 @ 18:31:
Waarom gebruik je niet de gepivotte versie van die tabel? Dus restant_muntjes(machine_id,aantal1ct,aantal5ct,aantal10ct)?

In mijn voorbeeld gebruik ik alleen 1, 5 en 10ct echter komen de waarden uit mijn "begin situatie" weer uit een andere wiskundige formule, waardoor het in theorie elke willekeurig numerieke waarde zou kunnen bevatten (in praktijk zal deze waarde liggen tussen de 1 en 10000).

Waarvan er per beginsituatie maar slechts een paar 100 verschillende waarden per machine worden gebruikt. Als ik dan 10000 cols zou aanmaken zou ik in dus mijn benodigde opslag ruimte met factor 100x gaan verveelvoudige.

Aangezien ik uit eindelijk ook een historie bij wil houden, waarop ik makkelijk kan zoeken lijkt me dat dus niet de meest practische oplossing en wil ik dus overbodig data zoveel mogelijk beperken.

Ik hoop dat ik het zo duidelijk heb uitgelegd.

[pedorus]

In dat geval is mijn zeer sterke vermoeden dat dit niet met een standaard SQL-statement is op te lossen. Met een UDF of stored procedure met een cursor kan het waarschijnlijk wel. Ik zou dan direct gaan voor:

Uhmmie schreef op donderdag 11 september 2008 @ 17:04:
uit eindelijk zou ik eigenlijk via SQL willen berekenen welke machine mij het hoogste bedrag geeft als ik om X muntjes vraag (en welk bedrag dat dan is).

(Gaat het over MySQL?)

[ Voor 11% gewijzigd door pedorus op 11-09-2008 21:06 ]

[pistole]

Uhmmie schreef op donderdag 11 september 2008 @ 17:04:
uit eindelijk zou ik eigenlijk via SQL willen berekenen welke machine mij het hoogste bedrag geeft als ik om X muntjes vraag (en welk bedrag dat dan is).

Hoogste bedrag=eerste record van query "select totaal uitgekeerd geld per machine"

Die query (of view of whatever) is betrekkelijk eenvoudig gede-normaliseerd op te slaan als soort van 'status-tabel' die wijzigt op moment dat een machine een transactie doet...toch?

edit:
SQL is geen rekenmachine.

[ Voor 3% gewijzigd door pistole op 11-09-2008 21:34 ]

[Uhmmie]

Na de hele avond te klote heb ik eindelijk een oplossing.

Ik registreer per machine elke munt die er inzit, waarbij ik elke munt nummer op volgorde.

machine_id - volgorde - muntwaarde
1 - 1 - 1
1 - 2 - 1
1 - 3 - 1
1 - 4 - 1
1 - 5 - 1
1 - 6 - 5
1 - 7 - 5
1 - 8 - 5
1 - 9 - 5
1 - 10 - 5
etc .. etc..
2 - 1 - 1..
etc.. etc..

Vervolgens registreer ik hoeveel munten de machine heeft uitgegeven in een andere tabel.

Vervolgens selecteer ik alle munten vanaf het punt waar het (volgorde nummer+1) gelijk is aan de waarde van de reeds uitgegeven munten tot het punt waar volgorde nummer+aantal munten dat ik wil hebben). Vervolgens krijg ik precies een overzicht per machine welke munten hij zou uitkeren.

De query is nog niet helemaal perfect, maar ik denk dat ik er op deze manier wel ga komen.

[pistole]

Als je nu eens zoiets zou doen:

machine: info over een machine
coin: info over een munt
stock: hoeveel van elke munt heeft een machine in voorraad
transaction: wat heeft elke machine ooit uitgekeerd (historie?)
uitkering: wat is het totaal bedrag dat een machine uitkeert bij een bepaalde hoeveelheid muntjes

Je zou bij elke transactie (ook resetten van machine) de tabel 'uitkering' (kon niks beters verzinnen) updaten voor die machine, en dan op basis van die tabel bepalen welke machine je moet kiezen. Nadeel is dat je voor elke machine meerdere records krijgt omdat het uitgekeerd bedrag anders is als je een andere hoeveelheid muntjes kiest....

Maar volgens mij werkt deze methode in ieder geval "snel", denk ik.

[pedorus]

Uhmmie schreef op vrijdag 12 september 2008 @ 01:00:
De query is nog niet helemaal perfect, maar ik denk dat ik er op deze manier wel ga komen.

Dat zal wel lukken dan, want die query maken is 1 min werk, zowel voor het nieuwe totaalbedrag als de uitgegeven muntjes. Ik had die optie al uitgesloten in de verwachting dat ook het aantal muntjes per soort niet binnen de perken zou blijven...
(Tip: zet uitgegeven_overzicht als eerste tabel in de query, dat is makkelijker voor de optimizer.)

Als het aantal op te vragen muntjes binnen de perken blijft zal bovenstaande oplossing ook prima werken. Mijn vermoeden is dat dat door de updates net iets trager is, maar meten=weten. Het is nogal afhankelijk van de precieze omstandigheden (zoals max. op te vragen aantal muntjes).

[Confusion]

Uhmmie schreef op vrijdag 12 september 2008 @ 01:00:
Ik registreer per machine elke munt die er inzit, waarbij ik elke munt nummer op volgorde.

Voor kleine hoeveelheden muntjes werkt dat natuurlijk, maar als dit moet schalen naar grote hoeveelheden, dan loopt de afmeting van de tabellen uit de klauwen. Dan is een stored procedure die het resultaat, met behulp van meerdere losse queries, uitrekent geschikter.

[winkbrace]

Uiteraard is alles prima mogelijk in SQL

Ik heb wel een id toegevoegd aan je tabel om te kunnen joinen met de regel erboven.
Verder heb ik een tabel "gevraagd" gemaakt met de kolomnaam "gevraagd" en de waarde van het gevraagde aantal muntjes.

Deze oplossing werkt tot 2 muntjes per machine. Voor meer muntjes moet je een self join toevoegen en de case statement uitbreiden. Ik zou deze oplossing echter afraden als je verwacht meer dan 4 of 5 verschillende muntjes uit een machine te trekken.

Heb je er wat aan dat ik deze oplossing geef of heb je liever een beschrijving van hoe je het zou kunnen aanpakken?

code:

/**
 * Create en vul de tabellen
 *
 
    create table restant_overzicht (id int, machine int, aantal int, munt int);

    insert into restant_overzicht values (1, 1, 10, 1);

    insert into restant_overzicht values (2, 1, 10, 5);

    insert into restant_overzicht values (3, 1, 5, 10);

    insert into restant_overzicht values (4, 2, 25, 5);

    insert into restant_overzicht values (5, 3, 10, 1);

    insert into restant_overzicht values (6, 3, 15, 10);
    
    
    create table gevraagd (gevraagd int);
    
    insert into gevraagd values (15);

 */

select * from restant_overzicht;

select machine
,      gevraagd
,      opgebracht
from   
(
    select r.id
    ,      r.machine
    ,      r.munt
    ,      r.aantal
    ,      g.gevraagd
    ,      case 
             when g.gevraagd > r.aantal 
               then (r.aantal * r.munt) + ((g.gevraagd - r.aantal) * r2.munt)
             else g.gevraagd * r.munt
           end opgebracht
    ,      min(r.id) over (partition by r.machine) laagste_munt_id
    from   restant_overzicht r
    left join restant_overzicht r2 on r.id = r2.id - 1  -- haal de vorige rij erbij
    cross join gevraagd g  -- plaats het gevraagde aantal naast elke rij
    order by r.id
)
where id = laagste_munt_id;

[dvvelzen]

Niet bepaald een gelukkige tabel keuze, zoals al eerder vermeld door andere posters. Maar vond het wel grapping om ff op "jouw" manier in elkaar te draaien.

(case statement ftw..... )

code:

DROP TABLE restant_overzicht
go

DROP TABLE uitgegeven_overzicht
go

DROP PROCEDURE get_next_coins
go

DROP PROCEDURE reset_coins
go

CREATE TABLE restant_overzicht
( machine_id   tinyint  not null
, aantal       int      not null
, muntwaarde   tinyint  not null 
-- int ipv char (ct) voor een goede min/max bepaling
)
go

INSERT restant_overzicht VALUES ( 1, 10,  1)
INSERT restant_overzicht VALUES ( 1, 10,  5)
INSERT restant_overzicht VALUES ( 1,  5, 10)
INSERT restant_overzicht VALUES ( 2, 25,  5)
INSERT restant_overzicht VALUES ( 3, 10,  1)
INSERT restant_overzicht VALUES ( 3, 15, 10)
go

CREATE TABLE uitgegeven_overzicht
( machine_id        tinyint  not null
, reeds_uitgegeven  int      not null
)
go

INSERT uitgegeven_overzicht VALUES ( 1, 3 )
INSERT uitgegeven_overzicht VALUES ( 2, 0 )
INSERT uitgegeven_overzicht VALUES ( 3, 0 )
go

CREATE PROCEDURE reset_coins
AS
   UPDATE uitgegeven_overzicht
   SET    reeds_uitgegeven = 0
go

CREATE PROCEDURE get_next_coins ( @i int )
AS
   SELECT    o1.machine_id
   ,         o1.muntwaarde
   ,         CASE WHEN ( SELECT SUM(o2.aantal) FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde <= o1.muntwaarde )
                    <= ( SELECT o2.reeds_uitgegeven + @i FROM uitgegeven_overzicht o2 WHERE o2.machine_id = o1.machine_id )
                  THEN ( SELECT o2.aantal FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde = o1.muntwaarde )
                  ELSE CASE WHEN ( SELECT o2.reeds_uitgegeven + @i FROM uitgegeven_overzicht o2 WHERE o2.machine_id = o1.machine_id )
                               - ( SELECT ISNULL(SUM(o2.aantal),0) FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde < o1.muntwaarde )
                              <= 0
                            THEN 0
                            ELSE ( SELECT o2.reeds_uitgegeven + @i FROM uitgegeven_overzicht o2 WHERE o2.machine_id = o1.machine_id )
                               - ( SELECT ISNULL(SUM(o2.aantal),0) FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde < o1.muntwaarde )
                       END
             END
             -
             CASE WHEN ( SELECT SUM(o2.aantal) FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde <= o1.muntwaarde )
                    <= ( SELECT o2.reeds_uitgegeven FROM uitgegeven_overzicht o2 WHERE o2.machine_id = o1.machine_id )
                  THEN ( SELECT o2.aantal FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde = o1.muntwaarde )
                  ELSE CASE WHEN ( SELECT o2.reeds_uitgegeven FROM uitgegeven_overzicht o2 WHERE o2.machine_id = o1.machine_id )
                               - ( SELECT ISNULL(SUM(o2.aantal),0) FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde < o1.muntwaarde )
                              <= 0
                            THEN 0
                            ELSE ( SELECT o2.reeds_uitgegeven FROM uitgegeven_overzicht o2 WHERE o2.machine_id = o1.machine_id )
                               - ( SELECT ISNULL(SUM(o2.aantal),0) FROM restant_overzicht o2 WHERE o2.machine_id = o1.machine_id AND o2.muntwaarde < o1.muntwaarde )
                       END
             END as 'uit_te_geven'
   FROM      restant_overzicht o1
   GROUP BY  o1.machine_id
   order by  o1.machine_id, o1.muntwaarde
   
   UPDATE uitgegeven_overzicht
   SET    reeds_uitgegeven = reeds_uitgegeven + @i
go



1>
2> get_next_coins 6
3> go
 machine_id muntwaarde uit_te_geven
 ---------- ---------- ------------
          1          1            6
          1          5            0
          1         10            0
          2          5            6
          3          1            6
          3         10            0

(6 rows affected)
(return status = 0)
1> get_next_coins 4
2> go
 machine_id muntwaarde uit_te_geven
 ---------- ---------- ------------
          1          1            1
          1          5            3
          1         10            0
          2          5            4
          3          1            4
          3         10            0

(6 rows affected)
(return status = 0)

gr,
Dennis

(PS: Geschreven in ASE in een versie die nog geen der. tab. aan kan.)