Aggrégation par période de temps relative

Version imprimable

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:

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:

Citation:

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

Bonjour Babyneedle,

Citation:

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.

Citation:

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:

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 ?

@++ ;)

18/04/2013, 14h53
Babyneedle

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.

Citation:

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.

Citation:

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.

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:

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