[SQL] Two optimization questions
Hi,
I think it can be done better than I did and I want to learn...
1. I have a table that registers the history of messages:
output_message_history(id, event_type, event_time)
I need those ID-s from the table where there is one 'MESSAGE SENT' event
and one 'MESSAGE SUBMITTED' event and there are no more events on that
message.
select id
from output_message_history
group by content_id
having
sum(case when event_type='MESSAGE SENT' then 1 else 0 end) = 1
and
sum(case when event_type='MESSAGE SUBMITTED' then 1 else 0 end) = 1
and count(*) = 2
Can it be done another, more optimal way?
2. I have to create reports like this one:
time count
12:00 3
12:01 2
12:02 4
12:03 0 --- now this one is problematic for me
12:04 5
So I need something like this:
select date_trunc('minute', crd), count(*) from subscriber
where crd between '2006-09-08' and '2006-09-12'
group by date_trunc('minute', crd)
But the output of this query won't show minutes with 0 count. I searched
the archives and found an example using a view to solve this problem,
but creating such a view for grouping by minutes or seconds doesn't seem
to be nice solution. Any ideas how to solve this problem?
Thanks for any answers,
Zizi
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Joint a table back on itself?
Hi all, I have a union query that generates a table with directional measurments (a=azimuth, i=depth) at various depths (md) down a hole. The results look like: hole_id | md | a| i|e |n |v ---++++--+--+-- GND-06-65 | 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 GND-06-65 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 GND-06-65 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 GND-06-65 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 GND-06-65 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 GND-06-65 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 The depths ( md column) will always start with zero and the intervals will be variable. So how can I join this view back onto itself so each record is joined to the next record? Such as: md1 | a1 | i1 |e1| n1|v1| md2 | a2 | i2 |e2|n2| v2| +++--+--+--|+++--+--+-- 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 My reason for wanting this is so I can joint this table with a between clause to another table with depth measurments recorded along this hole and perform a calculation. Thanks, Phillip J. Allen Consulting Geochemist [EMAIL PROTECTED] The union query is as follows: SELECT c.hole_id, 0 AS md, c.collar_azimuth AS a, c.collar_dip AS i, c.e_utm AS e, c.n_utm AS n, c.elv_utm AS v FROM dh_collar AS c WHERE (((c.hole_id)='GND-06-65')) UNION SELECT s.hole_id, s.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_survey AS s WHERE (((s.hole_id)='GND-06-65')) UNION SELECT s.hole_id, c.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_collar AS c RIGHT JOIN dh_survey AS s ON c.hole_id = s.hole_id WHERE (((s.depth_m)= ( SELECT Max(stmp.depth_m) AS MaxOfdepth_m FROM dh_survey AS stmp GROUP BY stmp.hole_id HAVING (((stmp.hole_id)='GND-06-65')) )) AND ((s.hole_id)='GND-06-65')); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Sorting items in aggregate function
I would like to concatenate sorted strings in an aggregate function. I found a way to do it without sorting[1], but not with. Here is an example of a setup and what I could like to achieve. Does anyone have suggestions on what is the best way to get the desired result? Thanks, Steven. CREATE TABLE a ( -- Names id INT PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE b ( -- Codes id INT PRIMARY KEY, code CHAR(2) NOT NULL); CREATE TABLE ab ( -- m:n relationship between a and b id SERIAL PRIMARY KEY, a_id INT NOT NULL, b_id INT NOT NULL); COPY a(id,name) FROM STDIN DELIMITER '|'; 1|Alice 2|Bob 3|Charlie \. COPY b(id, code) FROM STDIN DELIMITER '|'; 1|a 2|b 3|c 4|d \. COPY ab(a_id, b_id) FROM STDIN DELIMITER '|'; 2|4 2|1 3|2 3|3 \. -- Custom aggregate function which concatenates strings CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = textcat, STYPE = text, INITCOND = '', ); -- Current query SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes FROM a LEFT JOIN ab ON (a.id=ab.a_id) LEFT JOIN b ON (ab.b_id=b.id) GROUP BY a.name ORDER BY codes; -- Actual output: -- -- name | codes -- -+--- -- Alice | -- Charlie | b c -- Bob | d a -- Desired output: -- -- name | codes -- -+ -- Alice | -- Bob | a d -- Charlie | b c [1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html -- w: http://www.cl.cam.ac.uk/users/sjm217/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Joint a table back on itself?
On 9/12/06 11:55 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi all, I have a union query that generates a table with directional measurments (a=azimuth, i=depth) at various depths (md) down a hole. The results look like: hole_id | md | a| i|e |n |v ---++++--+--+- - GND-06-65 | 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 GND-06-65 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 GND-06-65 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 GND-06-65 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 GND-06-65 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 GND-06-65 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 The depths ( md column) will always start with zero and the intervals will be variable. So how can I join this view back onto itself so each record is joined to the next record? Such as: md1 | a1 | i1 |e1| n1|v1| md2 | a2 | i2 |e2|n2| v2| +++--+--+- -|+++--+--+--- --- 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 My reason for wanting this is so I can joint this table with a between clause to another table with depth measurments recorded along this hole and perform a calculation. Leaving out the extra columns: create table holes( hole_id text, md int, a decimal ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 0, 90 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 19, 90.37 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 28, 91.18 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 37, 91.04 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 46, 92.38 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 55, 92.86 ); go select a.md as md1, a.a as a1, b.md as md2, b.a as a2 from holes a join holes b on b.hole_id = a.hole_id where b.md = ( select min( c.md ) from holes c where c.hole_id = a.hole_id and c.a a.a ) order by a.md, b.md; md1 a1 md2 a2 -- - -- - 0 90 19 90.37 19 90.37 28 91.18 28 91.18 46 92.38 37 91.04 28 91.18 46 92.38 55 92.86 5 record(s) selected [Fetch MetaData: 8/ms] [Fetch Data: 0/ms] [Executed: 9/12/06 2:05:52 PM EDT ] [Execution: 123/ms] Thanks, Phillip J. Allen Consulting Geochemist [EMAIL PROTECTED] The union query is as follows: SELECT c.hole_id, 0 AS md, c.collar_azimuth AS a, c.collar_dip AS i, c.e_utm AS e, c.n_utm AS n, c.elv_utm AS v FROM dh_collar AS c WHERE (((c.hole_id)='GND-06-65')) UNION SELECT s.hole_id, s.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_survey AS s WHERE (((s.hole_id)='GND-06-65')) UNION SELECT s.hole_id, c.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_collar AS c RIGHT JOIN dh_survey AS s ON c.hole_id = s.hole_id WHERE (((s.depth_m)= ( SELECT Max(stmp.depth_m) AS MaxOfdepth_m FROM dh_survey AS stmp GROUP BY stmp.hole_id HAVING (((stmp.hole_id)='GND-06-65')) )) AND ((s.hole_id)='GND-06-65')); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Daryl Email *my = [ daryl at: @eddl dot: @us ]; Weblog *blog = @²http://itsallsemantics.com²; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Sorting items in aggregate function
On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote: Here is an example of a setup and what I could like to achieve. Does anyone have suggestions on what is the best way to get the desired result? Use the aggregate over an ordered subquery: SELECT name, trim(concat(code || ' ')) AS codes FROM ( SELECT a.name, b.code FROM a LEFT JOIN ab ON a.id = ab.a_id LEFT JOIN b ON ab.b_id = b.id ORDER BY b.code ) AS s GROUP BY name ORDER BY name; Here's a comment from Tom Lane, one of the core developers, on feeding the aggregate based on the subquery's order: http://archives.postgresql.org/pgsql-general/2005-09/msg00047.php -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] on connect/on disconnect
On 9/11/06, Robert Edwards [EMAIL PROTECTED] wrote: Markus Schaber wrote: Hi, Robert, Robert Edwards wrote:(this is my first post to this list...) Welcome here. :-)I am wondering if Postgres, and/or SQL in general, has a facility to run a function at connection set-up time (after a successful connectionattempt) and/or at session completion (or disconnect)? Most JDBC connection pooling implementations will do that, at least for connection and session setup. (JBoss is one of them.)I want to pre-populate a table (actually an INSERT rule on a view)with some user-specific data that is unlikely to change during the session and which is difficult to process (ie. affects performanceto do it too often). The problem here is that the INSERT rule might be globally visible to other, concurrent users on the database. Indeed it is, but the sole reason to use a rule (instead of a straightINSERT) is that it qualifies the INSERT against the current user. Could you explain what exactly you want to achieve, may be we find a better way to do the whole thing.Basically, I have a heirarchical arrangement of users in roles (almostthe same as the 8.1 user/group/role mechanism, but in PUBLIC schema space, and with various triggers etc. in play). The access controlsapply conditions based on which roles (groups) the current user isa member of (and these users never have super-user privilege, so the SET SESSION AUTHORIZATION mechanism does not apply here). The heirarchyis not a tree structure - any role can belong to any number of otherroles (have many parents), so it is a Digraph (directed graph). I have some plpgsql functions, one of which is used to determine whichroles a user is in, but it is necessarily recursively called, whichmeans it runs in non-deterministic time.(Just for completeness, I'll include that function here: create or replace function get_anc () returns setof member as 'declare rMem member;begin for rMem in select * from member where child = $1 loop return next rMem; for rMem in select * from get_anc ( rMem.parent) loop return next rMem; end loop; end loop; return;end;' language plpgsql;If your membership (member table) does not change very often, I would recommend adding an insert/update trigger to that table which updates a flattened version of these results (basically a materialized view). Then you don't need to run this function unless the membership changes and only once per membership change. Then you authorize the person against that flattened table rather than look at the member table. Then you wouldn't need to run this upon every log in. I do something like this (though simpler) and have the application get the permissions and cache them. It sounds like you are probably trying to put your permission checking in the database. You might want to consider using a session based temp table and run a different function that first checks the temp table, if the table is empty it runs the function above and populates the temp table, if the table is not empty it uses the temp table. This is a form of lazy instantiation and would not require you to have connect/disconnect triggers. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Sorting items in aggregate function
On Sep 12 04:46, Steven Murdoch wrote: I would like to concatenate sorted strings in an aggregate function. I found a way to do it without sorting[1], but not with. If the array elements will be made of integers, then you can use sort() procedure comes with intarray contrib module. For instance, SELECT concat(T.sorted_arr) FROM (SELECT sort(arr) FROM tbl) AS T (sorted_arr); If related column will also include text values, you can create a suitable sort() procedure for text[] type and use it instead. Regards. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Two optimization questions
On 9/12/06, Mezei Zoltán [EMAIL PROTECTED] wrote:
Hi,I think it can be done better than I did and I want to learn...1. I have a table that registers the history of messages:output_message_history(id, event_type, event_time)I need those ID-s from the table where there is one 'MESSAGE SENT' event
and one 'MESSAGE SUBMITTED' event and there are no more events on thatmessage.select idfrom output_message_historygroup by content_idhavingsum(case when event_type='MESSAGE SENT' then 1 else 0 end) = 1
andsum(case when event_type='MESSAGE SUBMITTED' then 1 else 0 end) = 1and count(*) = 2Can it be done another, more optimal way?You could split it into sub-queries but would that make the performance better or worse? I guess it depends on how much data is there, and what frequency you have ot the event_type's but indexing the event_type column would help. This may be worth a try - use EXPLAIN to see which is better.
SELECT idFROM output_message_historyWHERE NOT content_id IN ( SELECT content_id -- distinct(content_id) FROM output_message_history WHERE NOT event_type IN ('MESSAGE SENT', 'MESSAGE SUBMITTED')
)GROUP BY content_idHAVING sum(case when event_type='MESSAGE SENT' then 1 else 0 end) = 1 AND sum(case when event_type='MESSAGE SUBMITTED' then 1 else 0 end) = 1
2. I have to create reports like this one:time count12:00312:01212:02412:030--- now this one is problematic for me12:045So I need something like this:
select date_trunc('minute', crd), count(*) from subscriberwhere crd between '2006-09-08' and '2006-09-12'group by date_trunc('minute', crd)But the output of this query won't show minutes with 0 count. I searched
the archives and found an example using a view to solve this problem,but creating such a view for grouping by minutes or seconds doesn't seemto be nice solution. Any ideas how to solve this problem?
Questions like this come up frequently and there are some nice solutions:See if this gives you some insight:select '2006-01-15'::date + s.incfrom generate_series(0, ('2006-02-20'::date - '2006-01-15'::date)::integer) as s(inc)
See http://www.postgresql.org/docs/8.1/static/functions-srf.html and
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html for more.== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
[SQL] How to get all users under a group
Hello, I know \du+ can get all group info for each user. Could someone tell me how to get all users under each group please? such as provide the group name, showing all users under the group. Thanks, Emi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sorting items in aggregate function
Michael Fuhr [EMAIL PROTECTED] writes: On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote: Here is an example of a setup and what I could like to achieve. Does anyone have suggestions on what is the best way to get the desired result? Use the aggregate over an ordered subquery: SELECT name, trim(concat(code || ' ')) AS codes FROM ( SELECT a.name, b.code FROM a LEFT JOIN ab ON a.id = ab.a_id LEFT JOIN b ON ab.b_id = b.id ORDER BY b.code ) AS s GROUP BY name ORDER BY name; Note that if you need to GROUP in the outer query, it's best to sort the inner query's output first by the outer query's grouping: SELECT name, trim(concat(code || ' ')) AS codes FROM ( SELECT a.name, b.code FROM a LEFT JOIN ab ON a.id = ab.a_id LEFT JOIN b ON ab.b_id = b.id ORDER BY a.name, b.code ^^ ) AS s GROUP BY name ORDER BY name; This way will still work if the planner decides to use a GroupAggregate (which in fact it probably will, if it sees it can avoid another sort step). The way Michael showed will only work if the plan uses HashAggregate --- if the planner decides it needs Sort+GroupAggregate in the outer query, the re-sort will probably destroy the ordering by b.code. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Sorting items in aggregate function
Steven Murdoch escreveu: I would like to concatenate sorted strings in an aggregate function. I found a way to do it without sorting[1], but not with. Here is an example of a setup and what I could like to achieve. Does anyone have suggestions on what is the best way to get the desired result? Thanks, Steven. CREATE TABLE a ( -- Names id INT PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE b ( -- Codes id INT PRIMARY KEY, code CHAR(2) NOT NULL); CREATE TABLE ab ( -- m:n relationship between a and b id SERIAL PRIMARY KEY, a_id INT NOT NULL, b_id INT NOT NULL); COPY a(id,name) FROM STDIN DELIMITER '|'; 1|Alice 2|Bob 3|Charlie \. COPY b(id, code) FROM STDIN DELIMITER '|'; 1|a 2|b 3|c 4|d \. COPY ab(a_id, b_id) FROM STDIN DELIMITER '|'; 2|4 2|1 3|2 3|3 \. -- Custom aggregate function which concatenates strings CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = textcat, STYPE = text, INITCOND = '', ); -- Current query SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes FROM a LEFT JOIN ab ON (a.id=ab.a_id) LEFT JOIN b ON (ab.b_id=b.id) GROUP BY a.name ORDER BY codes; -- Actual output: -- -- name | codes -- -+--- -- Alice | -- Charlie | b c -- Bob | d a -- Desired output: -- -- name | codes -- -+ -- Alice | -- Bob | a d -- Charlie | b c [1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html Look this message: http://archives.postgresql.org/pgsql-sql/2006-05/msg00044.php []s Osvaldo ___ Você quer respostas para suas perguntas? Ou você sabe muito e quer compartilhar seu conhecimento? Experimente o Yahoo! Respostas ! http://br.answers.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
