List supported aggregates

[sumedhpathak]

I think doing sqrt((sum(power(value, 2)) - count(value)*power(avg(value),2)) / (count(value)-1)) instead of stddev(value) should work

Why are we implementing it? (sales eng)

STDDEV doesn't work out of the box. The above example shows a workaround for the feature.

What are the typical use cases?

Communication goals (e.g. detailed howto vs orientation)

Good locations for content in docs structure

SQL Workarounds

How does this work? (devs)

Example sql

Corner cases, gotchas

Are there relevant blog posts or outside documentation about the concept/feature?

Link to relevant commits and regression tests if applicable

[begriffs]

I don't see stddev mentioned in the docs anywhere. Are you suggesting this as another entry in the SQL workarounds section?

[begriffs]

(Talking about this, and yeah it's just a new thing for the workarounds section, although it hasn't come up in any questions yet)

[bharathnfer]

Hi, I do not know how the forum works. I apologize in advance if I did do something wrong here.

I was recently going through docs - I think it would be helpful people knew the list of supported aggregation functions. I had to install and then know it does not work and the possible workaround can be suggested.

@begriffs

[serprex]

@bharathnfer we use a hard coded list based on the string name of the aggregate. I know this list is at least available here: https://github.com/citusdata/citus/blob/ed7c55a7af26d6b06a2528a7df2431795dd07eb9/src/include/distributed/multi_logical_optimizer.h#L118

[jonels-msft]

More info from @serprex to incorporate into docs

Phase 1 of support is in https://github.com/citusdata/citus/pull/2921

It adds zero config support for some aggregates, those aggregates being:

• they have a single argument
• they have a combinefunc
• their transition type is not a pseudotype (internal, anyelement, etc)

For now we only check for this after our existing checks, so we’re still using the specialized code paths for sum, every, etc. This also means users can still name their aggregate ‘sum’ to hit those code paths

This adds support for the following aggregates included with postgres:

• var_pop(float4), var_pop(float8)
• var_samp(float4), var_samp(float8)
• variance(float4), variance(float8)
• stddev_pop(float4), stddev_pop(float8)
• stddev_samp(float4), stddev_samp(float8)
• stddev(float4), stddev(float8)

NB these aggregates have signatures for integer types, but those versions aren’t yet supported