Re: Challenging : Aggregating several columns to s
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-singl
e-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
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 S
Made some changes to handle less than 3 ids in a 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");
insert into t values (16, 5, "one");
select
m.cat,
m.id1,
m.data1,
case when m.id2 = 0 then null else m.id2 end id2,
m.data2,
case when m.id3 = 0 then null else m.id3 end id3,
m.data3
from
(
select
t1.cat,
t1.id id1,
t1.data data1,
nvl(t2.id, 0) id2,
t2.data data2,
nvl(t3.id, 0) id3,
t3.data data3
from
t t1 left outer join t t2 on
t2.cat = t1.cat and
t2.id != t1.id
left outer join t t3 on
t3.cat = t2.cat and
t3.id != t2.id and
t3.id != t1.id
where
(t3.id >= t2.id or t3.id is null) and
(t2.id >= t1.id or t2.id is null)
) m left outer join (
select
t1.cat,
t1.id id1,
t1.data data1,
nvl(t2.id, 0) id2,
t2.data data2,
nvl(t3.id, 0) id3,
t3.data data3
from
t t1 left outer join t t2 on
t2.cat = t1.cat and
t2.id != t1.id
left outer join t t3 on
t3.cat = t2.cat and
t3.id != t2.id and
t3.id != t1.id
where
(t3.id >= t2.id or t3.id is null) and
(t2.id >= t1.id or t2.id is null)
) n on
n.cat = m.cat and
n.id1 + n.id2 + n.id3 > m.id1 + m.id2 + m.id3
where
n.cat is null;
Coworker came up with a better way to do it.
select
cat,
max(case when cnt = 3 then id end) as id1,
max(case when cnt = 3 then data end) as data1,
max(case when cnt = 2 then id end) as id2,
max(case when cnt = 2 then data end) as data2,
max(case when cnt = 1 then id end) as id3,
max(case when cnt = 1 then data end) as data3
from
(
select
a.cat,
a.id,
a.data,
count(*) as cnt
from
t a,
t b
where
a.cat = b.cat and
a.id <= b.id
group by
a.id,
a.cat,
a.data
having
count(*) <= 3
)
group by
1
order by
1;
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ANDREW
FORD
Sent: Thursday, March 14, 2013 2:46 AM
To: ids@iiug.org
Subject: Re: Challenging : Aggregating several columns to s [29734]
Made some changes to handle less than 3 ids in a 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"); insert into t values (16, 5, "one");
select
m.cat,
m.id1,
m.data1,
case when m.id2 = 0 then null else m.id2 end id2,
m.data2,
case when m.id3 = 0 then null else m.id3 end id3,
m.data3
from
(
select
t1.cat,
t1.id id1,
t1.data data1,
nvl(t2.id, 0) id2,
t2.data data2,
nvl(t3.id, 0) id3,
t3.data data3
from
t t1 left outer join t t2 on
t2.cat = t1.cat and
t2.id != t1.id
left outer join t t3 on
t3.cat = t2.cat and
t3.id != t2.id and
t3.id != t1.id
where
(t3.id >= t2.id or t3.id is null) and
(t2.id >= t1.id or t2.id is null)
) m left outer join (
select
t1.cat,
t1.id id1,
t1.data data1,
nvl(t2.id, 0) id2,
t2.data data2,
nvl(t3.id, 0) id3,
t3.data data3
from
t t1 left outer join t t2 on
t2.cat = t1.cat and
t2.id != t1.id
left outer join t t3 on
t3.cat = t2.cat and
t3.id != t2.id and
t3.id != t1.id
where
(t3.id >= t2.id or t3.id is null) and
(t2.id >= t1.id or t2.id is null)
) n on
n.cat = m.cat and
n.id1 + n.id2 + n.id3 > m.id1 + m.id2 + m.id3 where
n.cat is null;
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.