postgresql - postgres 的线性回归

Question

我使用 Postgres 并且我有大量行，其中包含每个站点的值和日期。(日期可以相隔几天。)

id      | value | idstation | udate
--------+-------+-----------+-----
1       |  5    | 12        | 1984-02-11 00:00:00
2       |  7    | 12        | 1984-02-17 00:00:00
3       |  8    | 12        | 1984-02-21 00:00:00
4       |  9    | 12        | 1984-02-23 00:00:00
5       |  4    | 12        | 1984-02-24 00:00:00
6       |  8    | 12        | 1984-02-28 00:00:00
7       |  9    | 14        | 1984-02-21 00:00:00
8       |  15   | 15        | 1984-02-21 00:00:00
9       |  14   | 18        | 1984-02-21 00:00:00
10      |  200  | 19        | 1984-02-21 00:00:00

请原谅这个可能很愚蠢的问题，但我不是一个数据库专家。

是否可以直接输入一个 SQL 查询来计算每个站点每个日期的线性回归，知道回归必须只用实际 ID 日期、上一个 ID 日期和下一个 ID 日期计算？

例如，对于 id 2 的线性回归，必须使用日期 1984-02-17 和 1984-02 的值 7(实际)、5(上一个)、8(下一个)进行计算-11 和 1984-02-21

编辑:我必须使用regr_intercept(value,udate)，但如果我必须只使用实际的、以前的和每行的下一个值/日期。

Edit2 : idstation(12) 添加了 3 行； ID 和日期数字已更改

希望您能帮帮我，谢谢!

Answer 1

这是 Joop 的统计数据和 Denis 的窗口函数的组合:

WITH num AS (
        SELECT id, idstation
        , (udate - '1984-01-01'::date) as idate -- count in dayse since jan 1984
        , value AS value
        FROM thedata
        )
        -- id + the ids of the {prev,next} records
        --  within the same idstation group
, drag AS (
        SELECT id AS center
                , LAG(id) OVER www AS prev
                , LEAD(id) OVER www AS next
        FROM thedata
        WINDOW www AS (partition by idstation ORDER BY id)
        )
        -- junction CTE between ID and its three feeders
, tri AS (
                  SELECT center AS this, center AS that FROM drag
        UNION ALL SELECT center AS this , prev AS that FROM drag
        UNION ALL SELECT center AS this , next AS that FROM drag
        )
SELECT  t.this, n.idstation
        , regr_intercept(value,idate) AS intercept
        , regr_slope(value,idate) AS slope
        , regr_r2(value,idate) AS rsq
        , regr_avgx(value,idate) AS avgx
        , regr_avgy(value,idate) AS avgy
FROM num n
JOIN tri t ON t.that = n.id
GROUP BY t.this, n.idstation
        ;

结果:

INSERT 0 7
 this | idstation |     intercept     |       slope       |        rsq        |       avgx       |       avgy       
------+-----------+-------------------+-------------------+-------------------+------------------+------------------
    1 |        12 |               -46 |                 1 |                 1 |               52 |                6
    2 |        12 | -24.2105263157895 | 0.578947368421053 | 0.909774436090226 | 53.3333333333333 | 6.66666666666667
    3 |        12 | -10.6666666666667 | 0.333333333333333 |                 1 |             54.5 |              7.5
    4 |        14 |                   |                   |                   |               51 |                9
    5 |        15 |                   |                   |                   |               51 |               15
    6 |        18 |                   |                   |                   |               51 |               14
    7 |        19 |                   |                   |                   |               51 |              200
(7 rows)

使用 rank() 或 row_number() 函数可以更优雅地完成三人组的聚类，这也允许使用更大的滑动窗口。