使用postgres进行线性回归

Leasye 发表于 Dev

莱西

我使用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日期来计算？

例如，对于日期1984-02-17，1984-02-11和1984-02-21 ，ID 2的线性回归必须计算为值7（实际），5（上一个），8（下一个）

编辑：我必须使用regr_intercept（value，udate），但如果我必须为每行仅使用实际的，上一个和下一个值/日期，我真的不知道该怎么做。

Edit2：将3行添加到idstation（12）; ID和日期数字已更改

希望你能帮助我，谢谢！

野生撒尿

这是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（）函数更优雅地完成三人一组的聚类，这也将允许使用更大的滑动窗口。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。