Creación de Query para reporte Estadístico (AYUDA)
Publicado por oflodrih (1 intervención) el 04/07/2012 20:26:47
Buenos dias amigos en este mi primer mensaje.
Mi asunto es el siguiente: Requiero extraer de una tabla totales y cantidades con ciertos calculos. Estoy utilizando selects anidados con UNION, pero me consegui que existe un limite en MySQL para estas instrucciones. Mi proposito es generar el query y luego llevarlo a una tabla temporal para almacenar los resultados en forma lineal y no uno por registro como me los trae el query con el UNION:
He aqui el Query:
$query = "SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
COUNT(*) AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 2
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
COUNT(*) AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 4
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.fdespims) AS anno, MONTH(a.fdespims) AS num_mes, MONTHNAME(a.fdespims) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
COUNT(*) AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 2
AND a.`delete` = 0
AND a.fdespims != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.fdespims) AS anno, MONTH(a.fdespims) AS num_mes, MONTHNAME(a.fdespims) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
COUNT(*) AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 4
AND a.`delete` = 0
AND a.fdespims != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
(SUM(to_days(curdate()) - to_days(a.fdb)) / COUNT(*)) AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
(SUM(to_days(curdate()) - to_days(a.frd)) / COUNT(*)) AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
(SUM((to_days(curdate()) - to_days(a.fdb) - (to_days(curdate()) - to_days(a.frd)))) / COUNT(*)) AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0 AND a.frd !=0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
COUNT(*) AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0 AND a.frd !=0
AND (to_days(curdate()) - to_days(a.fdb)) > 80
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
COUNT(*) AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0 AND a.frd !=0
AND (to_days(curdate()) - to_days(a.fdb) - (to_days(curdate()) - to_days(a.frd))) > 15
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
COUNT(*) AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.condicion = 0
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
COUNT(*) AS reparados
FROM inventario a, lineas b, reparaciones c
WHERE b.id = a.linea
AND a.id = c.idcontenedor
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4 ORDER BY 1, 2, 4";
$result = mysql_query($query, $conexion) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalRows = mysql_num_rows($result);
Me da el siguiente error:
Request-URI Too Large
The requested URL's length exceeds the capacity limit for this server.
Y esta es la estructura de la tabla temporal:
CREATE TABLE IF NOT EXISTS `estadisticas` (
`anno` int(4) NOT NULL,
`num_mes` int(2) NOT NULL,
`mes` char(20) NOT NULL,
`linea` char(100) NOT NULL,
`patio` char(15) NOT NULL,
`tipo20I` int(5) NOT NULL,
`tipo40I` char(5) NOT NULL,
`totalI` int(7) NOT NULL,
`tipo20E` int(5) NOT NULL,
`tipo40E` int(5) NOT NULL,
`totalE` int(7) NOT NULL,
`tt_ven` int(8) NOT NULL,
`tt_patio` int(8) NOT NULL,
`tt_import` int(8) NOT NULL,
`may_80` int(8) NOT NULL,
`upd_perf` int(8) NOT NULL,
`may_15` int(8) NOT NULL,
`dannados` int(8) NOT NULL,
`reparados` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Como dije anteriormente mi proposito es llevar el resultado del query que me trae varios registros para llevarlo a un solo registro en esta tabla y manejar con mayor eficiencia el reporte estadistico.
Agradecido por vuestra ayuda, si alguien tiene una mejor idea de realizar este query para el proposito deseado.
Mi asunto es el siguiente: Requiero extraer de una tabla totales y cantidades con ciertos calculos. Estoy utilizando selects anidados con UNION, pero me consegui que existe un limite en MySQL para estas instrucciones. Mi proposito es generar el query y luego llevarlo a una tabla temporal para almacenar los resultados en forma lineal y no uno por registro como me los trae el query con el UNION:
He aqui el Query:
$query = "SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
COUNT(*) AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 2
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
COUNT(*) AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 4
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.fdespims) AS anno, MONTH(a.fdespims) AS num_mes, MONTHNAME(a.fdespims) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
COUNT(*) AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 2
AND a.`delete` = 0
AND a.fdespims != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.fdespims) AS anno, MONTH(a.fdespims) AS num_mes, MONTHNAME(a.fdespims) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
COUNT(*) AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b, tequipos d
WHERE b.id = a.linea
AND d.id = a.tcont
AND SUBSTR(d.tipo,1,1) = 4
AND a.`delete` = 0
AND a.fdespims != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
(SUM(to_days(curdate()) - to_days(a.fdb)) / COUNT(*)) AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
(SUM(to_days(curdate()) - to_days(a.frd)) / COUNT(*)) AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
(SUM((to_days(curdate()) - to_days(a.fdb) - (to_days(curdate()) - to_days(a.frd)))) / COUNT(*)) AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0 AND a.frd !=0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
COUNT(*) AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0 AND a.frd !=0
AND (to_days(curdate()) - to_days(a.fdb)) > 80
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
COUNT(*) AS may15,
0 AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.`delete` = 0
AND a.c = 0
AND a.fdb != 0 AND a.frd !=0
AND (to_days(curdate()) - to_days(a.fdb) - (to_days(curdate()) - to_days(a.frd))) > 15
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
COUNT(*) AS dannados,
0 AS reparados
FROM inventario a, lineas b
WHERE b.id = a.linea
AND a.condicion = 0
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4
UNION
SELECT YEAR(a.frd) AS anno, MONTH(a.frd) AS num_mes, MONTHNAME(a.frd) AS mes, b.nombre AS linea,
0 AS cant20I,
0 AS cant40I,
0 AS cant20E,
0 AS cant40E,
0 AS ttven,
0 AS ttpatio,
0 AS ttimport,
0 AS may80,
0 AS updperf,
0 AS may15,
0 AS dannados,
COUNT(*) AS reparados
FROM inventario a, lineas b, reparaciones c
WHERE b.id = a.linea
AND a.id = c.idcontenedor
AND a.`delete` = 0
AND a.c = 0
AND a.frd != 0
GROUP BY 1, 2, 4 ORDER BY 1, 2, 4";
$result = mysql_query($query, $conexion) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalRows = mysql_num_rows($result);
Me da el siguiente error:
Request-URI Too Large
The requested URL's length exceeds the capacity limit for this server.
Y esta es la estructura de la tabla temporal:
CREATE TABLE IF NOT EXISTS `estadisticas` (
`anno` int(4) NOT NULL,
`num_mes` int(2) NOT NULL,
`mes` char(20) NOT NULL,
`linea` char(100) NOT NULL,
`patio` char(15) NOT NULL,
`tipo20I` int(5) NOT NULL,
`tipo40I` char(5) NOT NULL,
`totalI` int(7) NOT NULL,
`tipo20E` int(5) NOT NULL,
`tipo40E` int(5) NOT NULL,
`totalE` int(7) NOT NULL,
`tt_ven` int(8) NOT NULL,
`tt_patio` int(8) NOT NULL,
`tt_import` int(8) NOT NULL,
`may_80` int(8) NOT NULL,
`upd_perf` int(8) NOT NULL,
`may_15` int(8) NOT NULL,
`dannados` int(8) NOT NULL,
`reparados` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Como dije anteriormente mi proposito es llevar el resultado del query que me trae varios registros para llevarlo a un solo registro en esta tabla y manejar con mayor eficiencia el reporte estadistico.
Agradecido por vuestra ayuda, si alguien tiene una mejor idea de realizar este query para el proposito deseado.
Valora esta pregunta
0