entre Desarrolladores

Recibe ayuda de expertos

Registrate y pregunta

Es gratis y fácil

Recibe respuestas

Respuestas, votos y comentarios

Vota y selecciona respuestas

Recibe puntos, vota y da la solución

Pregunta

3votos

Consulta de base de datos con totales con datos agrupados

Hola,
Tengo en una tabla unos datos relacionados y necesito traerlos en una consulta con el siguiente formato

+ Dato 1;TOTAL 1 ;TOTAL 2
++ Dato 1.1;VALOR 1;VALOR 2
++ Dato 1.2;VALOR 1;VALOR 2
++ Dato 1.3; SUBTOTAL 1; SUBTOTAL 2
+++ Dato 1.3.1;SUBVALOR 1; SUBVALOR 2
+++ Dato 1.3.2; SUBVALOR 1; SUBVALOR 2
+ Dato 2;TOTAL 1 ;TOTAL 2
++ Dato 2.1;VALOR 1;VALOR 2
++ Dato 2.2;VALOR 1;VALOR 2
++ Dato 2.3;VALOR 1;VALOR 2

En la tabla los datos tiene el siguiente formato:

Id;nombre_dato;jerarquía;valor1;valor2
1;Dato 1;NULL;0;0
2;Dato 1.1;1;10;15
3;Dato 1.2;1;0;10
4;Dato 1.3; 1;0;0
5;Dato 1.3.1;4;20;20
6;Dato 1.3.2;4;10;10
7;Dato 2;NULL;0 ;0
8;Dato 2.1;7;0;10
8;Dato 2.2;7;0;15
9;Dato 2.3;7;0;0

Lo primero tiene que mostrar es el dato que agrupa los otros datos con un total (por ej: Dato 1) y luego detallar cada subdato con sus valores.

Actualmente estoy haciendo esto con una función que carga cada dato que agrupa, luego carga los subdatos de cada grupo, calcula los totales y devuelve los datos ordenados. Pero como es difícil de mantener me gustaría saber si hay posibilidades de hacerlo de una manera mas fácil?

El resultado que necesito obtener es:

+ Dato 1;40;55 (Dato 1.1 + Dato 1.2 + Dato 1.3)
++ Dato 1.1;10;15
++ Dato 1.2;0;10
++ Dato 1.3;30;30 (Dato 1.3.1 + Dato 1.3.2)
+++ Dato 1.3.1;20;20
+++ Dato 1.3.2;10;10
+ Dato 2;0;25 (Dato 2.1 + Dato 2.2 + Dato 2.3)
++ Dato 2.1;0;10
++ Dato 2.2;0;15
++ Dato 2.3;0;0

Esto se puede hacer por consulta?

Saludos

1 Respuesta

2votos

Leonardo-Tadei Puntos227320

Una query con esta forma te devuelve los totales y subtotales que et hacen falta:

SELECT *, ( SELECT (SUM(T.valor1)
FROM Tabla  AS T WHERE T.jerarquia <= Tabla.jerarquia ) AS total1,
( SELECT (SUM(T.valor2)
FROM Tabla  AS T WHERE T.jerarquia <= Tabla.jerarquia ) AS total2,
FROM Tabla ORDER BY jerarquia

No tengo a mano una DB para probarlo, pero sintaxis más o menos, esta es la query. La idea es que sumes todos los valores de la jararquía actual y de todas las jerarquías superiores.

Saludos!

0voto

Leonardo-Tadei comentado

Hola @Wilson,

para la proyección que mostrás, en que los nodos totalizan la suma de solamente sus nodos hijo, sería así:

SELECT id, nombre_dato, jerarquia,
IFNULL( ( SELECT (SUM( T.valor1 ))
FROM Totales  AS T WHERE T.jerarquia = Totales.id ), valor1) AS total1,
IFNULL( ( SELECT (SUM( T.valor2 ))
FROM Totales  AS T WHERE T.jerarquia = Totales.id ), valor2) AS total2
FROM Totales

Estoy sumando los totales de la subjerarquia, salvo que no exsistan, en cuyo caso es el valor de la fila.

Saludos cordiales!

0voto

Wilson comentado

funciona casi perfecto, muchas gracias.
lo unico que no hace bien es sumar el total de las subsubjerarquia, como el Dato 1.3 que tiene el Dato 1.3.1 y 1.3.2

el resultado que muestra esta consulta es:

+ Dato 1;10;25 (Dato 1.1 + Dato 1.2 + Dato 1.3; Esta mal, no suma el total de Dato 1.3 por la subsubjerarquia. tiene que ser 40 y 55 los totales)
++ Dato 1.1;10;15
++ Dato 1.2;0;10
++ Dato 1.3;30;30 (Dato 1.3.1 + Dato 1.3.2 = OK)
+++ Dato 1.3.1;20;20
+++ Dato 1.3.2;10;10
+ Dato 2;0;25 (Dato 2.1 + Dato 2.2 + Dato 2.3 = OK)
++ Dato 2.1;0;10
++ Dato 2.2;0;15
++ Dato 2.3;0;0

0voto

Leonardo-Tadei comentado

Hola @Wilson,

yo ejecuto esa query y sí obtengo 40 y 55 como suma de Dato 1:

id  nombre_dato     jerarquia   total1  total2
1   Dato 1  NULL    40  55
2   Dato 1.1    1   10  15
3   Dato 1.2    1   0   10
4   Dato 1.3    1   30  30
5   Dato 1.3.1  4   20  20
6   Dato 1.3.2  4   10  10
7   Dato 2  NULL    0   25
8   Dato 2.1    7   0   10
9   Dato 2.2    7   0   15
10  Dato 2.3    7   0   0

La tabla es:

CREATE TABLE IF NOT EXISTS `Totales` (
  `id` int(11) NOT NULL DEFAULT '0',
  `nombre_dato` varchar(40) COLLATE utf8_spanish2_ci NOT NULL,
  `jerarquia` int(11) DEFAULT NULL,
  `valor1` int(11) NOT NULL,
  `valor2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;

INSERT INTO `Totales` (`id`, `nombre_dato`, `jerarquia`, `valor1`, `valor2`) VALUES
(1, 'Dato 1', NULL, 0, 0),
(2, 'Dato 1.1', 1, 10, 15),
(3, 'Dato 1.2', 1, 0, 10),
(4, 'Dato 1.3', 1, 30, 30),
(5, 'Dato 1.3.1', 4, 20, 20),
(6, 'Dato 1.3.2', 4, 10, 10),
(7, 'Dato 2', NULL, 0, 0),
(8, 'Dato 2.1', 7, 0, 10),
(9, 'Dato 2.2', 7, 0, 15),
(10, 'Dato 2.3', 7, 0, 0);

Debés de haber escrito algo mal cuando adaptaste la query a tus tablas, o los datos no deben coincidir...

0voto

Wilson comentado

Hola @Leonardo-Tadei
estuve probando esa consulta de nuevo por si se me habia pasado algo y no funciona bien por lo siguiente:
la consulta lo que suma es la jerarquia inmediata, no la siguiente por ej totaliza la jerarquia 1 y no impluye la cuatro. el hecho de que de 40 y 55 es por que el dato 1.3 tiene 30 y 30 no por que sume tambien dato 1.3.1 y 1.3.2.
por ejemplo si cambio los valores a Dato 1.3.1 y 1.3.2 y no cambio el valor de 1.3 igual me ba a seguir dando 40 y 55 pero me deberia cambiar el total del dato 1 en este caso.

Por esto me dio cuenta que para usar la consulta (que esta bien) deberia hacer lo siguiente:
1- al guardar o actualizar un dato tambien deberia actualizar los totales de la jerarquia superior para cuando use la consulta me de bien
o
2- hacer el calculo con una funcion recursiva, que es dificil de mantener

voy a probar el rendimiento que tiene con un trigger de este tipo y comento los resultado

0voto

Leonardo-Tadei comentado

Pero Wilson!

a tu almacenamiento le falta información para poder hacer una query que devuelva totales con ilimitados niveles de anidamiento...

El problema es que, por ejemplo, el registro con ID = 5 no tiene información suficiente para determinar que debe participar de la suma del registro con ID = 1 en vez de por ejemplo del registro con ID = 7, suiendo ambos nodos de igual nivel en la jerarquía.

La solución es como sugerís en 1, crear un trigger que actualice el subtotal parcial del nodo superior. Es mucho más eficiente y simple de escribir que una Store Procedure recursivo (en caso que el RDBMS lo soporte).

Saludos cordiales!

Por favor, accede o regístrate para responder a esta pregunta.

Otras Preguntas y Respuestas


...

Bienvenido a entre Desarrolladores, donde puedes realizar preguntas y recibir respuestas de otros miembros de la comunidad.

Conecta