递归CTE表-计算EWMA (EMA) -如何优化/重构这段代码，这样它就不会每次都被TDWM杀死？
递归CTE表-计算EWMA (EMA) -如何优化/重构这段代码，这样它就不会每次都被TDWM杀死？
提问于 2020-06-16 12:38:52
我创建了一个生成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;回答 1
Stack Overflow用户
回答已采纳
发布于 2020-06-16 13:10:06
您是否尝试过设置一个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;假设numbered表非常大，使用my_table的递归连接可能会导致问题。理想情况下，您希望在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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62408704
复制相关文章
相似问题
腾讯云开发者
