Ik heb nu dus deze

Ik heb nu dus deze query staan om te hoeveel pagina’s iemand bezoekt op een site tijdens een session:

SELECT log_remotehost, COUNT(log_id) AS path
FROM   tblLog
WHERE  log_sessionID IS NOT NULL and log_remotehost!=”
GROUP BY log_remotehost, log_sessionID
HAVING COUNT(log_id) > 1
ORDER BY path DESC, log_remotehost, log_sessionID

De simpelste vorm daarvan is iets als dit:

SELECT log_remotehost, COUNT(log_id) AS path
FROM   tblLog
GROUP BY log_remotehost

En dat geeft mij dus per sessie van een remotehost het aantal log_id’s. Om de zaken een beetje proper te houden heb ik er het volgende nog bijgezet:

• WHERE  log_sessionID IS NOT NULL and log_remotehost!=”
  alleen dingen tonen waar er ook effektief een sessionID is ingevuld, en waar de remotehost bekend is
  
• HAVING COUNT(log_id) > 1
  alleen die sessies tonen waar er meer dan één log-entry is
  
• ORDER BY path DESC, log_remotehost
  de hele zooi sorteren: de langste sessies bovenaan, en daarbinnen gesorteerd op host

De vraag is of dat eenvoudiger kan met een subquery. Laurens zegt:

Volgens mij is die group by onnodig, en als je die weglaat kun je er dan geen correlated subselect insteken die zoekt of eenzelfde log_remotehost bestaat? iets van:

SELECT L1.log_remotehost, L1.log_useragent, COUNT(L1.log_id) AS path
FROM   tblLog L1,tblLog L2
WHERE  (L1.log_sessionID IS NOT NULL AND COUNT(L1.log_id) > 1) OR
       L1.log_remotehost IN (SELECT DISTINCT L2.log_remotehost FROM tblLog) ORDER BY path DESC, log_useragent, log_remotehost, log_sessionID DESC

Even mijn hoofd errond draaien. Wacht hé. Wat doet de query hierboven? Zoals hij er nu staat in ieder geval niet veel:

Server: Msg 147, Level 15, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Gr. Eerst eens kijken wat er eigenlijk uit mag. Useragent is niet nodig, en pak nu nog dat alle sessionID’s ingevuld zijn. Dan mag log_useragent weg, en één van die voorwaarden in de WHERE. En in de ORDER BY mag path ook zeker weg. Dat geeft dus:

SELECT L1.log_remotehost, COUNT(L1.log_id) AS path
FROM   tblLog L1,tblLog L2
WHERE  (COUNT(L1.log_id) > 1) OR
       L1.log_remotehost IN (SELECT DISTINCT L2.log_remotehost FROM tblLog) ORDER BY log_remotehost, log_sessionID DESC

Er is niets wezenlijks aan veranderd, en het geeft nog altijd dezelfde foutboodschap, da’s dus wel goed. En nu eens zien wat er precies aan de hand is. Wat doet die query eigenlijk?

• selecteer dingen uit de logtabel (1x als L1, 1x als L2)


• selecteer hostnaam uit L1, en tel het aantal ID’s in L1


• waar




• het aantal ID’s in L1 > 1


• of de hostnaam uit L1 is te vinden in de lijst van hostnamen uit L2


• sorteer dat allemaal op hotnaam en sessionID

Ofwel is het te laat, ofwel begrijp ik er niets van, ofwel is er ergens iets fout: is het niet zo dat die FROM tblLog L1, tblLog L2 een cross join doet? En als er ergens count(ding) staat, moet dan niet aangegeven worden per wat er gecount moet worden? En is die subclause niet per definitie altijd waar (select x FROM tbl AS A WHERE x in (select distinct x from tbl as B))?

I dunno. Ik weet niet hoe veel dat van mij nog kan vereenvoudigd worden:

• selecteer hostname, tel aantal ID’s (per host en per session)


• uit tblLog


• gegroepeerd op host en session


• waar session en host ingevuld zijn


• toon enkel die waar een session meer dan één ID bevat


• en sorteer dat aflopens op aantal ID’s per sessie

Misschien weet één van mijn fijne collega’s of het echt nog eenvoudiger kan?