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(dot)Mun(dot)Heng(at)wdc(dot)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