SQL - 使用 CTE 或聚合计算指数移动平均线？

Question

EMA的一般公式:

EMA(xn) = α * xn + (1 - α) * EMA(xn-1)

Where:

xn = PRICEα  = 0.5 -- Given 3-day SMA

The following recursive CTE does the job:

WITH recursive
ewma_3 (DATE, PRICE, EMA_3, rn)
AS (

    -- Anchor
    -- Feed SMA_3 to recursive CTE
    SELECT rows."DATE", rows."PRICE", sma.sma AS ewma, rows.rn
    FROM (
        SELECT "DATE", "PRICE", ROW_NUMBER() OVER(ORDER BY "DATE") rn
        FROM PRICE_TBL
    ) rows
    JOIN (
        SELECT "DATE",
           ROUND(AVG("PRICE"::numeric)
              OVER(ORDER BY "DATE" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 6) AS sma
        FROM PRICE_TBL
    ) sma ON sma."DATE" = rows."DATE"
    WHERE rows.rn = 3

    UNION ALL

    -- Recursive Member
    SELECT rows."DATE", rows."PRICE"
    -- Calculate EMA_3 below
    ,ROUND(((rows."PRICE"::numeric * 0.5) +
            (ewma.EMA_3 * (1 - 0.5))), 6) AS ewma
    , rows.rn
    FROM ewma_3 ewma
    JOIN (
        SELECT "DATE", "PRICE", ROW_NUMBER() OVER(ORDER BY "DATE") rn
        FROM PRICE_TBL
    ) rows ON ewma.rn + 1 = rows.rn
    WHERE rows.rn > 3
)

SELECT ewma_3.rn AS "ID", DATE, PRICE, EMA_3
FROM ewma_3
;

这更像是一个效率和迅速的问题。 9852 行的样本集需要 11 s 631 ms去完成。

我读过聚合器会保留最后一个计算元素的结果，如果是这样:

有人可以提供一个使用聚合函数的工作示例吗？

我也愿意接受任何改进 CTE 的建议，但是，我以某种方式相信 aggregates会更快。我也知道这是一个较旧的话题，但我对 posgres 有点陌生。所以任何帮助都受到高度赞赏。谢谢!

更新

样本数据:



我的 7 天 CTE 返回(不包括 DATE):

ID  PRICE       EMA_7
--+----------+-----------
7   0.529018    0.4888393
8   0.551339    0.5044642
9   0.580357    0.5234374
10  0.633929    0.5510603
11  0.642857    0.5740095
12  0.627232    0.5873151

尽管@GordonLinoff 提供的递归CTE 快了一秒，但 聚合器(聚合函数)将是最佳的速度。我试过 this但得到:

ERROR: function ema(numeric, numeric) does not exist

显然，没有函数匹配给定的名称和参数类型。显式类型转换？无知

Answer 1

我将递归 CTE 写为:

with recursive p as (
      select p.*, row_number() over (order by date) as seqnum
      from price_tbl p
     ),
     cte as (
      select seqnum, date, price, price * 1.0 as exp_avg
      from p
      where seqnum = 1
      union all
      select p.seqnum, p.date, p.price, (cte.exp_avg * 0.5  + p.price * 0.5) 
      from cte join
           p
           on p.seqnum = cte.seqnum + 1
     )
select *
from cte;

0.5真的是 0.5和 1 - 0.5 .您可以针对不同的 alpha 轻松调整它。

你也可以使用窗口函数来做到这一点:

select p.*,
       (sum(power((1 / 0.5), seqnum) * price) over (order by seqnum) +
        first_value(price) over (order by seqnum)
       ) / power((1 / 0.5), seqnum + 1)
from (select p.*,
             row_number() over (order by date) - 1 as seqnum
      from price_tbl p
     ) p;

first_value()是因为计算的怪癖。第一个和第二个值实际上计算的数量相同，因此需要“重新添加”第一个数量。

也就是说，如果您的序列甚至有几十行长，这很容易出现溢出和被零除错误。

Here是一个db<> fiddle 。