Requete récursive avec agrégat [Résolu]

[jowsuket]

Bonjour,

Je me demandais si il était possible de faire une requête récursive avec des agrégats.
Je m'explique; prenons les tables suivantes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE [dbo].[Category](
	[Id] [int] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[ParentId] [int] NULL,
	[IsFinal] [bit] NOT NULL
)
CREATE TABLE [dbo].[CategoryAsset](
	[CategoryId] [int] NOT NULL,
	[AssetId] [int] NOT NULL
)
CREATE TABLE [dbo].[Asset](
	[Id] [int] NOT NULL
)

Grâce au CTE (Common Table Expression), j'arrive a obtenir la notion de niveau pour mes catégories.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
WITH Categories(Id, Name, ParentId, Level) AS 
(
    SELECT Id, Name, ParentId, 0 AS Level
    FROM Category
    WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.Name, c.ParentId, cte.Level + 1
    FROM Category AS c
        INNER JOIN Categories AS cte
        ON c.ParentId = cte.Id 
)
SELECT Id, Name, ParentId, Level
FROM Categories
ORDER BY Level;
GO

Maintenant je voudrais obtenir la même requête avec une colonne me donnant le nombre d'Assets rattachés à chacune des catégories via la table CategoryAsset.

NB : Les lignes présentes dans la table de référence CategoryAsset ne font référence uniquement au enregistrements de Category ayant IsFinal=1 (Marqueur indiquant que la catégorie n'est parente d'aucune catégorie)
NB2 : Il faut que la la somme des assets pour une categorie soit la somme des sommes d'assets des sous catégories du niveau (n-1)...

Merci d'avance et bonne journée à tous!

[SQLpro]

Il suffit de faire une CTE avant celle récursive qui calcule le COUNT du groupage minimal puis dans la CTE de l'additionner par récursivité.

A +

[Waldar]

Il faut fournir quelques données avec vos tables !

[jowsuket]

Voici un jeu de données pour les tables (énonncées dans mon post initial)

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
 
INSERT INTO Category(Id, Name, ParentId, IsFinal)
VALUES(1, 'Name1', NULL, 0);
INSERT INTO Category(Id, Name, ParentId, IsFinal)
VALUES(2, 'Name2', NULL, 0);
INSERT INTO Category(Id, Name, ParentId, IsFinal)
VALUES(11, 'Name11', 1, 0);
INSERT INTO Category(Id, Name, ParentId, IsFinal)
VALUES(12, 'Name12', 1, 1);
INSERT INTO Category(Id, Name, ParentId, IsFinal)
VALUES(111, 'Name111', 11, 1);
INSERT INTO Category(Id, Name, ParentId, IsFinal)
VALUES(22, 'Name22', 2, 1);
 
INSERT INTO Asset(Id)
VALUES(1);
INSERT INTO Asset(Id)
VALUES(2);
INSERT INTO Asset(Id)
VALUES(3);
INSERT INTO Asset(Id)
VALUES(4);
 
INSERT INTO CategoryAsset(CategoryId, AssetId)
VALUES(12, 1);
INSERT INTO CategoryAsset(CategoryId, AssetId)
VALUES(12, 2);
INSERT INTO CategoryAsset(CategoryId, AssetId)
VALUES(12, 3);
INSERT INTO CategoryAsset(CategoryId, AssetId)
VALUES(111, 4);
INSERT INTO CategoryAsset(CategoryId, AssetId)
VALUES(22, 4);
INSERT INTO CategoryAsset(CategoryId, AssetId)
VALUES(22, 3);

La requête devrait me donner le résultat suivant :
Champs : Category.Id, Category.Name, Level, AssetCount
1, 'Name1', 0, 4
2, 'Name2', 0, 2
11, 'Name11', 1, 1
12, 'Name12', 1, 3
111, 'Name111', 2, 1
22, 'Name22', 1, 2

NB : Je travaille avec SQL SERVER 2005

[jowsuket]

Envoyé par SQLpro

Il suffit de faire une CTE avant celle récursive qui calcule le COUNT du groupage minimal puis dans la CTE de l'additionner par récursivité.

A +

J'ai bien essayé de faire ceci mais sans succès (peut être m'y suis-je mal pris)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH Categories(Id, Name, ShortName, ParentId, Rank, AssetCount) AS 
(
    SELECT c1.Id, c1.Name, c1.ShortName, c1.ParentId, COUNT(ca1.AssetId) AS AssetCount, c1.Rank
    FROM Category c1 INNER JOIN CategoryAsset ca1 ON c1.Id=ca1.CategoryId
    WHERE c1.IsFinal=1
    GROUP BY c1.Id, c1.Name, c1.ShortName, c1.ParentId, c1.Rank
    UNION ALL
    SELECT c2.Id, c2.Name, c2.ShortName, c2.ParentId, SUM(cte.AssetCount), c2.Rank
    FROM Category AS c2
        INNER JOIN Categories AS cte
        ON c2.Id = cte.ParentId
    GROUP BY c2.Id, c2.Name, c2.ShortName, c2.ParentId, c2.Rank
)
SELECT Id, Name, ShortName, ParentId, Rank
FROM Categories;
GO

Ceci me renvoi l'erreur :
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Categories'.
Ce qui est normal étant donnée que la documentation MSDN spécifie bien que le GROUP BY n'est pas autorisé dans la partie récursive... Mais sans faire de GROUP BY ne ne vois pas comment on peut faire la somme des assets.
Toujours pas de solution pour le moment
Je reste ouvert à toutes propositions

[elsuket]

Il fallait suivre ce que vous a conseillé SQLPro :

Il suffit de faire une CTE avant celle récursive qui calcule le COUNT du groupage minimal puis dans la CTE de l'additionner par récursivité.

Ce qui donne :

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
WITH
	ASSET_COUNT AS
	(
		SELECT		C.Id
				, C.Name
				, C.ParentId
				, CASE 
					WHEN CA.CategoryId IS NULL THEN 0
					ELSE COUNT(*) 
				END AS AssetCount
		FROM		dbo.Category AS C
		LEFT JOIN	dbo.CategoryAsset AS CA
					ON C.Id = CA.CategoryId
		GROUP BY	C.Id
				, C.Name
				, C.ParentId
				,  CA.CategoryId
	)
	, CTE (Id, Name, ParentId, Level, AssetCount) AS 
	(
			SELECT	Id
				, Name
				, ParentId
				, AssetCount
				, 0 AS Level
			FROM	ASSET_COUNT
			WHERE	ParentId IS NULL
		UNION ALL
			SELECT		AC.Id
					, AC.Name
					, AC.ParentId
					, R.AssetCount + AC.AssetCount
					, R.Level + 1
			FROM		ASSET_COUNT AS AC
			INNER JOIN	CTE AS R
						ON AC.ParentId = R.Id
	)
SELECT	Id
	, Name
	, ParentId
	, Level
	, AssetCount
FROM	CTE
ORDER	BY Level

@++

[jowsuket]

Bonjour Elsuket,

Merci de ta réponse. Ta requête est très intéressante et semble se rapprocher de ce que je cherche à faire.

Si j'ai bien compris :
- Le premier SELECT (ASSET_COUNT) fait la somme des actifs par catégorie.
NB :Sachant que seules les catégories n'ayant pas d'enfant ont un AssetCount > 0 (Ceci à cause du fait que seulement ces catégories sont présentes dans la table CategoryAsset).
- Le deuxième SELECT (CTE) renvoie les catégories du niveau de base avec la somme des actifs (calculé dans la première requête)
- Le troisième SELECT (Membre récursif) nous fait l'addition de la somme des actifs du niveau n-1 avec celui du niveau n.

Le problème est que, de cette façon, mes niveaux de bases aurons toujours un AssetCount = 0...

Existe-il un moyen de faire la récursivité dans l'autre sens (depuis les niveau finaux vers les niveaux parents de base)?

[elsuket]

Le premier SELECT (ASSET_COUNT) fait la somme des actifs par catégorie.

Pas exactement : il les dénombre (COUNT(*))

- Le deuxième SELECT (CTE) renvoie les catégories du niveau de base avec la somme des actifs (calculé dans la première requête)

C'est correct si vous appelez somme ce que j'appelle dénombrement
On appelle ce SELECT l'ancre (anchor) de l'expression de table commune.

- Le troisième SELECT (Membre récursif) nous fait l'addition de la somme des actifs du niveau n-1 avec celui du niveau n.

C'est exact. On appelle cette requête le membre récursif de l'expression de table commune.

Le problème est que, de cette façon, mes niveaux de bases aurons toujours un AssetCount = 0...

On peut corriger cela

Existe-il un moyen de faire la récursivité dans l'autre sens (depuis les niveau finaux vers les niveaux parents de base)?

Oui, il suffit d'"inverser" la jointure dans le membre récursif, et dans ce cas la somme devrait éliminer le problème précédent

Je vous laisse chercher un peu ?

@++

[jowsuket]

Je pense avancer dans la résolution de mon problème.

La requête suivante me donne bien le bon nombre d'actifs par catégories :

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
 
WITH ASSET_COUNT AS
(
	SELECT	
		C.Id,
		C.Name,
		C.ParentId,
		CASE 
			WHEN CA.CategoryId IS NULL THEN 0
				ELSE COUNT(*) 
		END AS AssetCount
	FROM dbo.Category AS C
		LEFT JOIN dbo.CategoryAsset AS CA ON C.Id = CA.CategoryId
	GROUP BY C.Id, C.Name, C.ParentId, CA.CategoryId
)
, CTE (Id, Name, ParentId, AssetCount) AS 
(
		SELECT DISTINCT Id,
			Name,
			ParentId,
			AssetCount
		FROM ASSET_COUNT AS AC
			LEFT JOIN dbo.CategoryAsset AS CA ON AC.Id = CA.CategoryId
		WHERE CA.CategoryId IS NOT NULL
		UNION ALL
		SELECT AC.Id,
			AC.Name,
			AC.ParentId,
			R.AssetCount + AC.AssetCount
		FROM ASSET_COUNT AS AC
			INNER JOIN CTE AS R ON AC.Id = R.ParentId
)
SELECT Id,
	Name,
	ParentId, 
	SUM(AssetCount)
FROM CTE
GROUP BY Id, Name, ParentId

Je n'ai plus la notion de niveau mais je pense ne pas avoir besoin de cette colonne dans mon résultat.
Je n'ai plus non plus les catégories ne contenant aucun actif mais ceci n'est pas gênant non plus.

Si quelqu'un voit quelque chose à redire ou une meilleure façon de faire, je suis preneur

Merci encore pour votre participation