Consulta BD Oracle por segmentos de horas

publicado por: Anonymous

Hola tengo una tabla en BD Oracle que tiene los siguientes campos y valores:
introducir la descripción de la imagen aquí

Ahora necesito generar una consulta que me agrupe por tramos de media hora el número de elementos por FECHA_CREACION, por ejemplo para este caso el resultado que esperaria seria:

 Hora               Total
 10/7 14:00-14:30   3
 10/7 14:30-15:00   0
 10/7 15:00-15:30   0
 ...
 11/7 11:00-11:30   1

pero no encuentro una forma de hacerlo,
alguna idea?

solución

Importante: Oracle no es el dialecto de SQL que uso a diario, por lo que seguramente la solución que te prepongo tal vez no sea la mejor ya que es una adaptación de una forma de resolverlo en SQL Server.

Esta pregunta tiene un problema similar a esta otra que tiene que ver con la necesidad de generar una secuencia de períodos a medir en este caso de 30 minutos. El primer tema es definir la ventana de tiempo, el desde/hasta que queremos medir, con esa ventana de tiempo generaremos una tabla de Periodos con un registro por cada 30 minutos, son 48 registros si elegimos listar un solo día, 96 si son dos, etc.

Lo podemos hacer así:

CREATE GLOBAL TEMPORARY TABLE Periodos (
    PeriodoId          NUMBER,
    DesdeFechaHora     DATE,
    HastaFechaHora     DATE,
    Descripcion        VARCHAR(255)
);

INSERT INTO Periodos (PeriodoId,DesdeFechaHora,HastaFechaHora)
SELECT
  level,
  (to_date('2017-01-01','YYYY-MM-DD') + 1/48 * (level -1)),
  (to_date('2017-01-01','YYYY-MM-DD') + 1/48 * (level   ))
FROM dual
CONNECT BY LEVEL <= 48 * 2;


UPDATE  Periodos
    SET Descripcion = TO_CHAR(DesdeFechaHora,'YYYY-MM-DD') || ' ' || TO_CHAR(DesdeFechaHora, 'HH24:MI:SS') || '-' || TO_CHAR(HastaFechaHora, 'HH24:MI:SS');

Lo que hacemos es generar lo períodos de dos días LEVEL <= 48 * 2 desde el 2017-01-01. Si vemos un resumen de la salida, podemos darnos cuenta como vamos a usar esta tabla:

PERIODOID   DESDEFECHAHORA          HASTAFECHAHORA          DESCRIPCION
1           2017-01-01T00:00:00Z    2017-01-01T00:30:00Z    2017-01-01 00:00:00-00:30:00
2           2017-01-01T00:30:00Z    2017-01-01T01:00:00Z    2017-01-01 00:30:00-01:00:00
3           2017-01-01T01:00:00Z    2017-01-01T01:30:00Z    2017-01-01 01:00:00-01:30:00

Ahora como prueba de concepto armamos una tabla de ejemplo

CREATE GLOBAL TEMPORARY TABLE Ejemplo (
    Fecha              DATE
);

INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 09:05','YYYY-MM-DD HH24:MI'));
INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 09:15','YYYY-MM-DD HH24:MI'));
INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 09:31','YYYY-MM-DD HH24:MI'));
INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 10:12','YYYY-MM-DD HH24:MI'));

Y la consulta final mediante un LEFT JOIN nos permite obtener las cantidades de fechas entre períodos y además saber que períodos no registran movimientos

SELECT  P.Descripcion,
        COUNT(E.Fecha)
        FROM Periodos P
        LEFT JOIN Ejemplo E
            ON E.Fecha >= P.DesdeFechaHora
            AND E.Fecha < P.HastaFechaHora
        GROUP BY P.Descripcion, P.PeriodoId
        ORDER BY P.PeriodoId;

Y un extracto de la salida:

DESCRIPCION                     COUNT(E.FECHA)
2017-01-01 08:30:00-09:00:00    0
2017-01-01 09:00:00-09:30:00    2
2017-01-01 09:30:00-10:00:00    1
2017-01-01 10:00:00-10:30:00    1
2017-01-01 10:30:00-11:00:00    0

Espero te sirva..

Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *