Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Oracle »

Consulta SQL avanzada

 Estas en el tema de Consulta SQL avanzada en el foro de Oracle en Foros del Web. Tengo la siguiente tabla, con los siguientes datos: Tabla: Unidades Cod. unidad - FDesde - FHasta - Tipo Unidad Map1- 11/03/2002 - 11/03/2002 - 9 ...
  #1 (permalink)  
Antiguo 30/11/2011, 05:43
 
Fecha de Ingreso: noviembre-2007
Mensajes: 15
Antigüedad: 17 años
Puntos: 0
Consulta SQL avanzada
Tengo la siguiente tabla, con los siguientes datos:

Tabla: Unidades

Cod. unidad-FDesde- FHasta - Tipo Unidad
Map1- 11/03/2002 - 11/03/2002 - 9
Map1 - 12/03/2002 - 23/06/2002 - 1
Map1 - 24/06/2002 - 06/08/2002 - 9
Map1 - 07/08/2002 - 09/09/2002 - 9
Map1 - 10/09/2002 - 18/09/2002 - 9

Lo que quiero es obtener la fdesde minima y la fhasta maxima de cada
tipo unidad y cada vez que hay un cambio en un tipo de unidad en cada fila.

Los datos que debería mostrar la SQL, son estos:

Cod. unidad- FDesde- FHasta- Tipo Unidad
Map1- 11/03/2002- 11/03/2002- 9
Map1- 12/03/2002- 23/06/2002- 1
Map1- 07/08/2002- 18/09/2002- 9

La sentencia SQL que yo he escrito es esta:

select max(codunidad), min(fdesde), max(fhasta), tipoUnidad from unidades
where codunidad like 'Map1'
group by tipoUnidad

pero muestar los datos erróneamente, como podría modificar la consulta, para que me muestre los datos como los de encima. Saludos y muchas gracias de antemano.
Última edición por xaji; 30/11/2011 a las 05:50
  #2 (permalink)  
Antiguo 30/11/2011, 10:32
 
Fecha de Ingreso: noviembre-2007
Ubicación: Argentina
Mensajes: 134
Antigüedad: 17 años
Puntos: 3
Respuesta: Consulta SQL avanzada
Hola Xaji,

Voy a copiar aqui, las dos consultas (primera parte y segunda parte) luego las aplicamos paso por paso al final, te parece?

PARTE 1 (Creacion de tabla, insercion de datos y creacion de tabla aux)

Código SQL: 
Ver original
  1. DROP TABLE CONSULTA_SQL_AVANZADA PURGE;
  2.  
  3. CREATE TABLE CONSULTA_SQL_AVANZADA
  4. (
  5. CODIGO VARCHAR2(10),
  6. FECHA_DESDE DATE,
  7. FECHA_HASTA DATE,
  8. TIPO_UNIDAD NUMBER
  9. ) TABLESPACE AUXILIAR PCTFREE 5 PCTUSED 95;
  10.  
  11. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('10.03.2002', 'DD.MM.YYYY'), TO_DATE('10.03.2002', 'DD.MM.YYYY'), 1);
  12. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('11.03.2002', 'DD.MM.YYYY'), TO_DATE('11.03.2002', 'DD.MM.YYYY'), 9);
  13. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('12.03.2002', 'DD.MM.YYYY'), TO_DATE('15.03.2002', 'DD.MM.YYYY'), 9);
  14. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('16.03.2002', 'DD.MM.YYYY'), TO_DATE('23.04.2002', 'DD.MM.YYYY'), 1);
  15. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('24.04.2002', 'DD.MM.YYYY'), TO_DATE('25.04.2002', 'DD.MM.YYYY'), 1);
  16. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('26.04.2002', 'DD.MM.YYYY'), TO_DATE('23.06.2002', 'DD.MM.YYYY'), 1);
  17. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('24.06.2002', 'DD.MM.YYYY'), TO_DATE('06.08.2002', 'DD.MM.YYYY'), 9);
  18. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('07.08.2002', 'DD.MM.YYYY'), TO_DATE('09.09.2002', 'DD.MM.YYYY'), 9);
  19. INSERT INTO CONSULTA_SQL_AVANZADA VALUES ('Map1', TO_DATE('10.09.2002', 'DD.MM.YYYY'), TO_DATE('18.09.2002', 'DD.MM.YYYY'), 9);
  20. COMMIT;
  21.  
  22.  
  23. DROP TABLE AUX_CONSULTA_SQL_AVANZADA PURGE;
  24.  
  25. CREATE TABLE AUX_CONSULTA_SQL_AVANZADA TABLESPACE AUXILIAR PCTFREE 5 PCTUSED 95 AS
  26. SELECT CODIGO,
  27.        FECHA_DESDE,
  28.        FECHA_HASTA,
  29.        TIPO_UNIDAD,
  30.        MIN_FECHA_DESDE,
  31.        MAX_FECHA_HASTA
  32.   FROM (
  33. SELECT CODIGO,
  34.        FECHA_DESDE,
  35.        FECHA_HASTA,
  36.        TIPO_UNIDAD,
  37.        PROXIMO_TIPO_UNIDAD,
  38.        LOGICA,
  39.        ANTERIOR_LOGICA,
  40.        MIN_FECHA_DESDE,
  41.        MAX_FECHA_HASTA,
  42.        ORDEN,
  43.        MIN_ORDEN,
  44.        MAX_ORDEN,
  45.        DECODE(MIN_FECHA_DESDE, NULL, 0, 1) CNT_FECHA_DESDE,
  46.        DECODE(MAX_FECHA_HASTA, NULL, 0, 1) CNT_FECHA_HASTA
  47.   FROM (
  48. SELECT CODIGO,
  49.        FECHA_DESDE,
  50.        FECHA_HASTA,
  51.        TIPO_UNIDAD,
  52.        PROXIMO_TIPO_UNIDAD,
  53.        LOGICA,
  54.        ANTERIOR_LOGICA,
  55.        DECODE(ORDEN, MIN_ORDEN, FECHA_DESDE, 
  56.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN NULL
  57.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN FECHA_DESDE
  58.             ELSE NULL END) MIN_FECHA_DESDE,
  59.        DECODE(ORDEN, MAX_ORDEN, FECHA_HASTA, 
  60.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN FECHA_HASTA
  61.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN NULL
  62.             ELSE NULL END) MAX_FECHA_HASTA,
  63.        ORDEN,
  64.        MIN_ORDEN,
  65.        MAX_ORDEN
  66.   FROM (
  67. SELECT CODIGO,
  68.        FECHA_DESDE,
  69.        FECHA_HASTA,
  70.        TIPO_UNIDAD,
  71.        PROXIMO_TIPO_UNIDAD,
  72.        LOGICA,
  73.        LAG(LOGICA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_LOGICA,
  74.        ORDEN,
  75.        MIN(ORDEN) OVER() MIN_ORDEN,
  76.        MAX(ORDEN) OVER() MAX_ORDEN
  77.   FROM (
  78. SELECT CODIGO,
  79.        FECHA_DESDE,
  80.        FECHA_HASTA,
  81.        TIPO_UNIDAD,
  82.        NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD) PROXIMO_TIPO_UNIDAD,
  83.        ORDEN,
  84.        DECODE(TIPO_UNIDAD, NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD), 1, 0) LOGICA
  85.   FROM (
  86. SELECT CODIGO,
  87.        FECHA_DESDE,
  88.        FECHA_HASTA,
  89.        TIPO_UNIDAD,
  90.        LEAD(TIPO_UNIDAD, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMO_TIPO_UNIDAD,
  91.        ROW_NUMBER() OVER(ORDER BY FECHA_DESDE) ORDEN
  92.   FROM CONSULTA_SQL_AVANZADA
  93.        )
  94.        )
  95.        )
  96.        )
  97.        )
  98. WHERE (CNT_FECHA_DESDE + CNT_FECHA_HASTA) >= 1;

PARTE 2 (Select final)

Código SQL: 
Ver original
  1. SELECT CODIGO,
  2.        FECHA_DESDE,
  3.        FECHA_HASTA,
  4.        TIPO_UNIDAD
  5.   FROM (
  6. SELECT CODIGO,
  7.        MIN_FECHA_DESDE FECHA_DESDE,
  8.        MAX_FECHA_HASTA FECHA_HASTA,
  9.        TIPO_UNIDAD,
  10.        ROW_NUMBER () OVER(PARTITION BY CODIGO, MAX_FECHA_HASTA ORDER BY MIN_FECHA_DESDE) ORDEN
  11.   FROM (
  12. SELECT CODIGO,
  13.        FECHA_DESDE,
  14.        FECHA_HASTA,
  15.        TIPO_UNIDAD,
  16.        PROXIMO_TIPO_UNIDAD,
  17.        LOGICA,
  18.        ANTERIOR_LOGICA,
  19.        DECODE(ORDEN, MIN_ORDEN, FECHA_DESDE, 
  20.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN ANTERIOR_FECHA_DESDE
  21.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN FECHA_DESDE
  22.             ELSE FECHA_DESDE END) MIN_FECHA_DESDE,
  23.        DECODE(ORDEN, MAX_ORDEN, FECHA_HASTA, 
  24.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN FECHA_HASTA
  25.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN PROXIMA_FECHA_HASTA
  26.             ELSE FECHA_HASTA END) MAX_FECHA_HASTA,
  27.        ORDEN,
  28.        MIN_ORDEN,
  29.        MAX_ORDEN
  30.   FROM (
  31. SELECT CODIGO,
  32.        FECHA_DESDE,
  33.        FECHA_HASTA,
  34.        ANTERIOR_FECHA_DESDE,
  35.        PROXIMA_FECHA_HASTA,
  36.        TIPO_UNIDAD,
  37.        PROXIMO_TIPO_UNIDAD,
  38.        LOGICA,
  39.        LAG(LOGICA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_LOGICA,
  40.        ORDEN,
  41.        MIN(ORDEN) OVER() MIN_ORDEN,
  42.        MAX(ORDEN) OVER() MAX_ORDEN
  43.   FROM (
  44. SELECT CODIGO,
  45.        FECHA_DESDE,
  46.        FECHA_HASTA,
  47.        LAG(FECHA_DESDE, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_FECHA_DESDE,
  48.        LEAD(FECHA_HASTA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMA_FECHA_HASTA,
  49.        TIPO_UNIDAD,
  50.        NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD) PROXIMO_TIPO_UNIDAD,
  51.        ORDEN,
  52.        DECODE(TIPO_UNIDAD, NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD), 1, 0) LOGICA
  53.   FROM (
  54. SELECT CODIGO,
  55.        DECODE(MIN_FECHA_DESDE, NULL, FECHA_DESDE, MIN_FECHA_DESDE) FECHA_DESDE,
  56.        DECODE(MAX_FECHA_HASTA, NULL, FECHA_HASTA, MAX_FECHA_HASTA) FECHA_HASTA,
  57.        TIPO_UNIDAD,
  58.        LEAD(TIPO_UNIDAD, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMO_TIPO_UNIDAD,
  59.        ROW_NUMBER() OVER(ORDER BY FECHA_DESDE) ORDEN
  60.   FROM AUX_CONSULTA_SQL_AVANZADA
  61.        )
  62.        )
  63.        )
  64.        )
  65.        )
  66.  WHERE ORDEN = 1;
__________________
Y venció David al filisteo con honda y piedra; e hirió al filisteo y lo mató, sin tener David espada en su mano.
  #3 (permalink)  
Antiguo 30/11/2011, 10:33
 
Fecha de Ingreso: noviembre-2007
Ubicación: Argentina
Mensajes: 134
Antigüedad: 17 años
Puntos: 3
Respuesta: Consulta SQL avanzada
Xaji,

Este es un problema comun cuando se intenta identificar cuales fueron los cambios de periodo de "algo".
Los que a nosotros nos interesa son las transiciones, los cambios de un tipo de unidad a otro tipo de unidad sin importar las fechas que tenga de mas, si? OK.

Lo primero que vamos a hacer es identificar, cuando se produce un cambio en el tipo de unidad. Y para esto usamos una funcion analitica que trae el proximo registro de la misma columna.

Código SQL: 
Ver original
  1. LEAD(TIPO_UNIDAD, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMO_TIPO_UNIDAD,

Y con esto ya hemos avanzado mucho, porque sabemos cuando va a cambiar y cuando no. Pero aun no es suficiente.
Teniendo ahora, el proximo valor de tipo de unidad (que es lo que nos interesa agrupar por fecha) hacemos un pregunta con un decode:

"el proximo tipo de unidad es igual al actual, si? Entonces le pongamos un 1 (uno), caso contrario 0 (cero)"

Código SQL: 
Ver original
  1. DECODE(TIPO_UNIDAD, NVL(PROXIMO_TIPO_UNIDAD, TIPO_UNIDAD), 1, 0) LOGICA

(el NVL es simplemente para que no quede nulo y le agregue el valor del tipo de unidad actual, esto quiere decir que suponemos que el valor del tipo de unidad anterior "al primer registro" de la consulta es el mismo que el del primer registro)

Ahora, subimos un nivel de complejidad mas. Hacemos una operacion nueva usando la funcion LAG, pero esta vez con el campo LOGICA y la llamamos ANTERIOR_LOGICA. El LAG a diferencia del LEAD, no trae el proximo registro sino el anterior registro (siempre de la misma columna) generando una nueva columna.

Código SQL: 
Ver original
  1. LAG(LOGICA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_LOGICA,

Muy bien, ahora esta en condiciones de aplicar la cuenta principal que necesitamos para agrupar por tipo de unidad, señalando las fecha mas viejas de fecha_desde y las mas nuevas de fecha_hasta.

Código SQL: 
Ver original
  1. DECODE(ORDEN, MIN_ORDEN, FECHA_DESDE, 
  2.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN NULL
  3.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN FECHA_DESDE
  4.             ELSE NULL END) MIN_FECHA_DESDE,
  5.        DECODE(ORDEN, MAX_ORDEN, FECHA_HASTA, 
  6.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN FECHA_HASTA
  7.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN NULL
  8.             ELSE NULL END) MAX_FECHA_HASTA,

Realizamos una cuenta, un estilo flag de control para solo mostrar lo que queremos ver (las transiciones).

Código SQL: 
Ver original
  1. DECODE(MIN_FECHA_DESDE, NULL, 0, 1) CNT_FECHA_DESDE,
  2. DECODE(MAX_FECHA_HASTA, NULL, 0, 1) CNT_FECHA_HASTA

Filtramos por...

Código SQL: 
Ver original
  1. (CNT_FECHA_DESDE + CNT_FECHA_HASTA) >= 1

Y con esto terminamos la primera parte. El resultado de este select se almacena en un tabla auxiliar.

Que tengo en esa tabla? solo las transiciones, por lo tanto, solo tengo como maximo dos registros por tipo de unidad nada mas.

Y desde aqui es mucho mas facil, solo tenemos que completar algunos campos que estan nulos.
Aqui vamos a tener, los dos campos fechas (fecha_desde y fecha_hasta) y dos nuevos campos (min_fecha_desde y max_fecha_hasta)
Renombramos y completamos.

Código SQL: 
Ver original
  1. DECODE(MIN_FECHA_DESDE, NULL, FECHA_DESDE, MIN_FECHA_DESDE) FECHA_DESDE,
  2.        DECODE(MAX_FECHA_HASTA, NULL, FECHA_HASTA, MAX_FECHA_HASTA) FECHA_HASTA,

Aplicamos nuevamente la logica para generar LOGICA y PROXIMA_LOGICA.
Aplicamos un logica nueva para generar ANTERIOR_FECHA_DESDE y PROXIMA_FECHA_HASTA.

Código SQL: 
Ver original
  1. LAG(FECHA_DESDE, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) ANTERIOR_FECHA_DESDE,
  2.        LEAD(FECHA_HASTA, 1) OVER(PARTITION BY CODIGO ORDER BY FECHA_DESDE) PROXIMA_FECHA_HASTA,

Ya falta poco, no desespereis !!!!!
Aplicamos la ultima logica grande, que nos dara el resultado final.

Código SQL: 
Ver original
  1. DECODE(ORDEN, MIN_ORDEN, FECHA_DESDE, 
  2.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN ANTERIOR_FECHA_DESDE
  3.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN FECHA_DESDE
  4.             ELSE FECHA_DESDE END) MIN_FECHA_DESDE,
  5.        DECODE(ORDEN, MAX_ORDEN, FECHA_HASTA, 
  6.        CASE WHEN LOGICA = 0 AND ANTERIOR_LOGICA = 1 THEN FECHA_HASTA
  7.             WHEN LOGICA = 1 AND ANTERIOR_LOGICA = 0 THEN PROXIMA_FECHA_HASTA
  8.             ELSE FECHA_HASTA END) MAX_FECHA_HASTA,

No es complicado, no es mas de lo mismo.
Quizas las condiciones cambiaron porque cambiaron las condiciones en la tabla, ya no trabajamos mas sobre CONSULTA_SQL_AVAZANDA sino sobre AUX_CONSULTA_SQL_AVAZANDA.

Un ultimo campo orden,

Código SQL: 
Ver original
  1. ROW_NUMBER () OVER(PARTITION BY CODIGO, MAX_FECHA_HASTA ORDER BY MIN_FECHA_DESDE) ORDEN


Y filtramos por....

Código SQL: 
Ver original
  1. ORDEN = 1

Querido Xaji, espero pueda servirte. No tengas miedo de modificar o de hacer cambios.

Cualquier nueva duda, vuelve a consultar.
Te mando un saludo grande,
Mario.
__________________
Y venció David al filisteo con honda y piedra; e hirió al filisteo y lo mató, sin tener David espada en su mano.

Etiquetas: select, sql, tabla
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta


« Tema Anterior | Próximo Tema »


La zona horaria es GMT -6. Ahora son las 13:44.