CASE inet << inet ...
Just trying to summarize some traffic stats, and am either running the
query wrong, or you can't do this?
The query is:
SELECT CASE WHEN to_ip << '216.126.84.0/24' THEN to_ip ELSE from_ip END AS LocalAddr,
sum(bytes) as TotalBytes, date_trunc('day', runtime) AS Day
FROM stat_log
WHERE date_trunc('day', runtime) = '2001-01-02 00:00:00-05'
GROUP BY LocalAddr, Day;
returns:
localaddr | totalbytes | day
-----------------+------------+------------------------
24.6.125.174 | 13716 | 2001-01-02 00:00:00-05
24.43.137.113 | 13140 | 2001-01-02 00:00:00-05
24.128.201.128 | 14376 | 2001-01-02 00:00:00-05
64.39.38.43 | 14232 | 2001-01-02 00:00:00-05
128.11.44.16 | 25050 | 2001-01-02 00:00:00-05
130.149.17.13 | 14316 | 2001-01-02 00:00:00-05
142.177.197.180 | 179676 | 2001-01-02 00:00:00-05
151.164.30.54 | 13260 | 2001-01-02 00:00:00-05
166.84.192.39 | 13614 | 2001-01-02 00:00:00-05
192.67.198.32 | 13872 | 2001-01-02 00:00:00-05
192.245.12.7 | 14676 | 2001-01-02 00:00:00-05
193.228.80.12 | 13092 | 2001-01-02 00:00:00-05
194.126.24.131 | 21642 | 2001-01-02 00:00:00-05
194.209.182.36 | 14448 | 2001-01-02 00:00:00-05
195.46.202.129 | 73518 | 2001-01-02 00:00:00-05
195.117.86.253 | 13056 | 2001-01-02 00:00:00-05
196.38.110.24 | 15012 | 2001-01-02 00:00:00-05
202.160.254.40 | 38178 | 2001-01-02 00:00:00-05
207.123.82.5 | 15240 | 2001-01-02 00:00:00-05
207.136.80.247 | 25290 | 2001-01-02 00:00:00-05
208.158.96.110 | 17940 | 2001-01-02 00:00:00-05
209.47.145.10 | 2881400 | 2001-01-02 00:00:00-05
209.47.148.2 | 3263955 | 2001-01-02 00:00:00-05
209.223.182.2 | 222180 | 2001-01-02 00:00:00-05
212.43.217.25 | 22974 | 2001-01-02 00:00:00-05
216.126.72.6 | 1265472 | 2001-01-02 00:00:00-05
216.126.72.30 | 94615 | 2001-01-02 00:00:00-05
216.126.84.1 | 201733744 | 2001-01-02 00:00:00-05
216.126.84.10 | 151665 | 2001-01-02 00:00:00-05
216.126.84.11 | 103630 | 2001-01-02 00:00:00-05
216.126.84.14 | 752305 | 2001-01-02 00:00:00-05
Yet:
select * from stat_log_holding where from_ip << '216.126.84.0/24';
returns what I'd expect:
from_ip | to_ip | port | bytes | runtime
----------------+-----------------+------+----------+------------------------
216.126.84.1 | 212.7.160.126 | 873 | 16091760 | 2001-01-16 10:53:14-05
216.126.84.28 | 195.176.0.212 | 80 | 10247530 | 2001-01-16 10:53:14-05
216.126.84.73 | 193.172.127.85 | 80 | 7856477 | 2001-01-16 10:53:14-05
216.126.84.73 | 195.149.181.21 | 80 | 6343572 | 2001-01-16 10:53:14-05
216.126.84.1 | 216.126.84.253 | 53 | 4401161 | 2001-01-16 10:53:14-05
216.126.84.28 | 195.230.44.100 | 80 | 3157811 | 2001-01-16 10:53:14-05
216.126.84.95 | 194.206.159.140 | 80 | 3140439 | 2001-01-16 10:53:14-05
So, am I doing something wrong here, as far as that CASE statement is
concerned, or is this a bug in v7.0.3 that is fixed in v7.1?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
Just trying to summarize some traffic stats, and am either running the
query wrong, or you can't do this?
I can't tell if there's anything wrong with that or not. You didn't
show us the input data being used by the CASE expression ...
regards, tom lane
ah shit, trying to come up with an example, I figured out what I did ...
some of the records don't fall into that range, so of course, to_ip isn't,
so it just displays teh fron_ip *sigh*
I'm going back to sleep now ... :(
On Tue, 16 Jan 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
Just trying to summarize some traffic stats, and am either running the
query wrong, or you can't do this?I can't tell if there's anything wrong with that or not. You didn't
show us the input data being used by the CASE expression ...regards, tom lane
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org