Re: [HACKERS] Weighted Stats

[David Fetter]

On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote:
> On Tue, Mar 15, 2016 at 8:36 AM, David Fetter  wrote:
> >
> > Please find attached a patch that uses the float8 version to cover the
> > numeric types.
> 
> Is there a well-defined meaning for having a negative weight?  If no,
> should it be disallowed?

Done.

> Shouldn't these then give the same result:
> 
> select stddev_samp(val) from foo;
> stddev_samp
> ---
>  2887.054977297105
> 
> select weighted_stddev_samp(val,count) from foo2;
>  weighted_stddev_samp
> --
>  2887.19919651336
> 
> The 5th digit seems too early to be seeing round-off error.

Fixed down-thread.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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

Re: [HACKERS] Weighted Stats

[David Fetter]

On Fri, Apr 08, 2016 at 01:47:56PM -0700, David Fetter wrote:
> Sorry about the delay.  This patch disallows negative weights,
> although it still has that odd difference Jeff found.

Difference corrected.  It turned out that my reference was mistaken on
the calculation.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cb7fe38..ba31d02 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12750,6 +12750,29 @@ NULL baz(3 rows)
  
   

+weighted_average
+   
+   
+weighted_avg
+   
+   weighted_avg(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, numeric, or interval
+  
+  
+   numeric for any integer-type argument,
+   double precision for a floating-point argument,
+   otherwise the same as the argument data type
+  
+  the average (arithmetic mean) of all input values, weighted by 
the input weights
+ 
+
+ 
+  
+   
 bit_and

bit_and(expression)
@@ -13430,6 +13453,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y 
DESC) AS tab;
  
   

+weighted standard deviation
+population
+   
+   
+weighted_stddev_pop
+   
+   weighted_stddev_pop(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, or numeric
+  
+  
+   double precision for floating-point arguments,
+   otherwise numeric
+  
+  weighted population standard deviation of the input values
+ 
+
+ 
+  
+   
 standard deviation
 sample

@@ -13454,6 +13500,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y 
DESC) AS tab;
  
   

+weighted standard deviation
+sample
+   
+   
+weighted_stddev_samp
+   
+   weighted_stddev_samp(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, or numeric
+  
+  
+   double precision for floating-point arguments,
+   otherwise numeric
+  
+  weighted sample standard deviation of the input values
+ 
+
+ 
+  
+   
 variance

variance(expression)
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index c7c0b58..fe4a5e4 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -2405,6 +2405,7 @@ setseed(PG_FUNCTION_ARGS)
  * float8_accum- accumulate for AVG(), variance 
aggregates, etc.
  * float4_accum- same, but input data is float4
  * float8_avg  - produce final result for 
float AVG()
+ * float8_weighted_avg - produce final result for float 
WEIGHTED_AVG()
  * float8_var_samp - produce final result for float 
VAR_SAMP()
  * float8_var_pop  - produce final result for float 
VAR_POP()
  * float8_stddev_samp  - produce final result for float 
STDDEV_SAMP()
@@ -3205,6 +3206,163 @@ float8_regr_intercept(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(numeratorXXY / numeratorX);
 }
 
+/*
+ * ===
+ * WEIGHTED AGGREGATES
+ * ===
+ *
+ * The transition datatype for these aggregates is a 5-element array
+ * of float8, holding the values N, sum(W), sum(W*X), and sum(W*X*X)
+ * in that order.
+ *
+ * First, an accumulator function for those we can't pirate from the
+ * other accumulators.  This accumulator function takes out some of
+ * the rounding error inherent in the general one.
+ * https://en.wikipedia.org/wiki/Standard_deviation#Rapid_calculation_methods
+ *
+ * It consists of a five-element array which includes:
+ *
+ * N, the number of non-zero-weighted values seen thus far,
+ * W, the running sum of weights,
+ * WX, the running dot product of weights and values,
+ * A, an intermediate value used in the calculation, and
+ * Q, another intermediate value.
+ *
+ */
+
+Datum
+float8_weighted_accum(PG_FUNCTION_ARGS)
+{
+   ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
+   float8  newvalX = PG_GETARG_FLOAT8(1);
+   float8  newvalW = PG_GETARG_FLOAT8(2);
+   float8 *transvalues;
+   float8  N,  /* common */
+   W,  /* common */
+   WX, /* Used in avg */
+   A,  /* Used in stddev_* */
+   Q;  /* Used in 

Re: [HACKERS] Weighted Stats

[David Fetter]

On Sun, Mar 20, 2016 at 03:38:40PM -0700, David Fetter wrote:
> On Sat, Mar 19, 2016 at 05:04:08PM +0100, Tomas Vondra wrote:
> > Hi,
> > 
> > On 03/19/2016 07:34 AM, David Fetter wrote:
> > >On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote:
> > >>On Tue, Mar 15, 2016 at 8:36 AM, David Fetter  wrote:
> > >>>
> > >>>Please find attached a patch that uses the float8 version to cover the
> > >>>numeric types.
> > >>
> > >>Is there a well-defined meaning for having a negative weight?  If no,
> > >>should it be disallowed?
> > >
> > >Opinions on this appear to vary.  A Wikipedia article defines weights
> > >as non-negative, while a manual to which it refers only uses non-zero.
> > >
> > >https://en.wikipedia.org/wiki/Weighted_arithmetic_mean#Mathematical_definition
> > >https://www.gnu.org/software/gsl/manual/html_node/Weighted-Samples.html
> > 
> > I don't think that actually allows negative weights. It says that
> > 
> >w_i = 1/\sigma_i^2
> > 
> > and variance is always > 0, thus w_i > 0. The zero is used as a special flag
> > to remove the sample from the data set in a simple way.
> > 
> > >I'm not sure which if either would be authoritative, but I could
> > >certainly make up variants for each assumption.
> > >
> > >The assumption they have in common about weights is that a zero
> > >weight is not part of the calculation, which assumption is
> > >implemented in the previously submitted code.
> > 
> > I think that if we're not sure what should happen with negative weights,
> > then we should disallow them. It's easy to allow them later once we have a
> > reasonable definition, but if we allow them now and later realize it should
> > behave differently, we'll be in trouble because of breaking existing uses.
> 
> OK
> 
> > I can't really come up with a reasonable example that would actually use
> > negative weights. Can you? That would probably help with defining the
> > behavior correctly.
> 
> No, but I'm not a statistician.  I've seen them mentioned in contexts
> that appear to be discussions among same, and again opinions vary.
> 
> > Allowing negative weights has other consequences. For example, what if
> > sum(W) ends up being 0? For example
> > 
> > CREATE TABLE t (a float, b float);
> > INSERT INTO  t SELECT i,  1 FROM generate_series(1,1000) s(i);
> > INSERT INTO  t SELECT i, -1 FROM generate_series(1,1000) s(i);
> > 
> > SELECT weighted_avg(a,b) FROM t;
> >  weighted_avg
> > --
> >   NaN
> > (1 row)
> > 
> > Is that the correct behavior? Why?
> 
> It's not, and you're right.
> 
> I will send a patch that disallows negative weights this evening or
> tomorrow.  It will be slightly more complicated as I believe I will
> need to create a new accumulator function for the weighted_avg() case
> where I had been using an extant one before.
> 
> Cheers,
> David.

Sorry about the delay.  This patch disallows negative weights,
although it still has that odd difference Jeff found.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 88145c5..b939340 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12750,6 +12750,29 @@ NULL baz(3 rows)
  
   

+weighted_average
+   
+   
+weighted_avg
+   
+   weighted_avg(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, numeric, or interval
+  
+  
+   numeric for any integer-type argument,
+   double precision for a floating-point argument,
+   otherwise the same as the argument data type
+  
+  the average (arithmetic mean) of all input values, weighted by 
the input weights
+ 
+
+ 
+  
+   
 bit_and

bit_and(expression)
@@ -13430,6 +13453,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y 
DESC) AS tab;
  
   

+weighted standard deviation
+population
+   
+   
+weighted_stddev_pop
+   
+   weighted_stddev_pop(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, or numeric
+  
+  
+   double precision for floating-point arguments,
+   otherwise numeric
+  
+  weighted population standard deviation of the input values
+ 
+
+ 
+  
+   
 standard deviation
 sample

@@ -13454,6 +13500,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y 
DESC) AS tab;
  
   

+weighted standard deviation
+sample
+   
+   
+weighted_stddev_samp
+   
+   weighted_stddev_samp(value 
expression, weight 

Re: [HACKERS] Weighted Stats

[David Fetter]

On Sat, Mar 19, 2016 at 05:04:08PM +0100, Tomas Vondra wrote:
> Hi,
> 
> On 03/19/2016 07:34 AM, David Fetter wrote:
> >On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote:
> >>On Tue, Mar 15, 2016 at 8:36 AM, David Fetter  wrote:
> >>>
> >>>Please find attached a patch that uses the float8 version to cover the
> >>>numeric types.
> >>
> >>Is there a well-defined meaning for having a negative weight?  If no,
> >>should it be disallowed?
> >
> >Opinions on this appear to vary.  A Wikipedia article defines weights
> >as non-negative, while a manual to which it refers only uses non-zero.
> >
> >https://en.wikipedia.org/wiki/Weighted_arithmetic_mean#Mathematical_definition
> >https://www.gnu.org/software/gsl/manual/html_node/Weighted-Samples.html
> 
> I don't think that actually allows negative weights. It says that
> 
>w_i = 1/\sigma_i^2
> 
> and variance is always > 0, thus w_i > 0. The zero is used as a special flag
> to remove the sample from the data set in a simple way.
> 
> >I'm not sure which if either would be authoritative, but I could
> >certainly make up variants for each assumption.
> >
> >The assumption they have in common about weights is that a zero
> >weight is not part of the calculation, which assumption is
> >implemented in the previously submitted code.
> 
> I think that if we're not sure what should happen with negative weights,
> then we should disallow them. It's easy to allow them later once we have a
> reasonable definition, but if we allow them now and later realize it should
> behave differently, we'll be in trouble because of breaking existing uses.

OK

> I can't really come up with a reasonable example that would actually use
> negative weights. Can you? That would probably help with defining the
> behavior correctly.

No, but I'm not a statistician.  I've seen them mentioned in contexts
that appear to be discussions among same, and again opinions vary.

> Allowing negative weights has other consequences. For example, what if
> sum(W) ends up being 0? For example
> 
> CREATE TABLE t (a float, b float);
> INSERT INTO  t SELECT i,  1 FROM generate_series(1,1000) s(i);
> INSERT INTO  t SELECT i, -1 FROM generate_series(1,1000) s(i);
> 
> SELECT weighted_avg(a,b) FROM t;
>  weighted_avg
> --
>   NaN
> (1 row)
> 
> Is that the correct behavior? Why?

It's not, and you're right.

I will send a patch that disallows negative weights this evening or
tomorrow.  It will be slightly more complicated as I believe I will
need to create a new accumulator function for the weighted_avg() case
where I had been using an extant one before.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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

Re: [HACKERS] Weighted Stats

[Tomas Vondra]

Hi,

On 03/19/2016 07:34 AM, David Fetter wrote:

On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote:

On Tue, Mar 15, 2016 at 8:36 AM, David Fetter  wrote:


Please find attached a patch that uses the float8 version to cover the
numeric types.


Is there a well-defined meaning for having a negative weight?  If no,
should it be disallowed?


Opinions on this appear to vary.  A Wikipedia article defines weights
as non-negative, while a manual to which it refers only uses non-zero.

https://en.wikipedia.org/wiki/Weighted_arithmetic_mean#Mathematical_definition
https://www.gnu.org/software/gsl/manual/html_node/Weighted-Samples.html


I don't think that actually allows negative weights. It says that

   w_i = 1/\sigma_i^2

and variance is always > 0, thus w_i > 0. The zero is used as a special 
flag to remove the sample from the data set in a simple way.



I'm not sure which if either would be authoritative, but I could
certainly make up variants for each assumption.

The assumption they have in common about weights is that a zero
weight is not part of the calculation, which assumption is
implemented in the previously submitted code.


I think that if we're not sure what should happen with negative weights, 
then we should disallow them. It's easy to allow them later once we have 
a reasonable definition, but if we allow them now and later realize it 
should behave differently, we'll be in trouble because of breaking 
existing uses.


I can't really come up with a reasonable example that would actually use 
negative weights. Can you? That would probably help with defining the 
behavior correctly.


Allowing negative weights has other consequences. For example, what if 
sum(W) ends up being 0? For example


CREATE TABLE t (a float, b float);
INSERT INTO  t SELECT i,  1 FROM generate_series(1,1000) s(i);
INSERT INTO  t SELECT i, -1 FROM generate_series(1,1000) s(i);

SELECT weighted_avg(a,b) FROM t;
 weighted_avg
--
  NaN
(1 row)

Is that the correct behavior? Why?

So -1 to allowing negative weights, unless we can come up with proper 
definition or at least good examples demonstrating the usefulness.


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Weighted Stats

[Jeff Janes]

On Fri, Mar 18, 2016 at 11:34 PM, David Fetter  wrote:
> On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote:

>> Also, I think it might not give the correct answer even without
>> negative weights:
>>
>> create table foo as select floor(random()*1)::int val from
>> generate_series(1,1000);
>>
>> create table foo2 as select val, count(*) from foo group by val;
>>
>> Shouldn't these then give the same result:
>>
>> select stddev_samp(val) from foo;
>> stddev_samp
>> ---
>>  2887.054977297105
>>
>> select weighted_stddev_samp(val,count) from foo2;
>>  weighted_stddev_samp
>> --
>>  2887.19919651336
>>
>> The 5th digit seems too early to be seeing round-off error.
>
> Please pardon me if I've misunderstood, but you appear to be assuming
> that
>
> SELECT val, count(*) FROM foo GROUP BY val
>
> will produce precisely identical count(*)s at each row, which it
> overwhelmingly likely won't, producing the difference you see above.

I think the count for each val that gets put in foo2.count should be
the same as the weight of that val as it occurs in foo.  Surely they
shouldn't all have the same weight in foo2, unless they all have the
same number of appearances in foo.  Which, as you say, they are not
likely to.  But still, the foo2.count that they do individually get
should be equal to their weight, shouldn't it?

The other two methods (*avg and *stddev_pop) do give the same answers
using the two different methods (unweighted against foo, weighted
against foo2)

Cheers,

Jeff


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

Re: [HACKERS] Weighted Stats

[David Fetter]

On Fri, Mar 18, 2016 at 06:12:12PM -0700, Jeff Janes wrote:
> On Tue, Mar 15, 2016 at 8:36 AM, David Fetter  wrote:
> >
> > Please find attached a patch that uses the float8 version to cover the
> > numeric types.
> 
> Is there a well-defined meaning for having a negative weight?  If no,
> should it be disallowed?

Opinions on this appear to vary.  A Wikipedia article defines weights
as non-negative, while a manual to which it refers only uses non-zero.

https://en.wikipedia.org/wiki/Weighted_arithmetic_mean#Mathematical_definition
https://www.gnu.org/software/gsl/manual/html_node/Weighted-Samples.html

I'm not sure which if either would be authoritative, but I could
certainly make up variants for each assumption.

The assumption they have in common about weights is that a zero weight
is not part of the calculation, which assumption is implemented in the
previously submitted code.

> I don't know what I was expecting,  but not this:
> 
> select weighted_avg(x,1000-2*x) from generate_series(1,1000) f(x);
>weighted_avg
> --
>  1671666717.1

I'm guessing that negative weights can cause bizarre outcomes,
assuming it turns out we should allow them.

> Also, I think it might not give the correct answer even without
> negative weights:
> 
> create table foo as select floor(random()*1)::int val from
> generate_series(1,1000);
> 
> create table foo2 as select val, count(*) from foo group by val;
> 
> Shouldn't these then give the same result:
> 
> select stddev_samp(val) from foo;
> stddev_samp
> ---
>  2887.054977297105
> 
> select weighted_stddev_samp(val,count) from foo2;
>  weighted_stddev_samp
> --
>  2887.19919651336
> 
> The 5th digit seems too early to be seeing round-off error.

Please pardon me if I've misunderstood, but you appear to be assuming
that

SELECT val, count(*) FROM foo GROUP BY val

will produce precisely identical count(*)s at each row, which it
overwhelmingly likely won't, producing the difference you see above.

What have I misunderstood?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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

Re: [HACKERS] Weighted Stats

[Jeff Janes]

On Tue, Mar 15, 2016 at 8:36 AM, David Fetter  wrote:
>
> Please find attached a patch that uses the float8 version to cover the
> numeric types.

Is there a well-defined meaning for having a negative weight?  If no,
should it be disallowed?

I don't know what I was expecting,  but not this:

select weighted_avg(x,1000-2*x) from generate_series(1,1000) f(x);
   weighted_avg
--
 1671666717.1


Also, I think it might not give the correct answer even without
negative weights:

create table foo as select floor(random()*1)::int val from
generate_series(1,1000);

create table foo2 as select val, count(*) from foo group by val;

Shouldn't these then give the same result:

select stddev_samp(val) from foo;
stddev_samp
---
 2887.054977297105

select weighted_stddev_samp(val,count) from foo2;
 weighted_stddev_samp
--
 2887.19919651336

The 5th digit seems too early to be seeing round-off error.

Cheers,

Jeff


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

Re: [HACKERS] Weighted Stats

[David Fetter]

On Fri, Jan 08, 2016 at 04:37:36PM +1100, Haribabu Kommi wrote:
> On Mon, Dec 21, 2015 at 1:50 PM, David Fetter  wrote:
> > On Sun, Dec 20, 2015 at 06:13:33PM -0600, Jim Nasby wrote:
> >> On 11/2/15 5:46 PM, David Fetter wrote:
> >> >I'd like to add weighted statistics to PostgreSQL
> >>
> >> Anything happen with this? If community isn't interested, ISTM it'd be good
> >> to put this in PGXN.
> >
> > I think it's already in PGXN as an extension, and I'll get another
> > version out this early this week, as it involves mostly adding some
> > tests.
> >
> > I'll do the float8 ones for core this week, too, and unless there's a
> > really great reason to do more data types on the first pass, it should
> > be in committable shape.
> 
> I reviewed the patch, following are my observations.
> 
> 1. +   precision, numeric, or interval
> 
> with interval type it is giving problem. As interval data type is not 
> supported,
> so remove it in the list of supported inputs.

Done.

> 
> 2. +float8_weighted_avg(PG_FUNCTION_ARGS)
> 
> It will be helpful, if you provide some information as a function header,
> how the weighted average is calculated similar like other weighted functions.

Done.

> 3. + transvalues = check_float8_array(transarray,
> "float8_weighted_stddev_accum", 4);
> 
> The second parameter to check_float8_array should be "float8_weighted_accum".

Done.

> 4. There is an OID conflict of 4066 with latest master code.

Fixed.

> 5.+ A += newvalW * ( newvalX - transvalues[2] ) / W;
> + CHECKFLOATVAL(A, isinf(newvalW) || isinf(newvalX - transvalues[2])
> || isinf(1.0/W), true);
> 
> + Q += newvalW * (newvalX - transvalues[2]) * (newvalX - A);
> + CHECKFLOATVAL(A, isinf(newvalX -  transvalues[3]) || isinf(newvalX -
> A) || isinf(1.0/W), true);
> 
> 
> Is the need of calculation also needs to be passed to CHECKFLOATVAL?
> Just passing
> the variables involved in the calculation isn't enough? If expressions
> are required then
> it should be something as follows?
> 
> CHECKFLOATVAL(A, isinf(transvalues[2]) || isinf(newvalW) ||
> isinf(newvalX - transvalues[2]) || isinf(1.0/W), true);
> 
> CHECKFLOATVAL(Q, isinf(transvalues[3]) || isinf(newvalX -
> transvalues[2]) || isinf(newvalX - A) || isinf(1.0/W), true);

Done.

Please find attached a patch that uses the float8 version to cover the
numeric types.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 000489d..09ada7e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12645,6 +12645,29 @@ NULL baz(3 rows)
  
   

+weighted_average
+   
+   
+weighted_avg
+   
+   weighted_avg(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, numeric, or interval
+  
+  
+   numeric for any integer-type argument,
+   double precision for a floating-point argument,
+   otherwise the same as the argument data type
+  
+  the average (arithmetic mean) of all input values, weighted by 
the input weights
+ 
+
+ 
+  
+   
 bit_and

bit_and(expression)
@@ -13288,6 +13311,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y 
DESC) AS tab;
  
   

+weighted standard deviation
+population
+   
+   
+weighted_stddev_pop
+   
+   weighted_stddev_pop(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, or numeric
+  
+  
+   double precision for floating-point arguments,
+   otherwise numeric
+  
+  weighted population standard deviation of the input values
+ 
+
+ 
+  
+   
 standard deviation
 sample

@@ -13311,6 +13357,29 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y 
DESC) AS tab;
  
   

+weighted standard deviation
+sample
+   
+   
+weighted_stddev_samp
+   
+   weighted_stddev_samp(value 
expression, weight 
expression)
+  
+  
+   smallint, int,
+   bigint, real, double
+   precision, or numeric
+  
+  
+   double precision for floating-point arguments,
+   otherwise numeric
+  
+  weighted sample standard deviation of the input values
+ 
+
+ 
+  
+   
 variance

variance(expression)
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index d4e5d55..c03f712 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -2364,6 +2364,7 @@ setseed(PG_FUNCTION_ARGS)
  * float8_accum   

Re: [HACKERS] Weighted Stats

[Alvaro Herrera]

I'm closing this for the current commitfest as returned-with-feedback.
Please resubmit for the 2016-03 CF once you have it.

Thanks!

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Weighted Stats

[David Fetter]

On Fri, Jan 08, 2016 at 04:37:36PM +1100, Haribabu Kommi wrote:
> On Mon, Dec 21, 2015 at 1:50 PM, David Fetter  wrote:
> > On Sun, Dec 20, 2015 at 06:13:33PM -0600, Jim Nasby wrote:
> >> On 11/2/15 5:46 PM, David Fetter wrote:
> >> >I'd like to add weighted statistics to PostgreSQL
> >>
> >> Anything happen with this? If community isn't interested, ISTM it'd be good
> >> to put this in PGXN.
> >
> > I think it's already in PGXN as an extension, and I'll get another
> > version out this early this week, as it involves mostly adding some
> > tests.
> >
> > I'll do the float8 ones for core this week, too, and unless there's a
> > really great reason to do more data types on the first pass, it should
> > be in committable shape.
> 
> I reviewed the patch, following are my observations.
> 
> 1. +   precision, numeric, or interval
> 
> with interval type it is giving problem. As interval data type is not 
> supported,
> so remove it in the list of supported inputs.

I'd meant to add more, but will make sure that the next version
documents exactly the types it supports.

> postgres=# select weighted_avg(f7,f1) from tbl;
> ERROR:  function weighted_avg(interval, smallint) does not exist at character 
> 8
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
> 
> 
> 2. +float8_weighted_avg(PG_FUNCTION_ARGS)
> 
> It will be helpful, if you provide some information as a function header,
> how the weighted average is calculated similar like other weighted functions.

Will do.

> 3. + transvalues = check_float8_array(transarray,
> "float8_weighted_stddev_accum", 4);
> 
> The second parameter to check_float8_array should be "float8_weighted_accum".

Oops.  Will fix.

> 4. There is an OID conflict of 4066 with latest master code.

Will fix.

> 5.+ A += newvalW * ( newvalX - transvalues[2] ) / W;
> + CHECKFLOATVAL(A, isinf(newvalW) || isinf(newvalX - transvalues[2])
> || isinf(1.0/W), true);
> 
> + Q += newvalW * (newvalX - transvalues[2]) * (newvalX - A);
> + CHECKFLOATVAL(A, isinf(newvalX -  transvalues[3]) || isinf(newvalX -
> A) || isinf(1.0/W), true);
> 
> 
> Is the need of calculation also needs to be passed to CHECKFLOATVAL?
> Just passing
> the variables involved in the calculation isn't enough? If expressions
> are required then
> it should be something as follows?
> 
> CHECKFLOATVAL(A, isinf(transvalues[2]) || isinf(newvalW) ||
> isinf(newvalX - transvalues[2]) || isinf(1.0/W), true);
> 
> CHECKFLOATVAL(Q, isinf(transvalues[3]) || isinf(newvalX -
> transvalues[2]) || isinf(newvalX - A) || isinf(1.0/W), true);

Will fix.

> I verified the stddev transition and final function calculations
> according to wikipedia
> and they are fine.

Thanks for reviewing this!

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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

Re: [HACKERS] Weighted Stats

[Haribabu Kommi]

On Mon, Dec 21, 2015 at 1:50 PM, David Fetter  wrote:
> On Sun, Dec 20, 2015 at 06:13:33PM -0600, Jim Nasby wrote:
>> On 11/2/15 5:46 PM, David Fetter wrote:
>> >I'd like to add weighted statistics to PostgreSQL
>>
>> Anything happen with this? If community isn't interested, ISTM it'd be good
>> to put this in PGXN.
>
> I think it's already in PGXN as an extension, and I'll get another
> version out this early this week, as it involves mostly adding some
> tests.
>
> I'll do the float8 ones for core this week, too, and unless there's a
> really great reason to do more data types on the first pass, it should
> be in committable shape.

I reviewed the patch, following are my observations.

1. +   precision, numeric, or interval

with interval type it is giving problem. As interval data type is not supported,
so remove it in the list of supported inputs.

postgres=# select weighted_avg(f7,f1) from tbl;
ERROR:  function weighted_avg(interval, smallint) does not exist at character 8
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.


2. +float8_weighted_avg(PG_FUNCTION_ARGS)

It will be helpful, if you provide some information as a function header,
how the weighted average is calculated similar like other weighted functions.


3. + transvalues = check_float8_array(transarray,
"float8_weighted_stddev_accum", 4);

The second parameter to check_float8_array should be "float8_weighted_accum".


4. There is an OID conflict of 4066 with latest master code.


5.+ A += newvalW * ( newvalX - transvalues[2] ) / W;
+ CHECKFLOATVAL(A, isinf(newvalW) || isinf(newvalX - transvalues[2])
|| isinf(1.0/W), true);

+ Q += newvalW * (newvalX - transvalues[2]) * (newvalX - A);
+ CHECKFLOATVAL(A, isinf(newvalX -  transvalues[3]) || isinf(newvalX -
A) || isinf(1.0/W), true);


Is the need of calculation also needs to be passed to CHECKFLOATVAL?
Just passing
the variables involved in the calculation isn't enough? If expressions
are required then
it should be something as follows?

CHECKFLOATVAL(A, isinf(transvalues[2]) || isinf(newvalW) ||
isinf(newvalX - transvalues[2]) || isinf(1.0/W), true);

CHECKFLOATVAL(Q, isinf(transvalues[3]) || isinf(newvalX -
transvalues[2]) || isinf(newvalX - A) || isinf(1.0/W), true);


I verified the stddev transition and final function calculations
according to wikipedia
and they are fine.

Regards,
Hari Babu
Fujitsu Australia


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

Re: [HACKERS] Weighted Stats

[Jim Nasby]

On 11/2/15 5:46 PM, David Fetter wrote:

I'd like to add weighted statistics to PostgreSQL


Anything happen with this? If community isn't interested, ISTM it'd be 
good to put this in PGXN.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [HACKERS] Weighted Stats

[David Fetter]

On Sun, Dec 20, 2015 at 06:13:33PM -0600, Jim Nasby wrote:
> On 11/2/15 5:46 PM, David Fetter wrote:
> >I'd like to add weighted statistics to PostgreSQL
> 
> Anything happen with this? If community isn't interested, ISTM it'd be good
> to put this in PGXN.

I think it's already in PGXN as an extension, and I'll get another
version out this early this week, as it involves mostly adding some
tests.

I'll do the float8 ones for core this week, too, and unless there's a
really great reason to do more data types on the first pass, it should
be in committable shape.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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