PostgreSQL中的滞后计算

本文介绍了PostgreSQL中的滞后计算的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含以下数据的表:计算列:当前

I have a table with following data: computed column : current

| id | Date (dd/mm/yyyy) | Factor | Actual | Current | |----|-------------------|--------|--------|----------| | 1 | 04/01/2017 | 0.5 | 100 | 100 | | 2 | 04/02/2017 | 0.5 | 120 | 100 | | 3 | 04/03/2017 | 0.5 | 120 | 110 | | 4 | 04/04/2017 | 0.5 | 115 | 115 | | 5 | 04/05/2017 | 0.5 | 125 | 115 | | 6 | 04/06/2017 | 0.5 | 100 | 120 | | 7 | 04/07/2017 | 0.5 | 100 | 110 |

当前行=上一行的当前值+因子*(上一行的实际值-上一行的当前值)

Current row = current of previous row + factor * (actual of previous row - current of previous row)

For id = 1, current = same as actual = 100 For id = 2, current = 100 + 0.5 * (100 - 100) = 100 For id = 3, current = 100 + 0.5 * (120 - 100) = 110 For id = 4, current = 110 + 0.5 * (120 - 110) = 115 and so on...

如何使用查询在Postgresql中实现?

How to achieve in postgresql using query?

推荐答案

您需要递归查询.

with recursive my_table_with_rn as ( select *, row_number() over (order by id) as rn from my_table ), rec_query(rn, id, date, factor, actual, current) as ( select rn, id, date, factor, actual, actual from my_table_with_rn where rn = 1 union all select t.rn, t.id, t.date, t.factor, t.actual, p.current + t.factor * (p.actual - p.current) from rec_query p join my_table_with_rn t on t.rn = p.rn + 1 ) select id, date, factor, actual, current from rec_query order by id;

请注意，添加row_number()是为了在ids不是连续的情况下起作用(对于实际数据而言不是必需的，您可以使用id代替rn).

Note, that row_number() was added to work in cases when ids are not consecutive (it's not necessary for actual data, you can use id instead of rn).

在此处进行测试.