Re: Column as arrays.. more efficient than columns?

On Thu, 2007-09-06 at 21:53 -0400, Merlin Moncure wrote:
> On 9/6/07, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
> > Table is like
> >
> > create table foo (
> > number int,
> > subset int,
> > value  int
> > )
> >
> > select * from foo;
> > number | subset | value
> > 1        1        1
> > 1        2        2
> > 1        3        10
> > 1        4        3
> >
> > current query is like
> >
> > select number,
> > avg(case when subset = 1 then value else null end) as v1,
> > avg(case when subset = 2 then value else null end) as v2,
> > avg(case when subset = 3 then value else null end) as v3,
> > avg(case when subset = 4 then value else null end) as v4
> > from foo
> > group by number
>
> arrays are interesting and have some useful problems.  however, we
> must first discuss the problems...first and foremost if you need to
> read any particular item off the array you must read the entire array
> from disk and you must right all items back to disk for writes.  also,
> they cause some problems with constraints and other issues that come
> up with de-normalization tactics.

I see. Didn't know that.. Good to know.

> select number, subset, avg(value) from foo group by subset;
>
> does this give you the answer that you need?

No it doesn't

select * from foo order by subset;
 code | subset | value
------+--------+-------
 A    | 0      |    98
 A    | 1      |    20
 A    | 2      |    98
 A    | 3      |    98
 A    | 4      |    98


=> select code, subset, avg(value)  from foo  group by subset;
ERROR:  column "foo.code" must appear in the GROUP BY clause or be used
in an aggregate function

=> select code, subset, avg(value)  from foo  group by subset, code;
 code | subset |         avg
------+--------+---------------------
 A    | 3      | 98.0000000000000000
 A    | 1      | 20.0000000000000000
 A    | 4      | 98.0000000000000000
 A    | 0      | 98.0000000000000000
 A    | 2      | 98.0000000000000000


=> select code, round(avg(case when subset = '0' then value else null
end),0) as v0,
round(avg(case when subset = '1' then value else null end),0) as v1,
round(avg(case when subset = '2' then value else null end),0) as v2,
round(avg(case when subset = '3' then value else null end),0) as v3,
round(avg(case when subset = '4' then value else null end),0) as v4
from foo
group by code;
 code | v0 | v1 | v2 | v3 | v4
------+----+----+----+----+----
 A    | 98 | 20 | 98 | 98 | 98