Thread: Aggregate from CASE WHEN...
Hello, 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, ....... The logic breaks where PGSQL forces me(?) to group by msg_type and obj_type while I was hoping that msg_type and obj_type could just be considered as the variables that the conditions would be checked against. Neither I want to use some form of aggregation on these two fields. Is there any way that I can achieve something like this? Thank you Yiannis
Ioannis Anagnostopoulos <ioannis@anatec.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
On 08/07/2012 16:24, Tom Lane wrote: > Ioannis Anagnostopoulos <ioannis@anatec.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
Ioannis Anagnostopoulos <ioannis@anatec.com> writes: > 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? You would need to create a new SQL function declaration, referencing the same underlying C code but marked strict. Then make a new aggregate referencing that as the sfunc instead of the original. regards, tom lane
On 08/07/2012 23:54, Tom Lane wrote: > Ioannis Anagnostopoulos <ioannis@anatec.com> writes: >> 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? > You would need to create a new SQL function declaration, referencing the > same underlying C code but marked strict. Then make a new aggregate > referencing that as the sfunc instead of the original. > > regards, tom lane Thank you, I was actually on my way to do exactly this... Regards Yiannis