Como completar una consulta con un registro anterior cada 10 minutos

Question

A partir de una consulta, obtengo:

TIME	                MILLISECOND	VALUE   
2019-05-05 03:00:00.0000000	64	1
2019-05-05 03:02:00.0000000	79	2
2019-05-05 03:06:00.0000000	59	3
2019-05-05 03:08:00.0000000	23	4
2019-05-05 03:12:00.0000000	79	5
2019-05-05 03:18:00.0000000	35	6
2019-05-05 03:22:00.0000000	84	7
2019-05-05 03:24:00.0000000	61	8
2019-05-05 03:28:00.0000000	36	9
2019-05-05 03:32:00.0000000	36	0

Y Deseo obtener:

TIME	                    MI	VALUE   
2019-05-05 03:00:00.0000000 64	1
2019-05-05 03:02:00.0000000 79	2
2019-05-05 03:06:00.0000000 59	3
2019-05-05 03:08:00.0000000 23	4
2019-05-05 03:10:00.0000000 23	4 -> completa con valores de la fila anterior
2019-05-05 03:12:00.0000000 79  5
2019-05-05 03:18:00.0000000 35	6
2019-05-05 03:20:00.0000000 35	6 -> completa con valores de la fila anterior
2019-05-05 03:22:00.0000000 84  7
2019-05-05 03:24:00.0000000 61	8
2019-05-05 03:28:00.0000000 36  9
2019-05-05 03:30:00.0000000 36  9 -> completa con valores de la fila anterior
2019-05-05 03:32:00.0000000 37	0

Deseo obtener un registro cada 10 minutos, que repita el ultimo valor mas cercano a esa marca.

Si podrían darme una idea de como hacerlo, y yo sigo intentando la busqueda.

Gracias!

Answer 1

Hola carlino70:

Te planteo una idea cercana a lo que quieres:

CREATE TABLE con
(fecha DATETIME2
, value INT
);
GO
INSERT INTO con (fecha, value)
VALUES
('2019-05-05 03:00:00.0000000',64),
('2019-05-05 03:02:00.0000000',79),
('2019-05-05 03:06:00.0000000',59),
('2019-05-05 03:08:00.0000000',23),
('2019-05-05 03:12:00.0000000',79),
('2019-05-05 03:18:00.0000000',35),
('2019-05-05 03:22:00.0000000',84),
('2019-05-05 03:24:00.0000000',61),
('2019-05-05 03:28:00.0000000',36),
('2019-05-05 03:32:00.0000000',36);
GO

El escenario que planteas, yo he entendido así, como este.

WITH CTE
	AS (SELECT TOP (144) NUMBER
	    FROM             
		    MASTER..spt_values
	    WHERE            TYPE = 'P  '
	    /* con master..spt_values tengo un rango de números para establecer una unidad de tiempo cada 10minutos */
	   ),
	   TIEMPO AS (
	   /* a una fecha dada, le sumo el rango de numeros por 10 y lo modifico a datetime2 */
	   SELECT CAST(DATEADD(MINUTE,(C.number*10), '20190505') AS DATETIME2) AS UNIDAD
	   FROM CTE C
	   
	   ), DATOS AS 
	   (
	   /* cruzo los datos de la tabla tiempo, que tiene todas las horas cada 10 minutos para el día en cuestion 
	   con los datos reales, cuando haya coincidencia solo se mostrará uno (FULL OUTER JOIN)*/
		  SELECT CASE WHEN FECHA IS NULL THEN TIEMPO.UNIDAD ELSE CON.fecha END AS TIME,
		  value
		  FROM CON FULL OUTER JOIN TIEMPO ON TIEMPO.UNIDAD = CON.fecha
	   ), VALORSIGUIENTE AS
	   (
		  /* OBTENGO EL VALOR ANTERIOR DE VALUE CUANDO SEA NULO.
		  ESTA PARTE ES LA QUE TENDRÁS QUE PULIR, PARA QUE SE ADAPTE COMPLETAMENTE A TU ESCENARIO
		   */
		  SELECT D.TIME, 
		  CASE WHEN VALUE IS NULL THEN LEAD(VALUE) OVER (ORDER BY TIME) ELSE VALUE END AS ANT
		  FROM DATOS D
	   )
	   /* RECOJO LA SALIDA */
	SELECT TIME, ANT
	FROM   VALORSIGUIENTE
	WHERE VALORSIGUIENTE.TIME BETWEEN '2019-05-05 03:00:00.0000000' AND '2019-05-05 03:32:00.0000000'
	ORDER BY TIME
		

Espero te ayude

Answer 2

Excelente tu logica Javi Fernandez.

Tengo que resolver que pasa cuando en el lapso de 10' no hay ningun dato...a eso te referis no?

En ese caso debería copiar el ultimo valor obtenido...aun cuando no corresponda al periodo de 10' ...????

Desde ya muchas gracias por tu aporte.

Answer 3

Eso es..... y si quieres saltar de día, por ejemplo también tendrás que quitar el corte del top 144

Answer 4

Javi Fernandez, en el caso de que el dato en la marca de 10' EXISTA en la tabla...como lo mantengo?, o sea no necesito modificarlo si existe...

Answer 5

Hola carlino70:

Si te das cuenta, estoy utilizando un case para esto.

CASE WHEN FECHA IS NULL THEN TIEMPO.UNIDAD ELSE CON.fecha END AS TIME

Quiere decir que si el resultado de uno de los conjuntos es nulo, me pones el otro, y si no lo es me pones el mismo. De este modo si 10 existe entra por el ELSE, por tanto recoge 10 de la tabla original.

CASE

https://docs.microsoft.com/es-es/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

Answer 6

Javi Fernandez, con mis datos en ANALOG_NEW, solo obtengo VALUES=NULL, salvo en el ultimo registro:

SELECT TOP (100)[TIME]      ,[VALUE]FROM [eta_user].[eta_user].[ANALOG_NEW];

TIME                       VALUE

2019-04-29 12:03:56.0000000 311
2019-04-29 12:03:56.0000000 586
2019-04-29 12:03:56.0000000 592
2019-04-29 12:03:56.0000000 787
2019-04-29 12:06:00.0000000 74
2019-04-29 12:14:00.0000000 577
2019-04-29 12:16:00.0000000 646
2019-04-29 12:20:00.0000000 463
2019-04-29 12:24:00.0000000 826
2019-04-29 12:26:00.0000000 409
2019-04-29 12:32:00.0000000 43
2019-04-29 12:36:00.0000000 677
2019-04-29 12:40:00.0000000 203
2019-04-29 12:48:00.0000000 688
2019-04-29 12:50:00.0000000 308
2019-04-29 12:54:00.0000000 407
2019-04-29 12:56:00.0000000 40
2019-04-29 13:00:00.0000000 266
2019-04-29 13:04:00.0000000 219
2019-04-29 13:06:00.0000000 43
2019-04-29 13:10:00.0000000 230
2019-04-29 13:12:00.0000000 137
2019-04-29 13:15:42.0000000 399
2019-04-29 13:15:42.0000000 820
2019-04-29 13:15:42.0000000 680
2019-04-29 13:15:42.0000000 745
2019-04-29 13:16:00.0000000 675
2019-04-29 13:22:00.0000000 556
2019-04-29 13:26:00.0000000 177
2019-04-29 13:30:00.0000000 855

aqui el codigo, modificado:

  --BUSQUEDA CADA 10' EN TABLA ANALOG_NEW
  WITH CTE AS 
       (SELECT /*TOP (144)*/ NUMBER
    FROM             
    MASTER..spt_values
    WHERE            TYPE = 'P  '
    /* con master..spt_values tengo un rango de números para establecer una unidad de tiempo cada 10minutos */
   ),
  TIEMPO AS 
   (/* a una fecha dada, le sumo el rango de numeros por 10 y lo modifico a datetime2 */
   SELECT CAST(DATEADD(MINUTE,(C.number*10), '2019-04-29') AS DATETIME2) AS UNIDAD
   FROM CTE C
   ), 
  DATOS AS 
   (
   /* cruzo los datos de la tabla tiempo, que tiene todas las horas cada 10 minutos para el día en cuestion 
   con los datos reales, cuando haya coincidencia solo se mostrará uno (FULL OUTER JOIN)*/
  SELECT CASE WHEN TIME IS NULL THEN TIEMPO.UNIDAD ELSE TIME END AS TIME, VALUE
  FROM ETA_USER.ANALOG_NEW 
  FULL OUTER JOIN TIEMPO ON TIEMPO.UNIDAD = ETA_USER.ANALOG_NEW.TIME
   ), 
   VALORSIGUIENTE AS
   (
  /* OBTENGO EL VALOR ANTERIOR DE VALUE CUANDO SEA NULO. ESTA PARTE HAY QUE PULIR, PARA QUE SE ADAPTE COMPLETAMENTE AL ESCENARIO */
  SELECT D.TIME, 
  CASE WHEN VALUE IS NULL THEN LEAD(VALUE) OVER (ORDER BY TIME) 
  ELSE VALUE END AS ANT
  FROM DATOS D
   )
   /* CONSULTO LA SALIDA */
SELECT TIME, ANT
FROM   VALORSIGUIENTE
WHERE VALORSIGUIENTE.TIME BETWEEN '2019-04-29 00:00:00.0000000' AND '2019-04-29 12:00:00.0000000'
ORDER BY TIME;
/*fin*/

Y los resultados:

TIME                                         ANT
2019-04-29 00:00:00.0000000 NULL
2019-04-29 00:10:00.0000000 NULL
2019-04-29 00:20:00.0000000 NULL
2019-04-29 00:30:00.0000000 NULL
2019-04-29 00:40:00.0000000 NULL
2019-04-29 00:50:00.0000000 NULL
2019-04-29 01:00:00.0000000 NULL
2019-04-29 01:10:00.0000000 NULL
2019-04-29 01:20:00.0000000 NULL
2019-04-29 01:30:00.0000000 NULL
2019-04-29 01:40:00.0000000 NULL
2019-04-29 01:50:00.0000000 NULL
2019-04-29 02:00:00.0000000 NULL
2019-04-29 02:10:00.0000000 NULL
2019-04-29 02:20:00.0000000 NULL
2019-04-29 02:30:00.0000000 NULL
2019-04-29 02:40:00.0000000 NULL
2019-04-29 02:50:00.0000000 NULL
2019-04-29 03:00:00.0000000 NULL
2019-04-29 03:10:00.0000000 NULL
2019-04-29 03:20:00.0000000 NULL
2019-04-29 03:30:00.0000000 NULL
2019-04-29 03:40:00.0000000 NULL
2019-04-29 03:50:00.0000000 NULL
2019-04-29 04:00:00.0000000 NULL
2019-04-29 04:10:00.0000000 NULL
2019-04-29 04:20:00.0000000 NULL
2019-04-29 04:30:00.0000000 NULL
2019-04-29 04:40:00.0000000 NULL
2019-04-29 04:50:00.0000000 NULL
2019-04-29 05:00:00.0000000 NULL
2019-04-29 05:10:00.0000000 NULL
2019-04-29 05:20:00.0000000 NULL
2019-04-29 05:30:00.0000000 NULL
2019-04-29 05:40:00.0000000 NULL
2019-04-29 05:50:00.0000000 NULL
2019-04-29 06:00:00.0000000 NULL
2019-04-29 06:10:00.0000000 NULL
2019-04-29 06:20:00.0000000 NULL
2019-04-29 06:30:00.0000000 NULL
2019-04-29 06:40:00.0000000 NULL
2019-04-29 06:50:00.0000000 NULL
2019-04-29 07:00:00.0000000 NULL
2019-04-29 07:10:00.0000000 NULL
2019-04-29 07:20:00.0000000 NULL
2019-04-29 07:30:00.0000000 NULL
2019-04-29 07:40:00.0000000 NULL
2019-04-29 07:50:00.0000000 NULL
2019-04-29 08:00:00.0000000 NULL
2019-04-29 08:10:00.0000000 NULL
2019-04-29 08:20:00.0000000 NULL
2019-04-29 08:30:00.0000000 NULL
2019-04-29 08:40:00.0000000 NULL
2019-04-29 08:50:00.0000000 NULL
2019-04-29 09:00:00.0000000 NULL
2019-04-29 09:10:00.0000000 NULL
2019-04-29 09:20:00.0000000 NULL
2019-04-29 09:30:00.0000000 NULL
2019-04-29 09:40:00.0000000 NULL
2019-04-29 09:50:00.0000000 NULL
2019-04-29 10:00:00.0000000 NULL
2019-04-29 10:10:00.0000000 NULL
2019-04-29 10:20:00.0000000 NULL
2019-04-29 10:30:00.0000000 NULL
2019-04-29 10:40:00.0000000 NULL
2019-04-29 10:50:00.0000000 NULL
2019-04-29 11:00:00.0000000 NULL
2019-04-29 11:10:00.0000000 NULL
2019-04-29 11:20:00.0000000 NULL
2019-04-29 11:30:00.0000000 NULL
2019-04-29 11:40:00.0000000 NULL
2019-04-29 11:50:00.0000000 NULL
2019-04-29 12:00:00.0000000 311

Answer 7

Hola carlino70:

Tu escenario:

create table analog_new ([time] datetime2, [value] int)
go
Insert into analog_new ([time], [value])
values
('2019-04-29 12:03:56.0000000',311),
('2019-04-29 12:03:56.0000000',586),
('2019-04-29 12:03:56.0000000',592),
('2019-04-29 12:03:56.0000000',787),
('2019-04-29 12:06:00.0000000',74 ),
('2019-04-29 12:14:00.0000000',577),
('2019-04-29 12:16:00.0000000',646),
('2019-04-29 12:20:00.0000000',463),
('2019-04-29 12:24:00.0000000',826),
('2019-04-29 12:26:00.0000000',409),
('2019-04-29 12:32:00.0000000',43 ),
('2019-04-29 12:36:00.0000000',677),
('2019-04-29 12:40:00.0000000',203),
('2019-04-29 12:48:00.0000000',688),
('2019-04-29 12:50:00.0000000',308),
('2019-04-29 12:54:00.0000000',407),
('2019-04-29 12:56:00.0000000',40 ),
('2019-04-29 13:00:00.0000000',266),
('2019-04-29 13:04:00.0000000',219),
('2019-04-29 13:06:00.0000000',43 ),
('2019-04-29 13:10:00.0000000',230),
('2019-04-29 13:12:00.0000000',137),
('2019-04-29 13:15:42.0000000',399),
('2019-04-29 13:15:42.0000000',820),
('2019-04-29 13:15:42.0000000',680),
('2019-04-29 13:15:42.0000000',745),
('2019-04-29 13:16:00.0000000',675),
('2019-04-29 13:22:00.0000000',556),
('2019-04-29 13:26:00.0000000',177),
('2019-04-29 13:30:00.0000000',855);
go

Ojo yo voy a empezar a las 00:00:00 horas, y no hay anteriores, ya que el primer registro es a las 12:03 horas... luego a partir de ahí, te doy la solución, pero hasta ahí no he planteado un valor por defecto. Si quieres hacerlo por ejemplo, con el primer valor, no te será difícil.

DECLARE @DIA DATETIME= '20190429';

WITH CTE
	AS (SELECT

	    --   TOP (144)

	    NUMBER
	    FROM  
		    MASTER..spt_values
	    WHERE TYPE = 'P  '),
	TIEMPO
	AS (SELECT CAST(DATEADD(MINUTE, (C.number * 10), @DIA) AS DATETIME2) AS UNIDAD
	    FROM   
		    CTE C),
	DATOS
	AS (SELECT CASE WHEN [TIME] IS NULL
					THEN TIEMPO.UNIDAD
				 ELSE TIME
			 END AS TIME
		    , VALUE
		    , (CASE WHEN VALUE IS NULL
					 THEN 0
				  ELSE 1
			  END) AS GRP
	    FROM   
		    ANALOG_NEW A
			    FULL OUTER JOIN TIEMPO ON TIEMPO.UNIDAD = A.[time]),
	VALORES
	AS (SELECT D.[TIME]
		    , D.[value]
		    , D.GRP
		    , ROW_NUMBER() OVER(
			 ORDER BY TIME) AS RANKING
	    FROM   
		    datos D)
	SELECT    A.[TIME]
		   , CASE WHEN A.[value] IS NULL
				    THEN VAL.VALUE
				ELSE A.[value]
			END AS VALUE
	FROM      
		VALORES A
			OUTER APPLY
	(
	    SELECT ISNULL((B.value), 0) AS VALUE
		    , B.RANKING
		    , B.TIME
	    FROM   
		    VALORES B
	    WHERE  B.TIME < A.TIME
			 AND B.RANKING =
	    (
		   SELECT MAX(D.RANKING)
		   FROM   
			   VALORES D
		   WHERE  D.RANKING < A.RANKING
				AND D.GRP = 1
	    )
	) AS VAL
	ORDER BY A.RANKING;

Salida

TIME                        VALUE
--------------------------- -----------
2019-04-29 00:00:00.0000000 NULL
2019-04-29 00:10:00.0000000 NULL
2019-04-29 00:20:00.0000000 NULL
2019-04-29 00:30:00.0000000 NULL
2019-04-29 00:40:00.0000000 NULL
2019-04-29 00:50:00.0000000 NULL
2019-04-29 01:00:00.0000000 NULL
2019-04-29 01:10:00.0000000 NULL
2019-04-29 01:20:00.0000000 NULL
2019-04-29 01:30:00.0000000 NULL
2019-04-29 01:40:00.0000000 NULL
2019-04-29 01:50:00.0000000 NULL
2019-04-29 02:00:00.0000000 NULL
2019-04-29 02:10:00.0000000 NULL
2019-04-29 02:20:00.0000000 NULL
2019-04-29 02:30:00.0000000 NULL
2019-04-29 02:40:00.0000000 NULL
2019-04-29 02:50:00.0000000 NULL
2019-04-29 03:00:00.0000000 NULL
2019-04-29 03:10:00.0000000 NULL
2019-04-29 03:20:00.0000000 NULL
2019-04-29 03:30:00.0000000 NULL
2019-04-29 03:40:00.0000000 NULL
2019-04-29 03:50:00.0000000 NULL
2019-04-29 04:00:00.0000000 NULL
2019-04-29 04:10:00.0000000 NULL
2019-04-29 04:20:00.0000000 NULL
2019-04-29 04:30:00.0000000 NULL
2019-04-29 04:40:00.0000000 NULL
2019-04-29 04:50:00.0000000 NULL
2019-04-29 05:00:00.0000000 NULL
2019-04-29 05:10:00.0000000 NULL
2019-04-29 05:20:00.0000000 NULL
2019-04-29 05:30:00.0000000 NULL
2019-04-29 05:40:00.0000000 NULL
2019-04-29 05:50:00.0000000 NULL
2019-04-29 06:00:00.0000000 NULL
2019-04-29 06:10:00.0000000 NULL
2019-04-29 06:20:00.0000000 NULL
2019-04-29 06:30:00.0000000 NULL
2019-04-29 06:40:00.0000000 NULL
2019-04-29 06:50:00.0000000 NULL
2019-04-29 07:00:00.0000000 NULL
2019-04-29 07:10:00.0000000 NULL
2019-04-29 07:20:00.0000000 NULL
2019-04-29 07:30:00.0000000 NULL
2019-04-29 07:40:00.0000000 NULL
2019-04-29 07:50:00.0000000 NULL
2019-04-29 08:00:00.0000000 NULL
2019-04-29 08:10:00.0000000 NULL
2019-04-29 08:20:00.0000000 NULL
2019-04-29 08:30:00.0000000 NULL
2019-04-29 08:40:00.0000000 NULL
2019-04-29 08:50:00.0000000 NULL
2019-04-29 09:00:00.0000000 NULL
2019-04-29 09:10:00.0000000 NULL
2019-04-29 09:20:00.0000000 NULL
2019-04-29 09:30:00.0000000 NULL
2019-04-29 09:40:00.0000000 NULL
2019-04-29 09:50:00.0000000 NULL
2019-04-29 10:00:00.0000000 NULL
2019-04-29 10:10:00.0000000 NULL
2019-04-29 10:20:00.0000000 NULL
2019-04-29 10:30:00.0000000 NULL
2019-04-29 10:40:00.0000000 NULL
2019-04-29 10:50:00.0000000 NULL
2019-04-29 11:00:00.0000000 NULL
2019-04-29 11:10:00.0000000 NULL
2019-04-29 11:20:00.0000000 NULL
2019-04-29 11:30:00.0000000 NULL
2019-04-29 11:40:00.0000000 NULL
2019-04-29 11:50:00.0000000 NULL
2019-04-29 12:00:00.0000000 NULL
2019-04-29 12:03:56.0000000 311
2019-04-29 12:03:56.0000000 586
2019-04-29 12:03:56.0000000 592
2019-04-29 12:03:56.0000000 787
2019-04-29 12:06:00.0000000 74
2019-04-29 12:10:00.0000000 74
2019-04-29 12:14:00.0000000 577
2019-04-29 12:16:00.0000000 646
2019-04-29 12:20:00.0000000 463
2019-04-29 12:24:00.0000000 826
2019-04-29 12:26:00.0000000 409
2019-04-29 12:30:00.0000000 409
2019-04-29 12:32:00.0000000 43
2019-04-29 12:36:00.0000000 677
2019-04-29 12:40:00.0000000 203
2019-04-29 12:48:00.0000000 688
2019-04-29 12:50:00.0000000 308
2019-04-29 12:54:00.0000000 407
2019-04-29 12:56:00.0000000 40
2019-04-29 13:00:00.0000000 266
2019-04-29 13:04:00.0000000 219
2019-04-29 13:06:00.0000000 43
2019-04-29 13:10:00.0000000 230
2019-04-29 13:12:00.0000000 137
2019-04-29 13:15:42.0000000 399
2019-04-29 13:15:42.0000000 820
2019-04-29 13:15:42.0000000 680
2019-04-29 13:15:42.0000000 745
2019-04-29 13:16:00.0000000 675
2019-04-29 13:20:00.0000000 675
2019-04-29 13:22:00.0000000 556
2019-04-29 13:26:00.0000000 177
2019-04-29 13:30:00.0000000 855
2019-04-29 13:40:00.0000000 855
2019-04-29 13:50:00.0000000 855
2019-04-29 14:00:00.0000000 855
2019-04-29 14:10:00.0000000 855
2019-04-29 14:20:00.0000000 855
2019-04-29 14:30:00.0000000 855
2019-04-29 14:40:00.0000000 855
2019-04-29 14:50:00.0000000 855
2019-04-29 15:00:00.0000000 855
2019-04-29 15:10:00.0000000 855

….. ya que has quitado el limite del día, continúa así hasta 

2019-05-13 05:00:00.0000000 855
2019-05-13 05:10:00.0000000 855

(2072 filas afectadas)

Espero te sirva

Answer 8

funciona Javi Fernandez!, ahora lo lleve a un escenario real, donde debo hacerlo sobre una tabla con datos de dias, y se hace lento...es bastante costoso parece ...

Answer 9

podría darle una condicion:

DATOS AS 	
(SELECT CASE WHEN [TIME] IS NULL		
THEN TIEMPO.UNIDAD		 
ELSE TIME		 
END AS TIME	    
, VALUE	    
, (CASE WHEN VALUE IS NULL			 
THEN 0			  
ELSE 1	  END) AS GRP
FROM   eta_user.ANALOG_TEST3 A   
FULL OUTER JOIN TIEMPO ON TIEMPO.UNIDAD = A.[time]
WHERE eta_user.ANALOG_TEST3.TABLE_INDEX = 3615),
--No veo donde insertar el WHERE

Answer 10

Puedes limitar donde lo has puesto el origen. Tambien puedes limitar el conjunto master..spt con top. Otra opcion es crear una tabla con el rango para que no sea calculado. Y se le suma a una fecha. Lo que mas le cuesta es repetir esosvalores cuando value es null.

Tambien y dado que la coonsulta es un poco fuera de la matematica de conjuntos, puedes plantear hacerla con un simple while.

Answer 11

Muchas gracias. Gran solucion