[MySQL] Uniek ID met hoogste count

[Dutch_guy]

Ik heb een tabel waarin wordt bijgehouden welke gebruiker (client_id) welk object (reference_no) heeft bekeken en wanneer. Dit ziet er als volgt uit (klein deel van de data):



Nu wil ik alle objecten van de afgelopen week zien met daarbij per object de klant die het object het meest heeft bekeken.

Ik heb tot nu toe de volgende query:

code:

SELECT client_id, count(*) as times, reference_no  
FROM website_recently_viewed 
WHERE visit_date between date_sub(now(),INTERVAL 1 WEEK) and now() 
GROUP BY reference_no ORDER BY times DESC

En dat geeft dit als resultaat:



Dit is niet juist. Ik zie nu per object het totale aantal keer dat het object is bekeken in de laatste week en niet welke gebruiker dit object het meest heeft bekeken en hoe vaak.

Iemand een idee of dit überhaupt kan in 1 query?

[YRH]

Ook nog group op client id?

[Reinier]

YRH schreef op woensdag 11 december 2019 @ 22:56:
Ook nog group op client id?

Dan krijg je alle cliënten per object. Ik zou het resulaat daarvan ranken en per object de regel met rank = 1 selecteren.
http://www.mysqltutorial....ions/mysql-rank-function/

[Dutch_guy]

Inderdaad met group by op client_id erbij krijg ik alle gebruikers per object.

Ik zat zelf te denken om dan via de MAX functie het hoogste aantal "times" per object te tonen. Maar ik zou echt niet weten hoe.

[DJMaze]

Je hebt een subquery nodig.

SQL:

SELECT MAX(times), reference_no
FROM (SELECT count(*) as times, reference_no
    FROM .....
) as table

[ Voor 8% gewijzigd door DJMaze op 12-12-2019 11:03 ]

[Dutch_guy]

Daar was ik inderdaad al mee aan de slag gegaan. Dan krijg ik bijna wat ik wil hebben, de hoogste aantallen per object worden dan getoond, echter niet met de juiste gebruiker:

code:

SELECT MAX(times), reference_no, client_id FROM (
SELECT count(*) as times, reference_no, client_id  
    FROM website_recently_viewed 
    WHERE visit_date between date_sub(now(),INTERVAL 1 YEAR) and now() 
    GROUP BY reference_no, client_id ORDER BY reference_no DESC)
AS bla GROUP BY reference_no

Het laagste ID van client_id wordt dan getoond in het resultaat. En niet het client_id van de gerbuiker met de meeste aantallen.

Bijkomend ding is als er 2 of meer gebruikers zijn met hetzelfde aantal "times". Dan wordt ook het eerste ID getoond, wat op zich prima is in dit geval.

[ Voor 69% gewijzigd door Dutch_guy op 12-12-2019 11:32 ]

[Dutch_guy]

Bij nader inzien klopt het toch niet helemaal wat ik doe zie ik naar aanleiding van mijn laatste opmerking.

Als er 2 gebruikers zijn die het object ieder 10x hebben bekeken, dan wil ik die beide gebruikers zien. Beide gebruikers zijn namelijk net zo interessant.

Dit is overigens om te integreren in een webapplicatie (PHP).

Ik ga het anders doen, ik ga een tabel genereren met daarin alle gebruikers die het object de laatste week minimaal 10x hebben bekeken.

De query wordt dan:

code:

SELECT count(*) as times, reference_no, client_id  
    FROM website_recently_viewed 
    WHERE visit_date between date_sub(now(),INTERVAL 1 WEEK) and now()
    GROUP BY reference_no, client_id 
    HAVING times >= 10
    ORDER BY reference_no DESC

[DJMaze]

@Dutch_guy vroeger was er een snoep reclame met een olifant "bedenk goed wat je met je laatste rolo doet".

In dit geval: HAVING temporary table.
Kijk dus even in mysql hoeveel impact je query heeft.
Cache de statistieken

[Dutch_guy]

Uiteindelijk had ik die HAVING alweer verwijderd en vervangen door: ORDER BY times DESC LIMIT 20.