[MySQL] Ophalen uniek aantal bezoekers per field-combinatie

[Pantalaimon]

M'n probleem valt moeilijk te omschrijven in één korte topic titel

Maar het volgende is het geval: Ik heb een tabel waarin ik alle views van een bepaald onderdeel van m'n site opsla met de volgende velden:

id (auto_increment)
session_id (id van sessie van bezoeker)
section (bevat 'A' of 'B')
referer (bevat 'homepage' of 'direct')
item_id (bekeken item op pagina, verder niet belangrijk)

Een bezoeker kan op de A of B section komen via m'n homepage, of direct via invoer van de URL. De referer blijft net als de session_id identiek voor elke view van deze bezoeker, ook als ze switchen tussen A en B

Nu kan uiteraard een bezoeker meerdere views genereren in zowel de A als B section. Wat ik nu uit mijn database wil trekken is het aantal unieke bezoekers (unieke session_ids) die op elke van de combinatie van section en referer binnenkomt, dus de allereerste view van de desbetreffende bezoeker. Wat ik dus terug moet krijgen zijn 4 rijen, bijv

aantal_bezoekers	section	referer
12	A	homepage
26	A	direct
9	B	homepage
34	B	direct

Nou heb ik al een dag of wat erop lopen puzzelen, met in m'n hoofd het idee dat het toch echt niet zo moeilijk moest zijn.. maar lukken wilde het niet...

Met de simpele query

code:

SELECT session_id, section, referer FROM views GROUP BY session_id

krijg ik wel exact de rijen terug die ik opgeteld wil zien; ik krijg netjes de eerste view van elke unieke session_id terug

Daarna probeerde ik de volgende query

code:

SELECT COUNT(DISTINCT(session_id)), section, referer FROM views GROUP BY section,referer

met als resultaat dat ik inderdaad 4 rijen terugkrijg met het aantal bezoekers per section-referer combinatie, alleen worden bezoekers die zowel section A als B hebben bekeken dubbel geteld. dit omdat DISTINCT niet voor 1 veld geldt, maar voor de hele rij, en ziet dus session_id + sectionA en session_id + sectionB als unieke (distinct) rijen.

Dat verhaal gaat dus ook niet door.

Uiteindelijk met veel gepuzzel kwam er het volgende gedrocht uit de mouw:

code:

SET @direct_a:=0;
SET @homepage_a:=0;
SET @direct_b:=0;
SET @homepage_b:=0;
SET @dummy:=0;
SET @sessionid='';
SELECT @direct_a, @homepage_a, @direct_b, @homepage_b, @dummy, @sessionid, IF(section = 'A' AND referer = 'direct' AND @sessionid <> session_id, @direct_a:=@direct_a+1,
        IF(section = 'A' AND referer = 'homepage' AND @sessionid <> session_id, @homepage_a:=@homepage_a+1,
            IF(section = 'B' AND referer = 'direct' AND @sessionid <> session_id, @direct_b:=@direct_b+1,
                IF(section = 'B' AND referer = 'homepage' AND @sessionid <> session_id, @homepage_b:=@homepage_b+1, @dummy=0)
            )
        )
    ) AS calc, @sessionid:=session_id
FROM views
ORDER BY session_id,id

SELECT @direct_a,@homepage_a,@direct_b,@homepage_b;

Dit geeft uiteindelijk WEL het gewenste resultaat, alleen mijn grote maar in deze query is dat het mij veel te omslachtig en te zwaar dan nodig is. Ook kan ik natuurlijk verder in PHP de rijen gaan tellen mbv een eerdere geplaatste query. Alleen moet voor mijn idee dezelfde uitkomst ook gewoon met een mysql query teruggegeven moeten kunnen worden.. Is hetgene wat ik wil (voor zoverre dat duidelijk is ) niet met een (één!) veel simpelere query op te lossen?? Of moet ik alsnog terugvallen naar wat extra PHP of bovenstaande lap?

[Voutloos]

Maak een query met het aantal sessions met beide sections en corrigeer je aantal daarmee, of doe een full outer join van views tegen views, met aan elke kant een andere section en zorg dmv een if() of iets dergelijks voor een section 'Both'.

En als referer constant is voor een bepaalde session, hoort het dan niet gewoon bij de session tabel?

[ Voor 17% gewijzigd door Voutloos op 27-03-2008 10:19 ]

[Sp3ci3s8472]

Wat komt er uit deze query?

SELECT count(session_id) FROM sessions WHERE 1 GROUP BY (section, referrer)

Als het goed is krijg je nu je tabelletje zoals je wilt

[Verwijderd]

Bijna goed

SQL:

SELECT 
  count(DISTINCT session_id) 
FROM sessions 
GROUP BY (section, referrer)

zou het volgens mij moeten zijn

[ Voor 5% gewijzigd door Verwijderd op 27-03-2008 14:59 . Reden: where 1 weggehaald ]

[TeeDee]

* TeeDee krijgt een prachtige error in MS-SQL. Maar dit is zeker een van de 'quirks' in MySQL? Handig dat het werkt, alleen zou ik er niet op vertrouwen.

[Sp3ci3s8472]

@ osnoekie
Aha, ik heb nog nooit met distinct gewerkt
Overigens is WHERE 1 wel zo netjes, MySQL slikt het om het weg te laten, anderen niet afaik.
@ TeeDee
Welke error krijg je? Kan kloppen dat het wel een MS iets is, ze staan immers in browserland ook bekend om hun compatibility

[ Voor 18% gewijzigd door Sp3ci3s8472 op 27-03-2008 15:13 ]

[TeeDee]

Sp3ci3s8472 schreef op donderdag 27 maart 2008 @ 15:10:
Welke error krijg je? Kan kloppen dat het wel een MS iets is, ze staan immers in browserland ook bekend om hun compatibility

MS heeft o.a. hun SQL standaard beter op orde dan MySQL. Dus dat is imo een rare opmerking.

De WHERE 1 zorgt in ieder geval in MS-SQL wel voor problemen. Het kan natuurlijk ook aan de gebruikte dataset liggen...

Sp3ci3s8472 schreef op donderdag 27 maart 2008 @ 15:15:
MySQL heeft ook zijn eigen quirks hoor

Dat zei ik toch ook al

[ Voor 16% gewijzigd door TeeDee op 27-03-2008 15:15 ]

[Sp3ci3s8472]

MySQL heeft ook zijn eigen quirks hoor
Raar dat WHERE 1 voor problemen zorgt, ik dacht juist dat deze juist verplicht was voor compatibility.

[Voutloos]

Pantalaimon schreef op donderdag 27 maart 2008 @ 09:39:
Met de simpele query

code:

SELECT session_id, section, referer FROM views GROUP BY session_id

krijg ik wel exact de rijen terug die ik opgeteld wil zien; ik krijg netjes de eerste view van elke unieke session_id terug

Nee, je krijgt toevallig de 1e view terug. section en referer staan niet in group by en zijn ook geen resultaat van een aggregate function en dus krijg je de waardes van een willekeurige row. Dit mag enkel in mysql, maar resulteert (terecht) in een foutmelding bij oa mssql.

Om te zorgen dat je geen random data krijgt, maar gewoon de eerste hit van een session, en vervolgens enkel van die eerste hits je statistiek uit te draaien zou ik het volgende doen:
$derived_table = SELECT session_id, MIN(id) as min_id FROM views GROUP BY session_id

Vervolgens kan je je gewenste kolommen selecteren uit views inner join ($derived_table) met join condities session_id en id=min_id en de uiteindelijke group by.

Oftewel: een efficiente groupwise minimum query. Zie groupwise-max (het voorbeeld boven: 'this was too simple' ).

Negeer m'n eerste post maar, ik zie nu beter wat het probleem is, en dit is gewoon de oplossing.

[ Voor 14% gewijzigd door Voutloos op 27-03-2008 16:04 ]