Google+ Followers

maandag 8 april 2013

Zomaar een voorbeeldje van PostgreSQL

In antwoord op een vraag op pfz.nl: http://www.pfz.nl/forum/topic/9388-gemiddelde-prijzen-van-selectie-groep/ heb ik verklaard dat dat in PostgreSQL een peulenschil zou zijn (en in MySQL een drama) en om dat te bewijzen zet ik hier een concept uitwerking van het probleem:

Eerst een tabel om mee te testen. Via GENERATE_SERIES() is dat zo gebeurd:


DROP TABLE IF EXISTS prijzencircus;
CREATE TABLE prijzencircus (productid SERIAL, prijs int);
INSERT INTO prijzencircus SELECT productid, 10000 * RANDOM() FROM GENERATE_SERIES(1,10000) AS productid;


Dan het selecteren van de gemiddelde prijs per blok van tien aaneengesloten prijzen:

WITH prijzenslag AS
(
SELECT
productid,
prijs,
RANK() OVER (ORDER BY productid) AS slagvolgorde
FROM prijzencircus
)
SELECT
  slagvolgorde
  , prijs
  , AVG(prijs) OVER (w)
FROM
  prijzenslag
WINDOW w AS (PARTITION BY ROUND(slagvolgorde / 10))
ORDER BY
    slagvolgorde;

RANK geeft een getal wat effetief een volgnummer per record is. Dat getal kan wordt verderop gebruikt om de setjes van tien te bepalen. WITH definieert de query als een tijdelijk view, zodat die verderop in de query wat overzichtelijker aangeroepen kan worden.

In de query zelf wordt de AVG(prijs) berekend over een WINDOW en dat window deelt de records op in groepnes (PARTITIONs) over slagvolgorde/10, dus alles met een slagvolgorde van 0-9 komt in partition 0, 10-19 in 1,  20-29 in 2, etc. De AVG() wordt hierdoor dus gemaakt over setjes van tien records.

En voila.