IDS Forum

RE: Challenging : Aggregating several columns ....

Posted By: Andrew Ford
Date: Thursday, 14 March 2013, at 2:50 a.m.

In Response To: Re: Challenging : Aggregating several columns to s (Cesar Inacio Martins)

I think this works.

Join the table to itself 3 times to transpose the id and data fields of each
category into columns of a row and only return rows where id1 < id2 < id3.

Then left outer join this result to itself on the sum of the table n ids
being greater than the sum of the table m ids and the m row with no matching
n row must be the one with the highest sum of consecutive ids and therefore
the 3 largest ids for the category.

create temp table t(

id smallint,

cat smallint,

data char(10)
) with no log;

insert into t values (1, 1, "foo");
insert into t values (2, 1, "bar");
insert into t values (3, 1, "baz");
insert into t values (4, 2, "some");
insert into t values (5, 2, "random");
insert into t values (6, 3, "Data 1");
insert into t values (7, 2, "data");
insert into t values (8, 3, "Data 2");
insert into t values (9, 3, "Data 3");
insert into t values (10, 4, "some");
insert into t values (11, 4, "more");
insert into t values (12, 4, "random");
insert into t values (13, 4, "data");
insert into t values (14, 4, "for");
insert into t values (15, 4, "testing");

select

m.cat,

m.id1,

m.data1,

m.id2,

m.data2,

m.id3,

m.data3
from

(

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) m left outer join (

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) n on

n.cat = m.cat and

n.id1 + n.id2 + n.id3 > m.id1 + m.id2 + m.id3
where

n.cat is null;

Here are the results of a test.

cat id1 data1 id2 data2 id3 data3

1 1 foo 2 bar 3 baz

2 4 some 5 random 7 data

3 6 Data 1 8 Data 2 9 Data 3

4 13 data 14 for 15 testing

Here is the query plan, you'll probably want to create some indexes.

QUERY: (OPTIMIZATION TIMESTAMP: 03-14-2013 01:42:43)
------
select

m.cat,

m.id1,

m.data1,

m.id2,

m.data2,

m.id3,

m.data3
from

(

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) m left outer join (

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) n on

n.cat = m.cat and

n.id1 + n.id2 + n.id3 > m.id1 + m.id2 + m.id3
where

n.cat is null

Estimated Cost: 16
Estimated # of Rows Returned: 1

1) informix.t1: SEQUENTIAL SCAN

2) informix.t2: SEQUENTIAL SCAN

DYNAMIC HASH JOIN

Dynamic Hash Filters: informix.t2.cat = informix.t1.cat

Other Join Filters: informix.t2.id > informix.t1.id

3) informix.t3: SEQUENTIAL SCAN

DYNAMIC HASH JOIN

Dynamic Hash Filters: informix.t1.cat = informix.t3.cat

Other Join Filters: informix.t3.id > informix.t2.id

QUERY: (OPTIMIZATION TIMESTAMP: 03-14-2013 01:42:43)
------
select

m.cat,

m.id1,

m.data1,

m.id2,

m.data2,

m.id3,

m.data3
from

(

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) m left outer join (

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) n on

n.cat = m.cat and

n.id1 + n.id2 + n.id3 > m.id1 + m.id2 + m.id3
where

n.cat is null

Estimated Cost: 16
Estimated # of Rows Returned: 1

1) informix.t1: SEQUENTIAL SCAN

2) informix.t2: SEQUENTIAL SCAN

DYNAMIC HASH JOIN

Dynamic Hash Filters: informix.t2.cat = informix.t1.cat

Other Join Filters: informix.t2.id > informix.t1.id

3) informix.t3: SEQUENTIAL SCAN

DYNAMIC HASH JOIN

Dynamic Hash Filters: informix.t1.cat = informix.t3.cat

Other Join Filters: informix.t3.id > informix.t2.id

QUERY: (OPTIMIZATION TIMESTAMP: 03-14-2013 01:42:43)
------
select

m.cat,

m.id1,

m.data1,

m.id2,

m.data2,

m.id3,

m.data3
from

(

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) m left outer join (

select

t1.cat,

t1.id id1,

t1.data data1,

t2.id id2,

t2.data data2,

t3.id id3,

t3.data data3

from

t t1,

t t2,

t t3

where

t2.cat = t1.cat and

t3.cat = t2.cat and

t3.id > t2.id and

t2.id > t1.id

) n on

n.cat = m.cat and

n.id1 + n.id2 + n.id3 > m.id1 + m.id2 + m.id3
where

n.cat is null

Estimated Cost: 8
Estimated # of Rows Returned: 1

1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN

2) (Temp Table For Collection Subquery): AUTOINDEX PATH

(1) Index Name: (Auto Index)

Index Keys: cat

Lower Index Filter: (Temp Table For Collection Subquery).cat =
(Temp Table For Collection Subquery).cat

ON-Filters:((Temp Table For Collection Subquery).cat = (Temp Table For
Collection Subquery).cat AND (Temp Table For Collection Subquery).id1 +
(Temp Table For Collection Subquery).id2 + (Temp Table For Collection
Subquery).id3 > (Temp Table For Collection Subquery).id1 + (Temp Table For
Collection Subquery).id2 + (Temp Table For Collection Subquery).id3 )

NESTED LOOP JOIN(LEFT OUTER JOIN)

PostJoin-Filters:(Temp Table For Collection Subquery).cat IS NULL

Andrew

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Cesar
Inacio Martins
Sent: Wednesday, March 13, 2013 7:21 AM
To: ids@iiug.org
Subject: Re: Challenging : Aggregating several columns .... [29730]

Hi Fernando,

This is something what I try before , the problem is have all fields of same
row togheter... where difficult use the data without include a treatment to
separate each information.

expression MULTISET{'1,1,foo ','2,1,bar ','3,1,baz '} expression
MULTISET{'4,2,some ','5,2,random ','7,2,data '} expression
MULTISET{'6,3,Data 1 ','8,3,Data 2 ','9,3,Data 3 '}

2013/3/13 Fernando Nunes <domusonline@gmail.com>

> Food for thought:
>
> drop table teste;
> create temp table teste ( id smallint, cat smallint, data char(10));
> insert into teste values ( 1, 1, 'foo ' ); insert into teste values (
> 2, 1, 'bar ' ); insert into teste values ( 3, 1, 'baz ' ); insert into
> teste values ( 4, 2, 'some ' ); insert into teste values ( 5, 2,
> 'random ' ); insert into teste values ( 6, 3, 'Data 1 ' ); insert into
> teste values ( 7, 2, 'data ' ); insert into teste values ( 8, 3, 'Data
> 2 ' ); insert into teste values ( 9, 3, 'Data 3 ' ); insert into teste
> values ( 10, 3, 'Data 4 ' );
>
> select * from teste;
> select ms.*
> from
> (
> SELECT MULTISET( SELECT ITEM t.id || ',' || t.cat || ',' || t.data m1
> FROM teste t WHERE t.cat = tout.cat) FROM (SELECT unique cat from
> teste) tout
> ) ms
>
>
>
>
> On Wed, Mar 13, 2013 at 12:01 AM, Cesar Inacio Martins <
> cesar.inacio.martins@gmail.com> wrote:
>
>>
>> This is something where I always see as huge challenge when need to
>> play only with DML on Informix.
>> I play now a little with this... and get no easy/simple solution, so
>> far...
>>
>> Get this data :
>>
>> drop table teste;
>> create temp table teste ( id smallint, cat smallint, data char(10));
>> insert into teste values ( 1, 1, 'foo ' ); insert into teste values (
>> 2, 1, 'bar ' ); insert into teste values ( 3, 1, 'baz ' ); insert
>> into teste values ( 4, 2, 'some ' ); insert into teste values ( 5, 2,
>> 'random ' ); insert into teste values ( 6, 3, 'Data 1 ' ); insert
>> into teste values ( 7, 2, 'data ' ); insert into teste values ( 8, 3,
>> 'Data 2 ' ); insert into teste values ( 9, 3, 'Data 3 ' );
>>
>> and transform into this :
>>
>> cat id1 data1 id2 data2 id3 data3
>> -----------------------------------------------------
>> 1 1 foo 2 bar 3 baz
>> 2 4 some 5 random 7 data
>> 3 6 Data 1 8 Data 2 9 Data 3
>>
>> Where the logic is : aggregate into single line the 3 lines what have
>> the same "cat" .
>> This is possible on ifx 11.70 ? or 12.1?
>> Only with DML... no SPL. (consider the user/system don't have grant
>> to create procedure)
>>
>> Original question :
>>
http://stackoverflow.com/questions/15368750/aggregating-several-columns-to-s
ingle-colum
>>
>> Regards
>> Cesar
>>
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>>
>>
>
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>

--001a11c240489aadd504d7cd70c2

****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Re: Challenging : Aggregating several columns to s
  Cesar Inacio Martins -- Wednesday, 13 March 2013, at 8:21 a.m.
  • RE: Challenging : Aggregating several columns ....
    Andrew Ford -- Thursday, 14 March 2013, at 2:50 a.m.
  • Re: Challenging : Aggregating several columns to s
    ANDREW FORD -- Thursday, 14 March 2013, at 3:45 a.m.
    • RE: Challenging : Aggregating several columns to s
      Andrew Ford -- Thursday, 14 March 2013, at 1:40 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.