tipo - obtener mes y año de una fecha sql

Realización de agregación a través de fecha y hora en SQL

Question

Tengo un conjunto de datos que contiene observaciones durante varias semanas con una frecuencia de 2 minutos. Quiero aumentar el intervalo de tiempo de 2 minutos a 5 minutos. El problema es que la frecuencia de las observaciones no es siempre la misma. Quiero decir, teóricamente, cada 10 minutos debería haber 5 observaciones pero generalmente no es el caso. Por favor, hágame saber cómo puedo agregar las observaciones basadas en la función promedio y con respecto a la hora y fecha de las observaciones. En otras palabras, la agregación basada en cada 5 minutos mientras que el número de observaciones no es el mismo para cada intervalo de tiempo de 5 minutos. Además, tengo la fecha y la hora en formato de sello de tiempo.

Ejemplo de datos:

1 2007-09-14 22:56:12 5.39
2 2007-09-14 22:58:12 5.34
3 2007-09-14 23:00:12 5.16
4 2007-09-14 23:02:12 5.54
5 2007-09-14 23:04:12 5.30
6 2007-09-14 23:06:12 5.20

Resultados previstos:

1 2007-09-14 23:00 5.29
2 2007-09-14 23:05 5.34

Answer 1

Con mucho, la opción más simple es crear una tabla de referencia. En esa tabla, almacena los intervalos sobre los que está interesado:

(Adapte esto a la notación de fecha de su propio RDBMS).

CREATE TABLE interval (
  start_time    DATETIME,
  cease_time    DATETIME
);
INSERT INTO interval SELECT ''2012-10-22 12:00'', ''2012-10-22 12:05'';
INSERT INTO interval SELECT ''2012-10-22 12:05'', ''2012-10-22 12:10'';
INSERT INTO interval SELECT ''2012-10-22 12:10'', ''2012-10-22 12:15'';
INSERT INTO interval SELECT ''2012-10-22 12:15'', ''2012-10-22 12:20'';
INSERT INTO interval SELECT ''2012-10-22 12:20'', ''2012-10-22 12:25'';
INSERT INTO interval SELECT ''2012-10-22 12:25'', ''2012-10-22 12:30'';
INSERT INTO interval SELECT ''2012-10-22 12:30'', ''2012-10-22 12:35'';
INSERT INTO interval SELECT ''2012-10-22 12:35'', ''2012-10-22 12:40'';

Entonces solo te unes y agregas ...

SELECT
  interval.start_time,
  AVG(observation.value)
FROM
  interval
LEFT JOIN
  observation
    ON  observation.timestamp >= interval.start_time
    AND observation.timestamp <  interval.cease_time
GROUP BY
  interval.start_time

NOTA: solo necesita crear y completar esa tabla de intervalos una vez, luego puede volver a usarla muchas veces.

Answer 2

EDITAR: Pensé un poco más sobre esto y me di cuenta de que no se puede pasar de 2 minutos a 5 minutos. No tiene sentido. Seguiré sobre eso, pero el siguiente código funciona una vez que tienes datos de 1 minuto para agregar.

-

Si los datos están en un formato ''inicial'', puede usar el código dentro de esta función o crear la función en su base de datos para facilitar el acceso:

CREATE OR REPLACE FUNCTION dev.beginning_datetime_floor(timestamp without time zone,   
integer)  /* switch out ''dev'' with your schema name */
RETURNS timestamp without time zone AS
$BODY$ 
SELECT
date_trunc(''minute'',timestamp with time zone ''epoch'' + 
 floor(extract(epoch from $1)/($2*60))*$2*60
 * interval ''1 second'') at time zone ''CST6CDT'' /* change this to your time zone */
$BODY$
LANGUAGE sql VOLATILE;

Usted solo lo alimenta con el número entero de minutos que desea agregar (use 1, 2, 3, 4, 5, 6, 10, 12, 15, 20 o 30), aquí hay un par de resultados:

select dev.beginning_datetime_floor(''2012-01-01 02:02:21'',2)

= ''2012-01-01 02:02:00''

select dev.beginning_datetime_floor(''2012-01-01 02:02:21'',5)

= ''2012-01-01 02:00:00''

Simplemente pruébelo y agregue o reste tiempo para manejar las marcas de tiempo de principio a fin utilizando las funciones integradas de sello de tiempo .

Cuando obtenga la marca de tiempo que desea, haga lo que dijo Craig y GROUP BY en esa marca de tiempo, junto con sus funciones agregadas deseadas (promedios probables).

Podrías probar / ajustarlo con:

date_trunc(''minute'',timestamp with time zone ''epoch'' + 
 floor(extract(epoch from your_datetime)/(interval_minutes*60))*interval_minutes*60
 * interval ''1 second'') at time zone ''CST6CDT'' /* change this to your time zone */

Puede resultar que quiera promediar las marcas de tiempo, si la duración de su intervalo es volátil, por ejemplo. Para esto, podría hacer una función similar que redondee la marca de tiempo en lugar de tomar un piso.

Answer 3

Ok, esta es solo una forma de manejar esto. Espero que esto lo haga pensar en cómo convertir los datos para sus necesidades de análisis.

Hay un requisito previo para probar este código. Necesita tener una tabla con todas las marcas de tiempo de 1 minuto posibles. Hay muchas maneras de hacerlo, solo usaré lo que tengo disponible, que es una tabla: dim_time que tiene cada minuto (00:01:00) hasta (23:59:00) y otra tabla con todos los posibles fechas (dim_date). Cuando te unes a estos (1 = 1) obtienes todos los minutos posibles para todos los días posibles.

--first you need to create some functions I''ll use later
--credit to this first function goes to David Walling
CREATE OR REPLACE FUNCTION dev.beginning_datetime_floor(timestamp without time zone, integer)
  RETURNS timestamp without time zone AS
$BODY$ 
SELECT
date_trunc(''minute'',timestamp with time zone ''epoch'' + 
    floor(extract(epoch from $1)/($2*60))*$2*60
* interval ''1 second'') at time zone ''CST6CDT''
$BODY$
  LANGUAGE sql VOLATILE;

--the following function is what I described on my previous post  
CREATE OR REPLACE FUNCTION dev.round_minutes(timestamp without time zone, integer)
  RETURNS timestamp without time zone AS
$BODY$ 
  SELECT date_trunc(''hour'', $1) + cast(($2::varchar||'' min'') as interval) * round(date_part(''minute'',$1)::float / cast($2 as float)) 
$BODY$
  LANGUAGE sql VOLATILE;

--let''s load the data into a temp table, I added some data points. note: i got rid of the partial seconds
SELECT cast(timestamp_original as timestamp) as timestamp_original, datapoint INTO TEMPORARY TABLE timestamps_second2
FROM
(
SELECT ''2007-09-14 22:56:12'' as timestamp_original, 0 as datapoint
UNION
SELECT ''2007-09-14 22:58:12'' as timestamp_original, 1 as datapoint
UNION
SELECT ''2007-09-14 23:00:12'' as timestamp_original, 10 as datapoint 
UNION
SELECT ''2007-09-14 23:02:12'' as timestamp_original, 100 as datapoint
UNION
SELECT ''2007-09-14 23:04:12'' as timestamp_original, 1000 as datapoint
UNION
SELECT ''2007-09-14 23:06:12'' as timestamp_original, 10000 as datapoint
) as data

--this is the bit of code you''ll have to replace with your implementation of getting all possible minutes
--you could make some sequence of timestamps in R, or simply make the timestamps in Excel to test out the rest of the code
--the result of the query is simply ''2007-09-14 00:00:00'' through ''2007-09-14 23:59:00''
SELECT * INTO TEMPORARY TABLE possible_timestamps
FROM
(
select the_date + beginning_minute as minute_timestamp
FROM datawarehouse.dim_date as dim_date
JOIN datawarehouse.dim_time as dim_time
ON 1=1
where dim_date.the_date = ''2007-09-14''
group by the_date, beginning_minute
order by the_date, beginning_minute
) as data

--round to nearest minute (be sure to think about how this might change your results
SELECT * INTO TEMPORARY TABLE rounded_timestamps2
FROM
(
SELECT dev.round_minutes(timestamp_original,1) as minute_timestamp_rounded, datapoint
from timestamps_second2
) as data

--let''s join what minutes we have data for versus the possible minutes
--I used some subqueries so when you select all from the table you''ll see the important part (not needed)
SELECT * INTO TEMPORARY TABLE joined_with_possibles
FROM
(
SELECT *
FROM
(
SELECT *, (MIN(minute_timestamp_rounded) OVER ()) as min_time, (MAX(minute_timestamp_rounded) OVER ()) as max_time
FROM possible_timestamps as t1
LEFT JOIN rounded_timestamps2 as t2
ON t1.minute_timestamp = t2.minute_timestamp_rounded
ORDER BY t1.minute_timestamp asc
) as inner_query
WHERE minute_timestamp >= min_time
AND minute_timestamp <= max_time
) as data

--here''s the tricky part that might not suit your needs, but it''s one method
--if it''s missing a value it grabs the previous value
--if it''s missing the prior value it grabs the one before that, otherwise it''s null
--best practice would be run another case statement with 0,1,2 specifying which point was pulled, then you can count those when you aggregate
SELECT * INTO TEMPORARY TABLE shifted_values
FROM
(
SELECT 
*,
case 
when datapoint is not null then datapoint
when datapoint is null and (lag(datapoint,1) over (order by minute_timestamp asc)) is not null
  then lag(datapoint,1) over (order by minute_timestamp asc)
when datapoint is null and (lag(datapoint,1) over (order by minute_timestamp asc)) is null and (lag(datapoint,2) over (order by minute_timestamp asc)) is not null
  then lag(datapoint,2) over (order by minute_timestamp asc)
else null end as last_good_value
from joined_with_possibles
ORDER BY minute_timestamp asc
) as data

--now we use the function from my previous post to make the timestamps to aggregate on
SELECT * INTO TEMPORARY TABLE shifted_values_with_five_minute
FROM
(
SELECT *, dev.beginning_datetime_floor(minute_timestamp,5) as five_minute_timestamp
FROM shifted_values
) as data

--finally we aggregate
SELECT
AVG(datapoint) as avg_datapoint, five_minute_timestamp
FROM shifted_values_with_five_minute
GROUP BY five_minute_timestamp

Answer 4

Las respuestas a esta pregunta probablemente brinden buenas soluciones a su problema, mostrando formas de agregar datos de manera eficiente en ventanas de tiempo.

Esencialmente, use el agregado avg con:

GROUP BY floor(extract(epoch from the_timestamp) / 60 / 5)