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(dot)banerjee(at)actifio(dot)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(dot)banerjee(at)actifio(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13619
> Logged by: Biswadeep Banerjee
> Email address: biswadeep(dot)banerjee(at)actifio(dot)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.
>

​These are "aggregate" functions and you need to use them in a query 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.
​

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Sep 14, 2015 at 2:55 PM, Biswadeep Banerjee <
> biswadeep(dot)banerjee(at)actifio(dot)com> wrote:
>
>> Hi David,
>> May be I gave a wrong example to explain my issue.
>>
>> 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.
>>
>> 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 asc
>>
>
> ​You 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.

Thanks

On Tue, Sep 15, 2015 at 12:33 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Mon, Sep 14, 2015 at 2:55 PM, Biswadeep Banerjee <
>> biswadeep(dot)banerjee(at)actifio(dot)com> wrote:
>>
>>> Hi David,
>>> May be I gave a wrong example to explain my issue.
>>>
>>> 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.
>>>
>>> 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 asc
>>>
>>
>> ​You 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