Search Postgresql Archives

Re: trying to program in PostgreSQL C a statistics function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx





--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux