[sql] Data groeperen in PostgreSQL

[BetuweKees]

Hi,

Ik ben recent van MySQL overgestapt naar Postgres en meteen ook lekker aan het knutselen geslagen. Nu loop ik een beetje vast op onderstaande query. Ik probeer hiermee content uit een meertalige database te halen, en daarbij eveneens een foto weer te geven die op betreffende dag genomen is.

Natuurlijk klopt de query zoals die nu is niet; immers, zijn er meerdere foto's per dag gemaakt, dan komen die allemaal terug in de result. Dat is niet de bedoeling.

In mySQL zou ik in een dergelijk geval een GROUP BY doen op page.created, en daarmee het probleem opgelost hebben. PostgreSQL vindt dat echter geen goed idee..

Hoe zou ik dit kunnen oplossen? Is het mogelijk hiervoor een subquery te formuleren? Of moet ik er gewoon een losse query achteraan plakken en de bijbehorende foto op die manier uitvragen?

SQL:

SELECT
    page.created,
    content.title, content.content, content.url,
    image.file, image_local.title
FROM
    country_local
    INNER JOIN country ON (country_local.country_id = country.country_id AND country_local.language_id = 1)
    INNER JOIN visit ON (country.country_id = visit.country_id)
    INNER JOIN visit_local ON (visit.visit_id = visit_local.visit_id AND visit_local.language_id = 1)
    INNER JOIN page_visit ON (visit_local.visit_id = page_visit.visit_id)
    LEFT OUTER JOIN page ON (page_visit.page_id = page.page_id)
    LEFT OUTER JOIN content ON (page.page_id = content.page_id AND content.language_id = 1)
    LEFT OUTER JOIN visit AS trip ON (visit.trip_id = trip.visit_id)
    LEFT OUTER JOIN visit_local AS trip_local ON (trip.visit_id = trip_local.visit_id AND trip_local.language_id = 1)
    LEFT OUTER JOIN image ON (TO_CHAR(page.created, 'yymmdd') = TO_CHAR(image.date, 'yymmdd'))
    LEFT OUTER JOIN image_local ON (image.image_id = image_local.image_id AND image_local.language_id = 1)
WHERE
    country_local.url = 'zuid-korea' AND 
    visit_local.url = 'zuid-korea-2' 
ORDER BY 
    page.created ASC
LIMIT 10;

[PolarBear]

BetuweKees schreef op vrijdag 12 maart 2010 @ 14:28:
In mySQL zou ik in een dergelijk geval een GROUP BY doen op page.created, en daarmee het probleem opgelost hebben. PostgreSQL vindt dat echter geen goed idee..

Dat is nou net een van die eigenaardigheden van MySQL. Zie ook Programming FAQ - SQL

[RobIII]

Waar hoort mijn topic?
SEA >> PRG

Verder: Wat PolarBear zegt inderdaad. Hoe werkt dat GROUP BY nu eigenlijk?

[ Voor 42% gewijzigd door RobIII op 12-03-2010 14:34 ]

[BetuweKees]

ahh..nu voel ik me dom.. overal MIN() omheen (wat toch al de bedoeling was) en het werkt!

[RobIII]

BetuweKees schreef op vrijdag 12 maart 2010 @ 15:20:
ahh..nu voel ik me dom.. overal MIN() omheen (wat toch al de bedoeling was) en het werkt!

Dus je hebt nu:

SQL:

SELECT
    page.created,
    MIN(content.title), MIN(content.content), MIN(content.url),
    MIN(image.file), MIN(image_local.title)
...

??

Dan heb je het nog niet helemaal begrepen vrees ik. Het punt is juist dat je alle velden welke niet in aggregates (zoals Min/Max) gebruikt worden in de Group By horen.

Dus NIET zoals je had:

SQL:

SELECT
    page.created,
    content.title, content.content, content.url,
    image.file, image_local.title
FROM
...
GROUP BY page.created

Maar:

SQL:

SELECT
    page.created,
    content.title, content.content, content.url,
    image.file, image_local.title
FROM
...
GROUP BY page.created, content.title, content.content, content.url, image.file, image_local.title

[ACM]

't Kan overigens efficienter zijn om die "een foto van die dag" los op te halen met een nieuwe query, zeker als er een lange lijst met foto's is, of om juist de foto pas met een subquery als onderdeel van de select-list te halen.

Je lijkt nu in ieder geval een erg lange join op te bouwen om enkel 1 plaatje erbij te krijgen. Overigens forceer je hier mogelijk een vrij dure check mee als er veel afbeeldingen per trip zijn: "(TO_CHAR(page.created, 'yymmdd') = TO_CHAR(image.date, 'yymmdd')) ". Met een functionele index (mede) op die image.date hebt heb je dat natuurlijk niet. Een alternatief is wellicht om met een between te werken die dan iets als dit doet:
image.date between date_trunk('day', page.created) and date_trunk('day', page.created) + interval '1 days'

Een en ander hangt natuurlijk af van het aantal afbeeldingen, zo niet dan is het allemaal veel minder belangrijk om daar aandacht aan te besteden in je queries.