Thread: efficient count/join query
Hi folks, I've got two tables, first a history table containing tallies for staff/jobs prior to going live, and second a roster table showing date, diagram, job with one record per person per job per day. the tables are: create table history ( hsid int4 not null references staff(sid), hjid int4 not null references jobs(jid), hcount int4, primary key (hsid,hjid)); create table roster ( rodate date not null, rogid int4 not null references diagrams(gid), rojid int4 not null references jobs(jid), rosid int4 references staff(sid), primary key (rodate, rogid, rojid)); What's the best/quickest/cheapest way to create a view in the format of the history table but including the details from the roster table for all records prior to today. I've been looking at some form of sub-select/join scheme but as some will only exist on the history and some will only exist on the roster while many will exist on both. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary Stainburn wrote: > Hi folks, > > I've got two tables, first a history table containing tallies for staff/jobs > prior to going live, and second a roster table showing date, diagram, job > with one record per person per job per day. the tables are: > > create table history ( > hsid int4 not null references staff(sid), > hjid int4 not null references jobs(jid), > hcount int4, > primary key (hsid,hjid)); > > create table roster ( > rodate date not null, > rogid int4 not null references diagrams(gid), > rojid int4 not null references jobs(jid), > rosid int4 references staff(sid), > primary key (rodate, rogid, rojid)); > > What's the best/quickest/cheapest way to create a view in the format of the > history table but including the details from the roster table for all records > prior to today. > > I've been looking at some form of sub-select/join scheme but as some will only > exist on the history and some will only exist on the roster while many will > exist on both. Hello again. What if they exists in both tables - you need only one row result? If yes, you should use FULL OUTER JOIN and COALESCE. selectcoalesce(hjid,rjid) as jid,coalesce(hsid,rsid) as sid,hcount,rodate,rogid fromhistoryfull outer join roster on (hjid=rjid and hsid=rosid) Using other names for the same field in other tables comes again - If you have the same name for jid and sid, you wouldn't need coalesce. Regards, Tomasz Myrta
On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > Gary Stainburn wrote: > > Hi folks, > > > > I've got two tables, first a history table containing tallies for > > staff/jobs prior to going live, and second a roster table showing date, > > diagram, job with one record per person per job per day. the tables are: > > > > create table history ( > > hsid int4 not null references staff(sid), > > hjid int4 not null references jobs(jid), > > hcount int4, > > primary key (hsid,hjid)); > > > > create table roster ( > > rodate date not null, > > rogid int4 not null references diagrams(gid), > > rojid int4 not null references jobs(jid), > > rosid int4 references staff(sid), > > primary key (rodate, rogid, rojid)); > > > > What's the best/quickest/cheapest way to create a view in the format of > > the history table but including the details from the roster table for all > > records prior to today. > > > > I've been looking at some form of sub-select/join scheme but as some will > > only exist on the history and some will only exist on the roster while > > many will exist on both. > > Hello again. > > What if they exists in both tables - you need only one row result? > If yes, you should use FULL OUTER JOIN and COALESCE. > > select > coalesce(hjid,rjid) as jid, > coalesce(hsid,rsid) as sid, > hcount, > rodate, > rogid > from > history > full outer join roster on (hjid=rjid and hsid=rosid) > > Using other names for the same field in other tables comes again - > If you have the same name for jid and sid, you wouldn't need coalesce. > > Regards, > Tomasz Myrta Hi Tomasz, I don't think you understand what I mean. The history table could be thought of as the following SQL statement if the data had actually existed. This table actually represents a manually input summary of the pre-computerised data. insert into history select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; If I have a history of hsid | hjid | hcount ------+------+-------- 1 | 2 | 3 1 | 3 | 1 5 | 5 | 4 6 | 5 | 3 9 | 4 | 4 14 | 5 | 4 and I have a roster of rodate | rogid | rojid | rosid -----------+-------+-------+------- 2003-02-15 | 1 | 2 | 1 2003-02-15 | 1 | 5 | 5 2003-02-16 | 1 | 5 | 1 I want my view to show hsid | hjid | hcount ------+------+-------- 1 | 2 | 4 1 | 3 | 1 1 | 5 | 1 5 | 5 | 5 6 | 5 | 3 9 | 4 | 4 14 | 5 | 4 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Friday 07 Feb 2003 12:09 pm, Gary Stainburn wrote: > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > > Gary Stainburn wrote: > > > Hi folks, > > > > > > I've got two tables, first a history table containing tallies for > > > staff/jobs prior to going live, and second a roster table showing date, > > > diagram, job with one record per person per job per day. the tables > > > are: > > > > > > create table history ( > > > hsid int4 not null references staff(sid), > > > hjid int4 not null references jobs(jid), > > > hcount int4, > > > primary key (hsid,hjid)); > > > > > > create table roster ( > > > rodate date not null, > > > rogid int4 not null references diagrams(gid), > > > rojid int4 not null references jobs(jid), > > > rosid int4 references staff(sid), > > > primary key (rodate, rogid, rojid)); > > > > > > What's the best/quickest/cheapest way to create a view in the format of > > > the history table but including the details from the roster table for > > > all records prior to today. > > > > > > I've been looking at some form of sub-select/join scheme but as some > > > will only exist on the history and some will only exist on the roster > > > while many will exist on both. > > > > Hello again. > > > > What if they exists in both tables - you need only one row result? > > If yes, you should use FULL OUTER JOIN and COALESCE. > > > > select > > coalesce(hjid,rjid) as jid, > > coalesce(hsid,rsid) as sid, > > hcount, > > rodate, > > rogid > > from > > history > > full outer join roster on (hjid=rjid and hsid=rosid) > > > > Using other names for the same field in other tables comes again - > > If you have the same name for jid and sid, you wouldn't need coalesce. > > > > Regards, > > Tomasz Myrta > > Hi Tomasz, > > I don't think you understand what I mean. > > The history table could be thought of as the following SQL statement if the > data had actually existed. This table actually represents a manually input > summary of the pre-computerised data. > > insert into history > select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; > > If I have a history of > > hsid | hjid | hcount > ------+------+-------- > 1 | 2 | 3 > 1 | 3 | 1 > 5 | 5 | 4 > 6 | 5 | 3 > 9 | 4 | 4 > 14 | 5 | 4 > > and I have a roster of > > rodate | rogid | rojid | rosid > -----------+-------+-------+------- > 2003-02-15 | 1 | 2 | 1 > 2003-02-15 | 1 | 5 | 5 > 2003-02-16 | 1 | 5 | 1 > > I want my view to show > > hsid | hjid | hcount > ------+------+-------- > 1 | 2 | 4 > 1 | 3 | 1 > 1 | 5 | 1 > 5 | 5 | 5 > 6 | 5 | 3 > 9 | 4 | 4 > 14 | 5 | 4 Thinking about it, I'm not wanting to perform a join as such, but a merge of the two selects below, then some form of group by to sum() the two counts. select rosid as sid, rojid as jid, count(*) as count from roster group by sid, jid order by sid, jid; select hsid as sid, hjid as jid, hcount as count from history order by sid, jid; so that 1 2 1 1 3 2 and 1 3 1 1 4 2 becomes 1 2 1 1 3 3 1 4 2 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary Stainburn wrote: > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > <cut> > Hi Tomasz, > > I don't think you understand what I mean. > > The history table could be thought of as the following SQL statement if the > data had actually existed. This table actually represents a manually input > summary of the pre-computerised data. > > insert into history > select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; > > If I have a history of > > hsid | hjid | hcount > ------+------+-------- > 1 | 2 | 3 > 1 | 3 | 1 > 5 | 5 | 4 > 6 | 5 | 3 > 9 | 4 | 4 > 14 | 5 | 4 > > and I have a roster of > > rodate | rogid | rojid | rosid > -----------+-------+-------+------- > 2003-02-15 | 1 | 2 | 1 > 2003-02-15 | 1 | 5 | 5 > 2003-02-16 | 1 | 5 | 1 > > I want my view to show > > hsid | hjid | hcount > ------+------+-------- > 1 | 2 | 4 > 1 | 3 | 1 > 1 | 5 | 1 > 5 | 5 | 5 > 6 | 5 | 3 > 9 | 4 | 4 > 14 | 5 | 4 > I understood your problem well and I just thought some idea will be enough to continue work. Here is detailed query for your problem: create view some_view as selectcoalesce(hjid,rjid) as jid,coalesce(hsid,rsid) as sid,coalesce(hcount,1)+count(*)-1 as hcount fromhistoryfull outer join roster on (hjid=rjid and hsid=rosid) group by hjid,rjid,hsid,rosid; Regards, Tomasz Myrta
On Friday 07 Feb 2003 12:57 pm, Gary Stainburn wrote: > > Thinking about it, I'm not wanting to perform a join as such, but a merge > of the two selects below, then some form of group by to sum() the two > counts. > > select rosid as sid, rojid as jid, count(*) as count > from roster group by sid, jid order by sid, jid; > select hsid as sid, hjid as jid, hcount as count > from history order by sid, jid; > > so that > > 1 2 1 > 1 3 2 > > and > > 1 3 1 > 1 4 2 > > becomes > > 1 2 1 > 1 3 3 > 1 4 2 You want a UNION, something like (untested): CREATE VIEW all_counts AS SELECT rosid as sid, rojid as jid, count(*) as all_count FROM roster GROUP BY sid,jid UNION ALL SELECT hsid,hjid,hcount FROM history; SELECT sid,jid,SUM(all_count) FROM all_counts GROUP BY sid,jid; -- Richard Huxton
Hola a todos! Intento hacer una aplicacion en java que va contra pgsql y me gustaria bloquear registros. Un jps llama a un metodo de una clase para modificar un registro. Lo primero que hace es una consulta del registro (aquí quiero bloquear el registro) y despues se muestra en el navegador el resultado, un usuario lo modifica, pulsa el botón de actualizar y lo actualiza, desbloqueo. He probado con select for update y lo hace bien, solo que si un usuario quiere consultarlo para modificar tambien el mismo registro, se queda esperando a que el otro acabe la actualización. Hay alguna manera de detectar que ese registro se está consultando en select for update para que no se quede esperando o hay alguna forma de hacerlo? Gracias por todos y un saludo
On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote: > Gary Stainburn wrote: > > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > > <cut> > > > Hi Tomasz, > > [snip] > > I understood your problem well and I just thought some idea will be enough > to continue work. > > Here is detailed query for your problem: > > create view some_view as > select > coalesce(hjid,rjid) as jid, > coalesce(hsid,rsid) as sid, > coalesce(hcount,1)+count(*)-1 as hcount > from > history > full outer join roster on (hjid=rjid and hsid=rosid) > group by hjid,rjid,hsid,rosid; > > Regards, > Tomasz Myrta Many appologoes Tomasz. Because your select *LOOKED* wrong to me, I didn't even try it. Upon looking at it again I can see what you're doing. When I tried, it complained about the counts and grouping, so I moved the count(*) to a sub-select and changed the coalesce and it's working. Thanks, Gary > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary Stainburn wrote: > On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote: > >>Gary Stainburn wrote: >> <cut> > > Many appologoes Tomasz. No problem > > Because your select *LOOKED* wrong to me, I didn't even try it. Upon looking > at it again I can see what you're doing. When I tried, it complained about > the counts and grouping, so I moved the count(*) to a sub-select and changed > the coalesce and it's working. There were 2 bugs in this query: - in group by - add field hcount - counting when any roster doesn't exist for some history. Here is (I suppose) final query: create view some_view as selectcoalesce(hjid,rjid) as jid,coalesce(hsid,rsid) as sid,coalesce(hcount,0)+ sum(case when rjid is not null then 1 else0 end) as hcount fromhistoryfull outer join roster on (hjid=rjid and hsid=rosid) group by hjid,rjid,hsid,rosid,hcount; Tomasz Myrta