Date: Wed, 29 Mar 2006 18:42:03 +0300 (EEST)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Markus Schaber <schabi@logix-tt.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Ugly group by problem
In-Reply-To: <442A8E4B.7050106@logix-tt.com>
Message-ID: <Pine.LNX.4.44.0603291838520.5673-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: 8BIT

O Markus Schaber έγραψε στις Mar 29, 2006 :

> Hello,
> 
> I have a table filled from third party that basically has the following
> structure:
> 
> link_id | feat_id | other | columns...
> --------+---------+-------+-----------
> 1       | 2       | ...
> 2       | 5       | ...
> 2       | 23      | ...
> 3       | 5       | ...
> 3       | 23      | some  | data
> 3       | 23      | other | data
> 5       | 23      | ...
> 9       | 23      | ...
> 
> This structure is fixed, and we can't change it, but we can create
> 
> We have about 37 million different link_ids, and 35 million feat_ids.
> There are feat_ids that appear at several thousand link_ids, but a
> link_id does not have more than a douzen feat_ids.
> 
> Now I need to group together all link_ids that have the same set of
> feat_ids. In the example above, the sets would be (1), (2,3) and (5,9),
> and the other columns would be run through some aggregate functions.
> 
> Currently, this is done via an external JAVA application, but I'm
> looking for a way to express this via sql / plpgsql to ease deployment.
> 
> I could imagine some ugly code using ARRAY (not tried yet), but how
> would you pack this problem? It seems that I'm just stuck in my thoughts
> and miss the beauty way to solve it.

Well i did it with arrays, i dont know the performance 
implications tho:

foodb=# SELECT * from markustest ;
 link_id | feat_id | other
---------+---------+-------
       1 |       2 | 1
       2 |       5 | 2
       2 |      23 | 2
       3 |       5 | 3
       3 |      23 | 3
       3 |      23 | 3
       5 |      23 | 5
       9 |      23 | 9
(8 rows)

foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as 
foo,_int_union(array(select mt2.feat_id from markustest mt2 where 
mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as 
foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
  foo2  | sum
--------+-----
 {2}    |   1
 {5,23} |  13
 {23}   |  14
(3 rows)

foodb=#           

The _int_union trick is to force the arrays to have unique values.
The order by has the meaning that '{5,23}' and '{23,5}' should be treated
the same way.

> 
> Thanks,
> Markus
> 

-- 
-Achilleus