[SQL] Merging lines with NULLs (with example data)
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,'-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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Merging lines with NULLs (with example data)
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,'-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,'-MM-DD') as gday, > ( select count(id) from test t1 where proto='UDP' and > to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as > count_udp, > ( select count(id) from test t1 where proto='TCP' and > to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] generate_series to return row that doesn't exist in table...
Hi, I have a table wich contains aggregated data, table stats_activity logtime timestamptz, count int given this dataset "2006-03-24 03:00:00+01";55 "2006-03-24 04:00:00+01";33 "2006-03-24 06:00:00+01";46 "2006-03-24 07:00:00+01";63 "2006-03-24 08:00:00+01";88 I want to get this in order to plot the data "2006-03-24 03:00:00+01";55 "2006-03-24 04:00:00+01";33 >>"2006-03-24 05:00:00+01";0<< "2006-03-24 06:00:00+01";46 "2006-03-24 07:00:00+01";63 "2006-03-24 08:00:00+01";88 I used generate_series to get all the timestamps I need but I don't know how to write my query. I've tried various combination of subselects, joins, union,... and I never managed to get the result I wanted... I'm sure the solution is trivial but I don't get it... I prefer to generate missing rows "on the fly" intead of actually storing useless data on the table. Thanks for your help, -- MaXX ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] generate_series to return row that doesn't exist in
On Friday 24 March 2006 21:42, Scott Marlowe wrote: > More than likely you need a left join and a case statement. > > select , case when a.date is null then 0 else a.date end > from (select * from generate_series() -- magic to get dates goes here) > as p left join maintable as a on (p.date=a.date); > > There may be some small syntax error in there, as I've not tested it. > The relavent pages are: > > case: > http://www.postgresql.org/docs/8.1/static/functions-conditional.html > > joins: > http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html Perfect!! Time to RTFM again... Thanks, -- MaXX ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with optional parameters
Rob Tester wrote: I have the need to have optional values for a query in a stored procedure that I am building. (using postgres 8.1.4). This particular query executes against a huge table (several million rows) and has six optional parameters that can be sent to the function. If one of the parameters is null then the parameter doesn't need to be included in the query. Also the values in the columns that are optional can contain NULL values. One way that will work (although extremely cumbersome) is to have a switch on the parameters to execute the correct query: --This is a sample IF (a IS NULL AND b IS NULL) THEN select * from my_table; ELSEIF (a IS NOT NULL and b IS NULL) THEN select * from my_table where a=parama; ELSEIF (a IS NULL and b IS NOT NULL) THEN select * from my_table where b=paramb; ELSE select * from my_table where a=parama AND b=paramb; ENDIF; This is extremely bad when you have 6 parameters giving 64 possible queries. You can try something like this, it should show the basis, CREATE OR REPLACE FUNCTION test(int4, int4) RETURNS text AS $BODY$ DECLARE a ALIAS FOR $1; b ALIAS FOR $2; -- add as many as input param query_base text; has_param bool; query_where text; query_final text; BEGIN query_base := 'SELECT * FROM my_table '; has_param := FALSE; query_where := ''; IF (a IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'parama='||a; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 param END IF; IF (b IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'paramb='||b; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 param END IF; --copy/paste/edit this IF ENDIF block for each param query_final := query_base || query_where; RAISE NOTICE '%', query_final; RETURN query_final; -- EXECUTE query_final; -- END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; test: select test(null,null) union select test(1,null) union select test(1,1) union select test(null,1); result: "SELECT * FROM my_table " "SELECT * FROM my_table WHERE parama=1" "SELECT * FROM my_table WHERE parama=1 AND paramb=1" "SELECT * FROM my_table WHERE paramb=1" HTH, -- MaXX ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help with optional parameters
Curtis Scheer wrote: > I noticed this one by searching in the archives, as I am working with some > "optional" parameters myself and noticed your solution. I just wanted to > make one improvement suggestion which is instead of checking whether or not > a parameter has been used simply start your query like so > Query_base := 'SELECT * FROM my_table WHERE 1 =1; > If you do that then you can just add on any parameters you need or not add > any at all. I think that seems to be a bit simpler than having a has_param" > Boolean variable. True, I think I was too focused on the "clean" output, your solution wastes less cycles and is more readable... Snipet updated, Thanks, -- MaXX ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
