pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUgly group by problem
5+ messages / 3 participants
[nested] [flat]
* Ugly group by problem
@ 2006-03-29 13:40 Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 5+ messages in thread
From: Markus Schaber @ 2006-03-29 13:40 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ugly group by problem
@ 2006-03-29 14:44 Markus Schaber <schabi@logix-tt.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Markus Schaber @ 2006-03-29 14:44 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Hi, Achilleus,
Achilleus Mantzios wrote:
> 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)
This is much like I intended to do it, but using "select distinct" in
the inner select as I don't have _int_union here, and using a temporary
table to collect the sets of link ids.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ugly group by problem
@ 2006-03-29 15:42 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2006-03-29 15:42 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ugly group by problem
@ 2006-03-29 23:49 Niklas Johansson <spot@tele2.se>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Niklas Johansson @ 2006-03-29 23:49 UTC (permalink / raw)
To: pgsql-sql
On 29 mar 2006, at 17.42, Achilleus Mantzios wrote:
> 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.
I didn't have the _int_union function. Is it internal? What PG-
version did you use? I solved it using DISTINCT instead (perhaps
making it a little simpler as well):
SELECT
ARRAY(SELECT DISTINCT feat_id FROM linktest WHERE
link_id=t1.link_id ORDER BY feat_id) AS feat_group,
SUM(other)
FROM linktext t1
GROUP BY 1;
feat_group | sum
-----------+-----
{2} | 1
{5,23} | 13
{23} | 14
(3 rows)
Of course I haven't tested the performance on a table containing a
lot of data... I'm not sure how the planner will treat the ARRAY-
construct. You might be able to speed it up by turning it into a
function marked STABLE, something like this:
CREATE OR REPLACE FUNCTION feat_group(INTEGER) RETURNS INTEGER[] AS $$
SELECT ARRAY(SELECT DISTINCT feat_id FROM linktest WHERE link_id=$1
ORDER BY feat_id);
$$ LANGUAGE sql STABLE;
SELECT
feat_group(link_id),
SUM(other)
FROM linktext t1
GROUP BY 1;
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ugly group by problem
@ 2006-03-30 10:20 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 0 replies; 5+ messages in thread
From: Achilleus Mantzios @ 2006-03-30 10:20 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
O Markus Schaber Ýãñáøå óôéò Mar 29, 2006 :
> Hi, Achilleus,
>
> Achilleus Mantzios wrote:
>
> > 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)
>
> This is much like I intended to do it, but using "select distinct" in
> the inner select as I don't have _int_union here, and using a temporary
> table to collect the sets of link ids.
Just some facts:
sort(uniq(int[])) has the same effect as select DISTINCT... ORDER BY
DISTINCT. ORDER BY is ofcourse more intuitive and does not need
contrib/intarray.
In fact , i dont even remember why i didnt write the query with
DISTINCT... in the first place :)
>
> Markus
>
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-30 10:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-29 13:40 Ugly group by problem Markus Schaber <schabi@logix-tt.com>
2006-03-29 15:42 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-03-29 14:44 ` Markus Schaber <schabi@logix-tt.com>
2006-03-30 10:20 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-03-29 23:49 ` Niklas Johansson <spot@tele2.se>
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