Re: 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 | 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

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(at)yahoo(dot)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(at)postgresql(dot)org)
>
>
>