Re: Aggregate from CASE WHEN...

On 08/07/2012 16:24, Tom Lane wrote:
> 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
That was spot on Tom. The problem was that I was adding the CASE before
and not in the array_accum. As a result, apart from the fact that it was
not working correctly as you pointed, the msg_type had to be part of the
'group by' ruining my logic. Of course setting it correctly solved all
problems. Now, for the final step of it. You said that SFUNC =
array_append form the example array_accum must be delclared "strict".
May I ask how as I am lost?

Thank again
Yiannis