trying to summarize into a new table by time...

Поиск
Список
Период
Сортировка
От Larry Rosenman
Тема trying to summarize into a new table by time...
Дата
Msg-id 20010602051258.A18140@lerami.lerctr.org
обсуждение исходный текст
Ответы Re: trying to summarize into a new table by time...  (Larry Rosenman <ler@lerctr.org>)
Re: trying to summarize into a new table by time...  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: trying to summarize into a new table by time...  (Alex Pilosov <alex@pilosoft.com>)
Список pgsql-sql
I have a LARGE table with 5 minute summary information:

--
-- TOC Entry ID 9 (OID 539300)
--
-- Name: traffic Type: TABLE Owner: ler
--

CREATE TABLE "traffic" ("asn" integer,"protocol" integer,"pkts_src" integer,"pkts_dst" integer,"bytes_src"
integer,"bytes_dst"integer,"secs_src" integer,"secs_dst" integer,"early" timestamp with time zone,"late" timestamp with
timezone
 
);

I'd like to summarize it into:

--
-- TOC Entry ID 10 (OID 539319)
--
-- Name: traffic_summary Type: TABLE Owner: ler
--

CREATE TABLE "traffic_summary" ("asn" integer,"protocol" integer,"pkts_src" double precision,"pkts_dst" double
precision,"bytes_src"double precision,"bytes_dst" double precision,"secs_src" double precision,"secs_dst" double
precision,"early"timestamp with time zone,"late" timestamp with time zone
 
);

Where we group into six hour groupings.

I came up with the following:

insert into traffic_summary
select asn,protocol,
cast(sum(pkts_src) as float) as pkts_src,
cast(sum(pkts_dst) as float) as pkts_dst,
cast(sum(bytes_src) as float) as bytes_src,
cast(sum(bytes_dst) as float) as bytes_dst,
cast(sum(secs_src) as float)  as secs_src,
cast(sum(secs_dst) as float) as secs_dst,
min(early) as early,
max(late) as late 
from traffic 
where early >= '2001-01-01 00:00:00' and     early <= '2001-01-02 05:59:59'
GROUP BY asn,protocol;

BUT, I'm wondering if there is an easy way to generate the obvious 
where clauses automatically?
-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


В списке pgsql-sql по дате отправления:

Предыдущее
От: Roberto Mello
Дата:
Сообщение: Re: PGAccess/pgplsql Blues
Следующее
От: Larry Rosenman
Дата:
Сообщение: Re: trying to summarize into a new table by time...