pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaggregate query
4+ messages / 3 participants
[nested] [flat]
* aggregate query
@ 2007-05-29 04:32 Raj A <raj.ayappan@gmail.com>
2007-05-29 08:04 ` Re: aggregate query Andrew Kroeger <andrew@sprocks.gotdns.com>
0 siblings, 1 reply; 4+ messages in thread
From: Raj A @ 2007-05-29 04:32 UTC (permalink / raw)
To: pgsql-sql
I have a table
CREATE TABLE survey_load
(
meter_id character(5) NOT NULL,
number_of_bays integer NOT NULL,
bay_1_use integer,
bay_2_use integer,
bay_3_use integer,
bay_4_use integer,
bay_5_use integer,
date date NOT NULL,
inspection_id integer NOT NULL DEFAULT,
)
How do i present an aggregate query
inspection_id | meter_id | bay_use
1 12345 (value of bay_1_use)
1 12345 (value of bay_2_use)
1 12345 (value of bay_3_use)
2 23456 (value of bay_1_use)
2 23456 (value of bay_2_use)
2 23456 (value of bay_3_use)
2 23456 (value of bay_4_use)
2 23456 (value of bay_5_use)
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: aggregate query
2007-05-29 04:32 aggregate query Raj A <raj.ayappan@gmail.com>
@ 2007-05-29 08:04 ` Andrew Kroeger <andrew@sprocks.gotdns.com>
2007-05-29 10:50 ` Re: aggregate query Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andrew Kroeger @ 2007-05-29 08:04 UTC (permalink / raw)
To: Raj A <raj.ayappan@gmail.com>; +Cc: pgsql-sql
Raj A wrote:
> I have a table
>
> CREATE TABLE survey_load
> (
> meter_id character(5) NOT NULL,
> number_of_bays integer NOT NULL,
> bay_1_use integer,
> bay_2_use integer,
> bay_3_use integer,
> bay_4_use integer,
> bay_5_use integer,
> date date NOT NULL,
> inspection_id integer NOT NULL DEFAULT,
> )
>
> How do i present an aggregate query
>
> inspection_id | meter_id | bay_use
> 1 12345 (value of bay_1_use)
> 1 12345 (value of bay_2_use)
> 1 12345 (value of bay_3_use)
> 2 23456 (value of bay_1_use)
> 2 23456 (value of bay_2_use)
> 2 23456 (value of bay_3_use)
> 2 23456 (value of bay_4_use)
> 2 23456 (value of bay_5_use)
Firstly, the table definition you provided has an invalid specification
for the inspection_id column. For my examples, I simply used a "NOT
NULL" constraint, and did not implement any DEFAULT clause.
If I understand your issue correctly, it seems like the denormalized
nature of your table is causing you some problems. With the table
definition and output you provided, I am assuming the following contents
for the survey_load table:
meter_id | number_of_bays | bay_1_use | bay_2_use | bay_3_use |
bay_4_use | bay_5_use | date | inspection_id
----------+----------------+-----------+-----------+-----------+-----------+-----------+------------+---------------
12345 | 3 | 11 | 12 | 13 |
| | 2007-05-29 | 1
23456 | 5 | 21 | 22 | 23 |
24 | 25 | 2007-05-29 | 2
(2 rows)
The value of the number_of_bays column is driving the number of results
in your summary query, as opposed to allowing the data relations to
drive that on their own with a normalized representation. You could
normalize your data representation into 2 tables:
tmp1=> \d+ new_survey_load
Table "new_survey_load"
Column | Type | Modifiers | Description
---------------+--------------+-----------+-------------
survey_id | integer | not null |
meter_id | character(5) | not null |
date | date | not null |
inspection_id | integer | not null |
Indexes:
"new_survey_load_pkey" PRIMARY KEY, btree (survey_id)
Has OIDs: no
tmp1=> \d+ new_bay_use
Table "new_bay_use"
Column | Type | Modifiers | Description
------------+---------+-----------+-------------
survey_id | integer | not null |
bay_number | integer | not null |
bay_use | integer | not null |
Indexes:
"ind_new_bay_use__survey_id" btree (survey_id)
Foreign-key constraints:
"new_bay_use_survey_id_fkey" FOREIGN KEY (survey_id) REFERENCES
new_survey_load(survey_id)
Has OIDs: no
This normalization also makes it easier in the future to track
additional bays without having to alter the table structure.
Following your example from above, those tables would contain the
following values:
tmp1=> select * from new_survey_load ;
survey_id | meter_id | date | inspection_id
-----------+----------+------------+---------------
13 | 12345 | 2007-05-29 | 1
14 | 23456 | 2007-05-29 | 2
(2 rows)
tmp1=> select * from new_bay_use ;
survey_id | bay_number | bay_use
-----------+------------+---------
13 | 1 | 11
13 | 2 | 12
13 | 3 | 13
14 | 1 | 21
14 | 2 | 22
14 | 3 | 23
14 | 4 | 24
14 | 5 | 25
(8 rows)
The query to get the summary results you are looking for would then be:
select
l.inspection_id,
l.meter_id,
u.bay_use
from
new_survey_load l
join new_bay_use u on
l.survey_id = u.survey_id
order by
l.inspection_id, l.meter_id, u.bay_number;
You could also use a view to join the 2 new tables together to match the
format of your original table definition:
select
l.meter_id,
count(u.bay_number) as number_of_bays,
u1.bay_use as bay_1_use,
u2.bay_use as bay_2_use,
u3.bay_use as bay_3_use,
u4.bay_use as bay_4_use,
u5.bay_use as bay_5_use,
l.date,
l.inspection_id
from
new_survey_load l
join new_bay_use u on
l.survey_id = u.survey_id
left join new_bay_use u1 on
l.survey_id = u1.survey_id and u1.bay_number = 1
left join new_bay_use u2 on
l.survey_id = u2.survey_id and u2.bay_number = 2
left join new_bay_use u3 on
l.survey_id = u3.survey_id and u3.bay_number = 3
left join new_bay_use u4 on
l.survey_id = u4.survey_id and u4.bay_number = 4
left join new_bay_use u5 on
l.survey_id = u5.survey_id and u5.bay_number = 5
group by
1,3,4,5,6,7,8,9;
If you add more bays for tracking, you can simply add additional joins
against new_bay_use to mimic what you would have had in your original
table structure.
Hope this helps.
Andrew
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: aggregate query
2007-05-29 04:32 aggregate query Raj A <raj.ayappan@gmail.com>
2007-05-29 08:04 ` Re: aggregate query Andrew Kroeger <andrew@sprocks.gotdns.com>
@ 2007-05-29 10:50 ` Gregory Stark <stark@enterprisedb.com>
2007-05-29 12:32 ` Re: aggregate query Raj A <raj.ayappan@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory Stark @ 2007-05-29 10:50 UTC (permalink / raw)
To: Andrew Kroeger <andrew@sprocks.gotdns.com>; +Cc: Raj A <raj.ayappan@gmail.com>; pgsql-sql
"Andrew Kroeger" <andrew@sprocks.gotdns.com> writes:
> Raj A wrote:
>> I have a table
>>
>> CREATE TABLE survey_load
>> (
>> meter_id character(5) NOT NULL,
>> number_of_bays integer NOT NULL,
>> bay_1_use integer,
>> bay_2_use integer,
>> bay_3_use integer,
>> bay_4_use integer,
>> bay_5_use integer,
>> date date NOT NULL,
>> inspection_id integer NOT NULL DEFAULT,
>> )
>>
>> How do i present an aggregate query
>>
>> inspection_id | meter_id | bay_use
>> 1 12345 (value of bay_1_use)
>> 1 12345 (value of bay_2_use)
>> 1 12345 (value of bay_3_use)
>> 2 23456 (value of bay_1_use)
>> 2 23456 (value of bay_2_use)
>> 2 23456 (value of bay_3_use)
>> 2 23456 (value of bay_4_use)
>> 2 23456 (value of bay_5_use)
>
> If I understand your issue correctly, it seems like the denormalized
> nature of your table is causing you some problems.
True. Normalizing the tables would make this query easier which is a good sign
that that's probably the right direction.
If for some reason you can't or won't change the table definition there are a
number of possible tricky answers given the current definition. Something like
this for example:
SELECT inspection_id, meter_id,
case when bay=1 then bay_1_use
when bay=2 then bay_2_use
when bay=3 then bay_3_use
when bay=4 then bay_4_use
when bay=5 then bay_5_use
else null
end AS bay_use
FROM (
SELECT *, generate_series(1,number_of_bays) AS bay
FROM survey_load
) as x
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: aggregate query
2007-05-29 04:32 aggregate query Raj A <raj.ayappan@gmail.com>
2007-05-29 08:04 ` Re: aggregate query Andrew Kroeger <andrew@sprocks.gotdns.com>
2007-05-29 10:50 ` Re: aggregate query Gregory Stark <stark@enterprisedb.com>
@ 2007-05-29 12:32 ` Raj A <raj.ayappan@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Raj A @ 2007-05-29 12:32 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Andrew Kroeger <andrew@sprocks.gotdns.com>; pgsql-sql
Thank you guys!
I'm currently migrating an Oracle database to postgres and have
created tables using the scripts that were readily available. Glad I
can now improve this old system.
On 29/05/07, Gregory Stark <stark@enterprisedb.com> wrote:
> "Andrew Kroeger" <andrew@sprocks.gotdns.com> writes:
>
> > Raj A wrote:
> >> I have a table
> >>
> >> CREATE TABLE survey_load
> >> (
> >> meter_id character(5) NOT NULL,
> >> number_of_bays integer NOT NULL,
> >> bay_1_use integer,
> >> bay_2_use integer,
> >> bay_3_use integer,
> >> bay_4_use integer,
> >> bay_5_use integer,
> >> date date NOT NULL,
> >> inspection_id integer NOT NULL DEFAULT,
> >> )
> >>
> >> How do i present an aggregate query
> >>
> >> inspection_id | meter_id | bay_use
> >> 1 12345 (value of bay_1_use)
> >> 1 12345 (value of bay_2_use)
> >> 1 12345 (value of bay_3_use)
> >> 2 23456 (value of bay_1_use)
> >> 2 23456 (value of bay_2_use)
> >> 2 23456 (value of bay_3_use)
> >> 2 23456 (value of bay_4_use)
> >> 2 23456 (value of bay_5_use)
>
>
> >
> > If I understand your issue correctly, it seems like the denormalized
> > nature of your table is causing you some problems.
>
> True. Normalizing the tables would make this query easier which is a good sign
> that that's probably the right direction.
>
> If for some reason you can't or won't change the table definition there are a
> number of possible tricky answers given the current definition. Something like
> this for example:
>
> SELECT inspection_id, meter_id,
> case when bay=1 then bay_1_use
> when bay=2 then bay_2_use
> when bay=3 then bay_3_use
> when bay=4 then bay_4_use
> when bay=5 then bay_5_use
> else null
> end AS bay_use
> FROM (
> SELECT *, generate_series(1,number_of_bays) AS bay
> FROM survey_load
> ) as x
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-05-29 12:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-29 04:32 aggregate query Raj A <raj.ayappan@gmail.com>
2007-05-29 08:04 ` Andrew Kroeger <andrew@sprocks.gotdns.com>
2007-05-29 10:50 ` Gregory Stark <stark@enterprisedb.com>
2007-05-29 12:32 ` Raj A <raj.ayappan@gmail.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