regr_slope returning NULL - Mailing list pgsql-general
| From | Steve Baldwin |
|---|---|
| Subject | regr_slope returning NULL |
| Date | |
| Msg-id | CAKE1AiYv2phXum3yN01Eorkj86JqarACyU_gtX1LSpT65EW9Uw@mail.gmail.com Whole thread Raw |
| Responses |
Re: regr_slope returning NULL
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | 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
pgsql-general by date: