遍历PostgreSQL记录。如何参考下一行的数据？

Question

我是PostgreSQL的新手，在这里编写函数非常困难。所以我希望有人可以帮助我知道该怎么做。

我有一张股票价格和日期表。我想为每个条目计算与前一天相比的变化百分比。对于最早的数据，不会有前一天的记录，因此条目可以简单地为Nil。有人可以查看我的功能并为我提供
以下帮助：a）如何引用下一行的数据，以及
b）帮助我清理数据？

我知道该WITH声明可能不应该放在该IF声明之上。但是从逻辑上讲，这是到目前为止我一直在思考的方式，因此也是我编写它的方式。如果您能建议应该如何看待它，也将不胜感激。

CREATE FUNCTION percentage_change_func(asset_histories) RETURNS 
numeric LANGUAGE sql IMMUTABLE AS $func$

DECLARE
r asset_histories%rowtype
BEGIN   
WITH twodaysdata AS (SELECT date,price,asset_symbol FROM asset_histories 
           WHERE asset_symbol = $1.asset_symbol 
           AND asset_histories.date <= $1.date 
           ORDER BY date DESC LIMIT 2), 
         numberofrecords AS (SELECT count(*) FROM twodaysdata)

IF numberofrecords = 2 THEN
        RETURN r.price / (r+1).price - 1  <---How do I reference r + 1??/
ELSE
        RETURN NIL
ENDIF
END
$func$

PostgreSQL 9.2。

Answer 1

我想为每个条目计算与前一天相比的变化百分比。

通常，在开始提出问题之前，您需要 学习基础知识 。
阅读优秀的手动约 CREATE FUNCTION ， PL /
pgSQL的 和
SQL函数
。

要点-为什么这个例子是胡说八道

• 首先，您不能像您一样上交 标识符 。不能在普通SQL中对标识符进行参数化。为此，您需要 动态SQL 。
  当然，根据您的要求，您实际上并不需要。仅涉及一张桌子。尝试对其进行参数化是胡说八道。

• 不要使用类型名称作为标识符。我使用_date而不是date作为参数名称，并将您的表列重命名为asset_date。ALTER您的表定义相应。

• 从表中获取数据的功能永远不可能IMMUTABLE。阅读手册。

• 您正在以荒谬的方式将SQL语法与plpgsql元素混合在一起。WITH是SELECT语句的一部分，不能与plpgsql控制结构（如LOOP或）混合使用IF。

适当的功能

适当的功能可能看起来像这样（多种方式之一）：

CREATE FUNCTION percentage_change_func(_asset_symbol text)
  RETURNS TABLE(asset_date date, price numeric, pct_change numeric) AS
$func$
DECLARE
   last_price numeric;
BEGIN

FOR asset_date, price IN
   SELECT a.asset_date, a.price
   FROM   asset_histories a
   WHERE  a.asset_symbol = _asset_symbol 
   ORDER  BY a.asset_date  -- traverse ascending
LOOP
   pct_change := price / last_price; -- NULL if last_price is NULL
   RETURN NEXT;
   last_price := price;
END LOOP;

END
$func$ LANGUAGE plpgsql STABLE

性能不应该那么差，但这只是毫无意义的复杂性。

正确的解决方案：简单查询

最简单（可能最快）的方法是使用window函数
lag() ：

SELECT asset_date, price
      ,price / lag(price) OVER (ORDER BY asset_date) AS pct_change
FROM   asset_histories
WHERE  asset_symbol = _asset_symbol 
ORDER  BY asset_date;

标准偏差

根据您以后的评论，您想要计算统计数字（例如标准差）。PostgreSQL中
有专用的
聚合函数用于统计 。