X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 72B6E3A5F84 for ; Thu, 27 Jan 2005 17:43:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89112-08 for ; Thu, 27 Jan 2005 17:43:29 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id E75F83A5F49 for ; Thu, 27 Jan 2005 17:43:28 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 40so277830wri for ; Thu, 27 Jan 2005 09:43:25 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=YfRdaZZqz52YknNrp85eRvObTScMvCbx1BwY7c6A3s4TUPivb1F9WMVFadZu5NKR8BsBskoRbKk8ttnie/E/ffGyArXBFzE+jcOGFSWPkRrtm3V7sSW5qwNyC3pJC1RSxO8lJTIIYBEt3Vbj8un+M9YN08CtMkbkFSqtnr+D7eU= Received: by 10.54.20.36 with SMTP id 36mr150235wrt; Thu, 27 Jan 2005 09:43:25 -0800 (PST) Received: by 10.54.20.47 with HTTP; Thu, 27 Jan 2005 09:43:25 -0800 (PST) Message-ID: <1dc7f0e30501270943c8ad16@mail.gmail.com> Date: Thu, 27 Jan 2005 12:43:25 -0500 From: Alexandre Leclerc Reply-To: Alexandre Leclerc To: Merlin Moncure Subject: Re: Flattening a kind of 'dynamic' table Cc: PERFORM In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A75ED@Herge.rcsinc.local> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <6EE64EF3AB31D5448D0007DD34EEB3412A75ED@Herge.rcsinc.local> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/547 X-Sequence-Number: 10237 On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure wrote: > 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 > [...] Hello Merlin, First of all, thanks for your time. Yes this is exactly what I'm doing right now (if I understand well your point here). All records in design.product_department_time are unique for each (product_id, req_time) combo and 0-null values are not possible. This is the first listing you have. In my query I added the sum() and GROUP BY stuff to avoid having such a listing: product_id | a | c | d ------------+------+------+------ 906 | 3000 | | 906 | | 3000 | 906 | | | 1935 907 | 1500 | | 907 | | 1500 | 907 | | | 4575 924 | 6000 | | 924 | | 1575 | So that for a given product_id I have all the times by departments in a single row (second listing I posted). > 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} > [...] I did not used arrays because I didn't think about it, but I don't know if this is still the most efficient way. My software will have to work out the data, unless the array expands in good columns. But I'm not an expert at all. I try to do good DB design, but sometimes this is more complicated to work with the data. Here is the table definition if it can help: design.products (product_id serial PRIMARY KEY, ...); prod.departments (department_id varchar(3) PRIMARY KEY, ...); design.product_department_time ( product_id integer REFERENCES design.products ON DELETE CASCADE ON UPDATE CASCADE, department_id varchar(3) REFERENCES prod.departments ON DELETE CASCADE ON UPDATE CASCADE, req_time integer NOT NULL DEFAULT 0 CHECK (req_time >= 0), CONSTRAINT product_department_time_pkey PRIMARY KEY (product_id, department_id) ); And i also have a jobs table which has one product_id attached to one job with the required quantity to produce. So I must shouw the user how much time this will require by departments for each jobs. :) This is a nice report, but I don't want to kill the database each time the user want to see it. Thanks for your contrib so far, this will help me looking for other ways doing it. I'm always ready to hear more! Regards. -- Alexandre Leclerc