Thread: assistance on self join pls
Hi all,
I have the following firewall connection data.
datetime | protocol | port | inside_ip | outside_ip
| outbound_count | outbound_bytes
---------------------+----------+-------+----------------+--------------
--+----------------+---------------2004-05-05 05:00:00 | 6 | 21 | 192.168.11.191 |
205.227.137.53 | 6 | 38812004-05-05 05:00:00 | 6 | 22326 | 192.168.11.191 |
205.227.137.53 | 1 | 25922004-05-05 05:00:00 | 6 | 38005 | 192.168.11.191 |
205.227.137.53 | 1 | 512862004-05-05 05:00:00 | 6 | 51861 | 192.168.11.191 |
205.227.137.53 | 1 | 424602004-05-05 05:00:00 | 6 | 52095 | 192.168.11.191 |
205.227.137.53 | 1 | 25582004-05-05 05:00:00 | 6 | 59846 | 192.168.11.191 |
205.227.137.53 | 1 | 1182004-05-05 05:00:00 | 6 | 60243 | 192.168.11.191 |
205.227.137.53 | 1 | 20922004-05-05 06:00:00 | 6 | 21 | 192.168.11.185 |
205.227.137.53 | 6 | 38142004-05-05 06:00:00 | 6 | 29799 | 192.168.11.185 |
205.227.137.53 | 1 | 1182004-05-05 06:00:00 | 6 | 30138 | 192.168.11.185 |
205.227.137.53 | 1 | 20922004-05-05 06:00:00 | 6 | 30215 | 192.168.11.185 |
205.227.137.53 | 1 | 424602004-05-05 06:00:00 | 6 | 51279 | 192.168.11.185 |
205.227.137.53 | 1 | 13322004-05-05 06:00:00 | 6 | 52243 | 192.168.11.185 |
205.227.137.53 | 1 | 512862004-05-05 06:00:00 | 6 | 60079 | 192.168.11.185 |
205.227.137.53 | 1 | 2558
I am wanting to aggregate / collapse each entry to something similar to:
datetime | protocol | port | inside_ip | outside_ip
| outbound_count | outbound_bytes
---------------------+----------+-------+----------------+--------------
--+----------------+---------------2004-05-05 05:00:00 | 6 | 21 | 192.168.11.191 |
205.227.137.53 | 12 | 1049872004-05-05 06:00:00 | 6 | 21 | 192.168.11.185 |
205.227.137.53 | 12 | 103660
I have not had much success - any assistance greatly appreciated
Darren
PostgreSQL 7.4.2 ...
Background: I'm attempting to migrate tables which were created in the
pre-schema days to a sensible schema setup. I'm using the "uniqueidentifier"
column in some of these tables. When I created the new schema, I created an
instance of "uniqueidentifier" and its supporting functions and casts within
the new schema. When I try to "INSERT INTO myschema.mytable ... SELECT ... FROM
public.mytable;" It's having difficulty seeing that the data types are
compatible across the schema. An explicit cast (without first casting to a
neuter data-type) won't work for the same reason.
I'm torn: Should I create a "cast" to allow for casting of this data-type
across schemas, or should I have created the table referencing the user-defined
type in the public schema?
I expect that this problem will rise up now and again. I'd like to solve it in
the this early phase with a proper deisgn-based fix.
If it makes a difference, I would like to not include this schema in the search
path, to explicitly refer to it as myschema.mytable anywhere I need to
reference it.
CG
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
Chris Gamache <cgg007@yahoo.com> writes:
> I'm using the "uniqueidentifier" column in some of these tables. When
> I created the new schema, I created an instance of "uniqueidentifier"
> and its supporting functions and casts within the new schema. When I
> try to "INSERT INTO myschema.mytable ... SELECT ... FROM
> public.mytable;" It's having difficulty seeing that the data types are
> compatible across the schema.
Indeed, since as far as the system knows those two datatypes have
nothing to do with each other. I'd go ahead and define an assignment
cast WITHOUT FUNCTION to let you do the conversion.
regards, tom lane
Dear Darren, Your question is not very clear to me. On what columns do you want to aggregate? suppose u want to aggregate on outsite and inside ip you shud group by those columns and run a aggregate function like sum or avg etc , suppose u want the total traffic for every pair you can do this: select inside_ip,outside_ip , sum(outbound_bytes) as total_traffic from connection_data group by inside_ip,outside_ip ; Hope it helps. Regds Mallah. email lists wrote: >Hi all, > >I have the following firewall connection data. > > datetime | protocol | port | inside_ip | outside_ip >| outbound_count | outbound_bytes >---------------------+----------+-------+----------------+-------------- >--+----------------+--------------- > 2004-05-05 05:00:00 | 6 | 21 | 192.168.11.191 | >205.227.137.53 | 6 | 3881 > 2004-05-05 05:00:00 | 6 | 22326 | 192.168.11.191 | >205.227.137.53 | 1 | 2592 > 2004-05-05 05:00:00 | 6 | 38005 | 192.168.11.191 | >205.227.137.53 | 1 | 51286 > 2004-05-05 05:00:00 | 6 | 51861 | 192.168.11.191 | >205.227.137.53 | 1 | 42460 > 2004-05-05 05:00:00 | 6 | 52095 | 192.168.11.191 | >205.227.137.53 | 1 | 2558 > 2004-05-05 05:00:00 | 6 | 59846 | 192.168.11.191 | >205.227.137.53 | 1 | 118 > 2004-05-05 05:00:00 | 6 | 60243 | 192.168.11.191 | >205.227.137.53 | 1 | 2092 > 2004-05-05 06:00:00 | 6 | 21 | 192.168.11.185 | >205.227.137.53 | 6 | 3814 > 2004-05-05 06:00:00 | 6 | 29799 | 192.168.11.185 | >205.227.137.53 | 1 | 118 > 2004-05-05 06:00:00 | 6 | 30138 | 192.168.11.185 | >205.227.137.53 | 1 | 2092 > 2004-05-05 06:00:00 | 6 | 30215 | 192.168.11.185 | >205.227.137.53 | 1 | 42460 > 2004-05-05 06:00:00 | 6 | 51279 | 192.168.11.185 | >205.227.137.53 | 1 | 1332 > 2004-05-05 06:00:00 | 6 | 52243 | 192.168.11.185 | >205.227.137.53 | 1 | 51286 > 2004-05-05 06:00:00 | 6 | 60079 | 192.168.11.185 | >205.227.137.53 | 1 | 2558 > >I am wanting to aggregate / collapse each entry to something similar to: > > datetime | protocol | port | inside_ip | outside_ip >| outbound_count | outbound_bytes >---------------------+----------+-------+----------------+-------------- >--+----------------+--------------- > 2004-05-05 05:00:00 | 6 | 21 | 192.168.11.191 | >205.227.137.53 | 12 | 104987 > 2004-05-05 06:00:00 | 6 | 21 | 192.168.11.185 | >205.227.137.53 | 12 | 103660 > >I have not had much success - any assistance greatly appreciated > >Darren > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > >