使用PostgreSQL用户定义的函数实现类似窗口函数的行为

Question

假设给定一个具有属性date(day(和value的表observations_tbl，我想生成新的属性prev_day_value以获得下表：

|---------------------|-------|----------------|
|     date            | value | prev_day_value |
|---------------------|-------|----------------|
| 01.01.2015 00:00:00 | 5     | 0              |
| 02.01.2015 00:00:00 | 4     | 5              |
| 03.01.2015 00:00:00 | 3     | 4              |
| 04.01.2015 00:00:00 | 2     | 3              |
|---------------------|-------|----------------|

我很清楚，这样的输出通常可以使用WINDOW函数来获得。但是，我如何通过PostgreSQL用户定义的函数来实现这一点呢？我想指出的是，我必须使用一个函数，如果不详细说明，很难解释为什么——这些都是我的限制，如果有什么不同的话，这是一个技术挑战。

考虑此模板查询：

SELECT *, lag(value,1) AS prev_day_value -- or lag(record,1) or lag(date,value,1) or lag(date,1) or lag(observations_tbl,1), etc.
FROM observations_tbl

我使用函数lag和参数1来查找在当前行之前1的值——1行的距离。我不在乎函数lag还可以有什么其他参数(表名、其他属性(——函数lag可以是什么样子来实现这样的功能？该函数可以是任何语言，SQL、PL/pgSQL，甚至使用PostgreSQL API/后端的C。

我知道一个答案可以是将WINDOW查询封装在lag用户定义的函数中。但我认为，如果我必须扫描整个表两次(一次在lag函数内部，一次在外部(，这将是一个相当昂贵的操作。我在想，也许每个PostgreSQL记录都会有一个指向其前一个记录的指针，可以直接访问？或者我可以在这个特定的行/行号打开光标，而不必扫描整个表？或者我所要求的是不可能的？

Answer 1

您的请求无法用关系工具解决(窗口函数不是SQL中的关系扩展(。在C语言中，您可以编写自己的函数滞后替代方案。您可以用PL8语言(Javascript(做同样的工作。遗憾的是，对于PL/pgSQL，不存在用于窗口函数的API。您不能编写简单的PL/pgSQL函数，它可以访问与处理的行不同的行。

一个可能的替代方案(但有一些性能风险(是编写表函数。在那里，您可以控制所有已处理的数据集，并且可以简单地执行此操作。

CREATE OR REPLACE FUNCTION report()
RETURNS TABLE(d date, v int, prev_v int) $$
DECLARE r RECORD;
BEGIN
prev_v := 0;
FOR r IN SELECT date, value FROM observations_tbl t ORDER BY 1
LOOP
d := r.date; v := r.value;
RETURN NEXT;
prev_v := v;
END LOOP;
END;
$$ LANGUAGE plpgsql;

没有任何其他可供选择的可用解决方案。在很早的时候，这些值是用相关的自连接计算的，但这个解决方案的性能相当糟糕。

Answer 2

帕维尔发布的内容，只是作业较少。应该更快：

CREATE OR REPLACE FUNCTION report()
RETURNS TABLE(d date, v int, prev_v int) AS
$func$
BEGIN
prev_v := 0;
FOR d, v IN
SELECT date, value FROM observations_tbl ORDER BY 1
LOOP
RETURN NEXT;
prev_v := v;
END LOOP;
END
$func$  LANGUAGE plpgsql;

如果实际上用一次扫描取代了对表格的多次扫描，那么总体想法是可行的。像这里：

• GROUP BY和聚合顺序数值