Thread: enumerate groups given a certain value

enumerate groups given a certain value

From
"Picavet Vincent"
Date:
Hello,

Here is a sql problem, which I thought simple at first, but for which I
ended up with a solution I find surprisingly complicated.
I really think I could have achieved a much easier way of handling this,
but I do not manage to find the trick allowing a very simple and
efficient query to solve the problem.

Let's present it with a sample case. The initial table is the following
one :

--------------------
drop table if exists test_gen ;

create table test_gen as
select *
from (select     chr((round(random()* 25) +65)::int)  as id    , random()* 100  as valfrom    generate_series(1,200) as
gorder    by id 
) as foo

select * from test_gen;
-------------------

What I want to do is to enumerate lines for each group of id, following
the order of val.

For example :
id    val            gen
A    2.65105138532817    1
A    38.9289360493422    2
A    74.6089164167643    3
B    2.01512188650668    1
B    11.4642047323287    2
B    31.2643219716847    3
B    65.8427979797125    4
C    0.759994331747293    1
C    11.8905796203762    2
C    13.7388648930937    3
C    49.1934351157397    4
C    83.1861903425306    5
D    45.8268967922777    1
D    57.1161589119583    2
E    9.72125697880983    1
E    61.324825277552    2
E    70.3348958399147    3
F    0.49891234234237    1


Here is the solution I ended up with :

---------------------------

-- first count number of ids per group
drop table test_gen2 ;

create table test_gen2 as
select t1.*, t2.nb
from test_gen as t1,(SELECT     id, count(*) as nb FROM     test_gen GROUP BY id) as t2
WHERE t1.id =t2.id
ORDER BY t1.id;

create sequence seq_test_gen start with 1;
create sequence seq_test_gen2 start with 1;

-- get the table with the order set (gen is our order)
select*
from(select    foo1.*,    nextval('seq_test_gen') as serialfrom (    select        *    from        test_gen2    order
by       id, val    ) as foo1) as t1,(select    foo.*,    nextval('seq_test_gen2') as serialfrom (    select
gb1.*,        generate_series(1, gb1.nb) as gen    from (        select            id, nb        from
test_gen2       group by            id, nb        ) as gb1     order by         gb1.id, gen    ) as foo) as t2 
wheret1.serial = t2.serial;
-----------------------------------

The problem seems to be as easy as : <sort my two sets and put them side
to side>. But I could not find a better way to do that than putting a
serial on left and right side and do a join on this serial.
I also tried to find a solution using a modulo but could not manage to
get it work.

Anybody for a ray of light on a different approach ? This look like a
recurrent problem, isn't there an experienced sql programmer here who
tackled this issued a couple of time ?

Thanks for any help,
Vincent



Re: enumerate groups given a certain value

From
"Yura Gal"
Date:
Hello, Picavet.

> Anybody for a ray of light on a different approach ? This look like a
> recurrent problem, isn't there an experienced sql programmer here who
> tackled this issued a couple of time ?

Actually, I'm not very experienced in SQL. But from my point of view
this problem could be solved much more easily using plpgsql rather
than plain SQL.

Your initial query have been a little bit modified for convenience:
CREATE TABLE test_gen AS
SELECT *
FROM(SELECT  chr((round(random()* 25) +65)::int) AS id,  random()* 100 AS val,  0::INTEGER AS genFROM
generate_series(1,200)as gORDER BY id
 
) foo;

CREATE OR REPLACE FUNCTION enum_groups (varchar) RETURNS SETOF test_gen AS
$body$
DECLAREr chip.test_gen%ROWTYPE;_id VARCHAR;i INTEGER := 0;q TEXT;
BEGINq := 'SELECT * FROM ' || $1 || ' ORDER BY id ASC, val ASC;';FOR r IN EXECUTE q LOOP  IF ((_id IS NULL) OR (_id =
r.id))THEN    i := i + 1;  ELSE    i := 1;  END IF;  _id := r.id;  r.gen := i;  RETURN NEXT r;END LOOP;RETURN;
 
END;
$body$
LANGUAGE 'plpgsql';

Now you can SELECT * FROM enum_groups('test_gen') and you'll get following:

id      val     gen
B    2,35326588153839    1
B    11,4269167650491    2
B    11,9314394891262    3
B    27,9016905929893    4
B    28,548994101584    5
B    48,8151242025197    6
B    50,215089507401    7
B    59,613792411983    8
B    61,2281930632889    9
B    80,49540463835    10
C    5,86635880172253    1
C    11,5974457468838    2
C    15,8136531710625    3
C    29,8465201631188    4
C    52,9871591832489    5
C    57,3461000341922    6
C    63,3344274014235    7
...

HTH

PS. Sorry, I forget to reply all first time.
-- 
Best regards. Yuri.