pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedefficient count/join query
8+ messages / 3 participants
[nested] [flat]
* efficient count/join query
@ 2003-02-07 10:07 Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Gary Stainburn @ 2003-02-07 10:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-02-07 10:48 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-02-07 10:48 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
@ 2003-02-07 12:09 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 12:57 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 13:26 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 8+ messages in thread
From: Gary Stainburn @ 2003-02-07 12:09 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-02-07 12:57 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 13:36 ` Re: efficient count/join query Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Gary Stainburn @ 2003-02-07 12:57 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 12:57 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-02-07 13:36 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2003-02-07 13:36 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-02-07 13:26 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-07 14:21 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-02-07 13:26 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 13:26 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
@ 2003-02-07 14:21 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 14:38 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Gary Stainburn @ 2003-02-07 14:21 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: efficient count/join query
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 13:26 ` Re: efficient count/join query Tomasz Myrta <jasiek@klaster.net>
2003-02-07 14:21 ` Re: efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-02-07 14:38 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 8+ messages in thread
From: Tomasz Myrta @ 2003-02-07 14:38 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
select
coalesce(hjid,rjid) as jid,
coalesce(hsid,rsid) as sid,
coalesce(hcount,0)+
sum(case when rjid is not null then 1 else 0 end) as hcount
from
history
full outer join roster on (hjid=rjid and hsid=rosid)
group by hjid,rjid,hsid,rosid,hcount;
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-02-07 14:38 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-07 10:07 efficient count/join query Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 10:48 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-07 12:09 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 12:57 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 13:36 ` Richard Huxton <dev@archonet.com>
2003-02-07 13:26 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-07 14:21 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-07 14:38 ` Tomasz Myrta <jasiek@klaster.net>
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