计算EMA Postgresql

Question

简介-

我想为一套收盘价计算EMA。

使用的公式是

((closing-price - previous EMA) * smoothing factor) + previous EMA

查询-

SELECT 
symbol,
close_price,
date,
CASE 
WHEN 
    ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY date ASC) < 5 
THEN 
    0 
ELSE 
    CASE 
    WHEN 
        ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY date ASC) = 5 
    THEN 
        round(
            (
                AVG(close_price) 
                OVER(PARTITION BY symbol ORDER BY date ASC rows between 5 preceding and 0 following)
            )::numeric,
            3
        ) 
    ELSE 
        round(
        (
            (
                (close_price - LAG(ema,1) OVER(PARTITION BY symbol ORDER BY date ASC)) * 0.181818
            ) + LAG(ema,1) OVER(PARTITION BY symbol)
        )::numeric,
        3) 
    END 
END 
AS ema 
FROM price_data

解释-

EMA为5个句点，因此前4行设置为0。第五层是5排的SMA。之后，我想从上一行得到EMA，并进一步计算EMA。这涉及到从上一行获取数据，而我在那里遇到了问题。

ERROR:  column "ema" does not exist

数据集-

                  id                  |   symbol   |           date            | close_price
--------------------------------------+------------+---------------------------+-------------
 692c4614-13ed-49ab-b701-3ba0e1b2cd78 | 1018GS2026 | 2019-10-31 00:00:00+05:30 |      112.46
 adbba627-a8d5-4b9b-83f7-bd44e6cc790c | 1018GS2026 | 2022-02-11 00:00:00+05:30 |         101
 2ae1ff84-01b3-4924-9563-16d919774c93 | 1018GS2026 | 2022-01-18 00:00:00+05:30 |      100.02
 b11df1dd-ae06-4265-843c-1aedb4d809dc | 1018GS2026 | 2022-01-14 00:00:00+05:30 |          97
 73ff0624-170b-4a4a-a304-09df36432165 | 1018GS2026 | 2022-01-03 00:00:00+05:30 |       95.05
 59ca7e36-8d4b-4f94-ba1b-e0e193d8c194 | 1018GS2026 | 2021-11-22 00:00:00+05:30 |         100
 9431c3fd-2a0f-4f39-bbf2-14f55022fd10 | 1018GS2026 | 2021-12-06 00:00:00+05:30 |         100
 320f452c-e168-4900-9504-16a7d3923e2b | 1018GS2026 | 2021-11-18 00:00:00+05:30 |         100
 205fd2f5-25e4-46a9-a451-56eba580d573 | 1018GS2026 | 2021-10-28 00:00:00+05:30 |          96
 f0e7ca13-e27e-4e66-af6e-2beff5888b0d | 1018GS2026 | 2021-10-21 00:00:00+05:30 |         101
 5ffe2a37-18ee-44db-b3ec-7b882efc801c | 1018GS2026 | 2021-10-18 00:00:00+05:30 |       96.95
 a0a26d06-17ab-45e8-8a0b-4ffc4523cde9 | 1018GS2026 | 2021-09-30 00:00:00+05:30 |         102
 3f4b65a1-1969-46e2-b4a4-3b34e46893c0 | 1018GS2026 | 2021-09-24 00:00:00+05:30 |         102
 398aa57b-91a1-48e7-aed1-8bbb7e8d5211 | 1018GS2026 | 2021-09-22 00:00:00+05:30 |         100
 6a235ca2-ada3-4518-b4d2-ec5331e4ed2b | 1018GS2026 | 2021-09-21 00:00:00+05:30 |      100.05
 18220669-868e-4c61-8754-55ab72e0b469 | 1018GS2026 | 2021-09-20 00:00:00+05:30 |         100
 003a962f-41ba-492e-a45c-a2b556c6d81b | 1018GS2026 | 2021-09-14 00:00:00+05:30 |       97.05
 1024a124-bf44-4e26-af2f-fc0bfbd3d985 | 1018GS2026 | 2021-09-13 00:00:00+05:30 |       96.95
 e4e790a2-e74f-48b6-9457-1484e0cc80d6 | 1018GS2026 | 2021-09-08 00:00:00+05:30 |         102
 b57c35d0-876c-4f05-a5f4-0cf0114e44ce | 1018GS2026 | 2021-08-03 00:00:00+05:30 |      101.81

问题-

我无法从的ema列中获取数据。

Answer 1

不一定能很好地理解您的预期结果，但是下面是一个返回的解决方案：

• mea = round(avg(close_price) OVER (PARTITION BY symbol ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 3)，用于由symbol分区并由date ASC排序的第5行
• mea = round(((close_price - previous_mea) * 0.181818 + previous_mea)::numeric, 3)用于下一个由symbol分区并由date ASC排序的行，其中previous_mea是根据分区中的date ASC顺序确定的上一行的mea值。

该方法依赖于聚合函数mea_agg迭代计算mea值。这个聚合函数需要两个输入/输出参数，即类型数值的mea值和symbol分区内行的rank，因此我们首先创建复合类型num_bigint：

CREATE TYPE num_bigint AS (mea numeric, rank bigint) ;

然后将聚合函数mea_agg定义为：

CREATE OR REPLACE FUNCTION mea (x num_bigint, close_price numeric)
RETURNS num_bigint LANGUAGE sql IMMUTABLE AS
$$
  SELECT
    CASE
      WHEN x IS NULL
      THEN row(close_price, 1) :: num_bigint
      WHEN 
           (x).rank < 5 
      THEN 
          row( round( ((x).mea * (x).rank + close_price) / ((x).rank + 1)
                    , 3
                    ) :: numeric
             , (x).rank + 1
             ) :: num_bigint
      ELSE 
          row( round( (close_price - (x).mea) * 0.181818 + (x).mea
                    , 3
                    ) :: numeric
             , (x).rank + 1
             ) :: num_bigint
    END ;
$$ ;

CREATE AGGREGATE mea_agg(numeric)
( sfunc = mea
, stype = num_bigint
) ;

以下查询提供了最终结果：

SELECT *
     , (mea_agg(close_price) OVER (PARTITION BY symbol ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)).*
  FROM price_data
 ORDER BY date ASC

[医]小提琴中的全部测试结果