Re: trying to program in PostgreSQL C a statistics function

Hi Andre,

I've written some C statistics functions for Postgres before, here:

https://github.com/pjungwir/aggs_for_arrays
https://github.com/pjungwir/aggs_for_vecs

They are all really simple, although they operate on arrays, so yours
should be even simpler. The second link has aggregate functions,
including one for sample variance, so that would probably be the easiest
to adapt to your own needs.

In general Postgres C functions use macros to access their arguments
(and to return something). You can read more about it here:

https://www.postgresql.org/docs/current/static/xfunc-c.html

I'm happy to help more if you need it. I've found extending Postgres in
this way to be a lot of fun and very effective at gaining performance.

Good luck!

Paul

On 06/11/2017 09:57 AM, Andre Mikulec wrote:
> SUMMARY
> ------
>
> I am trying to program in PostgreSQL a statistics function.
>
> My old method in SQL is here.
>
> select ( avg(measure) - 0.0 ) / nullif(stddev_pop(case when measure >
> 0.00 then 0.00 else measure end ),0) sortino_true from TABLE/VIEW;
>
> The logic is based on
> SORTINO RATIO: ARE YOU CALCULATING IT WRONG?
> SEPTEMBER 11, 2013
> https://www.rcmalternatives.com/2013/09/sortino-ratio-are-you-calculating-it-wrong/
>
> In PostgreSQL C, how do I get access to the 'X' (data) variable?
>
> DETAILS
> -------
>
> I see a similar function with other variables in here.
> But I am not trying to re-program 'stddev_samp'.
>
> float8_stddev_samp ( lines 2741 through 2767 )
> https://github.com/postgres/postgres/blob/9a34123bc315e55b33038464422ef1cd2b67dab2/src/backend/utils/adt/float.c
>
> N = transvalues[0];
> sumX = transvalues[1];
> sumX2 = transvalues[2];
>
> The following article explains it and this makes sense.
>
> Sum of Squares Formula Shortcut
> https://www.thoughtco.com/sum-of-squares-formula-shortcut-3126266
>
> I can match article symbols to PostgreSQL C varaibles.
>
> numerator = N * sumX2 - sumX * sumX;
> N * SIGMA(X**2) - (SIGMA(X))**2
>
> BUT I NEED ...
> --------------
>
> However in my case,
> I have this adjustement
>
> "case when measure > 0.00 then 0.00 else measure end"
>
> So the case seems that I need access to 'X'
> but access to sumX and sumX2 are only shown.
>
> How would I get accress to X?
>
> May anyone point me to a simple good working example?
>
> Thanks,
> Andre Mikulec
> Andre_Mikulec(at)Hotmail(dot)com
>
>
>