我正在 PostgreSQL 9.2 中编写一个函数。
对于股票价格和日期的表,我想计算每个条目较前一天的百分比变化。对于最早一天的数据,不会有前一天,因此该条目可以简单地为 Nil。
我知道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$;
如何正确引用下一行的数据?
我想计算每个条目与前一天相比的百分比变化
学习基础知识第一的。阅读有关的优秀手册CREATE FUNCTION https://www.postgresql.org/docs/current/sql-createfunction.html, PL/pgSQL https://www.postgresql.org/docs/current/plpgsql.html and SQL函数 https://www.postgresql.org/docs/current/xfunc-sql.html.
为什么这个例子是废话的要点
-
您不能提交标识符像你所做地。标识符不能在普通 SQL 中参数化。你需要动态SQL https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN为了那个原因。
当然,您的目标实际上并不需要。仅涉及一张表。参数化它是无意义的。
-
不要使用类型名称作为标识符。我用_date
代替date
作为参数名称并将表列重命名为asset_date
. ALTER
相应的表定义。
-
从表中获取数据的函数永远不会IMMUTABLE
。不要这样声明。阅读手册。 https://www.postgresql.org/docs/current/sql-createfunction.html
-
您以无意义的方式将 SQL 语法与 PL/pgSQL 元素混合在一起。WITH
是一个的一部分SELECT
语句并且不能与 PL/pgSQL 控制结构混合,例如LOOP
or IF
.
消毒功能
正确的函数可能如下所示(多种方法之一):
CREATE FUNCTION percentage_change_func(_asset_symbol text)
RETURNS TABLE(asset_date date, price numeric, pct_change numeric)
LANGUAGE plpgsql STABLE AS
$func$
DECLARE
last_price numeric;
BEGIN
FOR asset_date, price IN
SELECT a.asset_date, a.price -- table-qualify to disambiguate!
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$;
性能不应该那么差,但仍然是毫无意义的复杂化。
正确的解决方案:普通查询
最简单(可能也是最快)的方法是使用窗口函数lag() https://www.postgresql.org/docs/current/functions-window.html:
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;
标准差
根据您后来的评论,您想要计算标准差等统计数字。
Postgres 专门统计聚合函数 https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)