[SQLSERVER]Subset ?

Subset ? [SQLSERVER] - SQL/NoSQL - Programmation

Marsh Posté le 31-08-2010 à 10:28:51    

Bonjour  :o  
 
J'ai une requête comme celle-ci:

Code :
  1. SELECT prod.tag_id AS prod_id
  2.         ,level1.visitors
  3.         ,d.date
  4.         FROM tags prod
  5.         INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  6.         INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  7.         LEFT JOIN l_dates d ON d.date_id = level1.date_id
  8.         WHERE cat.parent_id = 3
  9.         AND level1.site = 100003
  10.         AND d.date  BETWEEN '2010-8-28' AND '2010-8-29'
  11.         ORDER BY level1.date_id ASC, level1.visitors DESC


C'est le order by qui est important ici.
 
Le résultat est le suivant:
 

Code :
  1. prod_id visitors date
  2. 303010000 1889 2010-08-28 00:00:00.000
  3. 303020000 1377 2010-08-28 00:00:00.000
  4. 303010004 1201 2010-08-28 00:00:00.000
  5. 303000005 831 2010-08-28 00:00:00.000
  6. 303030000 797 2010-08-28 00:00:00.000
  7. 303010008 763 2010-08-28 00:00:00.000
  8. 303000000 608 2010-08-28 00:00:00.000
  9. 303000003 584 2010-08-28 00:00:00.000
  10. 303000001 555 2010-08-28 00:00:00.000
  11. 303080000 525 2010-08-28 00:00:00.000
  12. 303000002 522 2010-08-28 00:00:00.000
  13. 303000004 433 2010-08-28 00:00:00.000
  14. 303050000 430 2010-08-28 00:00:00.000
  15. 303090000 374 2010-08-28 00:00:00.000
  16. 303100001 146 2010-08-28 00:00:00.000
  17. 303060001 129 2010-08-28 00:00:00.000
  18. 303010003 125 2010-08-28 00:00:00.000
  19. 303010009 120 2010-08-28 00:00:00.000
  20. 303060000 96 2010-08-28 00:00:00.000
  21. 303100004 96 2010-08-28 00:00:00.000
  22. 303010005 88 2010-08-28 00:00:00.000
  23. 303010007 84 2010-08-28 00:00:00.000
  24. 303100000 13 2010-08-28 00:00:00.000
  25. 303040000 8 2010-08-28 00:00:00.000
  26. 303100002 7 2010-08-28 00:00:00.000
  27. 303100003 6 2010-08-28 00:00:00.000
  28. 303010000 1779 2010-08-29 00:00:00.000
  29. 303020000 1286 2010-08-29 00:00:00.000
  30. 303010004 1218 2010-08-29 00:00:00.000
  31. 303000005 888 2010-08-29 00:00:00.000
  32. 303010008 765 2010-08-29 00:00:00.000
  33. 303030000 732 2010-08-29 00:00:00.000
  34. 303000000 670 2010-08-29 00:00:00.000
  35. 303000001 623 2010-08-29 00:00:00.000
  36. 303000003 592 2010-08-29 00:00:00.000
  37. 303000002 563 2010-08-29 00:00:00.000
  38. 303080000 546 2010-08-29 00:00:00.000
  39. 303000004 504 2010-08-29 00:00:00.000
  40. 303090000 338 2010-08-29 00:00:00.000
  41. 303050000 275 2010-08-29 00:00:00.000
  42. 303100001 172 2010-08-29 00:00:00.000
  43. 303060001 131 2010-08-29 00:00:00.000
  44. 303010009 128 2010-08-29 00:00:00.000
  45. 303010003 115 2010-08-29 00:00:00.000
  46. 303100004 100 2010-08-29 00:00:00.000
  47. 303010007 89 2010-08-29 00:00:00.000
  48. 303010005 84 2010-08-29 00:00:00.000
  49. 303060000 76 2010-08-29 00:00:00.000
  50. 303040000 14 2010-08-29 00:00:00.000
  51. 303100000 12 2010-08-29 00:00:00.000
  52. 303100002 8 2010-08-29 00:00:00.000
  53. 303100003 6 2010-08-29 00:00:00.000
  54. 303070000 1 2010-08-29 00:00:00.000
  55. 303070006 1 2010-08-29 00:00:00.000


C'est fantastique SAUF qu'il me faut le top 5 des visits par jour (date)
Il me semble que je ne peux pas faire de group by sinon je vais me retrouver avec 2 records au lieu de 10.
Comment je peux m'en sortir ?

Reply

Marsh Posté le 31-08-2010 à 10:28:51   

Reply

Marsh Posté le 31-08-2010 à 11:27:00    

Code :
  1. WITH recordset AS (SELECT 
  2.                 prod.tag_id AS prod_id
  3.         ,level1.visitors
  4.         ,d.date
  5.                 ,row_number() over (partition BY d.date ORDER BY level1.date_id, level1.visitors DESC) AS rownumber
  6.         FROM tags prod
  7.         INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  8.         INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  9.         LEFT JOIN l_dates d ON d.date_id = level1.date_id
  10.         WHERE cat.parent_id = 3
  11.         AND level1.site = 100003
  12.         AND d.date  BETWEEN '2010-8-28' AND '2010-8-29'
  13.         ORDER BY level1.date_id ASC, level1.visitors DESC)
  14.                 SELECT prod_id, visitors, date FROM recordset WHERE rownumber BETWEEN 1 AND 5


 
(pas testé... )

Reply

Marsh Posté le 31-08-2010 à 12:22:41    

j'ai fait un truc comme ça:
 

Code :
  1. SELECT * FROM (
  2.         SELECT prod.tag_id AS prod_id
  3. ,RANK() over (partition BY d.date ORDER BY level1.date_id, level1.visitors DESC) AS ranking
  4.         ,level1.visitors
  5.         ,d.date
  6.         FROM tags prod
  7.         INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  8.         INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  9.         LEFT JOIN l_dates d ON d.date_id = level1.date_id
  10.         WHERE cat.parent_id = 3
  11.         AND level1.site = 100003
  12.         AND d.date  BETWEEN '2010-8-28' AND '2010-8-29'
  13.         ORDER BY level1.date_id ASC, level1.visitors DESC
  14. ) tmp
  15. WHERE ranking BETWEEN 1 AND 5


et ça marche très bien, merci  :jap:  
 
 
Maintenant, deuxième problème: il y a un SUM() dans le select, du coup il n'y a plus rien qui marche  [:corten:1]  
C'est toujours la même problématique avec les group by. Et la je ne peux pas faire le système du inner join parce que les rows se trouvent dans ... les inner join de la query.

Reply

Marsh Posté le 31-08-2010 à 13:17:40    

Bon, on va faire autrement :d
Soit la requête simplifiée ci dessous:

Code :
  1. SELECT 
  2.         prod.tag_id AS prod_id
  3.         ,level1.visitors
  4.         ,cat.tag_id AS cat_id
  5.         ,d.week_year, d.week_week
  6.         FROM tags prod
  7.         INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  8.         INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  9.         LEFT JOIN l_dates d ON d.date_id = level1.date_id
  10.         WHERE cat.parent_id = 3
  11.         AND  site = -1 
  12.         AND  d.date BETWEEN '2010-1-01' AND '2010-8-30' 
  13.         ORDER BY week_week DESC


qui me donne le résultat suivant: (je ne mets que les deux premières semaines pour des raisons évidentes  [:cerveau du chaos] )


prod_id visitors cat_id week_year week_week
300020106 1 300020001 2010 2
300020202 2 300020002 2010 2
300020504 4 300020005 2010 2
300020506 3 300020005 2010 2
300020106 5 300020001 2010 2
300020107 2 300020001 2010 2
300020108 4 300020001 2010 2
300020201 8 300020002 2010 2
300020202 13 300020002 2010 2
300020504 11 300020005 2010 2
300020506 5 300020005 2010 2
300020106 12 300020001 2010 2
300020107 15 300020001 2010 2
300020108 4 300020001 2010 2
300020201 10 300020002 2010 2
300020202 12 300020002 2010 2
300020504 9 300020005 2010 2
300020506 6 300020005 2010 2
300020106 15 300020001 2010 2
300020107 20 300020001 2010 2
300020108 2 300020001 2010 2
300020201 10 300020002 2010 2
300020202 14 300020002 2010 2
300020504 14 300020005 2010 2
300020506 7 300020005 2010 2
300020106 21 300020001 2010 2
300020107 18 300020001 2010 2
300020108 7 300020001 2010 2
300020201 17 300020002 2010 2
300020202 27 300020002 2010 2
300020504 23 300020005 2010 2
300020506 6 300020005 2010 2
300020106 6 300020001 2010 2
300020107 6 300020001 2010 2
300020201 7 300020002 2010 2
300020202 6 300020002 2010 2
300020504 21 300020005 2010 2
300020506 3 300020005 2010 2
300020107 1 300020001 2010 2
300020201 2 300020002 2010 2
300020202 3 300020002 2010 2
300020504 2 300020005 2010 2
300020506 1 300020005 2010 2
300020106 1 300020001 2010 1
300020107 1 300020001 2010 1
300020202 1 300020002 2010 1
300020504 2 300020005 2010 1
300020107 1 300020001 2010 1
300020202 1 300020002 2010 1
300020504 1 300020005 2010 1


Je voudrais le top 5 par semaine (week_week) de la somme des visiteurs par prod_id

Reply

Marsh Posté le 31-08-2010 à 13:42:55    

Code :
  1. SELECT *
  2. FROM (
  3.     SELECT prod_id, SUM(visitors) visitors, cat_id, week_year, week_week, ROW_NUMBER() over (partition BY week_week ORDER BY week_week, SUM(visitors) DESC) rOrder
  4.     FROM #truc
  5.     GROUP BY prod_id, cat_id, week_year, week_week
  6.     ) a
  7. WHERE rOrder <= 5


 
La table #truc est le resultat de ta query.
 
Ca devrai donner ca (mais pas testé vu que j'ai pas tes tables):

Code :
  1. SELECT *
  2. FROM (
  3.     SELECT    prod.tag_id AS prod_id 
  4.             ,SUM(level1.visitors) visitors
  5.             ,cat.tag_id AS cat_id
  6.             ,d.week_year
  7.             ,d.week_week
  8.             ,ROW_NUMBER() over (partition BY week_week ORDER BY week_week, SUM(level1.visitors) DESC) rOrder
  9.     FROM tags prod
  10.         INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  11.         INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  12.         LEFT JOIN l_dates d ON d.date_id = level1.date_id
  13.     WHERE cat.parent_id = 3
  14.         AND  site = -1
  15.         AND  d.date BETWEEN '2010-1-01' AND '2010-8-30'
  16.     GROUP BY prod.tag_id, cat.tag_id, d.week_year, d.week_week
  17.     ) a
  18. WHERE rOrder <= 5


 
Voila la resultat:

Code :
  1. prod_id visitors cat_id week_year week_week rOrder
  2. 300020504 3 300020005 2010 1 1
  3. 300020202 2 300020002 2010 1 2
  4. 300020107 2 300020001 2010 1 3
  5. 300020106 1 300020001 2010 1 4
  6. 300020504 84 300020005 2010 2 1
  7. 300020202 77 300020002 2010 2 2
  8. 300020107 62 300020001 2010 2 3
  9. 300020106 60 300020001 2010 2 4
  10. 300020201 54 300020002 2010 2 5


Message édité par Oliiii le 31-08-2010 à 13:44:07
Reply

Marsh Posté le 31-08-2010 à 14:12:55    

Tu es formidable [:shimay:1]  
 
 
j'avais commencé un truc du genre, mais  [:cerveau du chaos]  
avec un select distinct sans group by (j'ai du mal avec le fait de recopier les trucs du select)
puis avec le ROW_NUMBER()... dans le select * parce que j'avais tenté de faire un ORDER BY week_week, visitors DESC mais ça ne marche pas
Et puis encore un select * qui engloble le tout pour pouvoir faire WHERE rOrder <= 5
Bref, du bricolage :d
 
Je prends ta solution qui est bien plus claire.

Reply

Marsh Posté le 31-08-2010 à 15:46:54    

Hum, c'est encore moi [:cerveau du chaos]  
 
Alors j'ai oublié dans mon select de prendre le MIN(d.date) pour chaque week_week, encore une aggrégation  [:ninja dago]  
 
j'ai donc tenté de faire un select ... MIN(d.date) ... group by ..., d.date
Mais ça ne va pas car la date min du group by peut être autre chose que la date min du week_week
 
J'ai donc eu la merveilleuse idée de faire un  
MIN(d.date) OVER (PARTITION BY d.week_week) as minDate
 
Ca marche mais il ne me les regroupe plus par prod_id  [:pingouino]  
 
Comment se fait-ce ?

Reply

Marsh Posté le 31-08-2010 à 16:06:24    

C'est parce que je DOIS mettre un d.date dans le group by et donc il fait des sum par d.date aussi :/
Comment on se sort de ça ?

Reply

Marsh Posté le 01-09-2010 à 08:31:21    

Il ne faut pas faire un MIN(d.date) si le d.date est dans le Group By.
 
Quand les queries deviennent trop compliquée comme ca, c'est des fois mieux de les faire en plusieurs partie.
Une query de base dans une table temporaire, puis reutiliser cette table pour afficher les choses correctement ou refaire d'autre group by.

Reply

Marsh Posté le 01-09-2010 à 16:58:08    

Bon, hum
je laisse tomber le problème de date pour le moment parce que j'ai un problème plus grave.
On ne veut pas le top 5 sur la semaine. On veut le top 5 de tous les temps, Puis on prend les sum par semaine.
J'ai donc fait la chose suivante:
 

Code :
  1. SELECT   prod.tag_id AS prod_id 
  2.                 ,prod.value AS prod_name
  3.                 ,SUM(ISNULL(level1.visitors,0)) visitors
  4.                 ,cat.tag_id AS cat_id
  5.                 ,cat.value AS cat_name
  6.                 ,d.week_year
  7.                 ,d.week_week
  8.         FROM tags prod
  9.             INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  10.             INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  11.             LEFT JOIN l_dates d ON d.date_id = level1.date_id
  12. INNER JOIN ( --> ici pour le top 5 sur toute la durée
  13.         SELECT  top 5
  14.                 prod.tag_id AS prod_id 
  15.                 ,SUM(level1.visitors) visits
  16.         FROM tags prod
  17.             INNER JOIN tags cat ON prod.parent_id = cat.tag_id
  18.             INNER JOIN stats_daily_level1 level1 ON level1.tag1 = prod.tag_id
  19.             LEFT JOIN l_dates d ON d.date_id = level1.date_id
  20.         WHERE cat.parent_id = 3
  21.             AND  site = -1
  22.             AND  d.date BETWEEN '2010-1-01' AND '2010-8-31'
  23.         GROUP BY prod.tag_id,prod.value
  24.         ORDER BY visits DESC
  25.         ) top10 ON top10.prod_id = prod.tag_id
  26.                 WHERE cat.parent_id = 3
  27.             AND  site = -1
  28.             AND  d.date BETWEEN '2010-1-01' AND '2010-8-31'
  29. GROUP BY prod.tag_id, prod.value, cat.tag_id, cat.value, d.week_year, d.week_week
  30. ORDER BY week_week ASC


 
Y a juste un minuscule problème.  
C'est que si n'y a pas de visiteurs pour une semaine. Il n'y a pas de record. Et il faut absolument qu'il me sorte un record avec visitors = 0
C'est possible ?

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed