pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDenormalizing during select
11+ messages / 7 participants
[nested] [flat]
* Denormalizing during select
@ 2003-02-24 17:18 no.spam@address.com
0 siblings, 1 reply; 11+ messages in thread
From: no.spam@address.com @ 2003-02-24 17:18 UTC (permalink / raw)
To: pgsql-sql
I have two tables (A and B) in a one-to-many relationship. When
retrieving data from one table (A), I need to denormalize the tables
during a select so that values of a column in the many table (B)
appear as a list in one field of the output. E.g.:
create table one_table (
name varchar(20),
age integer,
--
constraint one_table_pk
primary key (name)
);
create table many_table (
name varchar(20),
property varchar(15),
--
constraint many_table_pk
primary key (name, property),
constraint many_table_fk
foreign key (name) references one_table (name)
);
So one_table could contain:
name age
---- ---
Mickey Mouse 50
Donald Duck 60
Goofy 45
and many_table could contain:
name property
---- --------
Mickey Mouse Small
Mickey Mouse Black
Mickey Mouse Squeaks
Donald Duck Quacks
The query I'm trying to write would have as output something like:
name age properties
---- --- ----------
Mickey Mouse 50 Small, Black, Squeaks
Donald Duck 60 Quacks
Goofy 45 <null>
Could somebody please help point me in the right direction? I imagine
this is a common need...
Thanks,
...Edmund.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-02-24 19:12 Jeff Boes <jboes@nexcerpt.com>
parent: no.spam@address.com
0 siblings, 2 replies; 11+ messages in thread
From: Jeff Boes @ 2003-02-24 19:12 UTC (permalink / raw)
To: pgsql-sql
On Mon, 24 Feb 2003 12:18:24 -0500, no.spam wrote:
> I have two tables (A and B) in a one-to-many relationship. When
> retrieving data from one table (A), I need to denormalize the tables
> during a select so that values of a column in the many table (B) appear
> as a list in one field of the output.
Well, the straightforward way to do this would be with a function:
create or replace function my_func(text) returns text as '
declare
name_one alias for $1;
accum text;
result record;
begin
accum := \'\';
for result in select property from many_table where name = name_one
loop
if accum != \'\'
then
accum := accum || \',\';
end if;
accum := accum || result.property;
end loop;
return accum;
end;
' language 'plpgsql';
Note that the quotes internal to the function must be escaped.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-02-24 22:33 Josh Berkus <josh@agliodbs.com>
parent: Jeff Boes <jboes@nexcerpt.com>
1 sibling, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2003-02-24 22:33 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; pgsql-sql
Jeff,
> > I have two tables (A and B) in a one-to-many relationship. When
> > retrieving data from one table (A), I need to denormalize the tables
> > during a select so that values of a column in the many table (B) appear
> > as a list in one field of the output.
>
> Well, the straightforward way to do this would be with a function:
Can you post the start of this thread? I think there's a more efficient way
using custom aggregates, but somhow I never received the original question.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-02-25 03:37 no.spam@address.com
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: no.spam@address.com @ 2003-02-25 03:37 UTC (permalink / raw)
To: pgsql-sql
On Tue, 25 Feb 2003 01:22:42 +0000 (UTC), josh@agliodbs.com (Josh
Berkus) wrote:
>Can you post the start of this thread? I think there's a more
>efficient way using custom aggregates, but somhow I never
>received the original question.
I'm hoping there is... somehow, using a function to loop over every
row seems a bit inefficient? Here's my original question...
I have two tables (A and B) in a one-to-many relationship. When
retrieving data from one table (A), I need to denormalize the tables
during a select so that values of a column in the many table (B)
appear as a list in one field of the output. E.g.:
create table one_table (
name varchar(20),
age integer,
--
constraint one_table_pk
primary key (name)
);
create table many_table (
name varchar(20),
property varchar(15),
--
constraint many_table_pk
primary key (name, property),
constraint many_table_fk
foreign key (name) references one_table (name)
);
So one_table could contain:
name age
---- ---
Mickey Mouse 50
Donald Duck 60
Goofy 45
and many_table could contain:
name property
---- --------
Mickey Mouse Small
Mickey Mouse Black
Mickey Mouse Squeaks
Donald Duck Quacks
The query I'm trying to write would have as output something like:
name age properties
---- --- ----------
Mickey Mouse 50 Small, Black, Squeaks
Donald Duck 60 Quacks
Goofy 45 <null>
Could somebody please help point me in the right direction? I imagine
this is a common need...
Thanks,
...Edmund.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-02-25 03:44 Edmund Lian <no.spam@address.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 11+ messages in thread
From: Edmund Lian @ 2003-02-25 03:44 UTC (permalink / raw)
To: pgsql-sql
Jeff and Josh,
I found this example in "Practical PostgreSQL"... will it do the job?
"""
The following example defines an aggregate function named sum(), for
use with the text data type. This aggregate calls the
textcat(text,text) function built into PostgreSQL to return a
concatenated "sum" of all the text found in its input values:
booktown=# CREATE AGGREGATE sum ( BASETYPE = text,
booktown(# SFUNC = textcat,
booktown(# STYPE = text,
booktown(# INITCOND = '' );
CREATE
booktown=# SELECT sum(title || ' ') FROM books WHERE title ~ '^L';
sum
-------------------------------
Little Women Learning Python
(1 row)
"""
...Edmund.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-02-25 18:24 Josh Berkus <josh@agliodbs.com>
parent: Jeff Boes <jboes@nexcerpt.com>
1 sibling, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2003-02-25 18:24 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; pgsql-sql
Jeff,
> > I have two tables (A and B) in a one-to-many relationship. When
> > retrieving data from one table (A), I need to denormalize the tables
> > during a select so that values of a column in the many table (B) appear
> > as a list in one field of the output.
>
> Well, the straightforward way to do this would be with a function:
Actually, it's much easier to do this using a custom aggregate:
CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 = '''' THEN $1
WHEN $1 IS NULL OR $1 = '''' THEN $2
ELSE $1 || '', '' || $2
END
' LANGUAGE 'sql';
CREATE AGGREGATE comma_list ( BASETYPE = text, SFUNC = comma_cat, STYPE =
text,
INITCOND = '' );
SELECT a.id, a.col1, a.col2, comma_list(b.text)
FROM a JOIN b on a.id = b.a_id
GROUP BY a.id, a.col1, a.col2
The only drawback of this approach is that you cannot order the items in the
list, but it is *much* faster than the function method that Jeff outlined.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-02-25 18:48 Edmund Lian <no.spam@address.com>
parent: Edmund Lian <no.spam@address.com>
0 siblings, 3 replies; 11+ messages in thread
From: Edmund Lian @ 2003-02-25 18:48 UTC (permalink / raw)
To: pgsql-sql
On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote:
>I found this example in "Practical PostgreSQL"... will it do the job?
Answering my own question: kind of. The problem with custom aggregates
is that they need to be used with a "group by" clause, and this means
that the select cannot return columns that are not aggregates of some
kind. What I'm trying to return are rows that are a combination of
columns and aggregates.
...Edmund.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-03-01 17:07 Rod Taylor <rbt@rbt.ca>
parent: Edmund Lian <no.spam@address.com>
2 siblings, 1 reply; 11+ messages in thread
From: Rod Taylor @ 2003-03-01 17:07 UTC (permalink / raw)
To: Edmund Lian <no.spam@address.com>; +Cc: pgsql-sql
On Tue, 2003-02-25 at 13:48, Edmund Lian wrote:
> On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote:
>
> >I found this example in "Practical PostgreSQL"... will it do the job?
>
> Answering my own question: kind of. The problem with custom aggregates
> is that they need to be used with a "group by" clause, and this means
> that the select cannot return columns that are not aggregates of some
> kind. What I'm trying to return are rows that are a combination of
> columns and aggregates.
I've been trying to figure out how to give a running total (similar
issue I think).
key value
1 5
1 5
1 5
2 1
2 2
2 1
Query output:
key value sum to point
1 5 5
1 5 10
1 5 15
2 1 1
2 2 3
2 1 4
I think I should be able to do it with an aggregate -- but the best I've
been able to come up with is a Set Returning Function.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-03-01 17:07 Rod Taylor <rbt@rbt.ca>
parent: Edmund Lian <no.spam@address.com>
2 siblings, 0 replies; 11+ messages in thread
From: Rod Taylor @ 2003-03-01 17:07 UTC (permalink / raw)
To: Edmund Lian <no.spam@address.com>; +Cc: pgsql-sql
On Tue, 2003-02-25 at 13:48, Edmund Lian wrote:
> On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote:
>
> >I found this example in "Practical PostgreSQL"... will it do the job?
>
> Answering my own question: kind of. The problem with custom aggregates
> is that they need to be used with a "group by" clause, and this means
> that the select cannot return columns that are not aggregates of some
> kind. What I'm trying to return are rows that are a combination of
> columns and aggregates.
I've been trying to figure out how to give a running total (similar
issue I think).
key value
1 5
1 5
1 5
2 1
2 2
2 1
Query output:
key value sum to point
1 5 5
1 5 10
1 5 15
2 1 1
2 2 3
2 1 4
I think I should be able to do it with an aggregate -- but the best I've
been able to come up with is a Set Returning Function.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-03-01 17:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Edmund Lian <no.spam@address.com>
2 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2003-03-01 17:13 UTC (permalink / raw)
To: Edmund Lian <no.spam@address.com>; +Cc: pgsql-sql
Edmund Lian <no.spam@address.com> writes:
> Answering my own question: kind of. The problem with custom aggregates
> is that they need to be used with a "group by" clause, and this means
> that the select cannot return columns that are not aggregates of some
> kind. What I'm trying to return are rows that are a combination of
> columns and aggregates.
This strikes me as evidence of fuzzy thinking. What sets of rows are
the aggregates taken over? Which column values within those sets of
rows do you expect the non-aggregated column references to return?
If the columns aren't the ones grouped by, seems like you have an
inherently undefined result.
If you know for some reason that there will be only one unique value
of a column in a grouped row set, or you don't actually much care which
one you get, then you could use MIN() or MAX() on the column reference
to make it look like a kosher query.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Denormalizing during select
@ 2003-03-01 17:53 Greg Stark <gsstark@mit.edu>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Greg Stark @ 2003-03-01 17:53 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Edmund Lian <no.spam@address.com>; pgsql-sql
Rod Taylor <rbt@rbt.ca> writes:
> I've been trying to figure out how to give a running total (similar
> issue I think).
Running totals are a "hard problem". They certainly cannot be solved using
aggregates. They're similar to the ranking problem of assigning a sequential
number to each item within each group.
The problem is they share certain properties of aggregate functions, namely
that they require persistent state storage and a state transition function.
But they definitely aren't aggregate functions in that they return a value for
every record, not one for the whole group.
I'm not even clear how to write an embedded (plpgsql or perl or python)
function, since I'm not clear how to allocate space for the state that will be
available for each call on each record but independent from other calls in the
same query. You have to be able to handle two running totals at the same time.
Note that running totals are not very sql-ish. Since sql deals in unordered
sets a running total is pretty ill-defined. It would have to be calculated
after the sort operation or else require you to sort the input tables in a
subquery or something.
To write a proper well-defined sql-ish query for running totals you would have
to do the very inefficient:
select employee_id, salary,
(select count(*) from employees x where x.salary < employee.salary) as salary_rank,
(select sum(salary) from employees x where x.salary < employee.salary) as running_total
from employees
order by salary desc
Finding a way to transform that into the single-scan plan that's obviously the
right way to execute it would be really cool but seems pretty far-fetched. I
don't think any database is capable of it today.
--
greg
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-03-01 17:53 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-24 17:18 Denormalizing during select no.spam@address.com
2003-02-24 19:12 ` Jeff Boes <jboes@nexcerpt.com>
2003-02-24 22:33 ` Josh Berkus <josh@agliodbs.com>
2003-02-25 03:37 ` no.spam@address.com
2003-02-25 03:44 ` Edmund Lian <no.spam@address.com>
2003-02-25 18:48 ` Edmund Lian <no.spam@address.com>
2003-03-01 17:07 ` Rod Taylor <rbt@rbt.ca>
2003-03-01 17:53 ` Greg Stark <gsstark@mit.edu>
2003-03-01 17:07 ` Rod Taylor <rbt@rbt.ca>
2003-03-01 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-25 18:24 ` Josh Berkus <josh@agliodbs.com>
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