none
Calcular medias en intervalos de 15 minutos RRS feed

  • Pregunta

  • Buenos días,

    Tengo que hace una consulta en sql un poco compleja (o a mí me lo parece), tengo una tabla, adjunto una captura. Lo que necesito es agrupar los datos por cada id en periodos de 15 minutos y calcular la media de estos periodos, lo estoy intentando y no hay manera.

    Gracias de antemano

    id time Dato1 Dato2
    1001 2017-09-11 12:02:30.000 24,9666672 23,6291676
    1002 2017-09-11 12:02:30.000 13,8355331 19,5659332
    1003 2017-09-11 12:02:30.000 24,4261017 30,469059
    1002 2017-09-11 12:00:50.000 14,9105091 19,2919674
    1003 2017-09-11 12:00:50.000 25,680933 36,57348
    1001 2017-09-11 12:00:50.000 23,8933334 19,4091663
    1001 2017-09-11 11:59:10.000 22,9083328 15,1194439
    1002 2017-09-11 11:59:10.000 15,994318 19,0118313
    1003 2017-09-11 11:59:10.000 26,9357662 42,6778946
    1002 2017-09-11 11:57:30.000 16,5147343 18,882618
    1003 2017-09-11 11:57:30.000 26,12973 44,28306
    1001 2017-09-11 11:57:30.000 22,6416664 16,2372227
    1001 2017-09-11 11:55:50.000 24,715 17,3233337
    1002 2017-09-11 11:55:50.000 15,7615585 16,8423176
    1003 2017-09-11 11:55:50.000 25,8104668 41,99874
    1002 2017-09-11 11:54:10.000 14,3869581 15,6995487
    1003 2017-09-11 11:54:10.000 27,2132664 36,6689262
    1001 2017-09-11 11:54:10.000 25,675 25,94389
    1001 2017-09-11 11:52:30.000 18,5633335 26,0999985
    1002 2017-09-11 11:52:30.000 13,1336746 15,6850681
    1003 2017-09-11 11:52:30.000 29,7257328 35,35704
    1002 2017-09-11 11:50:50.000 13,1132412 17,451067
    1003 2017-09-11 11:50:50.000 32,5645 34,22059
    1001 2017-09-11 11:50:50.000 16,49 26,146389
    1001 2017-09-11 11:49:10.000 16,515 21,0036125
    1002 2017-09-11 11:49:10.000 13,8127317 18,5430336
    1003 2017-09-11 11:49:10.000 33,6811943 36,12963
    1002 2017-09-11 11:47:29.000 14,9903336 15,8047762
    1003 2017-09-11 11:47:29.000 34,2536354 37,98838

    Un saludo

    Manu

    lunes, 11 de septiembre de 2017 10:39

Respuestas

Todas las respuestas

  • Cual version de SQL Server usas?

    Una forma de agrupar es calculando la diferencia en minutos contra una fecha ancla (ej. 19000101) y dividiendo por 15.  Esto agruparia en intervalos de 15 minutos desde las 00:00 - 00:14:59, 00:15 - 00:29, etc.

    El resto esta explicado en este articulo donde se usa la nueva facilidad OFFSET - FETCH de la clausula ORDER BY.

    http://sqlmag.com/sql-server-2012/sql-server-2012-solutions-median-calculation

    DECLARE @T table (
    id int NOT NULL,
    time datetime NOT NULL,
    Dato1 numeric(12, 7) NOT NULL,
    Dato2 numeric(12, 7) NOT NULL
    );
    
    INSERT INTO @T (
    	id,
    	time,
    	Dato1,
    	Dato2
    	)
    VALUES
    	(1001, '2017-09-11 12:02:30.000', 24.9666672, 23.6291676),
    
    	(1001, '2017-09-11 12:00:50.000', 23.8933334, 19.4091663),
    	(1001, '2017-09-11 11:59:10.000', 22.9083328, 15.1194439),
    
    	(1002, '2017-09-11 12:02:30.000', 13.8355331, 19.5659332),
    	(1002, '2017-09-11 12:00:50.000', 14.9105091, 19.2919674),
    
    	(1002, '2017-09-11 11:59:10.000', 15.9943180, 19.0118313),
    	(1002, '2017-09-11 11:57:30.000', 16.5147343, 18.8826180),
    	(1002, '2017-09-11 11:55:50.000', 15.7615585, 16.8423176),
    
    	(1003, '2017-09-11 12:02:30.000', 24.4261017, 30.4690590),
    	(1003, '2017-09-11 12:00:50.000', 25.6809330, 36.5734800),
    
    	(1003, '2017-09-11 11:59:10.000', 26.9357662, 42.6778946);
    
    WITH R1 AS (
    SELECT
    	T1.id,
        T1.[time],
        T1.Dato1,
        T1.Dato2,
    	T2.grp
    FROM
    	@T AS T1
    	CROSS APPLY
    	(VALUES (DATEDIFF(MINUTE, '19000101', [time]) / 15)) AS T2(grp)
    )
    , R2 AS (
    SELECT
    	id,
    	grp,
    	(COUNT(*) - 1) / 2 AS offset_Val,
    	2 - COUNT(*) % 2 AS fetch_Val
    FROM
    	R1
    GROUP BY
    	id,
    	grp
    )
    SELECT
    	R.id,
    	R.grp,
    	AVG(1. * S1.Dato1) AS median_dato1,
    	AVG(1. * S2.Dato2) AS median_dato2
    FROM
    	R2 AS R
    	CROSS APPLY
        (
    	SELECT
    		T.Dato1
    	FROM
    		R1 AS T
    	WHERE
    		T.id = R.id
    		AND T.grp = R.grp
    	ORDER BY
    		T.Dato1
    	OFFSET R.offset_Val ROWS FETCH NEXT R.fetch_Val ROWS ONLY
    	) AS S1
    	CROSS APPLY
        (
    	SELECT
    		T.Dato2
    	FROM
    		R1 AS T
    	WHERE
    		T.id = R.id
    		AND T.grp = R.grp
    	ORDER BY
    		T.Dato2
    	OFFSET R.offset_Val ROWS FETCH NEXT R.fetch_Val ROWS ONLY
    	) AS S2
    GROUP BY
    	R.id,
    	R.grp
    ORDER BY
    	R.id,
    	R.grp;

    Para mejorar el desempenio pudieras usar una columna calculada y persistente para la agrupacion.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    lunes, 11 de septiembre de 2017 13:15
  • Hola,

    Uso SQL Server 2008.

    Lo he probado pero me da error en en offset no me reconoce la sintaxis.

    Un saludo

    lunes, 11 de septiembre de 2017 14:31
  • Correcto, esa funcionalidad se introdujo con la version 2012.

    Sigue el link que adjunte y transforma el codigo para adaptarlo al metodo que usa ROW_NUMBER.  Hazlo para una unica columna (Dato1 o Dato2) hasta que estes familiarizado con el metodo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    lunes, 11 de septiembre de 2017 14:52