Re: Ugly group by problem

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