遍历PostgreSQL记录。如何引用下一行的数据?

I'm new to PostgreSQL and writing functions here is tough as nails. So I'm hoping someone can help let me know how to do what I'm trying to do.

我是PostgreSQL的新手，在这里编写函数非常困难。所以我希望有人能帮助我，让我知道如何去做我想做的事情。

I have a table of stock prices and dates. I want to calculate the percent change from the previous day for each entry. For the earliest day of data, there won't be a previous day, so that entry can simply be Nil. Can someone look over my function and help me with
a) how to reference data from the next row and
b) help me clean it up?

我有一张股票价格和日期的表格。我想计算每一项与前一天相比的变化百分比。对于最早的数据，不会有前天，所以这个条目可以是Nil。有人能帮我检查一下我的功能吗? a)如何引用下一行的数据，b)帮助我清理?

I'm aware that the WITH statement is probably not supposed to be above the IF statement. However logically, this is how I've thought about it so far and thus how I've written it. If you could advise how that is supposed to look it would be much appreciated as well.

我知道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.

PostgreSQL 9.2。

2 个解决方案

#1

I want to calculate the percent change from the previous day for each entry.

我想计算每一项与前一天相比的变化百分比。

Generally you need to study the basics, before you start asking questions.
Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

一般来说，在开始提问之前，你需要学习基本知识。阅读关于创建函数、PL/pgSQL和SQL函数的优秀手册。

Major points why the example is nonsense

• First, you cannot hand in an identifier like you do. Identifiers cannot be parameterized in plain SQL. You'd need dynamic SQL for that.
  Of course, you don't actually need that, according to your requirements. There is only one table involved. It is nonsense to try and parameterize it.

  首先，您不能像您这样使用标识符。标识符不能在纯SQL中参数化。为此需要动态SQL。当然，根据您的需求，您实际上并不需要它。只涉及到一张表。试图将其参数化是毫无意义的。

• Don't use type names as identifiers. I use _date instead of date as parameter name and renamed your table column to asset_date. ALTER your table definition accordingly.

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

• A function fetching data from a table can never be IMMUTABLE. Read the manual.

  从表中获取数据的函数永远不会是不可变的。阅读手册。

• You are mixing SQL syntax with plpgsql elements in nonsensical ways. WITH is part of a SELECT statement and cannot be mixed with plpgsql control structures like LOOP or IF.

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

Proper function

A proper function could look like this (one of many ways):

一个合适的函数可以是这样的(很多方法之一):

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

Performance shouldn't be so bad, but it's just pointless complication.

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

Proper solution: plain query

The simplest (and probably fastest) way would be with the window function lag():

最简单(可能也是最快)的方法是使用窗口函数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;

Standard deviation

As per your later comment, you want to calculate statistical numbers like standard deviation.
There are dedicated aggregate functions for statistics in PostgreSQL.

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

#2

Simple things like just calculating the per_change, can be done within a view instead, this would also result in faster results

像计算per_change这样的简单操作，可以在视图中执行，这也会带来更快的结果

create view view_stock_details AS ( SELECT 
    date, 
    price, 
    symbol, 
    pervious_day_close, 
    (price-pervious_day_close)/pervious_day_close*100 as per_change 
FROM (
    SELECT
        date,
        price,
        symbol,
        ( SELECT price FROM asset_histories t WHERE t.symbol = outers.symbol AND t.date < outers.date limit 1 ) as pervious_day_close
    FROM 
        asset_histories as outers
    ); 

To view the stock details, you can then use

要查看股票详细信息，您可以使用

SELECT 
    *
FROM
    view_stock_details
WHERE
    date = '2012-01-03' 
    AND symbol = 'A'