[PostgreSQL] Vergelijken van rows en waarden overnemen*

[FreakNL]

Volgens mij kijk ik er volledig overheen, maar het wil me maar niet lukken.

Ik heb de volgende tabel (in Postgresql):

postgres=# select * from car_fix order by dat ASC;
clientnr | receiptnr | description | dat
---------------+----------------+----------------------+------------
1 | 1 | Changed Tires | 2006-04-12
4 | 4 | Changed oil | 2006-05-12
4 | 3 | Regular Checkup | 2006-05-12
2 | 2 | Changed Tires | 2006-05-12
3 | 5 | Fixed Airbag | 2006-05-12
2 | 8 | Flat Tire | 2006-06-13
1 | 7 | Broken Axis | 2006-06-13
5 | 6 | Brake problem | 2006-06-13
(8 rows)

Die verklein ik als volgt:

postgres=# select to_char(dat, 'yyyy') as year, to_char(dat, 'mm') as month,
count(receiptnr) AS fixes from car_fix group by month,year order by month;
year | month | fixes
------+-------+-----------
2006 | 04 | 1
2006 | 05 | 4
2006 | 06 | 3
(3 rows)

postgres=#

Per maand heb ik nu dus het aantal "car_fixes". Wat is wil doen, is het SQL statement op zodanige wijze aanpassen, dat het verschil tussen de huidige en vorige rij getoond wordt.

Zoiets dus:

year|month|fixes|increase
------+-------+------+-----------
2006 | 04 | 1 | 0
2006 | 05 | 4 | 3
2006 | 06 | 3 | -1

(3 rows)

Dit moet toch heel makkelijk mogelijk zijn?? Maar het stomme is dat ik er weinig over kan vinden....

[ Voor 4% gewijzigd door FreakNL op 13-06-2006 16:10 ]

[DEVoTi0N]

Gewoon simpel optellen en aftrekken met php???

[ Voor 9% gewijzigd door DEVoTi0N op 13-06-2006 16:13 ]

[FreakNL]

Mja, maar het moet toch ook mogelijk zijn met een statement binnen postgresql zelf?

[The - DDD]

Met simpele sql gaat dit je volgens mij niet lukken.

[NMe]

MySQL heeft mogelijkheden voor variabelen, en dat zul je in Postgre ook wel hebben. Ik denk echter dat je je beter op een oplossing binnen je applicatie kunt richten, aangezien dit ofwel onmogelijk is, ofwel een complexe query gaat opleveren.

[cameodski]

Dit kun je toch gewoon met een subquery doen door daar altijd het aantal van een maand terug te bepalen. Overigens is het meestal sneller als je een derived table gebruikt.

[P_de_B]

Hier is wel een oplossing voor te bedenken, maar ik denk dat je dit in dit geval niet in SQL moet oplossen. De oplossing die je zoekt is niet 'set gebaseerd' (immers rij 2 moet informatie van rij 1 hebben) en dat is nu net hetgene SQL sterk in is. Zodra non set based acties moet gaan doen kun je het beter aan de client kant oplossen.

[Rigi]

Het lijkt me om heel eerlijk te zijn ook geen standaard actie op een database. Wat gebeurd er bv als er rijen verdwijnen etc?

Dit zal je imho toch echt buiten de database om moeten doen.

[FreakNL]

cameodski schreef op dinsdag 13 juni 2006 @ 16:41:
Dit kun je toch gewoon met een subquery doen door daar altijd het aantal van een maand terug te bepalen. Overigens is het meestal sneller als je een derived table gebruikt.

Hier zat ik ook al aan te denken. Volgens mij moet dat met een regel of twee zo voor elkaar zijn eigenlijk....

Rigi schreef op dinsdag 13 juni 2006 @ 16:42:
Het lijkt me om heel eerlijk te zijn ook geen standaard actie op een database. Wat gebeurd er bv als er rijen verdwijnen etc?

Dit zal je imho toch echt buiten de database om moeten doen.

Tijdens een select verdwijnen er natuurlijk geen rijen, en zeker niet in zo'n simpele database....

[ Voor 34% gewijzigd door FreakNL op 13-06-2006 16:44 ]

[Rigi]

FreakNL schreef op dinsdag 13 juni 2006 @ 16:44:


[...]


Tijdens een select verdwijnen er natuurlijk geen rijen, en zeker niet in zo'n simpele database....

laat ik het maar op het warme weer gooien;) (en verder maar gewoon niets meer zeggen )

[jochemd]

FreakNL schreef op dinsdag 13 juni 2006 @ 16:08:

postgres=# select * from car_fix order by dat ASC;
clientnr | receiptnr | description | dat
---------------+----------------+----------------------+------------
1 | 1 | Changed Tires | 2006-04-12
4 | 4 | Changed oil | 2006-05-12
4 | 3 | Regular Checkup | 2006-05-12
2 | 2 | Changed Tires | 2006-05-12
3 | 5 | Fixed Airbag | 2006-05-12
2 | 8 | Flat Tire | 2006-06-13
1 | 7 | Broken Axis | 2006-06-13
5 | 6 | Brake problem | 2006-06-13
(8 rows)

Die verklein ik als volgt:

postgres=# select to_char(dat, 'yyyy') as year, to_char(dat, 'mm') as month,
count(receiptnr) AS fixes from car_fix group by month,year order by month;
year | month | fixes
------+-------+-----------
2006 | 04 | 1
2006 | 05 | 4
2006 | 06 | 3
(3 rows)

postgres=#

Per maand heb ik nu dus het aantal "car_fixes".

Niet noodzakelijkerwijs. Als er in een maand geen fixes zijn geweest komt die maand niet voor in plaats van wel voor te komen met het aantal 0. Dat lijkt hier nog niet zo erg.

Wat is wil doen, is het SQL statement op zodanige wijze aanpassen, dat het verschil tussen de huidige en vorige rij getoond wordt.

Maar hier is het vervolgens wel erg.

Als je dit echt in PostgreSQL wil doen in plaats van in je applicatie dan zou ik eens goed naar de mogelijkheden van een Set Returning Function kijken. Als het echt in SQL moet dan heb je een stamtabel nodig met daarin alle maanden en jaren om te garanderen dat je ze altijd hebt. En als je die stamtabel toch hebt dan kan je die net zo goed gaan gebruiken als een summary table.

[FreakNL]

In PHP heb ik het in een half uurtje voor elkaar. Probleem is alleen dat het in postgresql moet

Hoeft overigens niet perse in een query ofzo, mag ook in een view, maar ik krijg het nog niet echt voor elkaar Ben ook helemaal geen database/programmeerman maargoed.....

Ik weet dat dit geen huiswerkforum is, maar het is het laatste vak wat ik moet doen voor ik mag afstuderen. Bestond uit 15 opdrachten waarvan ik er 14 afheb, maar deze kom ik dus niet uit. Kan me trouwens niet voorstellen dat het heel complex is, alle andere opdrachten waren een regel of 5 tot 10 aan queries/code....

Heb dit van een student gehad die een soortgelijke opdracht had:

code:

CREATE VIEW ProcentueleStijging AS
SELECT Klant.Klantnummer, t1.MaandOmzet AS OmzetVorigeMaand, t2.MaandOmzet AS OmzetDezeMaand, (t2.MaandOmzet - t1.MaandOmzet) / t2.MaandOmzet * 100 AS StijgingsPercentage
FROM Klant 

LEFT OUTER JOIN (
    SELECT Klant.Klantnummer, SUM(Factuurregel.Prijs) AS MaandOmzet
    FROM Klant, Factuur, Factuurregel
    WHERE Klant.Klantnummer = Factuur.Klantnummer AND
    Factuur.Factuurnummer = Factuurregel.Factuurnummer AND
    date_part('month', Factuur.Orderdatum) = date_part('month', date('now')) - 1
    GROUP BY Klant.Klantnummer, date_part('month', Factuur.Orderdatum)
) AS t1 ON(Klant.Klantnummer = t1.Klantnummer) 

LEFT OUTER JOIN (
    SELECT Klant.Klantnummer, SUM(Factuurregel.Prijs) AS MaandOmzet
    FROM Klant, Factuur, Factuurregel
    WHERE Klant.Klantnummer = Factuur.Klantnummer AND
    Factuur.Factuurnummer = Factuurregel.Factuurnummer AND
    date_part('month', Factuur.Orderdatum) = date_part('month', date('now'))
    GROUP BY Klant.Klantnummer, date_part('month', Factuur.Orderdatum)
) AS t2 ON(Klant.Klantnummer = t2.Klantnummer);

Ik ben ervan overtuigd dat het zoiets moet zijn, maar ik krijg het niet voor elkaar

[ Voor 136% gewijzigd door FreakNL op 13-06-2006 20:56 ]

[ACM]

FreakNL schreef op dinsdag 13 juni 2006 @ 20:50:
In PHP heb ik het in een half uurtje voor elkaar. Probleem is alleen dat het in postgresql moet

Hoeft overigens niet perse in een query ofzo, mag ook in een view, maar ik krijg het nog niet echt voor elkaar Ben ook helemaal geen database/programmeerman maargoed.....

In postgresql kan je toch ook functions (o.a. in pl/pgsql) definieren? Daar moet je je php-code dan vast wel in kunnen vertalen.

Ik weet dat dit geen huiswerkforum is, maar het is het laatste vak wat ik moet doen voor ik mag afstuderen. Bestond uit 15 opdrachten waarvan ik er 14 afheb, maar deze kom ik dus niet uit. Kan me trouwens niet voorstellen dat het heel complex is, alle andere opdrachten waren een regel of 5 tot 10 aan queries/code....

De bezwaren zijn al genoemd. Het is geen set-operatie meer en dan is het toch erg lastig. Zie verder jochemd's opmerking over niet bestaande maanden. Als dat soort beperkingen allemaal geen bezwaar zijn kan je het met een subquery oplossen.

Heb dit van een student gehad die een soortgelijke opdracht had:

Ik ben ervan overtuigd dat het zoiets moet zijn, maar ik krijg het niet voor elkaar

Wat ik er in de gauwigheid van zie is dat ie gewoon "deze maand" en "vorige maand" in twee losse subqueries verpakt en de resultaatsets daarvan als tabel weer aan te bieden in de overkoepelende select.
Datzelfde trucje kan je uiteraard ook met jouw gegevens uithalen, maar heeft dus als nadeel dat je dan verkeerde resultaten kunt krijgen als er een maand is met geen waarden. Ik geloof dat je wel kan zorgen dat het vrij redelijk klopt zodat je missende maanden niet toont (wel tonen is vrijwel onmogelijk met je huidige dataset, zie jochemd) en dat je de daaropvolgende maand een vaste verandering van bijvoorbeeld 0% of 100% meegeeft.

[ACM]

Nouja, creatief met SQL. Er zitten allerlei haken en ogen aan mbt missende maanden, maar niettemin is het iig iets. Owja ik heb het ook verder niet getest enzo, dat mag je zelf doen, inclusief de check of het ook daadwerkelijk doet wat je wilt en of de resultaten dan nog kloppen ook.

SQL:

SELECT 
A.year, A.month, A.fixes,
   COALESCE((A.fixes - B.fixes) / B.fixes) * 100, 0) as percentage
FROM (SELECT
 EXTRACT(YEAR FROM dat) as year,
 EXTRACT(MONTH FROM dat) as month,
 count(receiptnr) AS fixes
FROM car_fix
group by month, year) as A
LEFT JOIN 
(SELECT
 EXTRACT(YEAR FROM dat) as year,
 EXTRACT(MONTH FROM dat) as month,
 count(receiptnr) AS fixes
FROM car_fix
group by month, year) as B ON 
     CASE A.month > 1 THEN (A.year = B.year AND A.month = B.month + 1)
              ELSE (A.year = B.year + 1 AND B.month = 12)
     END

[FreakNL]

Thx, ik heb hem werkende nu.

Je statement was al bijna helemaal goed, alleen was je een haakje vergeten bij COALESCE, en werkte die regel sowieso niet correct, heel vreemd, want hij zit er goed uit.

Ik heb hem verandert naar:

code:

 COALESCE ((A.fixes - B.fixes)  * 100 / B.fixes , 0 )as percentage

En dan werkt hij wel goed? Nou ja, geen zin meer om daarnaar te kijken, het is net een andere schrijfwijze die hij wel pikt? Beetje vaag, maarja....

[ Voor 3% gewijzigd door FreakNL op 14-06-2006 11:57 ]

[jochemd]

FreakNL schreef op woensdag 14 juni 2006 @ 11:56:
Je statement was al bijna helemaal goed, alleen was je een haakje vergeten bij COALESCE, en werkte die regel sowieso niet correct, heel vreemd, want hij zit er goed uit.

Waarschijnlijk een casting issue. INTEGER / INTEGER kan alleen een INTEGER uit komen en dus komt daar als percentage 0%, 100%, 200% etc. uit en niets er tussenin. Cast één van de twee (of beide) naar een float en het komt goed.