On Thu, Jun 23, 2011 at 12:48:51 -0400, Igor Tandetnik wrote:
> On 6/23/2011 11:37 AM, Jan Hudec wrote:
> > Is there a way to do group by query with well defined order of calls to the
> > aggregate function?
>
> Not reliably.
>
> > So I want to denormalize the database by doing:
> >
> > create temporary table packed_array (
> > obj_id integer primary key,
> > values text);
> > insert into packed_array (obj_id, values)
> > select
> > obj_id,
> > group_concat(value)
> > from array
> > group by obj_id;
>
> Can't you materialize the "array" table shown earlier:
>
> create table array (
> obj_id integer,
> seqnr integer,
> value integer,
> primary key (obj_id, seqnr))
It is materialized. The problem is that there is not one, but several of them
and the last step needs to do
select *
from packed_array1
natural join packed_array2
natural join packed_array3
where the arrays don't correspond to each other, so the group bys can't be
rearranged to a single common group-by. So I need to do each of the group bys
separately into either temporary tables or views (it seems temporary table
performance is better in this case, but generally it depends on what the
access pattern ends up to be, which depends on exact query) which is what
this is trying to do.
> > Unfortunately it does not work, because I need the array ordered by seqnr.
> > I tried:
> >
> > insert into packed_array (obj_id, values)
> > select obj_id, group_concat(value)
> > from (
> > select obj_id, value
> > order by obj_id, seqnr
> > )
> > group by obj_id;
>
> There should be a "from array" in there somewhere.
Yes, just missed it when writing it (this is obivously not the real query,
but a simplification of it -- the real query is a 3 table join.
> > But it did not seem to work right and I am not sure whether it should or
> > not.
>
> It is not guaranteed, but it'll probably work.
Unfortunately it does not. I am currently doing a simple group by (the one at
the begining), but it relies on the particular query plan and the fact, that
the table is being written in order. I accidentally dropped an index it was
using and the order changed and rewriting to this form did *not* help. Which
is why I came to ask in the first place.
--
Jan 'Bulb' Hudec <b...@ucw.cz>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
