[SQL] assistance on self join pls

[email lists]

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 [EMAIL PROTECTED])

Re: [SQL] query not using index for descending records?

[email lists]

Hi,

| You probably don't want to do that. The DESC only applies to the 
| one expression it follows. What you want is probably: 
| explain select datetime,id from trafficlogs order by 
| datetime desc,id desc limit 20;

This is exactly what I was after - worked a treat!

Thanks.

Darren

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] date_trunc for 5 minutes intervals

[email lists]

Hi,

Thanks for the several replies both on and off the list. 

To be more specific, I am wanting to aggregate data to a 5/10/15 min
interval. Currently, I am aggregating data that falls in hour / day /
month / year periods for both count() and sum(). The sql I am currently
using is:

SELECT count(id) AS count, sum(conn_bytes) AS
sum, hisec_port, conn_protocol,
date_trunc('hour'::text, datetime) AS date_trunc
FROM trafficlogs
WHERE (conn_outbound = false)
GROUP BY date_trunc('hour'::text, datetime),
conn_protocol, hisec_port
HAVING (count(*) = ANY (
SELECT count(*) AS count
FROM trafficlogs
GROUP BY hisec_port, date_trunc('hour'::text, datetime)
ORDER BY count(*) DESC)
);


Which produces:

count sum  hisec_portconn_protocol date_trunc
12192  5,050   2003/09/17 00:00:00
11176  5,050   2003/09/17 01:00:00
12192  5,050   2003/09/17 02:00:00
11176  5,050   2003/09/17 03:00:00
10160  5,050   2003/09/17 04:00:00


- if you know of a more efficient way to do this than the sql above, pls
let me know

In my previous post I should have said I wanted to aggregating data in
5/10/15 min intervals in a similar manner to the above


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html