postgresql – 使用postgres进行线性回归

概述我使用Postgres,我有大量的行,每个站点有值和日期. (日期可以分开几天.) id | value | idstation | udate--------+-------+-----------+-----1 | 5 | 12 | 1984-02-11 00:00:002 | 7 | 12 | 1984-02 我使用Postgres,我有大量的行,每个站点有值和日期.
(日期可以分开几天.)

ID      | value | IDstation | udate--------+-------+-----------+-----1       |  5    | 12        | 1984-02-11 00:00:002       |  7    | 12        | 1984-02-17 00:00:003       |  8    | 12        | 1984-02-21 00:00:004       |  9    | 12        | 1984-02-23 00:00:005       |  4    | 12        | 1984-02-24 00:00:006       |  8    | 12        | 1984-02-28 00:00:007       |  9    | 14        | 1984-02-21 00:00:008       |  15   | 15        | 1984-02-21 00:00:009       |  14   | 18        | 1984-02-21 00:00:0010      |  200  | 19        | 1984-02-21 00:00:00

原谅可能是一个愚蠢的问题,但我不是一个数据库大师.

是否可以直接输入一个SQL查询,该查询将计算每个日期的每个日期的线性回归,知道回归必须只计算实际的ID日期,前一个ID日期和下一个ID日期？

例如,ID 2的线性回归必须以值7(实际),5(上一个),8(下一个)计算日期1984-02-17,1984-02-11和1984-02-21

编辑：我必须使用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 avgyFROM num nJOIN tri t ON t.that = n.IDGROUP 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()函数可以更优雅地完成三组的聚类,这也允许使用更大的滑动窗口.

总结

以上是内存溢出为你收集整理的postgresql – 使用postgres进行线性回归全部内容，希望文章能够帮你解决postgresql – 使用postgres进行线性回归所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。