Re: Ayuda con query de intervalo de tiempo entre registro

Miguel Beltran R. escribió:
> Hola Lista
>
> Tengo unos registros con un campo datetime que representan cuando se
> insertaron
>
> ¿qué query me puede ayudar a saber el tiempo promedio entre uno y otro?
>
> Ej.
>
> valor captura
> 1 2010-02-15 12:00:01
> 2 2010-02-15 12:00:03
> 3 2010-02-15 12:00:08
>
> Entre el 1ro y 2do fueron 2 segundos y entre el 2do y 3ro fueron 5 segundos,
> sumados son 7 segundos entre 3 registros serian un promedio de 2.33 segundos

La manera más limpia es con una función ventana. Por ej.

alvherre=# create table miguel (a serial, b timestamp default now());
NOTICE: CREATE TABLE creará una secuencia implícita «miguel_a_seq» para la columna serial «miguel.a»
CREATE TABLE
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# insert into miguel default values;
INSERT 0 1
alvherre=# select * from miguel;
a | b
---+----------------------------
1 | 2010-02-15 17:45:34.116514
2 | 2010-02-15 17:45:36.236193
3 | 2010-02-15 17:45:39.316307
4 | 2010-02-15 17:45:40.044354
5 | 2010-02-15 17:45:43.292354
6 | 2010-02-15 17:45:44.644171
7 | 2010-02-15 17:45:46.204193
(7 filas)

alvherre=# select *, b - lag(b) over (order by b ROWS BETWEEN UNBOUNDED PRECEDING AND current row) from miguel;
a | b | ?column?
---+----------------------------+-----------------
1 | 2010-02-15 17:45:34.116514 |
2 | 2010-02-15 17:45:36.236193 | 00:00:02.119679
3 | 2010-02-15 17:45:39.316307 | 00:00:03.080114
4 | 2010-02-15 17:45:40.044354 | 00:00:00.728047
5 | 2010-02-15 17:45:43.292354 | 00:00:03.248
6 | 2010-02-15 17:45:44.644171 | 00:00:01.351817
7 | 2010-02-15 17:45:46.204193 | 00:00:01.560022
(7 filas)

A partir de ahí puedes hacer como quieras con las diferencias entre
valores. Para obtener el promedio de todos esos valores; por ejemplo,

alvherre=# select min(a), max(a), avg(dif) from (select a, b, b - lag(b) over (order by b ROWS BETWEEN UNBOUNDED PRECEDING AND current row ) as dif from miguel) foo;
min | max | avg
-----+-----+-----------------
1 | 7 | 00:00:02.014613
(1 fila)

--
Alvaro Herrera Vendo parcela en Valdivia: http://rie.cl/?a=255568
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)