Re: Ugly group by problem

From: Niklas Johansson <spot(at)tele2(dot)se>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Ugly group by problem
Date: 2006-03-29 23:49:18
Message-ID: 5047BA59-331B-4F6B-AF2C-9C9210E3CAE0@tele2.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Hrishikesh Deshmukh 2006-03-30 01:08:51 results being col wise rather than row wise
Previous Message Josh Rovero 2006-03-29 20:56:19 Re: [SQL] Flight numbers data