[SOLUCIONADO] Vista de datos por año para cobertura sig

horacionomade · #1 (**permalink**) 04/12/2015, 11:33

Hola gente, cómo va??!!
Años leyéndolos para aprender y hoy hago la 1º consulta:
Tengo la siguiente tabla con datos de expedientes:

# reg_id, Num_Expediente, anio, Fecha_Entrada, Provincia, idjurisbahra, Instituticion, Campo, Nom_Titulo, Observa, Nom_resp_info, Fecha_Salida
1, 1271/15, 2015, 09/08/2000, BUENOS AIRES, 6, Ministerio de Educación de la provincia de Buenos Aires, Formación profesional, Cursos de formación profesional, no es competencia de esta Área la evaluación curricular de las certificaciones solicitadas en los 16 cuerpos del presente expediente N° 12711/15, ,
2, 11141/13, 2013, 14/02/2003, CABA, 2, INSTITUTO SUPERIOR DE ENSEÑANZA INTERCAMBIO (A-1289), PSICOLOGIA SOCIAL, "TECNICATURA SUPERIO EN PSICOLOGIA SOCIAL", , ,
3, 149345-04-8/02, 2002, 07/06/2005, BUENOS AIRES , 6, DIRECCIÓN DE CAPACITACIÓN DE TÉCNICOS DE LA SALUD DE LA PROVINCIA DE BUENOS AIRES, SALUD, TECNICATURA SUPERIOR EN TECNOLOGÍA EN SALUD CON ESPECIALIDAD EN LABORATORIO DE ANÁLISIS CLÍNICOS- TECNICATURA SUPERIOR EN TECNOLOGÍA EN SALUD CON ESPECIALIDAD EN INSTRUMENTACIÓN QUIRÚRGICA- TECNICATURA SUPERIOR EN TECNOLOGÍA EN SALUD CON ESPECIALIDAD EN RADIOLOGÍA- TECNICATURA SUPERIOR EN TECNOLOGÍA EN SALUD CON ESPECIALIDAD EN HISTOLOGÍA-, , ,
4, 8591/08, 2008, 17/08/2005, MISIONES, 54, INSTITUTO CRISTO REY, TURISMO, "TECNICATURA EN GESTION Y DESARROLLO TURISTICO", , ,
5, 160/06, 2006, 20/05/2006, CABA, 2, ESCUELA DE POLICIA"COMISARIO INSPECTOR EDUARDO VICTORIANO TARET", SEGURIDAD, TCNICO SUPERIOR EN SEGURIDAD PULICA, , ,
6, 1473539/05, 2005, 04/06/2006, SALTA, 66, ISP"JEAN PIAGET", SALUD, FONOAUDIOLOGO, , ,
7, 1061266/06, 2006, 21/07/2006, SALTA, 66, INSTITUTO SUPERIOR DE FORMACIÓN DOCENTE N° 6010, SALUD, TECNICATURA SUPERIOR EN LABORATORIO CON ORIENTACIÓN EN ENFERMEDADES TROPICALES Y ENDÉMICAS, , ,
8, 843/06, 2006, 28/07/2006, CABA, 2, GOBIERNO DE LA CIUDAD DE BUENOS AIRES-SECRETARIA DE EDUCACIÓN-SUBSECRETARIA DE EDUCACIÓN, SALUD ( ENTRE OTROS), TRADUCTORA DO TÉCNICO CIENTÍFICO-LITERARIO EN IDIOMA INGLES- TECNICATURA SUPERIOR EN ADMINISTRACIÓN DE SERVICIOS DE SALUD- ENFERMERÍA PROFESIONAL- TECNICATURA SUPERIOR EN SEGURIDAD AMBIENTAL- TECNICATURA SUPERIOR EN GUÍA DE TURISMO CON ESPECIALIZACIÓN EN LA CIUDAD DE BUENOS AIRES - TECNICATURA EN RECREACIÓN., , ,
9, 1196753/06, 2006, 15/08/2006, RIO NEGRO, 62, IT.ALTA CAPACITACION, SALUD, TECNICO EN PROTESIS DENTAL, , ,
10, 81/06, 2006, 25/11/2006, RIO NEGRO, 62, INSTITUTO SUPERIOR PATAGONICO A-023, HOTELERIA - TURISMO, TECNICATURA SUPERIOR EN ADMINISTRACION Y GESTION HOTELERA"-"ORGANIZACIÓN DE EVENTOS"- "TECNICATURA SUPERIOR EN TURISMO Y HOSPITALIDAD"- "ESPECIALISTA EN ARTE CULINARIO- CHEF-", , , SE REMITE AL INET- NO CORRESPONDE SU EVALUACION EN ESTA DIRECCION

Necesito armar una cobertura para mostrar la cantidad de expedientes, procesador por año y provincia, para hacer una serie de mapas que muestren la evolución.
He probado con subconsultas y con vistas y no he llegado a buen puerto, alguien me podría orientar.

Desde ya mil gracias!!

Libras · #2 (**permalink**) 04/12/2015, 11:35

y que llevas de la consulta? cuales han sido tus pruebas?

gnzsoloyo · #3 (**permalink**) 04/12/2015, 11:43

Por lo pronto, si tienes una tabla con todo eso, como mínimo pare ser una tabla desnormalizada, lo que sería un error de diseño muy grave.
Por otro lado...

Cita:

Necesito armar una cobertura para mostrar la cantidad de expedientes, procesador por año y provincia, para hacer una serie de mapas que muestren la evolución.
He probado con subconsultas y con vistas y no he llegado a buen puerto, alguien me podría orientar.

Ok... Defineme "cobertura", porque la verdad no tengo ni idea de a qué te refieres.

horacionomade · #4 (**permalink**) 04/12/2015, 12:27

Hola gnzsoloyo, Gracias por responder
Cubertura, es un archivo de datos georreferenciados, puede ser un csv, por ejemplo:
# Provincia, 2005
BUENOS AIRES, 3
SALTA, 1

Este lo hago a partir de un query simple con count, más necesitaría obtener en el mismo csv los datos de los años 2006, 2007, 2008, etc. para que el programa qgis me los pueda levantar y poder representarlos en un mapa.

Necesitaría una salida como:

# Provincia,2005,2006,etc.
ESPAÑA, 6,,
CABA, 69,3
BUENOS AIRES, 31,56
CATAMARCA, 3,7
CORDOBA, 12,5
CORRIENTES, 68,23
CHACO, 15,,
CHUBUT, 6,2
JUJUY, 89,6
LA PAMPA, 12,9
LA RIOJA, 2,5
MENDOZA, 38,12
MISIONES, 42,7
NEUQUEN, 45,3
RIO NEGRO, 48,3
SALTA, 4,,
SAN JUAN, 29,2
SAN LUIS, 1,,
SANTA CRUZ, 10,54
SANTA FE, 23,87
TUCUMAN, 17,23
NACION, 2,45
TIERRA DEL FUEGO, ANTARTIDA E ISLAS DEL ATLANTICO SUR, 10,,

Me explico?

gnzsoloyo · #5 (**permalink**) 04/12/2015, 12:54

Mas o menos se va entendiendo.
Ahora bien, ¿Cual es la duda concreta?

Postea la query que haces, la estructura de tu base y explica claramente cuál es el problema a resolver. La descripción que haces es algo vaga e imprecisa.
En Bases de Datos tendemos a ser muy concretos en las cosas.

horacionomade · #6 (**permalink**) 04/12/2015, 13:29

Ok, disculpas!!

La tabla es:

Código MySQL:

Ver originalCREATE TABLE IF NOT EXISTS `territor_pgestionbdtmp`.`expedientes_historicos` (
  `reg_id` INT(11) NOT NULL,
  `Num_Expediente` VARCHAR(34) NULL DEFAULT NULL,
  `anio` YEAR NULL DEFAULT NULL,
  `Fecha_Entrada` VARCHAR(16) NULL DEFAULT NULL,
  `Provincia` VARCHAR(53) NULL DEFAULT NULL,
  `idjurisbahra` INT(4) NULL DEFAULT NULL,
  `Instituticion` VARCHAR(93) NULL DEFAULT NULL,
  `Campo` VARCHAR(56) NULL DEFAULT NULL,
  `Nom_Titulo` VARCHAR(813) NULL DEFAULT NULL,
  `Observa` MEDIUMTEXT NULL DEFAULT NULL,
  `Nom_resp_info` VARCHAR(10) NULL DEFAULT NULL,
  `Fecha_Salida` VARCHAR(65) NULL DEFAULT NULL,
  PRIMARY KEY (`reg_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

El query que estoy usando es:

Código SQL:

Ver originalSELECT Provincia, COUNT(*) AS '2005'
FROM territor_pgestionbdtmp.expedientes_historicos
WHERE anio= 2005
GROUP BY idjurisbahra

Necesitaría sacar los otros años y jurisdicciones a partir de una sola consulta.

Mil gracias!!!

gnzsoloyo · #7 (**permalink**) 04/12/2015, 13:44

Esa sola consulta, así como la planteas, está haciendo "desaparecer" los datos que quieres de los años, desde el momento en que no estás agrupando por año...
Tienes que modificarla.

Suponiendo que anio es la que contiene el numero del año sería:

Código MySQL:

Ver originalSELECT Provincia,anio, COUNT(*) TotlaPOrPeriodo
FROM territor_pgestionbdtmp.expedientes_historicos
GROUP BY idjurisbahra, anio

Por cierto, poner una FECHA en un campo VARCHAR es un error de esos calamitosos... una fecha es un DATE, DATETIME o un TIMESTAMP, pero nunca es VARCHAR

horacionomade · #8 (**permalink**) 04/12/2015, 15:26

IMPRESIONANTE!!!!

Hay alguna manera de que salgan los años como columnas y las jurisdicciones como filas?

VARCHAR: si es una tabla que migré de una planilla de cálculo y no le dediqué tiempo, más por ese campo no necesito filtrar.
Mil disculpas por la brutalidad ;-D

Otra pregunta, que libro/tutorial me recomendas sobre mysql? Es apasionante!!!!

gnzsoloyo · #9 (**permalink**) 04/12/2015, 16:20

Cita:

Hay alguna manera de que salgan los años como columnas y las jurisdicciones como filas?

Eso se llama tabla inversa, y no, no hay una forma fácil de que puedas hacerlo. Se recomienda hacerlo por programación y no por cosultas en SQL:
Algunos DBMS cuentan con capacidades especiales para hacer ese tipo de consultas (CUBE, por ejemplo), pero no son para principiantes, y en general la tarea se realiza con aplicativos específicos dedicados al BI y minería de datos.

En cuanto a un tutorial o libro, es más conocimiento y práctica de SQL que otra cosa.
DE todos modos mira el lik que aparece en este post: http://www.forosdelweb.com/f86/faqs-...3/#post4414201, te puede ayudar a orientar y experimentar con soluciones.

horacionomade · #10 (**permalink**) 04/12/2015, 18:16

OOppss!!! si en otros casos la inversión la hago con funciones de php, más pensé que desde mysql se podía hacer más simplemente.
Y con una tabla temporal, será mucho lío?

Mil gracias Gonzalo!!!! tu aporte es oro!!!
Quedo a tu disposición!!!

Nos leemos!!

horacionomade · #11 (**permalink**) 04/12/2015, 18:31

"Tabla inversa" (filas a columnas) acá encontré una forma de hacerla:
http://www.iteramos.com/pregunta/44498/mysql---filas-a-columnas

Nos leemos!!

horacionomade · #12 (**permalink**) 05/12/2015, 09:56

Gracias al invaluable aporte y acompañamiento de gnzsoloyo ("... eso se llama tabla inversa") encontré la solución a mi problema, con el siguiente query, las ordenes "view", "case when" y "sum" logramos armar una salida csv para incorporar a una vista de un sistema de información geográfico!!

Mil gracias!!! GONZALO!!!!

Código MySQL:

Ver original/*Invertir filas en columnas*/
create OR REPLACE view expedientes_historial as (
 
    SELECT Provincia,anio, COUNT(*) TotlaPOrPeriodo
    FROM territor_pgestionbdtmp.expedientes_historicos
    GROUP BY idjurisbahra, anio
);
 
create OR REPLACE view expedientes_historial_inversa as (
  select
    expedientes_historial.*,
    case when anio = "2002" then TotlaPOrPeriodo end as '2002',
    case when anio = "2003" then TotlaPOrPeriodo end as '2003',
    case when anio = "2005" then TotlaPOrPeriodo end as '2005',
    case when anio = "2006" then TotlaPOrPeriodo end as '2006',
    case when anio = "2007" then TotlaPOrPeriodo end as '2007',
    case when anio = "2008" then TotlaPOrPeriodo end as '2008',
    case when anio = "2009" then TotlaPOrPeriodo end as '2009',
    case when anio = "2010" then TotlaPOrPeriodo end as '2010',
    case when anio = "2011" then TotlaPOrPeriodo end as '2011',
    case when anio = "2012" then TotlaPOrPeriodo end as '2012',
    case when anio = "2013" then TotlaPOrPeriodo end as '2013',
    case when anio = "2014" then TotlaPOrPeriodo end as '2014',
    case when anio = "2015" then TotlaPOrPeriodo end as '2015'
  from expedientes_historial
order by Provincia, anio
);
/*
Select * from expedientes_historial_inversa
*/
create OR REPLACE view expedientes_historial_cosmetica as (
  select
    Provincia,
    sum(expedientes_historial_inversa.2002) as '2002',
    sum(expedientes_historial_inversa.2003) as '2003',
    sum(expedientes_historial_inversa.2005) as '2005',
    sum(expedientes_historial_inversa.2006) as '2006',
    sum(expedientes_historial_inversa.2007) as '2007',
    sum(expedientes_historial_inversa.2008) as '2008',
    sum(expedientes_historial_inversa.2009) as '2009',
    sum(expedientes_historial_inversa.2010) as '2010',
    sum(expedientes_historial_inversa.2011) as '2011',
    sum(expedientes_historial_inversa.2012) as '2012',
    sum(expedientes_historial_inversa.2013) as '2013',
    sum(expedientes_historial_inversa.2014) as '2014',
    sum(expedientes_historial_inversa.2015) as '2015'
  from expedientes_historial_inversa
  group by Provincia
);
 
Select * from expedientes_historial_cosmetica
 
/*FIN*/

Mil Gracias Libras!!! Gonzalo ya me orientó más que bien!!

Nos leemos!!

gnzsoloyo · #13 (**permalink**) 05/12/2015, 10:42

Es una solución. No te la sugerí porque tiene muchísimos problemas, ente ellos de performance.
Además genera un exceso de acoplamiento entre aplicación y base, sin contar con cierta rigidez en el resultado, ya que tienes que harcodear toda la salida.
En realidad, si lo meditas puedes lograr exactamente lo mismo en programación, y hasta es posible que funcione más rápido.

horacionomade · #14 (**permalink**) 05/12/2015, 14:41

Si, es verdad, tarda un par de segundos en responder.
Vos decís que si hago las consultas celda por celda con php la performance es mejor?
Es bueno saberlo!!!

gnzsoloyo · #15 (**permalink**) 05/12/2015, 14:57

No dije consultas...
Si ya tienes la tabla resultado, es mucho más fácil y eficiente mostrarla invertida en programación que lograr una consulta en SQL eficiente y optimizada.

Además eso me refiero.

Al menos en mi experiencia, logró con una docena de líneas en cualquier lenguaje lo mismo que con toda esa query.

horacionomade · #16 (**permalink**) 05/12/2015, 16:04

Perfecto!!! mil gracias!!!!!