Discussion :

[olibara]

Bonjour

Je cherche la methode la plus simple pour faire un comptage de plusieurs dates par tranche de semaines calendrier

Pratiquement j'ai une table contenant 3 date
d1= Date creation
d2= Date Expedition
d3= Date Reception

Je voudrais dresser un tableau qui par semaine calendrier me donne le nombre de record pour chaque date

En clair : combien de de creation, combien d'expedition combien de reception pour la semaine X,

Mon idee est de creer un table calendrier pour chaque jour de l'année incluant le numero de semaine
Je ferais alors une jointure sur ce calendrier pour chacune des dates

Un truc du genre ci-dessous, mais c'est pour mes Count que je me perds un peu ?
Mon approche est-elle bonne ?
Merci de votre aide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
Cal.Week,
COUNT(T1),
COUNT(T2),
COUNT(T3)
 
FROM Calendar Cal
LEFT OUTER JOIN Matable T1 ON CAST(T1.d1 AS DATE)=Cal.date
LEFT OUTER JOIN Matable T2 ON CAST(T2.d2 AS DATE)=Cal.date
LEFT OUTER JOIN Matable T3 ON CAST(T3.d3 AS DATE)=Cal.date
GROUP BY 
CAl.Week

[olibara]

Bonjour

Apres reflexion, voici ce qui me vient a l'esprit
Suis-je dans le bon ?
Une meilleure idée ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
Cal.Week,
SUM (CASE WHEN T1.D1 IS NULL THEN 0 ELSE 1 END) AS qtyD1,
SUM (CASE WHEN T2.D2 IS NULL THEN 0 ELSE 1 END) AS qtyD2,
SUM (CASE WHEN T3.D3 IS NULL THEN 0 ELSE 1 END) AS qtyD3
 
 
FROM Calendar Cal
LEFT OUTER JOIN Matable T1 ON CAST(T1.d1 AS DATE)=Cal.date
LEFT OUTER JOIN Matable T2 ON CAST(T2.d2 AS DATE)=Cal.date
LEFT OUTER JOIN Matable T3 ON CAST(T3.d3 AS DATE)=Cal.date
GROUP BY 
CAl.Week

[iberserk]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
Cal.Week,
SUM (T1.NBR )  AS qtyD1,
SUM (T2.NBR) AS qtyD2,
SUM (T3.NBR) AS qtyD3
 
 
FROM Calendar Cal
OUTER APPLY (SELECT COUNT(*) NBR FROM Matable T1 WHERE CAST(T1.d1 AS DATE)=Cal.Date) T1
OUTER APPLY (SELECT COUNT(*) NBR FROM Matable T2 WHERE CAST(T3.d2 AS DATE)=Cal.Date) T2
OUTER APPLY (SELECT COUNT(*) NBR FROM Matable T3 WHERE CAST(T3.d3 AS DATE)=Cal.Date) T3
GROUP BY  CAl.Week

[olibara]

Merci Ibersek

Ah du nouveau !
J'avais deja vu de loin un OUTER APPLY mais jamais utilisé

Je vais donc investiguer pour mieux comprendre les avantages qu'il apporte

[olibara]

Bonjour Ibersek

Bien apres plusieurs lectures et investigations, je n'ai pas encore bien compris l'avantage du OUTER APPLY par rapport a mes OUTER JOIN

Et je me demande aussi si SQLPro ne va pas bondir parce que OUTER APPLY Implique un SUB SELECT

Les avis sont bienvenus !

[iberserk]

ne t'inquiètes pas pour Frédéric je pense qu'il ne sera pas contre, le plan d’exécution sera le même que d'utiliser un LEFT OUTER JOIN sur une sous requête dans le FROM.

Tu confonds avec utiliser une sous requète dans le SELECT...

Au passage on peut ainsi facilement tirer profit de la création d'une vue indexée...

[hmira]

Soit par exemple l'extrait du contenu de la table Calendar

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT date, year, week 
FROM dbo.Calendar

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
date     year     week
.... 
2014-09-26  2014    39
2014-09-27  2014    39
2014-09-28  2014    39
2014-09-29  2014    40
2014-09-30  2014    40
2014-10-01  2014    40
2014-10-02  2014    40
2014-10-03  2014    40
2014-10-04  2014    40
2014-10-05  2014    40
2014-10-06  2014    41
2014-10-07  2014    41
2014-10-08  2014    41
2014-10-09  2014    41
2014-10-10  2014    41
...

Soit par exemple le contenu de la table Matable
Les colonnes calculées w1, w2, w3 représentent respectivement les numéros de semaine ISO des colonnes d1, d2 et d3 pour l'année 2014

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT id, d1, d2, d3, DATEPART(ISO_WEEK, d1) w1, DATEPART(ISO_WEEK, d2) w2, DATEPART(ISO_WEEK, d3) w3  
FROm dbo.Matable

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
id  d1  d2  d3  w1  w2  w3
1   2014-09-26  2014-10-01  2014-10-06  39  40  41
2   2014-09-27  2014-10-02  2014-10-07  39  40  41
3   2014-09-28  2014-10-03  2014-10-08  39  40  41
4   2014-09-29  2014-10-04  2014-10-09  40  40  41
5   2014-09-30  2014-10-05  2014-10-10  40  40  41
6   2014-10-01  2014-10-06  2014-10-11  40  41  41

Pour obtenir le résultat attendu, une autre solution serait celle-ci :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
with Cal_Week  AS 
  ( SELECT cal.year as [year], cal.Week as [week] 
    FROM dbo.Calendar cal  
    GROUP BY cal.year, cal.Week ) 
   , T_D1_Week AS 
     (SELECT DATEPART(Year, T.D1) AS D1_year, DATEPART(ISO_WEEK, T.D1) as D1_Week, COUNT(*) AS Qty_D1_week 
     FROM dbo.Matable T 
     WHERE D1 IS NOT NULL
     GROUP BY DATEPART(Year, T.D1), DATEPART(ISO_WEEK, T.D1)  ) 
    , T_D2_Week AS 
     ( SELECT DATEPART(Year, T.D2) AS D2_year, DATEPART(ISO_WEEK, T.D2) as D2_week, COUNT(*) AS Qty_D2_week 
       FROM dbo.Matable T 
       WHERE D2 IS NOT NULL
       GROUP BY DATEPART(Year, T.D2),  DATEPART(ISO_WEEK, T.D2) ) 
   , T_D3_Week AS 
     ( SELECT DATEPART(Year, T.D3) AS D3_year, DATEPART(ISO_WEEK, T.D3) as D3_week, COUNT(*) AS Qty_D3_week 
       FROM dbo.Matable T 
       WHERE D3 IS NOT NULL
       GROUP BY DATEPART(Year, T.D3),  DATEPART(ISO_WEEK, T.D3) ) 
SELECT cal_week.year, cal_week.week, T_D1_week.Qty_D1_week, T_D2_week.Qty_D2_week, T_D3_week.Qty_D3_week
FROm cal_week 
LEFT OUTER JOIN T_D1_week  
   ON   T_D1_Week.D1_year = cal_week.year
   AND  T_D1_Week.D1_week = cal_week.week
LEFT OUTER JOIN T_D2_week  
   ON   T_D2_Week.D2_year = cal_week.year
   AND  T_D2_Week.D2_week = cal_week.week
LEFT OUTER JOIN T_D3_week  
   ON   T_D3_Week.D3_year = cal_week.year
   AND  T_D3_Week.D3_week = cal_week.week

Résultat :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
year    week    Qty_D1_week Qty_D2_week Qty_D3_week
2014    37      NULL    NULL    NULL
2014    38      NULL    NULL    NULL
2014    39      3       NULL    NULL
2014    40      3       5       NULL
2014    41      NULL    1       6
2014    42      NULL    NULL    NULL
2014    43      NULL    NULL    NULL

Remarque :
La solution de ibersek mettant en oeuvre le OUTER APPLY est très juste concernant la formulation et les résultats obtenus. Elle donne exactement les mêmes résultats que ma solution ci-dessus.
J'ai, sur un exemple de données simple tel que celui mentionné ci-haut, mesuré rapidement avec le profiler les performances des 2 solutions sur le même jeux de données :

Solution ibeserk : cpu : 16 , read 3307 , duration : 10

Ma solution (hmira) : cpu : 0 , read 470 , duration : 4

Il faudra faire d'autres mesures plus approfondies, mais j'ai tendance à dire, sans vouloir me vanter ! que ma solution est plus optimale, reste à confirmer bien sûr avec d'autres mesures ..

A+

[olibara]

Merci hmira et Ibersek

Je fais des test aujourd'hui et je vous tiens informés

[iberserk]

La solution de Hmira sera forcément plus performante, puisqu’utilisant des colonnes calculées PERSISTED...

En revanche vous ne précisez pas la version de SQL SERVER... pas sûr donc que vous puissiez utiliser les colonnes calculées

[olibara]

Bonjour

Je travaille avec SQL Server 2008
Et effectivement il n'y a pas photos entre les deux methodes

2 min 48 secondes pour la methode Ibersek
5 secondes pour la methode hmira

[iberserk]

Hommage à Hmira, j'ai lu un peu vite (et pas en intégralité surtout!) il ne sagit pas de colonne calculée au sens SQL SERVER mais bien de l'utilisation de CTE...

Elle est donc en tout point supérieure à la mienne

[mikedavem]

Pour ma part j'aurais bien vu quelque chose comme cela également mais avec une jointure différente directement sur la date comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 
	dt.Year_Key AS [year], 
	dt.Week_NumberInYear AS [week],
	SUM (CASE WHEN ic.closed_dt IS NULL THEN 0 ELSE 1 END) AS qtyD1,
	SUM (CASE WHEN i.open_dt IS NULL THEN 0 ELSE 1 END) AS qtyD2
FROM dbo.dimension_time as dt
LEFT JOIN dbo.sv_inquiry as ic
	ON CAST(ic.closed_dt AS DATE) = dt.Day_Timestamp
LEFT JOIN dbo.sv_inquiry as i
	ON CAST(i.open_dt AS DATE) = dt.Day_Timestamp
GROUP BY dt.Year_Key,  dt.Week_NumberInYear

En fonction du type de critère les résultats peuvent changer du tout au tout. J'ai effectué un test pour le fun avec une table de date ayant la définition suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE TABLE [dbo].[dimension_time]
(
	[Day_Key] [int] NOT NULL,
	[Day_Timestamp] [datetime] NOT NULL,
	[Day_Name] [nvarchar](32) NOT NULL,
	[Day_Text] [nvarchar](32) NOT NULL,
	[Day_WeekdayName] [nvarchar](32) NOT NULL,
	[Day_IsWorkday] [int] NOT NULL,
	[Day_NumberInWeek] [int] NOT NULL,
	[Day_NumberInMonth] [int] NOT NULL,
	[Day_NumberInYear] [int] NOT NULL,
	[Day_NumberInFiscalYear] [int] NOT NULL,
	[Day_NumberSinceBeginning] [int] NOT NULL,
	[Week_Key] [int] NOT NULL,
	[Week_Name] [nvarchar](32) NOT NULL,
	[Week_Text] [nvarchar](32) NOT NULL,
	[Week_NumberInYear] [int] NOT NULL,
	[Month_Key] [int] NOT NULL,
	[Month_Name] [nvarchar](32) NOT NULL,
	[Month_Text] [nvarchar](32) NOT NULL,
	[Month_NumberInYear] [int] NOT NULL,
	[Month_NumberInFiscalYear] [int] NOT NULL,
	[Season_Key] [int] NOT NULL,
	[Season_Name] [nvarchar](32) NOT NULL,
	[Season_Text] [nvarchar](32) NOT NULL,
	[Quarter_Key] [int] NOT NULL,
	[Quarter_Name] [nvarchar](32) NOT NULL,
	[Quarter_Text] [nvarchar](32) NOT NULL,
	[Quarter_NumberInYear] [int] NOT NULL,
	[FiscalQuarter_Key] [int] NOT NULL,
	[FiscalQuarter_Name] [nvarchar](32) NOT NULL,
	[FiscalQuarter_Text] [nvarchar](32) NOT NULL,
	[FiscalQuarter_NumberInFiscalYear] [int] NOT NULL,
	[Semester_Key] [int] NOT NULL,
	[Semester_Name] [nvarchar](32) NOT NULL,
	[Semester_Text] [nvarchar](32) NOT NULL,
	[Semester_NumberInYear] [int] NOT NULL,
	[FiscalSemester_Key] [int] NOT NULL,
	[FiscalSemester_Name] [nvarchar](32) NOT NULL,
	[FiscalSemester_Text] [nvarchar](32) NOT NULL,
	[FiscalSemester_NumberInFiscalYear] [int] NOT NULL,
	[Year_Key] [int] NOT NULL,
	[Year_Name] [nvarchar](32) NOT NULL,
	[Year_Text] [nvarchar](32) NOT NULL,
	[FiscalYear_Key] [int] NOT NULL,
	[FiscalYear_Name] [nvarchar](32) NOT NULL,
	[FiscalYear_Text] [nvarchar](32) NOT NULL,
	[IsToday] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Day_Key] ASC
)

... et une table sv_inquiry avec 2 index sur les colonnes de date concernées (open_dt et closed_dt) :



L'exécution de la requête ci-dessus donne ce plan d'exécution :




L'exécution de la requête avec la CTE donne ce plan d'exécution :




L'exécution de la requête avec la solution proposée par Ibersek donne ce plan d'exécution :




Comme vous pouvez le remarquer les plans d'exécutions de la première et troisième requête sont assez similaire et utilisent un jointure externe dont le critère est basé directement sur les dates.
La CTE utilise une jointure externe dont le critère est composé (année et semaine). Dans mon cas le coût des plans d'exécution de la première et troisième requête sont d'environ 6.7 alors que pour la CTE le coût est de 10.9.

Les requêtes 1 et 3 ont un nombre de lecture plus importante que la 2ème à base de CTE (respectivement 11054 contre 2628) mais à l'inverse la consommation CPU est plus faible (respectivement 31ms contre 641ms) ainsi que la durée totale (92ms pour la première requête, 111 ms pour la 3ème et 843ms pour la 2ème requête) avec les opérateurs hash aggregate ayant très probablement une bonne part de responsabilité (opérateurs bloquants par nature) ...

Bon l'idée ici n'est pas de dire que telle ou telle requête est la meilleure mais au final on peut voir que la solution n'est pas si évidente ...

++

[olibara]

Merci pour cette Analyse Mikedavem

J'avais prealablement essayé une solution proche de la tienne (voir mon deuxieme post)
Mais elle me donnait des résultat aberrant que je n'ai pas trop cherché a comprendre parce que mes essai avec les solutions de Iberserk et hmira donnait les resultat attendus

Je vais quand meme me repencher sur le code pour comprendre ce qui clochait

[mikedavem]

05h54 ... je vois que certains ne dorment pas beaucoup non plus ...

Ceci dit je serais vraiment curieux d'avoir le résultat de ton côté .. il est probable que pour toi la solution de Iberserk ou hmira soit la meilleure dans ton cas.

Tu aurais éventuellement plus d'information sur la définition de tes tables et des index ?

++

[olibara]

Salut Mikedavem

J'essaye de t'envoyer ca aujourdhui