regr_slope returning NULL

From: Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: regr_slope returning NULL
Date: 2019-03-24 02:45:23
Message-ID: CAKE1AiYv2phXum3yN01Eorkj86JqarACyU_gtX1LSpT65EW9Uw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm not sure whether or not this is a bug, so I've posted here first (after
having posted on Stack Overflow).

Consider the following:

log=# create table sb1(id text, elapsed int, ts numeric);
CREATE TABLE
log=# insert into sb1 values
('317e',86,1552861322.627),('317e',58,1552861324.747),('317e',52,1552861325.722),('317e',58,1552861326.647),('317e',82,1552861327.609),('317e',118,1552861328.514),('317e',58,1552861329.336),('317e',58,1552861330.317),('317e',54,1552861330.935),('3441',68,1552861324.765),('3441',84,1552861326.665),('3441',56,1552861327.627),('3441',50,1552861330.952),('5fe6',42,1552993248.398),('5fe6',44,1552993255.883),('5fe6',44,1553166049.261),('c742',62,1552861322.149),('c742',68,1552861322.455);
INSERT 0 18
log=# select * from sb1 order by id, ts;
id | elapsed | ts
------+---------+----------------
317e | 86 | 1552861322.627
317e | 58 | 1552861324.747
317e | 52 | 1552861325.722
317e | 58 | 1552861326.647
317e | 82 | 1552861327.609
317e | 118 | 1552861328.514
317e | 58 | 1552861329.336
317e | 58 | 1552861330.317
317e | 54 | 1552861330.935
3441 | 68 | 1552861324.765
3441 | 84 | 1552861326.665
3441 | 56 | 1552861327.627
3441 | 50 | 1552861330.952
5fe6 | 42 | 1552993248.398
5fe6 | 44 | 1552993255.883
5fe6 | 44 | 1553166049.261
c742 | 62 | 1552861322.149
c742 | 68 | 1552861322.455
(18 rows)

log=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id;
id | trend
------+----------------------
c742 |
317e |
5fe6 | 5.78750952760444e-06
3441 |
(4 rows)

Interestingly, the same dataset and function in Oracle 11.2 returns the
same value for one of the ids and non-null values for the other ids :

SQL> select * from sb1 order by id, ts;

ID ELAPSED TS
---------- ---------- ----------------
317e 86 1552861322.627
317e 58 1552861324.747
317e 52 1552861325.722
317e 58 1552861326.647
317e 82 1552861327.609
317e 118 1552861328.514
317e 58 1552861329.336
317e 58 1552861330.317
317e 54 1552861330.935
3441 68 1552861324.765
3441 84 1552861326.665
3441 56 1552861327.627
3441 50 1552861330.952
5fe6 42 1552993248.398
5fe6 44 1552993255.883
5fe6 44 1553166049.261
c742 62 1552861322.149
c742 68 1552861322.455

18 rows selected.

SQL> select id, regr_slope(elapsed, ts) from sb1 group by id;

ID REGR_SLOPE(ELAPSED,TS)
---------- ----------------------
c742 19.6078431
5fe6 5.7875E-06
317e -1.0838511
3441 -3.8283951

If pg is correctly returning NULL, I'd be interested to understand the
circumstances under which this can occur.

Thanks,

Steve

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-03-24 05:34:41 Re: regr_slope returning NULL
Previous Message Kenneth Marshall 2019-03-23 22:12:41 Re: software or hardware RAID?