遍历PostgreSQL记录.如何参考下一行的数据? [英] Iterating through PostgreSQL records. How to reference data from next row?

问题描述

我是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.

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

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?

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

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  <---How do I reference r + 1??/
ELSE
        RETURN NIL
ENDIF
END
$func$

PostgreSQL 9.2.

PostgreSQL 9.2.

推荐答案

我想计算每个日期与前一天相比的变化百分比 项.

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

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

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

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

• 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.

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

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.

从表中获取数据的函数永远不能是IMMUTABLE. 阅读该手册.

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

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

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.

适当的功能可能看起来像这样(多种方式之一):

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.

最简单(可能最快)的方法是使用窗口函数 lag() :

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.

这篇关于遍历PostgreSQL记录.如何参考下一行的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！