Google+ Followers

zaterdag 4 februari 2012

Koppelingen tellen

Zoals gewoonlijk in antwoord op een PFZ topic, de vraag "hoe vind ik boeken die wel door meneer X zijn geschreven, maar waar meneer Y niet aan heeft meegewerkt?"

In de database gaat dit allen door een koppeltabel te maken tussen de boeken en de schrijvers, met daarin één record per schrijver per boek.

De oplossing voor het probleem is dan ook eenvoudig; gewoon tellen.


CREATE TEMPORARY TABLE data (x int, y int);
INSERT INTO data(x,y) VALUES(1,21), (1,22);
INSERT INTO data(x,y) VALUES(2,21), (2,23), (2,24);
INSERT INTO data(x,y) VALUES(3,21), (2,23), (2,22);

SELECT
x
, COUNT(*) AS num_authors
, SUM(CAST(y IN (21,22) AS INTEGER)) AS matching_authors
, SUM(CAST(y IN (23) AS INTEGER)) AS unwanted_authors
FROM data
GROUP BY x;




matching_authors geeft nu aan hoeveel van de schrijvers vallen in de verzameling (21,22) en unwanted laat zien hoeveel er zitten in de verzameling (22). Hierin kun je dus filteren met HAVING


SELECT
x
, COUNT(*) AS num_authors
, SUM(CAST(y IN (21,22) AS INTEGER)) AS matching_authors
, SUM(CAST(y IN (23) AS INTEGER)) AS unwanted_authors
FROM data
GROUP BY x
HAVING
SUM(CAST(y IN (21,22) AS INTEGER)) = 2
AND
SUM(CAST(y IN (23) AS INTEGER)) = 0;

Dat is: beide schrijvers uit (21,22) moeten gevonden zijn, en uit de verzameling (22) mag niemand gevonden worden.

De SUM() in het select deel staan hier zuiver voor debug doeleinden, in productie kun je volstaan met de HAVING.