none
Como sumar importe de elementos hijo y aplicarlo al elemento padre en forma de escalera en una consulta RRS feed

  • Pregunta

  • Buenas a todos, mi problema se encuentra en que en la siguiente consulta tengo los importes que se le han aplicado a los elementos hijos del arbol. Mi problema está en que necesito que el elemento padre tenga la suma de los importes de todos sus elementos hijos. La funcionalidad radica en que el valor de la columna [PADRE] es un valor de la columna [ID_COG] así puedo establecer la relación padre-hijo

    Ejemplifico la consulta, lo que obtengo y lo que deseo obtener en la siguiente imágen. Muchas gracias.

    miércoles, 4 de julio de 2018 22:09

Respuestas

  • No tenemos una manera de declarar eso en T-SQL (ni en ANSI-SQL), indicandole al motor que sumarize siguiendo una jerarquia.

    Una manera comun de lograr esto con codigo SQL en los ambientes de Data Warehouse es usando una tabla donde se tenga la relacion de cada padre con todos sus descendientes (bridge table), y que en la teoria de grafos se conoce como Transitive Closure (en este caso adicionando la relacion de cada fila con si misma).

    Usando la data de ejemplo quedaria algo asi como:

    id padre
    ==== =====

    1 1 11 1 12 1 111 1 112 1 113 1 114 1 121 1 122 1 11101 1 11201 1 11301 1 11302 1 12101 1 12102 1 12103 1 12201 1 12202 1 11 11 111 11 112 11 113 11 114 11 11101 11 11201 11 11301 11 11302 11 12 12 121 12 122 12 12101 12 12102 12 12103 12 12201 12 12202 12 111 111 11101 111 112 112 11201 112 113 113 11301 113 11302 113 114 114 121 121 12101 121 12102 121 12103 121 122 122 12201 122 12202 122 11101 11101 11201 11201 11301 11301 11302 11302 12101 12101 12102 12102 12103 12103 12201 12201 12202 12202


    Ahora podemos unir (join) este resultado con los importes usando la columna [id] y agrupar por padre.

    DECLARE @cat_cog table (id int NOT NULL, padre int null, descripcion varchar(100));
    DECLARE @hojas_presupuesto table (id int NOT NULL, presupuesto float);
    
    insert into @cat_cog (id, padre, descripcion) 
    VALUES 
    (1, NULL,'a'),
    (11, 1,'b'),
    (111, 11,'c'),
    (11101, 111,'d'),
    (112, 11,'e'),
    (11201, 112,'f'),
    (113, 11,'s'),
    (11301, 113,'g'),
    (11302, 113,'h'),
    (114, 11,'i'),
    (12, 1,'j'),
    (121, 12,'k'),
    (12101, 121,'l'),
    (12102, 121,'m'),
    (12103, 121,'n'),
    (122, 12,'o'),
    (12201, 122,'p'),
    (12202, 122,'q')
    ;
    
    insert into @hojas_presupuesto (id, presupuesto) 
    VALUES
    (111, 7443261.12),
    (11301, 195433157.76),
    (12102, 40727716.67),
    (12103, 3228105.26),
    (12201, 19052134.59),
    (12202, 6938459.99)
    ;
    
    WITH TC AS (
    SELECT
    	id,
    	id AS padre
    FROM
    	@cat_cog
    
    UNION ALL
    
    SELECT
    	C.id,
    	P.padre
    FROM
    	TC AS P
    	INNER JOIN
    	@cat_cog AS C
    	ON C.padre = P.id
    )
    , Agg AS (
    SELECT
    	TC.padre,
    	SUM(P.presupuesto) AS total_presupuesto
    FROM
    	TC
    	LEFT OUTER JOIN
    	@hojas_presupuesto AS P
    	ON P.id = TC.id
    GROUP BY
    	TC.padre
    )
    , H AS (
    SELECT
    	id,
    	descripcion,
    	1 AS lvl,
    	CAST(0 AS varbinary(900)) AS SortCol
    FROM
    	@cat_cog
    WHERE
    	padre IS NULL
    
    UNION ALL
    
    SELECT
    	C.id,
    	C.descripcion,
    	P.lvl + 1,
    	CAST(P.SortCol + CAST(ROW_NUMBER() OVER(
    	PARTITION BY C.padre
    	ORDER BY C.id
    	) AS binary(4)) AS varbinary(900))
    FROM
    	H AS P
    	INNER JOIN
    	@cat_cog AS C
    	ON P.id = C.padre
    )
    SELECT
    	REPLICATE('|', 8 * (H.lvl - 1)) + CONCAT(H.id, ' - ', H.descripcion) AS COG,
    	ISNULL(Agg.total_presupuesto, 0) AS presupuesto
    FROM
    	H
    	INNER JOIN
    	Agg
    	ON H.id = Agg.padre
    ORDER BY
    	H.SortCol
    ;
    GO
    
    /*
    
    
    COG	presupuesto
    1 - a	272822835.39
    ||||||||11 - b	202876418.88
    ||||||||||||||||111 - c	7443261.12
    ||||||||||||||||||||||||11101 - d	0
    ||||||||||||||||112 - e	0
    ||||||||||||||||||||||||11201 - f	0
    ||||||||||||||||113 - s	195433157.76
    ||||||||||||||||||||||||11301 - g	195433157.76
    ||||||||||||||||||||||||11302 - h	0
    ||||||||||||||||114 - i	0
    ||||||||12 - j	69946416.51
    ||||||||||||||||121 - k	43955821.93
    ||||||||||||||||||||||||12101 - l	0
    ||||||||||||||||||||||||12102 - m	40727716.67
    ||||||||||||||||||||||||12103 - n	3228105.26
    ||||||||||||||||122 - o	25990594.58
    ||||||||||||||||||||||||12201 - p	19052134.59
    ||||||||||||||||||||||||12202 - q	6938459.99
    
    */

    Se usaron tres CTEs que son TC, Agg y H.  La primera para calcular el Transitive Closure, la segunda para agregar y la tercera para formar la jerarquia de forma legible.  El query final une la jerarquia y el resultado de la agregacion.

    El resultado de calcular el Transitive Closure lo puedes materializar en una tabla y adicionar indices para soportar la union.

    Puedes leer mas al respecto en el ultimo libro escrito por Itzik Ben-Gan sobre T-SQL Querying.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Marcado como respuesta ÁlvaroLanda jueves, 5 de julio de 2018 16:43
    • Editado HunchbackMVP jueves, 5 de julio de 2018 16:44
    jueves, 5 de julio de 2018 13:11

Todas las respuestas

  • Hola AlvaroLanda:

    Escenario similar al tuyo, (como tenía parte de la estructura, no lo he plasmado idéntico pero casí).

    create table cat_capitulos_cog (id int identity (1,1), capitulo int, padre int, genero int, descripcion varchar(100))
    
    create table hojas_presupuesto (id int, presupuesto float)
    
    insert into cat_capitulos_cog (capitulo, genero,padre, descripcion) values 
    (1,1,0,'a'),
    (11,1,1,'b'),
    (111,1,11,'c'),
    (11101,1,111,'d'),
    (112,1,11,'e'),
    (11201,1,112,'f'),
    (113,1,11,'s'),
    (11301,1,113,'as'),
    (11302,1,113,'as'),
    (114,1,11,'sd'),
    (12,2,1,'sde'),
    (121,2,12,'ews'),
    (12101,2,121,'ews'),
    (12102,2,121,'ews'),
    (12103,2,121,'ews')
    insert into hojas_presupuesto (id, presupuesto) values
    (1,100.54),
    (1,200.21),
    (1,100.56),
    (11,12.23),
    (111,44.51),
    (11101,21.17),
    (11201,1.23),
    (113,1000),
    (11301,19041.15),
    (11302,1815),
    (114,132),
    (121,21223),
    (12101,123),
    (12102,23),
    (12103,21)

    Difiere un poco en el id, que a mi me quedo de la consulta anterior, por capitulo en la tabla c.

    SELECT o.id,
           o.capitulo,
           o.padre,
           o.descripcion,
           o.importe,
           SUM(o.importe) OVER(PARTITION BY o.padre) AS importePADRE
    FROM
    (
        SELECT c.id,
               c.capitulo,
               c.padre,
               c.descripcion,
               isnull(SUM(h.presupuesto), 0) AS importe
        FROM cat_capitulos_cog c
             LEFT JOIN hojas_presupuesto h ON c.capitulo = h.id
                                              OR h.id IS NULL
        GROUP BY c.capitulo,
                 c.descripcion,
                 c.id,
                 c.padre
    ) AS o;
    	
    

    Recojo la consulta en una tabla derivada, y le agrego un sumatorio y el punto de partición es la columna padre.

    Un saludo

    jueves, 5 de julio de 2018 5:52
  • No tenemos una manera de declarar eso en T-SQL (ni en ANSI-SQL), indicandole al motor que sumarize siguiendo una jerarquia.

    Una manera comun de lograr esto con codigo SQL en los ambientes de Data Warehouse es usando una tabla donde se tenga la relacion de cada padre con todos sus descendientes (bridge table), y que en la teoria de grafos se conoce como Transitive Closure (en este caso adicionando la relacion de cada fila con si misma).

    Usando la data de ejemplo quedaria algo asi como:

    id padre
    ==== =====

    1 1 11 1 12 1 111 1 112 1 113 1 114 1 121 1 122 1 11101 1 11201 1 11301 1 11302 1 12101 1 12102 1 12103 1 12201 1 12202 1 11 11 111 11 112 11 113 11 114 11 11101 11 11201 11 11301 11 11302 11 12 12 121 12 122 12 12101 12 12102 12 12103 12 12201 12 12202 12 111 111 11101 111 112 112 11201 112 113 113 11301 113 11302 113 114 114 121 121 12101 121 12102 121 12103 121 122 122 12201 122 12202 122 11101 11101 11201 11201 11301 11301 11302 11302 12101 12101 12102 12102 12103 12103 12201 12201 12202 12202


    Ahora podemos unir (join) este resultado con los importes usando la columna [id] y agrupar por padre.

    DECLARE @cat_cog table (id int NOT NULL, padre int null, descripcion varchar(100));
    DECLARE @hojas_presupuesto table (id int NOT NULL, presupuesto float);
    
    insert into @cat_cog (id, padre, descripcion) 
    VALUES 
    (1, NULL,'a'),
    (11, 1,'b'),
    (111, 11,'c'),
    (11101, 111,'d'),
    (112, 11,'e'),
    (11201, 112,'f'),
    (113, 11,'s'),
    (11301, 113,'g'),
    (11302, 113,'h'),
    (114, 11,'i'),
    (12, 1,'j'),
    (121, 12,'k'),
    (12101, 121,'l'),
    (12102, 121,'m'),
    (12103, 121,'n'),
    (122, 12,'o'),
    (12201, 122,'p'),
    (12202, 122,'q')
    ;
    
    insert into @hojas_presupuesto (id, presupuesto) 
    VALUES
    (111, 7443261.12),
    (11301, 195433157.76),
    (12102, 40727716.67),
    (12103, 3228105.26),
    (12201, 19052134.59),
    (12202, 6938459.99)
    ;
    
    WITH TC AS (
    SELECT
    	id,
    	id AS padre
    FROM
    	@cat_cog
    
    UNION ALL
    
    SELECT
    	C.id,
    	P.padre
    FROM
    	TC AS P
    	INNER JOIN
    	@cat_cog AS C
    	ON C.padre = P.id
    )
    , Agg AS (
    SELECT
    	TC.padre,
    	SUM(P.presupuesto) AS total_presupuesto
    FROM
    	TC
    	LEFT OUTER JOIN
    	@hojas_presupuesto AS P
    	ON P.id = TC.id
    GROUP BY
    	TC.padre
    )
    , H AS (
    SELECT
    	id,
    	descripcion,
    	1 AS lvl,
    	CAST(0 AS varbinary(900)) AS SortCol
    FROM
    	@cat_cog
    WHERE
    	padre IS NULL
    
    UNION ALL
    
    SELECT
    	C.id,
    	C.descripcion,
    	P.lvl + 1,
    	CAST(P.SortCol + CAST(ROW_NUMBER() OVER(
    	PARTITION BY C.padre
    	ORDER BY C.id
    	) AS binary(4)) AS varbinary(900))
    FROM
    	H AS P
    	INNER JOIN
    	@cat_cog AS C
    	ON P.id = C.padre
    )
    SELECT
    	REPLICATE('|', 8 * (H.lvl - 1)) + CONCAT(H.id, ' - ', H.descripcion) AS COG,
    	ISNULL(Agg.total_presupuesto, 0) AS presupuesto
    FROM
    	H
    	INNER JOIN
    	Agg
    	ON H.id = Agg.padre
    ORDER BY
    	H.SortCol
    ;
    GO
    
    /*
    
    
    COG	presupuesto
    1 - a	272822835.39
    ||||||||11 - b	202876418.88
    ||||||||||||||||111 - c	7443261.12
    ||||||||||||||||||||||||11101 - d	0
    ||||||||||||||||112 - e	0
    ||||||||||||||||||||||||11201 - f	0
    ||||||||||||||||113 - s	195433157.76
    ||||||||||||||||||||||||11301 - g	195433157.76
    ||||||||||||||||||||||||11302 - h	0
    ||||||||||||||||114 - i	0
    ||||||||12 - j	69946416.51
    ||||||||||||||||121 - k	43955821.93
    ||||||||||||||||||||||||12101 - l	0
    ||||||||||||||||||||||||12102 - m	40727716.67
    ||||||||||||||||||||||||12103 - n	3228105.26
    ||||||||||||||||122 - o	25990594.58
    ||||||||||||||||||||||||12201 - p	19052134.59
    ||||||||||||||||||||||||12202 - q	6938459.99
    
    */

    Se usaron tres CTEs que son TC, Agg y H.  La primera para calcular el Transitive Closure, la segunda para agregar y la tercera para formar la jerarquia de forma legible.  El query final une la jerarquia y el resultado de la agregacion.

    El resultado de calcular el Transitive Closure lo puedes materializar en una tabla y adicionar indices para soportar la union.

    Puedes leer mas al respecto en el ultimo libro escrito por Itzik Ben-Gan sobre T-SQL Querying.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Marcado como respuesta ÁlvaroLanda jueves, 5 de julio de 2018 16:43
    • Editado HunchbackMVP jueves, 5 de julio de 2018 16:44
    jueves, 5 de julio de 2018 13:11
  • Muchas gracias. Gracias a su ayuda pude obtener lo que buscaba. Gracias por la recomendación del libro de Itzik Ben-Gan. Si duda lo estudiaré a profundidad. Saludos
    jueves, 5 de julio de 2018 16:45
  • Mucas gracias, aprecio su ayuda, ha sido muy útil. Saludos
    jueves, 5 de julio de 2018 16:45