Requête infernale : trouver le nb d'enrgts à 2 dates distinctes [Résolu]

[uniterre]

Bonjour,

Malgré des heures de recherche, je n'arrive pas à trouver de solution pour finaliser une requête.
Voilà j'ai une table avec 2 champs : counter, time contenant par exemple :

5, 23/02/2011
6, 23/02/2011
5, 23/02/2011
12, 23/02/2011
5, 01/03/2011
36, 01/03/2011
5, 01/03/2011
5, 01/03/2011
8, 01/03/2011

J'ai besoin de récupérer le nombre d'enregistrements à 2 dates distinctes pour en calculer la différence.
Par exemple pour counter = 5 le 23/02/2011 : 2 enrgts et le 1/03/2011 : 3 enregts. Soit une différence de 1
Si un enregistrement est présent à une date mais pas à une autre, alors il ne faut le prendre en considération.

L'idéal serait de récupérer un tableau du genre :
counter, date1, hits1, date2, hits2

que je pensais peut être récupérer avec cette requête :

Code :

SELECT FROM_UNIXTIME(C1.time,'%Y-%m-%d') AS date1, FROM_UNIXTIME(C2.time,'%Y-%m-%d') AS date2, C1.counter, COUNT(C1.counter) AS hits1, COUNT(C2.time) AS hits2 FROM CC_visitor C1 INNER JOIN  CC_visitor C2 ON C1.counter=C2.counter WHERE (C2.time BETWEEN 1298934000 AND 1299020400) AND C1.time BETWEEN 1298415600 AND 1298502000 GROUP BY counter ORDER BY COUNTER

mais cela donne un mauvais résultat pour les count.

J'essaye donc avec un UNION :

Code :

1
2
3
SELECT FROM_UNIXTIME(time,'%Y-%m-%d') AS date, counter, COUNT(*) AS hits FROM CC_visitor WHERE time BETWEEN 1298415600 AND 1298502000 GROUP BY counter
UNION
(SELECT FROM_UNIXTIME(time,'%Y-%m-%d') AS date, counter, COUNT(*) AS hits FROM CC_visitor WHERE time BETWEEN 1298934000 AND 1299020400 GROUP BY counter) ORDER BY counter

mais cela me donne les résultats sur 2 lignes, ce dont je peux me débrouiller, mais surtout ca affiche hélas les enregistrements qui ne sont présents qu'à une seule date, ce qui me pose problème !!

J'ai trouvé une requête qui me donne bien tous les counter présents au 2 dates mais ca ne répond pas à me demande :

Code :

1
2
3
4
5
6
7
SELECT counter, COUNT(*) FROM
(
SELECT FROM_UNIXTIME(time,'%Y-%m-%d') AS date, counter, COUNT(*) AS hits FROM CC_visitor WHERE time BETWEEN 1298415600 AND 1298502000 GROUP BY counter
UNION
(SELECT FROM_UNIXTIME(time,'%Y-%m-%d') AS date, counter, COUNT(*) AS hits FROM CC_visitor WHERE time BETWEEN 1298934000 AND 1299020400 GROUP BY counter)
ORDER BY counter
) TA GROUP BY counter HAVING COUNT(*)=2

Voilà si quelqu'un d'entre vous est suffisamment calé pour m'aider, ca sera avec plaisir !

[skuatamad]

Essaie avec cette requête (j'ai viré les from_unixtime car je gère des dates mais si tu en as besoin rajoutes les):

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT t.counter,max(t.time),min(t.time)
       ,sum(case when t.time = t1.max_time then 1 end) AS nb_max_date
       ,sum(case when t.time = t1.min_time then 1 end) AS nb_min_date
  FROM t
  JOIN (
        SELECT counter,max(time) AS max_time,min(time) AS min_time,count(DISTINCT time) AS nb_time
          FROM t
         GROUP BY counter
        HAVING count(DISTINCT time) = 2
       ) t1
    ON t.counter = t1.counter
 GROUP BY t.counter;

qui donne avec ton jeu de test :

Code :

1
2
3
   COUNTER MAX(T.TIM MIN(T.TIM NB_MAX_DATE NB_MIN_DATE
---------- --------- --------- ----------- -----------
         5 01-MAR-11 23-FEB-11           3           2

[uniterre]

Merci !!!

J'avoue ne pas bien comprendre cette requête. Au moins ca me rassure, ca prouve que ca n'était pas si simple que ca !

Cependant, je ne suis pas sûr de l'endroit où rajouter les clauses WHERE afin de faire les tests de dates :
D'après ce que je comprends, c'est dans la sous requête :

Code :

1
2
3
4
5
6
SELECT counter,max(FROM_UNIXTIME(time,'%Y-%m-%d')) AS max_time, min(FROM_UNIXTIME(time,'%Y-%m-%d')) AS min_time, count(DISTINCT time) AS nb_time FROM CC_visitor 
WHERE (time BETWEEN 1298415600 AND 1298502000)
OR (time BETWEEN 1298934000 AND 1299020400)
GROUP BY counter 
HAVING count(DISTINCT time) > 1  
ORDER BY COUNTER

Seulement, je me retrouve avec des mauvais résultats, juste pour cette requête qui d'après ce que je comprends devrait renvoyer que des résultats avec des dates distinctes. Or ce n'est pas le cas. Dans cet autre exemple, je trouve que le counter 231 a deux fois, comme MIN ET MAX TIME la même valeur : 2011-02-23

1 2011-03-01 2011-02-23 3
55 2011-03-01 2011-02-23 8
231 2011-02-23 2011-02-23 2
299 2011-03-01 2011-02-23 18

[skuatamad]

La requête ne renvoie des résultats qu'avec des dates distinctes chez moi car j'ai une colonne date sans heures minutes secondes.
Utilise : count(DISTINCT FROM_UNIXTIME(time,'%Y-%m-%d')) dans le select et le group by

Sinon, les filtres sont en fait à placer dans la sous requête et dans la requête principale.
D'abord dans la sous requête pour ne récupérer que les counter ayant plusieurs dates dans la tranche donnée.
Puis dans la requête principale si tu souhaites afficher le max(date) et le min(date), sinon ce n'est pas nécessaire.

Concernant la requête, l'astuce du sum(case when t.time = t1.max_time then 1 end) AS nb_max_date
J'aurais pu rajouter case when t.time = t1.max_time then 1 ELSE 0 end, mais par défaut c'est ELSE NULL et sum ne tient pas compte des NULLs.

En gros l'idée est ajoute 1 à la variable nb_max_date chaque fois que t.time = t1.max_time.
Tu peux utiliser count à la place de sum si tu trouves count plus explicite.

La requête deviendrait donc :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT t.counter,max(FROM_UNIXTIME(t.time,'%Y-%m-%d')),min(FROM_UNIXTIME(t.time,'%Y-%m-%d'))
       ,sum(case when FROM_UNIXTIME(t.time,'%Y-%m-%d') = t1.max_time then 1 end) AS nb_max_date
       ,sum(case when FROM_UNIXTIME(t.time,'%Y-%m-%d') = t1.min_time then 1 end) AS nb_min_date
  FROM t
  JOIN (
        SELECT counter,max(FROM_UNIXTIME(time,'%Y-%m-%d')) AS max_time
               ,min(FROM_UNIXTIME(time,'%Y-%m-%d')) AS min_time
               ,count(DISTINCT FROM_UNIXTIME(time,'%Y-%m-%d')) AS nb_time
          FROM t
         WHERE (time BETWEEN 1298415600 AND 1298502000)
            OR (time BETWEEN 1298934000 AND 1299020400)
         GROUP BY counter
        HAVING count(DISTINCT FROM_UNIXTIME(time,'%Y-%m-%d')) > 1
       ) t1
    ON t.counter = t1.counter
 WHERE (t.time BETWEEN 1298415600 AND 1298502000)
    OR (t.time BETWEEN 1298934000 AND 1299020400)
 GROUP BY t.counter;

Par contre même s'il y a plus de 2 dates différentes, la requête ne renvoie que le décompte pour la min date et pour la max date.

[uniterre]

Merci pour tout, ca marche au poil ! Et chapeau pour la compétence !

Une question néanmoins, est-ce qu'il serait possible de rajouter une colonne dans le résultat qui donne la différence entre NB_MAX_DATE NB_MIN_DATE, voire même un calcul comme (NB_MAX_DATE-NB_MIN_DATE) / 7 ?

alexandre

[skuatamad]

Code :

1
2
(sum(case when FROM_UNIXTIME(t.time,'%Y-%m-%d') = t1.max_time then 1 end)
 - sum(case when FROM_UNIXTIME(t.time,'%Y-%m-%d') = t1.min_time then 1 end) ) / 7

devrait faire l'affaire ou sinon utilise une sous requête :

Code :

1
2
3
SELECT p.*
       ,(p.nb_max_date - p.nb_min_date) / 7 AS la_valeur_voulue
  FROM (la_requete_precedente) p

[uniterre]

Fabuleux !!!! Comment puis-je te remercier ?

[skuatamad]

facile ! vote pour une de mes réponses (ou toutes ) en cliquant sur "le pouce vers le haut" en bas à droite de nos messages

[edit] Merci