Обсуждение: 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