2009/4/7 Newton Teixeira do Nascimento Júnior
<[email protected]>:
> Caro Osvaldo, pode passar um exemplo pra seu colega não muito expert?
>
> De qualquer maneira, muito obrigado pela dica. Att.
>
Algo do tipo:
bdteste=# CREATE TEMP TABLE calendario(
bdteste(# data_hora TIMESTAMP NOT NULL PRIMARY KEY
bdteste(# );
NOTA: CREATE TABLE / PRIMARY KEY criará índice implícito
"calendario_pkey" na tabela "calendario"
CREATE TABLE
bdteste=#
bdteste=# -- Colocar na tabela calendario os inícios de intervalos desejados
bdteste=# -- Neste exemplo intervalo de 30 minutos
bdteste=# INSERT INTO calendario
bdteste-# SELECT '2009-04-06 12:00'::TIMESTAMP + i*'30 MINUTES'::INTERVAL
bdteste-# FROM generate_series(0,10) i;
INSERT 0 11
bdteste=#
bdteste=# SELECT * FROM calendario;
data_hora
---------------------
2009-04-06 12:00:00
2009-04-06 12:30:00
2009-04-06 13:00:00
2009-04-06 13:30:00
2009-04-06 14:00:00
2009-04-06 14:30:00
2009-04-06 15:00:00
2009-04-06 15:30:00
2009-04-06 16:00:00
2009-04-06 16:30:00
2009-04-06 17:00:00
(11 registros)
bdteste=#
bdteste=# --Suponha que sua tabela seja algo do tipo:
bdteste=# CREATE TEMP TABLE sua_tabela(
bdteste(# instante TIMESTAMP,
bdteste(# valor NUMERIC(10,2)
bdteste(# );
CREATE TABLE
bdteste=#
bdteste=# INSERT INTO sua_tabela VALUES ('2009-04-06
12:05'::TIMESTAMP, 10), ('2009-04-06 12:15'::TIMESTAMP, 20),
('2009-04-06 13:25'::TIMESTAMP, 30), ('2009-04-06 14:35'::TIMESTAMP,
40), ('2009-04-06 14:45'::TIMESTAMP, 50), ('2009-04-06
14:55'::TIMESTAMP, 60);
INSERT 0 6
bdteste=#
bdteste=# SELECT * FROM sua_tabela;
instante | valor
---------------------+-------
2009-04-06 12:05:00 | 10.00
2009-04-06 12:15:00 | 20.00
2009-04-06 13:25:00 | 30.00
2009-04-06 14:35:00 | 40.00
2009-04-06 14:45:00 | 50.00
2009-04-06 14:55:00 | 60.00
(6 registros)
bdteste=#
bdteste=# -- Resultado:
bdteste=# SELECT data_hora, coalesce(AVG(valor),0)::numeric(10,4) AS "Média"
bdteste-# FROM calendario LEFT OUTER JOIN sua_tabela
bdteste-# ON (instante BETWEEN data_hora AND
data_hora + '30 minutes'::interval)
bdteste-# GROUP BY data_hora;
data_hora | Média
---------------------+---------
2009-04-06 12:00:00 | 15.0000
2009-04-06 12:30:00 | 0.0000
2009-04-06 13:00:00 | 30.0000
2009-04-06 13:30:00 | 0.0000
2009-04-06 14:00:00 | 0.0000
2009-04-06 14:30:00 | 50.0000
2009-04-06 15:00:00 | 0.0000
2009-04-06 15:30:00 | 0.0000
2009-04-06 16:00:00 | 0.0000
2009-04-06 16:30:00 | 0.0000
2009-04-06 17:00:00 | 0.0000
(11 registros)
Osvaldo
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
