Re: Aggregate from CASE WHEN...

Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com> writes:
> I am trying to aggregate some data but conditionally as follows:

> SELECT
> feed_all_y2012m01.array_accum(message_copies.msg_id) as
> messages_array,
> uniq(feed_all_y2012m01.array_accum(obj_mmsi)) as mmsi_array,
> CASE WHEN msg_type BETWEEN 1 and 3 THEN
> feed_all_y2012m01.array_accum(message_copies.msg_id) END as msgA,
> CASE WHEN msg_type = 18 THEN
> feed_all_y2012m01.array_accum(message_copies.msg_id) END as msgB_std,
> CASE WHEN msg_type = 19 THEN
> feed_all_y2012m01.array_accum(message_copies.msg_id) END as msgB_ext,
> CASE WHEN obj_type = 'SHIP_TYPE_A' THEN
> uniq(feed_all_y2012m01.array_accum(obj_mmsi)) END as mmsi_type_a_array,
> CASE WHEN obj_type = 'SHIP_TYPE_B' THEN
> uniq(feed_all_y2012m01.array_accum(obj_mmsi)) END as mmsi_type_b_array,
> .......

It's not entirely clear what you want to do, but I think it's unlikely
that a query of this form is it. Those array_accum() aggregates will
all compute the same values --- the case expressions only run after
aggregation has finished. So regardless of any grouping issues, this
would not compute what you're wishing for.

I'm thinking maybe you want to do the cases inside the aggregate functions:

feed_all_y2012m01.array_accum(CASE WHEN msg_type BETWEEN 1 and 3 THEN message_copies.msg_id END) as msgA,
feed_all_y2012m01.array_accum(CASE WHEN msg_type = 18 THEN message_copies.msg_id END) as msgB_std,
...

Now, what this is going to feed to array_accum is the msg_id at rows
with the desired msg_type, and NULL at other rows. What you probably
want array_accum to do is ignore the nulls, which it won't do in the
standard incarnation shown in the manual; but you could make a variant
that does ignore nulls by declaring the aggregate transition function
as strict.

regards, tom lane