递归 CTE 表 - 计算 EWMA (EMA) - 如何优化/重构此代码，使其不会每次都被 TDWM 杀死？

Question

我创建了一个正在制作 CTE 表的查询，其中两个是非递归的，一个是递归的，以便计算指数加权移动平均线 (EMA(。 当我在 Teradata 中运行我的代码时，它会在一段时间后被 TDWM 杀死。

任何想法如何改进/解决这个问题？

WITH 
smooth AS (
SELECT CAST(0.741870935 AS NUMERIC (20,5)) AS alpha
),
numbered AS (
SELECT 
ROW_NUMBER() OVER (ORDER BY customer_name, closed_date) as rn,  
customer, 
closed_date, 
metric
FROM my_table
),
recursive EWMA AS (
SELECT rn, customer, closed_date, metric, CAST(metric AS NUMERIC(20,5)) as EWMA
FROM numbered
WHERE rn = 1
UNION ALL
SELECT numbered.rn, numbered.customer, numbered.closed_date, numbered.metric,
smooth.alpha * numbered.metric + (1-smooth.alpha) * EWMA.EWMA
FROM EWMA
JOIN numbered
ON EWMA.rn + 1 = numbered.rn
CROSS JOIN smooth   
)
SELECT * FROM EWMA
ORDER BY closed_date;

Answer 1

您是否尝试过设置depth字段来限制递归？ 像这样：

WITH smooth AS (...),
numbered AS (...),
recursive EWMA AS (
SELECT 
rn, customer, closed_date, metric, CAST(metric AS NUMERIC(20,5)) as EWMA, 
1 AS depth
FROM numbered
WHERE rn = 1
UNION ALL
SELECT 
numbered.rn, numbered.customer, numbered.closed_date, numbered.metric,
smooth.alpha * numbered.metric + (1-smooth.alpha) * EWMA.EWMA, 
EWMA.Depth + 1 AS Depth
FROM EWMA
INNER JOIN numbered ON EWMA.rn + 1 = numbered.rn
CROSS JOIN smooth   
WHERE depth <= 10 -- Restrict recursion
)
SELECT * 
FROM EWMA
ORDER BY closed_date;

假设my_table表非常大，则与numbered的递归联接可能会导致此问题。 理想情况下，您希望在 PI 列上进行直接相等连接 - 即table1.pi_col1 = table2.pi_col2. 不确定使用+1表达式将如何影响连接。

从高层次查看查询，似乎只想在当前行的计算中使用上一行的值。 如果是这种情况，那么您可以完全取消递归 CTE，只使用LAG()窗口函数：

WITH smooth AS (
SELECT CAST(0.741870935 AS NUMERIC (20,5)) AS alpha
)
SELECT 
ROW_NUMBER() OVER (ORDER BY customer_name, closed_date) as rn, -- row number
customer, 
closed_date, 
metric,
CAST(
COALESCE(
(smooth.alpha * metric + (1-smooth.alpha)) * -- current row's value
LAG((smooth.alpha * metric + (1-smooth.alpha))) OVER(
ORDER BY customer_name, closed_date) -- previous row's value
, metric -- handle first row (no previous "EWMA" value)
)
AS NUMERIC(20,5)) AS EWMA
FROM my_table;