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...
Re: trying to summarize into a new table by time... Re: trying to summarize into a new table by time... |
Список | 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 по дате отправления: