SQL 遍历 PostgreSQL 记录。如何引用下一行的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15714342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Iterating through PostgreSQL records. How to reference data from next row?
提问by Terence Chow
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 WITHstatement is probably not supposed to be above the IFstatement. 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 = .asset_symbol
AND asset_histories.date <= .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。
回答by Erwin Brandstetter
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/pgSQLand SQL functions.
一般来说，在开始提问之前，您需要学习基础知识。
阅读优秀的手动约CREATE FUNCTION，PL / pgSQL的和SQL函数。
Major points why the example is nonsense
为什么这个例子是无稽之谈的要点
First, you cannot hand in an identifierlike you do. Identifiers cannot be parameterized in plain SQL. You'd need dynamic SQLfor 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
_dateinstead ofdateas parameter name and renamed your table column toasset_date.ALTERyour 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.
WITHis part of aSELECTstatement and cannot be mixed with plpgsql control structures likeLOOPorIF.
首先，您不能像这样提交标识符。标识符不能在普通 SQL 中参数化。为此，您需要动态 SQL。
当然，根据您的要求，您实际上并不需要它。只涉及一张桌子。尝试将其参数化是无稽之谈。不要使用类型名称作为标识符。我使用
_date而不是date作为参数名称并将您的表列重命名为asset_date.ALTER您的表定义相应。从表中获取数据的函数永远不可能是
IMMUTABLE. 阅读手册。您正在以荒谬的方式将 SQL 语法与 plpgsql 元素混合在一起。
WITH是SELECT语句的一部分，不能与 plpgsql 控制结构（如LOOP或 ）混合使用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 statisticsin PostgreSQL.
根据您后来的评论，您想要计算标准偏差等统计数字。PostgreSQL 中
有专门用于统计的聚合函数。
回答by Akash
Simple things like just calculating the per_change, can be done within a viewinstead, this would also result in faster results
简单的事情，比如计算per_change, 可以在 a 内完成view，这也会导致更快的结果
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'
