sql server - recursive - Suma recursiva en estructura de árbol

sql server cte recursive

Question

Tengo una estructura de árbol en una sola mesa. La tabla es un árbol de categorías que se pueden anidar sin fin. Cada categoría tiene una columna de ProductCount que indica cuántos productos están directamente en la categoría (no sumando categorías secundarias).

Id | ParentId | Name | ProductCount
------------------------------------
1 | -1 | Cars | 0
2 | -1 | Bikes | 1
3 | 1 | Ford | 10
4 | 3 | Mustang | 7
5 | 3 | Focus | 4

Me gustaría hacer una consulta de SQL que para cada fila / categoría me dé el número de productos, incluidos los de las categorías secundarias.

La salida para la tabla de arriba debe ser

Id | ParentId | Name | ProductCount | ProductCountIncludingChildren
--------------------------------------------------------------------------
1 | -1 | Cars | 0 | 21
2 | -1 | Bikes | 1 | 1
3 | 1 | Ford | 10 | 21
4 | 3 | Mustang | 7 | 7
5 | 3 | Focus | 4 | 4

Sé que probablemente debería usar CTE, pero no puedo hacerlo funcionar como debería.

Cualquier ayuda es apreciada!

Answer 1

En realidad, esto podría ser un buen uso de HIERARCHYID en SQL Server.

CREATE TABLE [dbo].[CategoryTree]
(
[Id] INT,
[ParentId] INT,
[Name] VARCHAR(100),
[ProductCount] INT
)
GO
INSERT [dbo].[CategoryTree]
VALUES
(1, -1, ''Cars'', 0),
(2, -1, ''Bikes'', 1),
(3, 1, ''Ford'', 10),
(4, 3, ''Mustang'', 7),
(5, 3, ''Focus'', 4)
--,(6, 1, ''BMW'', 100)
GO

Consulta

WITH [cteRN] AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY [ParentId] ORDER BY [ParentId]) AS [ROW_NUMBER]
FROM [dbo].[CategoryTree]
),
[cteHierarchy] AS (
SELECT CAST(
CAST(hierarchyid::GetRoot() AS VARCHAR(100))
+ CAST([ROW_NUMBER] AS VARCHAR(100))
+ ''/'' AS HIERARCHYID
) AS [Node],
*
FROM [cteRN]
WHERE [ParentId] = -1
UNION ALL
SELECT CAST(
hierarchy.Node.ToString()
+ CAST(RN.[ROW_NUMBER] AS VARCHAR(100)
) + ''/'' AS HIERARCHYID),
rn.*
FROM [cteRN] rn
INNER JOIN [cteHierarchy] hierarchy
ON rn.[ParentId] = hierarchy.[Id]
)
SELECT x.[Node].ToString() AS [Node],
x.[Id], x.[ParentId], x.[Name], x.[ProductCount],
x.[ProductCount] + SUM(ISNULL(child.[ProductCount],0))
AS [ProductCountIncludingChildren]
FROM [cteHierarchy] x
LEFT JOIN [cteHierarchy] child
ON child.[Node].IsDescendantOf(x.[Node]) = 1
AND child.[Node] <> x.[Node]
GROUP BY x.[Node], x.[Id], x.[ParentId], x.[Name], x.[ProductCount]
ORDER BY x.[Id]

Resultado

Answer 2

Este es el mismo concepto que la respuesta de Tom, pero menos código (y mucho más rápido).

with cte as
(
select v.Id, v.ParentId, v.Name, v.ProductCount,
cast(''/'' + cast(v.Id as varchar) + ''/'' as varchar) Node
from Vehicle v
where ParentId = -1
union all
select v.Id, v.ParentId, v.Name, v.ProductCount,
cast(c.Node + CAST(v.Id as varchar) + ''/'' as varchar)
from Vehicle v
join cte c on v.ParentId = c.Id
)
select c1.Id, c1.ParentId, c1.Name, c1.ProductCount,
c1.ProductCount + SUM(isnull(c2.ProductCount, 0)) ProductCountIncludingChildren
from cte c1
left outer join cte c2 on c1.Node <> c2.Node and left(c2.Node, LEN(c1.Node)) = c1.Node
group by c1.Id, c1.ParentId, c1.Name, c1.ProductCount
order by c1.Id

SQL Fiddle (agregué algunas filas de datos adicionales para la prueba)

Answer 3

Esto no será óptimo pero funciona, sin embargo, involucra 2 CTE. 1 CTE principal y un CTE en una función con valores de tabla para resumir los valores de cada subárbol.

El primer cte

;WITH cte
AS
(
SELECT
anchor.Id,
anchor.ParentId,
anchor.Name,
anchor.ProductCount,
s.Total AS ProductCountIncludingChildren
FROM
testTable anchor
CROSS APPLY SumChild(anchor.id) s
WHERE anchor.parentid = -1
UNION ALL
SELECT
child.Id,
child.ParentId,
child.Name,
child.ProductCount,
s.Total AS ProductCountIncludingChildren
FROM
cte
INNER JOIN testTable child on child.parentid = cte.id
CROSS APPLY SumChild(child.id) s
)
SELECT * from cte 

Y la función

CREATE FUNCTION SumChild
(
@id int
)
RETURNS TABLE
AS
RETURN
(
WITH cte
AS
(
SELECT
anchor.Id,
anchor.ParentId,
anchor.ProductCount
FROM
testTable anchor
WHERE anchor.id = @id
UNION ALL
SELECT
child.Id,
child.ParentId,
child.ProductCount
FROM
cte
INNER JOIN testTable child on child.parentid = cte.id
)
SELECT SUM(ProductCount) AS Total from CTE
)
GO

Lo que resulta en:

de la tabla fuente

Disculpas por el formateo.

Answer 4

No pude encontrar una buena respuesta basada en T-SQL basada en conjuntos, pero sí se me ocurrió una respuesta: la tabla temporal imita la estructura de la tabla. La variable de tabla es una tabla de trabajo.

--Initial table
CREATE TABLE #products (Id INT, ParentId INT, NAME VARCHAR(255), ProductCount INT)
INSERT INTO #products
( ID,ParentId, NAME, ProductCount )
VALUES ( 1,-1,''Cars'',0),(2,-1,''Bikes'',1),(3,1,''Ford'',10),(4,3,''Mustang'',7),(5,3,''Focus'',4)
--Work table
DECLARE @products TABLE (ID INT, ParentId INT, NAME VARCHAR(255), ProductCount INT, ProductCountIncludingChildren INT)
INSERT INTO @products
( ID ,
ParentId ,
NAME ,
ProductCount ,
ProductCountIncludingChildren
)
SELECT Id ,
ParentId ,
NAME ,
ProductCount,
0
FROM #products
DECLARE @i INT
SELECT @i = MAX(id) FROM @products
--Stupid loop - loops suck
WHILE @i > 0
BEGIN
WITH cte AS (SELECT ParentId, SUM(ProductCountIncludingChildren) AS ProductCountIncludingChildren FROM @products GROUP BY ParentId)
UPDATE p1
SET p1.ProductCountIncludingChildren = p1.ProductCount + isnull(p2.ProductCountIncludingChildren,0)
FROM @products p1
LEFT OUTER JOIN cte p2 ON p1.ID = p2.ParentId
WHERE p1.ID = @i
SELECT @i = @i - 1
END
SELECT *
FROM @products
DROP TABLE #products

Me interesaría mucho ver un mejor enfoque basado en conjuntos. El problema con el que me encontré es que cuando utilizas los recursivos, comienzas con el padre y trabajas para conseguir a los niños; esto no funciona para obtener una suma a nivel de los padres. Tendrías que hacer algún tipo de recurso recursivo hacia atrás.

Answer 5

Puede usar un CTE recursivo en el que en la parte delimitadora obtiene todas las filas y en la parte recursiva se une para obtener las filas secundarias. Recuerde el Id original con alias RootID de la parte delimitada y sume el agregado en la consulta principal agrupada por RootID .

SQL Fiddle

Configuración del esquema MS SQL Server 2012 :

create table T
(
Id int primary key,
ParentId int,
Name varchar(10),
ProductCount int
);
insert into T values
(1, -1, ''Cars'', 0),
(2, -1, ''Bikes'', 1),
(3, 1, ''Ford'', 10),
(4, 3, ''Mustang'', 7),
(5, 3, ''Focus'', 4);
create index IX_T_ParentID on T(ParentID) include(ProductCount, Id);

Consulta 1 :

with C as
(
select T.Id,
T.ProductCount,
T.Id as RootID
from T
union all
select T.Id,
T.ProductCount,
C.RootID
from T
inner join C
on T.ParentId = C.Id
)
select T.Id,
T.ParentId,
T.Name,
T.ProductCount,
S.ProductCountIncludingChildren
from T
inner join (
select RootID,
sum(ProductCount) as ProductCountIncludingChildren
from C
group by RootID
) as S
on T.Id = S.RootID
order by T.Id
option (maxrecursion 0)

Results :

| ID | PARENTID | NAME | PRODUCTCOUNT | PRODUCTCOUNTINCLUDINGCHILDREN |
|----|----------|---------|--------------|-------------------------------|
| 1 | -1 | Cars | 0 | 21 |
| 2 | -1 | Bikes | 1 | 1 |
| 3 | 1 | Ford | 10 | 21 |
| 4 | 3 | Mustang | 7 | 7 |
| 5 | 3 | Focus | 4 | 4 |