Thread: caching subtotals: update vs sum -- aaugh!
to save a few cycles, i'm hoping to cache subtotals of
subsidiary records into the corresponding parent records -- but
i can't figure out how to update the parent table with the sums
of the child table fields:
create table invoice (
id serial,
bal numeric(8,2)
cost numeric(8,2),
charge numeric(8,2),
);
create table line_item (
id serial,
item_id integer references invoice ( id ),
bal numeric(8,2)
cost numeric(8,2),
charge numeric(8,2),
);
selecting the sums is easy:
select
item_id,
sum(bal) as bal,
sum(cost) as cost,
sum(charge) as charge
from
line_item
group by
item_id;
but i can't seem to get by cerebellum around how to flow all the
subtotals upstream -- these don't work:
-- all totals wind up in ONE invoice record
update invoice set
bal = sum(line_item.bal),
cost = sum(line_item.cost),
charge = sum(line_item.charge)
where line_item.item_id = set.id;
-- syntax error at 'group'
update invoice set
bal = sum(line_item.bal),
cost = sum(line_item.cost),
charge = sum(line_item.charge)
from line_item
where line_item.item_id = set.id GROUP BY line_item.item_id;
the next one works, but not even within earshot of 'elegant'.
there's GOTTA be a slicker way, right?
-- ridiculosity of redundancy but it limps into the right result
update invoice set
bal = (select sum(line_item.bal) where item_id = invoice.id),
cost = (select sum(line_item.cost) where item_id = invoice.id),
charge = (select sum(line_item.charge) where item_id = invoice.id)
from line_item
where line_item.item_id = set.id GROUP BY line_item.item_id;
or should i settle for something like
select
item_id,
sum(bal) as bal,
sum(cost) as cost,
sum(charge) as charge
into
fooey
from
line_item
group by
item_id
;
update invoice set
bal = fooey.bal,
cost = fooey.cost,
charge = fooey.charge
where fooey.item_id = id
;
drop table fooey
;
...? seems a bit of the old "long-way-around"... surely there's
a way--
--
DEBIAN NEWBIE TIP #110 from Dimitri Maziuk <dmaziuk@yola.bmrb.wisc.edu>
:
Here's how to TUNNEL SECURE X11 CONNECTIONS THROUGH SSH: on the
client, do this:
client# export DISPLAY=client:0.0
client# ssh -X server
then once you're logged in at the server, do:
server# netscape &
The environment created at the server will include the DISPLAY
variable, so netscape (or whatever) will dialogue with the
client machine. (See "man ssh" for more.)
Also see http://newbieDoc.sourceForge.net/ ...
On Thu, 10 Jan 2002, will trillich wrote: > or should i settle for something like > > select > item_id, > sum(bal) as bal, > sum(cost) as cost, > sum(charge) as charge > into > fooey > from > line_item > group by > item_id > ; > update invoice set > bal = fooey.bal, > cost = fooey.cost, > charge = fooey.charge > where fooey.item_id = id > ; > drop table fooey > ; > > ...? seems a bit of the old "long-way-around"... surely there's > a way-- A stored procedure sounds the way ahead to me. DO it with a trigger if you want updates to happen automagically whenever the line_items are changed: http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html http://www.postgresql.org/idocs/index.php?sql-createtrigger.html -- Alaric B. Snell, Developer abs@frontwire.com
Will, Where does 'set.id' come from? If this was a typo, was it in the sql query you executed? I would think that your first attempt should have worked if 'set.id' was replaced with 'invoice.id'. Best of luck, Andrew Gould --- will trillich <will@serensoft.com> wrote: > to save a few cycles, i'm hoping to cache subtotals > of > subsidiary records into the corresponding parent > records -- but > i can't figure out how to update the parent table > with the sums > of the child table fields: > > create table invoice ( > id serial, > bal numeric(8,2) > cost numeric(8,2), > charge numeric(8,2), > ); > create table line_item ( > id serial, > item_id integer references invoice ( id ), > bal numeric(8,2) > cost numeric(8,2), > charge numeric(8,2), > ); > > selecting the sums is easy: > > select > item_id, > sum(bal) as bal, > sum(cost) as cost, > sum(charge) as charge > from > line_item > group by > item_id; > > but i can't seem to get by cerebellum around how to > flow all the > subtotals upstream -- these don't work: > > -- all totals wind up in ONE invoice record > update invoice set > bal = sum(line_item.bal), > cost = sum(line_item.cost), > charge = sum(line_item.charge) > where line_item.item_id = set.id; > > -- syntax error at 'group' > update invoice set > bal = sum(line_item.bal), > cost = sum(line_item.cost), > charge = sum(line_item.charge) > from line_item > where line_item.item_id = set.id GROUP BY > line_item.item_id; > > the next one works, but not even within earshot of > 'elegant'. > there's GOTTA be a slicker way, right? > > -- ridiculosity of redundancy but it limps into the > right result > update invoice set > bal = (select sum(line_item.bal) where item_id = > invoice.id), > cost = (select sum(line_item.cost) where item_id > = invoice.id), > charge = (select sum(line_item.charge) where > item_id = invoice.id) > from line_item > where line_item.item_id = set.id GROUP BY > line_item.item_id; > > or should i settle for something like > > select > item_id, > sum(bal) as bal, > sum(cost) as cost, > sum(charge) as charge > into > fooey > from > line_item > group by > item_id > ; > update invoice set > bal = fooey.bal, > cost = fooey.cost, > charge = fooey.charge > where fooey.item_id = id > ; > drop table fooey > ; > > ...? seems a bit of the old "long-way-around"... > surely there's > a way-- > > -- > DEBIAN NEWBIE TIP #110 from Dimitri Maziuk > <dmaziuk@yola.bmrb.wisc.edu> > : > Here's how to TUNNEL SECURE X11 CONNECTIONS THROUGH > SSH: on the > client, do this: > client# export DISPLAY=client:0.0 > client# ssh -X server > then once you're logged in at the server, do: > server# netscape & > The environment created at the server will include > the DISPLAY > variable, so netscape (or whatever) will dialogue > with the > client machine. (See "man ssh" for more.) > > Also see http://newbieDoc.sourceForge.net/ ... > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/
will trillich <will@serensoft.com> writes:
> but i can't seem to get by cerebellum around how to flow all the
> subtotals upstream -- these don't work:
> -- all totals wind up in ONE invoice record
> update invoice set
> bal = sum(line_item.bal),
> cost = sum(line_item.cost),
> charge = sum(line_item.charge)
> where line_item.item_id = set.id;
UPDATEs containing top-level aggregate functions don't really work
correctly. SQL92 forbids such things entirely, suggesting that they
think it's not well-defined. Postgres doesn't currently reject the
query, but the behavior is rather broken IMHO. See past discussions
in the archives about whether we should reject this, and what it should
mean if we don't.
As for your example with UPDATE ... GROUP BY, I don't believe that that
will get past the parser.
The temp table is probably the right way to go, ugly as it is.
The only standards-conformant way to do it in one query would be three
independent subselects:
update invoice set
bal = (select sum(line_item.bal) from line_item where item_id = invoice.id),
cost = (select sum(line_item.cost) from line_item where item_id = invoice.id),
charge = (select sum(line_item.charge) from line_item where item_id = invoice.id);
and the amount of extra computation needed to do it that way is large.
Or ... wait a second. How about
update invoice set
bal = ss.bal,
cost = ss.cost,
charge = ss.charge
from
(select
item_id,
sum(bal) as bal,
sum(cost) as cost,
sum(charge) as charge
from
line_item
group by
item_id) ss
where ss.item_id = invoice.id;
I haven't tried this but it seems like it should work. Better check the
quality of the generated plan though. The temp table might be faster.
regards, tom lane
subtotalling child records into a parent field -- HOWTO? > --- will trillich <will@serensoft.com> wrote: > > create table invoice ( > > id serial, > > bal numeric(8,2) > > cost numeric(8,2), > > charge numeric(8,2), > > ); > > create table line_item ( > > id serial, > > item_id integer references invoice ( id ), > > bal numeric(8,2) > > cost numeric(8,2), > > charge numeric(8,2), > > ); > > i can't seem to get by cerebellum around how to > > flow all the > > subtotals upstream -- these don't work: > > > > -- all totals wind up in ONE invoice record > > update invoice set > > bal = sum(line_item.bal), > > cost = sum(line_item.cost), > > charge = sum(line_item.charge) > > where line_item.item_id = INVOICE.id; > > > > -- syntax error at 'group' > > update invoice set > > bal = sum(line_item.bal), > > cost = sum(line_item.cost), > > charge = sum(line_item.charge) > > from line_item > > where line_item.item_id = INVOICE.id GROUP BY > > line_item.item_id; On Thu, Jan 10, 2002 at 05:06:01AM -0800, Andrew Gould wrote: > Where does 'set.id' come from? If this was a typo, > was it in the sql query you executed? I would think > that your first attempt should have worked if 'set.id' > was replaced with 'invoice.id'. yes, that was a typo (which i fixed in my quoted sql above). and no, it doesn't do what i want. (it DOES if i have just ONE single invoice record in the table -- all totals wind up in just one invoice record.) if you have an example that DOES work i'd love to see it! i'm guessing that this isn't really an obscure task (subtotalling child records into a parent field) but getting it to work just ain't happenin' fer me yet. i'm SURE there's gotta be an sql-friendly way to do this... ? -- DEBIAN NEWBIE TIP #119 from Jonathan D. Proulx <jon@ai.mit.edu> : Having trouble RUNNING REMOTE X APPLICATIONS? You've tried "xhost +<host>", set the DISPLAY variable on the remote session, and checked that the "-nolisten tcp" flag is *not* being sent at X startup, right? Verify that X is really listening: "netstat -tl" will show all listening tcp ports; you should see port 6000 open if display :0 is listening (6001 for :1 etc.) If it is listening, I'd start wondering about packet filtering rules. Check ipchains or iptables... Also see http://newbieDoc.sourceForge.net/ ...
On Thu, Jan 10, 2002 at 10:41:50AM -0500, Tom Lane wrote:
> Or ... wait a second. How about
>
> update invoice set
> bal = ss.bal,
> cost = ss.cost,
> charge = ss.charge
> from
> (select
> item_id,
> sum(bal) as bal,
> sum(cost) as cost,
> sum(charge) as charge
> from
> line_item
> group by
> item_id) ss
> where ss.item_id = invoice.id;
>
> I haven't tried this but it seems like it should work. Better check the
> quality of the generated plan though. The temp table might be faster.
this is why we like having you around, Tom. you're sneaky in all
the right places. :)
i knew there was a solution in there somewhere...
UPDATE _acct SET
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
GROUP BY
acct_id) ppp
WHERE
acct_id = ppp.acct_id;
this seems like it'd properly update all _acct records with
appropriate subtotals from child _prop records. BUT-- after
doing just that (above), given this data in then _prop table,
db=# select id,acct_id,charge,cost from _prop;
id | acct_id | charge | cost
----+---------+--------+-------
3 | 4 | 0.00 | 0.00
4 | 3 | 0.00 | 0.00
5 | 2 | 210.98 | 7.25
2 | 2 | 384.95 | 40.00
(4 rows)
(then i do the update, above) i wind up with the following in
the _acct table:
db=# select id,charge,cost from _acct;
id | charge | cost
----+--------+-------
3 | 595.93 | 47.25
4 | 595.93 | 47.25
2 | 595.93 | 47.25
(3 rows)
the amounts are right for _acct.id = 2, but should be zero for
both of the others. arggh!
for completeness, i tried aliasing the main table, to no avail:
UPDATE _acct a SET -- alias for subquery?
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
WHERE -- hmm?
acct_id = a.id
GROUP BY
acct_id) ppp
WHERE
acct_id = ppp.acct_id;
ERROR: parser: parse error at or near "a"
i'm hoping to add this to a rule:
ON UPDATE TO acct DO INSTEAD (
UPDATE _acct SET
f1 = NEW.f1,
f2 = NEW.f2,
-- yada yada
fN = NEW.fN
WHERE
id = OLD.id
;
UPDATE _acct SET
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
-- WHERE
-- acct_id = OLD.acct_id
GROUP BY
acct_id
) ppp
WHERE
id = OLD.acct_id;
);
i'm not having any luck with this, though. i guess it's okay to
do the CREATE TEMP TABLE... DROP TABLE... within a rule, right?
:(
--
DEBIAN NEWBIE TIP #36 from Sean Quinlan <smq@gmx.co.uk>
:
Looking to CHANGE THE DEFAULT LS COLORS? It's simple: first,
dircolors -p >~/.dircolors
and then edit the results to suit your tastes; finally, insert
eval `dircolors -b ~/.dircolors`
in your ~/.bashrc. Next time you log in (or source ~/.bashrc)
your new colors will take effect.
Also see http://newbieDoc.sourceForge.net/ ...
On Fri, Jan 11, 2002 at 03:11:16PM -0600, i whined about:
> > the amounts are right for _acct.id = 2, but should be zero for
> > both of the others. arggh!
then Tom Lane <tgl@sss.pgh.pa.us> replied
> [ scratches head ] This may represent a bug. I'm too lazy today to
> whip up a test case based on your emails --- could I trouble you for
> a script that creates and loads the test tables?
here i had my hopes up. alas, it was only me and my bungles.
i think i found it-- this finally works (sure would like to have
the subquery use the WHERE, though...)
create rule acct_edit as
on update to acct
do instead (
update _acct set
code = NEW.code,
charge = p.charge,
cost = p.cost
from (
select
sum(charge) as charge,
sum(cost ) as cost,
acct_id
from
_prop
-- where
-- acct_id = OLD.id -- can't see *OLD* record here
group by
acct_id
) p
where
id = OLD.id
and
p.acct_id = OLD.id;
);
i also tried creating a temp table within the rule, but didn't
get very far. (and BOY is it crucial to have an index on
_prop(acct_id)!)
--
as for where the problem sat:
UPDATE _acct SET
cost = ppp.cost,
charge = ppp.charge
FROM
(SELECT
acct_id,
SUM(cost) AS cost,
SUM(charge) AS charge
FROM
_prop
GROUP BY
acct_id) ppp
WHERE
acct_id = ppp.acct_id;
the WHERE was wrong -- shoulda been
WHERE
id -- _acct.id, in top query
=
ppp.acct_id -- subquery from _prop.acct_id
;
--
DEBIAN NEWBIE TIP #60 from Vineet Kumar <debian-user@virtual.doorstop.net>
:
Been hoping to find A FEATURE-PACKED MUTT CONFIG FILE? Check
out the ones at Sven Guckes' site:
http://www.fefe.de/muttfaq/muttrc
There's also some great vimrc ideas there, too.
Also see http://newbieDoc.sourceForge.net/ ...