pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMerging lines with NULLs (with example data)
4+ messages / 3 participants
[nested] [flat]
* Merging lines with NULLs (with example data)
@ 2005-10-22 16:25 MaXX <bs139412@skynet.be>
0 siblings, 1 reply; 4+ messages in thread
From: MaXX @ 2005-10-22 16:25 UTC (permalink / raw)
To: pgsql-sql
Good afternoon,
I have a simple problem, and I feel stupid not finding myself what's the
solution... I try to explain shortly, but as I'm not really confident with
my explanation, I provided a complete example with data below.
How can I "merge" this
gday,count_udp,count_tcp
'2005-10-20','','2'
'2005-10-20','3',''
'2005-10-21','','1'
'2005-10-21','5',''
into that:
gday,count_udp,count_tcp
'2005-10-20','3','2'
'2005-10-21','5','1'
in a single query???
Thanks in advance,
MaXX
Here's all the details:
I have a table
CREATE TABLE test
(
id serial NOT NULL,
tstamp timestamptz,
host varchar(80),
rulenr int4,
act varchar(10),
proto varchar(4),
src_ip inet,
src_port int4,
dst_ip inet,
dst_port int4,
dir varchar(3),
if varchar(5),
reported bool,
protected bool,
CONSTRAINT pk_ipfw_id PRIMARY KEY (id)
)
WITH OIDS;
Data:
INSERT INTO test VALUES (453639,'2005-10-21
09:39:19+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (453634,'2005-10-21
09:36:21+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (453633,'2005-10-21
09:36:20+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (452610,'2005-10-21
03:33:28+02','akar',600,'Deny','TCP','10.182.174.7',4310,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (451735,'2005-10-21
00:11:52+02','akar',600,'Deny','UDP','10.216.48.231',6778,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (451734,'2005-10-21
00:11:50+02','akar',600,'Deny','UDP','10.216.48.231',6778,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448277,'2005-10-20
16:31:17+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448276,'2005-10-20
16:31:15+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448266,'2005-10-20
16:29:08+02','akar',600,'Deny','TCP','10.224.32.85',63891,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448265,'2005-10-20
16:29:05+02','akar',600,'Deny','TCP','10.224.32.85',63891,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448258,'2005-10-20
16:28:16+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
when I execute a query like this,
select to_date(tstamp,'YYYY-MM-DD')as gday,
case when proto='UDP'
then count(id)
else NULL
end as count_udp,
case when proto='TCP'
then count(id)
else NULL
end as count_tcp
from test
where tstamp >= (now() - interval '$days days' )
and dst_port = $port
group by gday, proto
order by gday;
I get:
gday,count_udp,count_tcp
'2005-10-20','','2'
'2005-10-20','3',''
'2005-10-21','','1'
'2005-10-21','5',''
This is not what I want, I want that:
gday,count_udp,count_tcp
'2005-10-20','3','2'
'2005-10-21','5','1'
--
MaXX
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Merging lines with NULLs (with example data)
@ 2005-10-25 13:42 Harald Fuchs <hf0923x@protecting.net>
parent: MaXX <bs139412@skynet.be>
0 siblings, 1 reply; 4+ messages in thread
From: Harald Fuchs @ 2005-10-25 13:42 UTC (permalink / raw)
To: pgsql-sql
In article <djdp5l$1l4f$1@talisker.lacave.net>,
MaXX <bs139412@skynet.be> writes:
> How can I "merge" this
> gday,count_udp,count_tcp
> '2005-10-20','','2'
> '2005-10-20','3',''
> '2005-10-21','','1'
> '2005-10-21','5',''
> into that:
> gday,count_udp,count_tcp
> '2005-10-20','3','2'
> '2005-10-21','5','1'
> in a single query???
Try something like that:
SELECT to_date (tstamp,'YYYY-MM-DD') AS gday,
sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
FROM test
WHERE tstamp >= now() - INTERVAL '$days DAYS'
AND dst_port = $port
GROUP BY gday
ORDER BY gday
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Merging lines with NULLs (with example data)
@ 2005-10-25 14:03 Daryl Richter <daryl@brandywine.com>
parent: Harald Fuchs <hf0923x@protecting.net>
0 siblings, 1 reply; 4+ messages in thread
From: Daryl Richter @ 2005-10-25 14:03 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Harald Fuchs wrote:
> In article <djdp5l$1l4f$1@talisker.lacave.net>,
> MaXX <bs139412@skynet.be> writes:
>
>
>>How can I "merge" this
>>gday,count_udp,count_tcp
>>'2005-10-20','','2'
>>'2005-10-20','3',''
>>'2005-10-21','','1'
>>'2005-10-21','5',''
>
>
>>into that:
>>gday,count_udp,count_tcp
>>'2005-10-20','3','2'
>>'2005-10-21','5','1'
>
>
>>in a single query???
>
>
> Try something like that:
>
> SELECT to_date (tstamp,'YYYY-MM-DD') AS gday,
> sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
> sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
> FROM test
> WHERE tstamp >= now() - INTERVAL '$days DAYS'
> AND dst_port = $port
> GROUP BY gday
> ORDER BY gday
>
Or, via a subquery:
select distinct to_date(tstamp,'YYYY-MM-DD') as gday,
( select count(id) from test t1 where proto='UDP' and
to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
count_udp,
( select count(id) from test t1 where proto='TCP' and
to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
count_tcp
from test
where tstamp >= (now() - interval '6 days' )
and dst_port = 2290
order by gday;
Harald's solution is better for your particular case and will almost
certainly be faster, but subqueries are good to know how to do. :)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Daryl Richter
Platform Author & Director of Technology
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Merging lines with NULLs (with example data)
@ 2005-10-27 20:07 MaXX <bs139412@skynet.be>
parent: Daryl Richter <daryl@brandywine.com>
0 siblings, 0 replies; 4+ messages in thread
From: MaXX @ 2005-10-27 20:07 UTC (permalink / raw)
To: pgsql-sql
Thank you, and sorry for the late answer, I was far away from a decent
internet connection...
I'll try both your solutions, EXPLAIN ANALYSE will elect the winner... In
any case that will be cleaner than my dirty hack (2 distinct queries) which
generate a lot of garbage...
Thanks again,
MaXX
Daryl Richter wrote:
> Harald Fuchs wrote:
>> Try something like that:
>> SELECT to_date (tstamp,'YYYY-MM-DD') AS gday,
>> sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
>> sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
>> FROM test
>> WHERE tstamp >= now() - INTERVAL '$days DAYS'
>> AND dst_port = $port
>> GROUP BY gday
>> ORDER BY gday
> Or, via a subquery:
> select distinct to_date(tstamp,'YYYY-MM-DD') as gday,
> ( select count(id) from test t1 where proto='UDP' and
> to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
> count_udp,
> ( select count(id) from test t1 where proto='TCP' and
> to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
> count_tcp
> from test
> where tstamp >= (now() - interval '6 days' )
> and dst_port = 2290
> order by gday;
>
> Harald's solution is better for your particular case and will almost
> certainly be faster, but subqueries are good to know how to do. :)
--
MaXX
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-27 20:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-22 16:25 Merging lines with NULLs (with example data) MaXX <bs139412@skynet.be>
2005-10-25 13:42 ` Harald Fuchs <hf0923x@protecting.net>
2005-10-25 14:03 ` Daryl Richter <daryl@brandywine.com>
2005-10-27 20:07 ` MaXX <bs139412@skynet.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox