Обсуждение: BUG #13619: regression functions return Null
The following bug has been logged on the website: Bug reference: 13619 Logged by: Biswadeep Banerjee Email address: biswadeep.banerjee@actifio.com PostgreSQL version: 9.4.4 Operating system: Any Description: Hi, Trending analysis is one of our use case, where I am trying the use the regression function in postgres 9.4.4. eg. regr_slope(y,x); regr_intercept(y,x). I have tried to use the functions to fit in our trending analysis SQL but returns NULL. As test also tried with constant values, like, select regr_slope(4,3), regr_intercept(4,3) from table; --- returns NULL. Is this a known issue? or I am using the function differently. Thanks
On Mon, Sep 14, 2015 at 6:14 AM, <biswadeep.banerjee@actifio.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13619 > Logged by: Biswadeep Banerjee > Email address: biswadeep.banerjee@actifio.com > PostgreSQL version: 9.4.4 > Operating system: Any > Description: > > Hi, > Trending analysis is one of our use case, where I am trying the use the > regression function in postgres 9.4.4. > eg. regr_slope(y,x); regr_intercept(y,x). > > I have tried to use the functions to fit in our trending analysis SQL but > returns NULL. > As test also tried with constant values, like, > select regr_slope(4,3), regr_intercept(4,3) from table; > > --- returns NULL. > > Is this a known issue? or I am using the function differently. > =E2=80=8BThese are "aggregate" functions and you need to use them in a quer= y that contains a "GROUP BY". Think about it this way - what is the slope and intercept of a single point? That is basically what you are asking when you write "regr_slope(4,3)" David J. =E2=80=8B
Oh, and don't reply to people individually. I'm going to move this to -general with one last copy for -bugs even though it is not one.
On Mon, Sep 14, 2015 at 3:01 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Below is a sample query that I have been trying to get the slope and intercept. The inner query basically gives me the time vs capacity, ie. x and y data showing the actuals. Based on the x, y value, I calculate slope and intercept value, as in the outer query.Hi David,May be I gave a wrong example to explain my issue.
But it looks like am back to your comment as it mentions I am asking for slope and intercept of a single point. Could you provide me some examples that I can use as a reference.
As a reference, I am following similar example as in http://stackoverflow.com/questions/20490756/linear-regression-with-postgres
select x, y, regr_slope(y,x), regr_intercept(y,x)
from (/* doesn't matter */
) i
group by x, y
order by x asc, y ascYou are grouping on (x, y) and then passing this single POINT into a function that requires multiple points in order to calculate the slope and intercept of a LINE (i.e., something requiring two points to describe) and are confused why it is giving you NULL...I have answered your question - you are using the functions incorrectly.This is operator error, not a bug.You have already found a reasonably good example of how these functions can be used. Given I have never used them myself I do not have anything better to offer.David J.
Oops!! My bad.. I did a reply and not a reply all...
Anyways Thanks David.. I knew this wasn't really a bug but could not figure out where to post my questions. Let me just try the same in a different way or do it differently as you explained and see if I can make it to work.
Anyways Thanks David.. I knew this wasn't really a bug but could not figure out where to post my questions. Let me just try the same in a different way or do it differently as you explained and see if I can make it to work.
Thanks
On Tue, Sep 15, 2015 at 12:33 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Oh, and don't reply to people individually. I'm going to move this to -general with one last copy for -bugs even though it is not one.On Mon, Sep 14, 2015 at 3:01 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Below is a sample query that I have been trying to get the slope and intercept. The inner query basically gives me the time vs capacity, ie. x and y data showing the actuals. Based on the x, y value, I calculate slope and intercept value, as in the outer query.Hi David,May be I gave a wrong example to explain my issue.
But it looks like am back to your comment as it mentions I am asking for slope and intercept of a single point. Could you provide me some examples that I can use as a reference.
As a reference, I am following similar example as in http://stackoverflow.com/questions/20490756/linear-regression-with-postgres
select x, y, regr_slope(y,x), regr_intercept(y,x)
from (/* doesn't matter */
) i
group by x, y
order by x asc, y ascYou are grouping on (x, y) and then passing this single POINT into a function that requires multiple points in order to calculate the slope and intercept of a LINE (i.e., something requiring two points to describe) and are confused why it is giving you NULL...I have answered your question - you are using the functions incorrectly.This is operator error, not a bug.You have already found a reasonably good example of how these functions can be used. Given I have never used them myself I do not have anything better to offer.David J.
--
Best Regards,
Biswadeep Banerjee