时间戳

首页 > 解决方案 > postgresql for in loop of a select statement - 通过迭代更新变量的问题

postgresql - postgresql for in loop of a select statement - 通过迭代更新变量的问题

问题描述

我编写了一个函数来计算价格的指数移动平均线，该价格循环遍历 SELECT 语句的结果。移动平均线的长度为 26 天。对于前 25 行，EMA 的值为空。第 26 天只是前 26 个值的简单平均值。26 之后的任何行的 EMA 值为（当前行价格 - 前一行 EMA）*(2/27) + previous_row_EMA。

下面的循环使用 CASE 语句执行此操作。我已经声明了一个变量“sumlong”并将其设置为零。“sumlong”变量保持前 25 行的运行总和。第二个 CASE 语句计算第 26 天的简单移动平均线。我还声明了一个变量“last_EMA26”作为下一次迭代中使用的临时持有者。

第 26 行似乎计算正确，但是第 26 行之后的任何行似乎都没有进行计算，并重用与第 26 行相同的值。知道我做错了什么吗？随时提供有关如何使此代码更高效的反馈。我敢肯定这不是最有效的方法，但我正在学习。

CREATE OR REPLACE FUNCTION MACD()
    RETURNS TABLE(
        _ID INTEGER,
        _CUSIP TEXT,
        _DATETIME TIMESTAMP WITHOUT TIME ZONE,
        _PCLOSE NUMERIC,
        _ROWNUM BIGINT,
        _EMAL NUMERIC
    )
AS $$
DECLARE 
    sumlong numeric := 0.00;
    sumshort numeric := 0.00;
    last_ema26 numeric := 0.00;
BEGIN
    FOR _ID, _CUSIP, _DATETIME, _PCLOSE, _ROWNUM IN
    SELECT ID, CUSIP, datetime, pclose, k
        FROM
        (SELECT ID, CUSIP, datetime, (dhist::json->>'close')::numeric AS pclose, ROW_NUMBER () OVER(w) as K 
        FROM dailyhist
        WHERE cusip = '00130H105'
        WINDOW w AS (PARTITION BY CUSIP ORDER BY datetime)
        ) EMA
    LOOP
        CASE
            WHEN _ROWNUM < 26 THEN
                _EMAL := NULL;
                sumlong := sumlong + _PCLOSE;
                 RAISE NOTICE '1 counter is: %,  row: %, close: %  emal: %, lastema: %', sumlong, _ROWNUM, _PCLOSE, _EMAL, last_ema26;
            WHEN _ROWNUM = 26 THEN
                _EMAL:= (sumlong + _PCLOSE)/26;
                last_ema26 := _EMAL;
                RAISE NOTICE '2 counter is: %,  row: %, close: %  emal: %, lastema: %', sumlong, _ROWNUM, _PCLOSE, _EMAL, last_ema26;
            WHEN _ROWNUM > 26 THEN
                _EMAL = (_PCLOSE - last_ema26)*(2/27) + last_ema26;
                last_ema26 := _EMAL;
                RAISE NOTICE '3 counter is: %,  row: %, close: %  emal: %, lastema: %', sumlong, _ROWNUM, _PCLOSE, _EMAL, last_ema26;
        END CASE;
        RETURN NEXT;    
    END LOOP;
END;$$
LANGUAGE 'plpgsql';

SELECT * FROM MACD();

我还包括了一部分输出：

NOTICE:  1 counter is: 3.37517,  row: 1, close: 3.37517  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 6.79201,  row: 2, close: 3.41684  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 10.18801,  row: 3, close: 3.396  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 13.60485,  row: 4, close: 3.41684  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 17.06336,  row: 5, close: 3.45851  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 20.48020,  row: 6, close: 3.41684  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 23.77203,  row: 7, close: 3.29183  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 27.06386,  row: 8, close: 3.29183  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 30.60570,  row: 9, close: 3.54184  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 34.43922,  row: 10, close: 3.83352  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 38.33525,  row: 11, close: 3.89603  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 42.16877,  row: 12, close: 3.83352  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 46.00229,  row: 13, close: 3.83352  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 50.04416,  row: 14, close: 4.04187  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 54.29437,  row: 15, close: 4.25021  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 58.54458,  row: 16, close: 4.25021  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 62.58645,  row: 17, close: 4.04187  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 66.62832,  row: 18, close: 4.04187  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 70.48268,  row: 19, close: 3.85436  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 74.23287,  row: 20, close: 3.75019  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 77.94139,  row: 21, close: 3.70852  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 81.69158,  row: 22, close: 3.75019  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 85.48344,  row: 23, close: 3.79186  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 89.56698,  row: 24, close: 4.08354  emal: <NULL>, lastema: <NULL>
NOTICE:  1 counter is: 93.73385,  row: 25, close: 4.16687  emal: <NULL>, lastema: <NULL>
NOTICE:  2 counter is: 93.73385,  row: 26, close: 4.20854  emal: 3.7670150000000000, lastema: 3.7670150000000000
NOTICE:  3 counter is: 93.73385,  row: 27, close: 4.16687  emal: 3.7670150000000000, lastema: 3.7670150000000000
NOTICE:  3 counter is: 93.73385,  row: 28, close: 4.08354  emal: 3.7670150000000000, lastema: 3.7670150000000000
NOTICE:  3 counter is: 93.73385,  row: 29, close: 4.12521  emal: 3.7670150000000000, lastema: 3.7670150000000000
NOTICE:  3 counter is: 93.73385,  row: 30, close: 4.12521  emal: 3.7670150000000000, lastema: 3.7670150000000000
NOTICE:  3 counter is: 93.73385,  row: 31, close: 4.12521  emal: 3.7670150000000000, lastema: 3.7670150000000000
NOTICE:  3 counter is: 93.73385,  row: 32, close: 4.0627  emal: 3.7670150000000000, lastema: 3.7670150000000000

标签： postgresqlloopsdebuggingplpgsqlincrement

解决方案

我不确定问题出在哪里，但我在表达式中看到一个可能错误的点：

_EMAL = (_PCLOSE - last_ema26)*(2/27) + last_ema26;

Postgres 默认使用整数除法。可能2/27不会返回您期望的结果

postgres=# select 2/27;
┌──────────┐
│ ?column? │
╞══════════╡
│        0 │
└──────────┘
 (1 row)

如果你想计算某个比率，那么你不应该使用整数常量

postgres=# select 2.0/27.0;
┌────────────────────────┐
│        ?column?        │
╞════════════════════════╡
│ 0.07407407407407407407 │
└────────────────────────┘
(1 row)

推荐阅读