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.

dinsdag 2 april 2013

PostgreSQL 9.3, meer JSON functies

PostgreSQL kende al functies om JSON te genereren, maar met 9.3 komen er eindelijk ook functies om JSON te verwerken.

Zie de post van Andrew Dunstan, die het allemaal op zijn geweten heeft: http://adpgtech.blogspot.nl/2013/03/json-processing-functions-and-new-json.html

In  de praktijk betekent dit dat je vanuit je AJAX applicatie direct in JSON met je database kunt babbelen. Het ophalen van een zoekresultaat wordt ineens wel heel eenvoudig een kwestie van één query waar een JSON string uit komt.

Natuurlijk kon dit al via XML maar XML blijkt in de praktijk vanuit javascript niet bijzonder gebruiksvriendelijk dus dat het nu met JSON kan is een hele verbetering.