Aggrégation par période de temps relative

**Babyneedle** · 17/04/2013, 21h34

Bonjour,

J'ai une requête qui calcule le nombre d'actions d'un usager par mois de façon *relative*, c'est à dire que le premier mois comptabilisé d'un usager pourrait être janvier 2012 alors que pour un autre ça serait mars 2013. Je veux comparer les niveaux d'activités dans les premiers mois et derniers mois d'une souscription.

J'ai déjà une requête qui fonctionne mais je me demande s'il ne serait pas possible de l'améliorer. Je suis de la vieille école (SQL 7 en 1998) et je n'ai jamais vraiment intégré les requêtes récursives.

Voici un exemple que vous pouvez tester par vous-mêmes.

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
 
DECLARE @Subscriptions TABLE (ID Int, DateActivity Date, Actions Int)
 
INSERT INTO @Subscriptions SELECT 1, '2010-01-01', 10
INSERT INTO @Subscriptions SELECT 1, '2010-01-02', 10
INSERT INTO @Subscriptions SELECT 1, '2010-02-01', 10
INSERT INTO @Subscriptions SELECT 2, '2010-01-01', 10
INSERT INTO @Subscriptions SELECT 2, '2010-01-02', 10
INSERT INTO @Subscriptions SELECT 2, '2010-02-01', 10
INSERT INTO @Subscriptions SELECT 2, '2010-02-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-03-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10
 
SELECT S.ID, DATEDIFF(month, MinActivity, DateActivity) + 1 As MonthNo, SUM(Actions) As QtyActions
FROM @Subscriptions S
JOIN
(
	SELECT ID, MIN(DateActivity) As MinActivity
	FROM @Subscriptions
	GROUP BY ID
) Ranges ON S.ID = Ranges.ID
GROUP BY S.ID, DATEDIFF(month, MinActivity, DateActivity) + 1
ORDER BY 1, 2

Voici les résultats attendus:

1 1 20
1 2 10
2 1 20
2 2 20
3 1 10
3 2 40

Donc: Est-il possible d'améliorer la lisibilité et/ou la performance de cette requête?

Merci

**elsuket** · 18/04/2013, 03h01

Bonjour Babyneedle,

je n'ai jamais vraiment intégré les requêtes récursives.

L'expression de votre requête me semble tout à fait correcte, et rien ici ne justifie l'utilisation d'une expression de table commune (CTE) récursive.
Par ailleurs les performances peuvent se dégrader par l'utilisation d'une CTE récursive, parce que les cardinalités sont estimées suivant l'expression de l'ancre de la CTE : si donc le nombre de lignes retournées par chaque itération varie, une telle requête peut être sous-performante.

Je veux comparer les niveaux d'activités dans les premiers mois et derniers mois d'une souscription.

C'est là où je suis perdu avec le résultat de votre requête, puisque le souscripteur d'ID 3 a une activité aux mois de Mars et Avril, mais ce n'est pas ce que montre le résultat. Ce dernier est d'ailleurs faussé par le fait que vous utilisez seulement deux mois consécutifs d'activité pour tous les souscripteurs dans votre jeu de données; il est fort probable que ceux-ci souscrivent sur une durée plus grande

Si vous souhaitez comparer l'activité du premier et du dernier mois d'activité, effectivement cela peut se faire avec un CTE (non-récursive), avec par exemple la requête qui suit :

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
53
54
55
56
57
58
59
60
61
DECLARE @Subscriptions TABLE 
(
	ID INT
	, DateActivity DATE
	, Actions INT
);
 
INSERT INTO @Subscriptions SELECT 1, '2010-01-01', 10; 
INSERT INTO @Subscriptions SELECT 1, '2010-01-02', 10; 
INSERT INTO @Subscriptions SELECT 1, '2010-02-01', 10; 
INSERT INTO @Subscriptions SELECT 2, '2010-01-01', 10; 
INSERT INTO @Subscriptions SELECT 2, '2010-01-02', 10; 
INSERT INTO @Subscriptions SELECT 2, '2010-02-01', 10; 
INSERT INTO @Subscriptions SELECT 2, '2010-02-01', 10; 
INSERT INTO @Subscriptions SELECT 3, '2010-03-01', 10; 
INSERT INTO @Subscriptions SELECT 3, '2010-04-01', 10; 
INSERT INTO @Subscriptions SELECT 3, '2010-05-01', 10; 
INSERT INTO @Subscriptions SELECT 3, '2010-06-01', 10; 
INSERT INTO @Subscriptions SELECT 3, '2010-07-01', 10; 
 
WITH
	ACTIVITY_FIRST_MONTH AS
	(
		SELECT		S.ID
				, AFM.StartMonth
				, SUM(S.Actions) AS TotalActions
		FROM		(
					SELECT	ID
						, MIN(MONTH(DateActivity)) AS StartMonth
					FROM	@Subscriptions
					GROUP	BY ID
				) AS AFM
		INNER JOIN	@Subscriptions AS S
					ON S.ID = AFM.ID
					AND MONTH(S.DateActivity) = AFM.StartMonth
		GROUP BY	S.ID , AFM.StartMonth
	)
	,  ACTIVITY_LAST_MONTH AS
	(
		SELECT		S.ID
				, AFM.LastMonth
				, SUM(S.Actions) AS TotalActions
		FROM		(
					SELECT	ID
						, MAX(MONTH(DateActivity)) AS LastMonth
					FROM	@Subscriptions
					GROUP	BY ID
				) AS AFM
		INNER JOIN	@Subscriptions AS S
					ON S.ID = AFM.ID
					AND MONTH(S.DateActivity) = AFM.LastMonth
		GROUP BY	S.ID , AFM.LastMonth
	)
SELECT		L.ID
		, F.StartMonth
		, F.TotalActions
		, L.TotalActions
		, L.LastMonth
FROM		ACTIVITY_LAST_MONTH AS L
INNER JOIN	ACTIVITY_FIRST_MONTH AS F
			ON L.ID = F.ID;

Si elle fonctionne avec ce jeu de données, elle a deux défauts :

- elle ne fonctionnera pas pour un abonnement qui s'étale sur plus d'une année (par exemple, Décembre 2012-Janvier 2013);
- le fait d'utiliser une fonction sur une colonne rend l'estimation de cardinalité par l'optimiseur de requêtes impossible;
- elle ne fonctionne que pour le premier et le dernier mois.

Il faudrait donc ajouter une colonne calculée pour l'année et le mois dans la table, les indexer, et modifier la requête pour qu'elle fonctionne avec ces nouvelles colonnes.

Plus généralement, si l'ID est réutilisé pour deux abonnements sur deux périodes séparées d'au mois un mois, que fait-on ?

@++

**Babyneedle** · 18/04/2013, 14h53

Bonjour elsuket, merci de la réponse.

J'aurais dû ajouter plus de contexte.

Les données des abonnement sont consécutives. Nous avons des package SSIS qui travaillent chaque jour à s'assurer de l'intégrité des données. Si un client part et revient, une nouvelle souscription commence, c'est donc une relation one-to-many client-soucriptions.

Les activités d'un client sont attachées à sa souscription et s'il n'y a pas d'activité pendant X jours, il y a toute une série de mesure prises qui font en sorte que la souscription sera terminée ou que les activités reprendront.

Donc, les activités dans le temps, surtout au niveau des mois, seront toujours continues.

C'est là où je suis perdu avec le résultat de votre requête, puisque le souscripteur d'ID 3 a une activité aux mois de Mars et Avril, mais ce n'est pas ce que montre le résultat. Ce dernier est d'ailleurs faussé par le fait que vous utilisez seulement deux mois consécutifs d'activité pour tous les souscripteurs dans votre jeu de données; il est fort probable que ceux-ci souscrivent sur une durée plus grande

Encore là, je me suis mal expliqué: je veux comparer les premiers et derniers mois d'une souscription dans une requête ultérieure basée sur celle que j'ai donnée en exemple: il ne sert à rien de construire une requête complexe si la base de cette requête reste incertaine.

J'ai revérifié les résultats et ils me semblent bons. La souscription 3 a bel et bien eu 10 activités à son premier mois et 40 à son deuxième, au mois d'avril. Si j'applique le même échantillon de données que vous avez utilisé, j'ai les résultats suivants.

1 1 20
1 2 10
2 1 20
2 2 20
3 1 10
3 2 10
3 3 10
3 4 10
3 5 10

Cela dit, merci d'avoir créé la requête de comparaison.

**aieeeuuuuu** · 18/04/2013, 15h38

Bonjour,

Je ne suis pas sûr non plus d'avoir bien compris le besoin.

Vous voulez obtenir, pour chaque ID et mois d'abonnement, la quantité totale (sur le mois) et le rang du mois par apport à l'abonnement ?

Est-ce que cette requête vous donne ce que vous voulez ? sur un jeu de données avec plus d'abonnés et des abonnements plus longs, elle pourrait être plus performante... à tester donc avec un jeu proche (en volumétrie et répartition) de vos données de production

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
 
SELECT 
		ID
 
	,	ROW_NUMBER() OVER(
				PARTITION BY ID
				ORDER BY DATEADD(
					MONTH
					, 0
					, DATEDIFF(
						MONTH
						, 0 
						, DateActivity
					) 
				) 
				ASC
			)AS MonthNo
,	SUM(Actions) AS QtyActions
FROM Subscriptions
GROUP BY ID, DATEADD(
				MONTH
				, 0
				, DATEDIFF(
					MONTH
					, 0 
					, DateActivity
				) 
			) 
ORDER BY 1,2

Aggrégation par période de temps relative

MS SQL Server

Discussions similaires

Partager

Partager