From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "Alexandre Leclerc" <alexandre(dot)leclerc(at)gmail(dot)com> |
Cc: | "PERFORM" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Flattening a kind of 'dynamic' table |
Date: | 2005-01-27 15:44:45 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3412A75ED@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Alexandre wrote:
> Here a normal listing of design.product_department_time:
> product_id | department_id | req_time
> ------------+---------------+----------
> 906 | A | 3000
> 906 | C | 3000
> 906 | D | 1935
> 907 | A | 1500
> 907 | C | 1500
> 907 | D | 4575
> 924 | A | 6000
> 924 | C | 1575
> product_id | a | c | d
> ------------+------+------+------
> 924 | 6000 | 1575 |
> 907 | 1500 | 1500 | 4575
> 906 | 3000 | 3000 | 1935
ok, you have a couple of different options here. The first thing that
jumps out at me is to use arrays to cheat using arrays.
Let's start with the normalized result set.
select product_id, department_id, sum(req_time) group by product_id,
department_id
product_id | department_id | sum
924 a 6000
924 c 1575
907 a 1500
[...]
This should be no slower (in fact faster) then your original query and
does not have to be re-coded when you add new departments (you have a
department table, right?).
If you absolutely must have 1 record/product, you can cheat using
arrays:
select q.product_id,
array_accum(q.department_id) as depts,
array_accum(q.req_time) as times
from
(
select product_id, department_id, sum(req_time) as req_time
group by product_id, department_id
) q
group by q.product_id;
select product_id, array_accum(department_id) sum(req_time) group by
product_id
product_id | department_id | sum
924 {a, c} {1500, 1575}
[...]
disclaimer 1: I never checked syntax
disclaimer 2: you may have to add array_accum to pg (check docs)
Merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Dawid Kuroczko | 2005-01-27 16:27:40 | Re: Flattening a kind of 'dynamic' table |
Previous Message | Alexandre Leclerc | 2005-01-27 15:23:34 | Flattening a kind of 'dynamic' table |