List supported aggregates

citusdata / citus_docs

Documentation for Citus. Distributed PostgreSQL as an extension.

Creative Commons Attribution 4.0 International

58 stars 59 forks source link

List supported aggregates #364

Open sumedhpathak opened 7 years ago

sumedhpathak commented 7 years ago

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 commented 6 years ago

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

begriffs commented 5 years ago

(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 commented 5 years ago

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 commented 4 years ago

@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 commented 4 years ago

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