postgresql取第一行数据_遍历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.

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?

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.

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

ELSE

RETURN NIL

ENDIF

END

$func$

PostgreSQL 9.2.

解决方案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.

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.

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.

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.

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():

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.