Thread: assistance on self join pls

assistance on self join pls

From
"email lists"
Date:
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


Schemata & User-Defined-Type casting issues

From
Chris Gamache
Date:
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/ 


Re: Schemata & User-Defined-Type casting issues

From
Tom Lane
Date:
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


Re: assistance on self join pls

From
Rajesh Kumar Mallah
Date:
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)
>
>  
>