MS SQL Server 2014递归计算列需求：基于历史值生成Coinh1d_EMA12

嘿，我来帮你搞定这个需求！首先得明确EMA12的计算逻辑，和Excel里的递归规则完全一致：

• 第一个Coinh1d_EMA12值直接等于对应的Coinh1d_Close
• 后续每个值的公式是：当前Close * (2/(12+1)) + 前一个EMA值 * (1 - 2/(12+1))，也就是EMA = Close * α + 前序EMA * (1-α)，其中α=2/13≈0.1538

不过要注意：SQL Server的普通计算列不能直接引用自身，所以我们得用替代方案，下面给你三种常用的实现方式，按需选择：

方案一：用递归CTE生成EMA（适合一次性查询或创建视图） #

这个方案通过递归CTE逐行计算EMA，逻辑最直观，和Excel的递归过程完全一致。前提是你的表有一个可以确定顺序的列（比如时间戳、自增ID），否则计算顺序会混乱。

-- 替换YourTableName和RecordTime为你的实际表名和排序列
WITH RankedData AS (
    SELECT 
        Coinh1d_Close,
        RecordTime,
        ROW_NUMBER() OVER (ORDER BY RecordTime) AS RowNum
    FROM YourTableName
),
RecursiveEMA AS (
    -- 初始化第一行的EMA值
    SELECT 
        RowNum,
        Coinh1d_Close,
        CAST(Coinh1d_Close AS FLOAT) AS Coinh1d_EMA12
    FROM RankedData
    WHERE RowNum = 1

    UNION ALL

    -- 递归计算后续行的EMA
    SELECT 
        rd.RowNum,
        rd.Coinh1d_Close,
        CAST(rd.Coinh1d_Close * (2.0/13) + re.Coinh1d_EMA12 * (11.0/13) AS FLOAT) AS Coinh1d_EMA12
    FROM RankedData rd
    JOIN RecursiveEMA re ON rd.RowNum = re.RowNum + 1
)
-- 关联原始数据和EMA结果
SELECT 
    rd.RecordTime,
    rd.Coinh1d_Close,
    re.Coinh1d_EMA12
FROM RankedData rd
JOIN RecursiveEMA re ON rd.RowNum = re.RowNum
ORDER BY rd.RecordTime;

如果需要长期使用，可以把这个查询封装成视图，方便随时调用。

方案二：用标量函数+计算列（适合需要直接在表中看到EMA列的场景） #

如果你希望EMA列直接作为表的一部分，可以创建一个递归标量函数，再基于函数创建计算列。注意：这个方案在数据量大时性能较差，因为每一行都要递归调用函数，建议小数据量场景使用。

步骤1：创建递归计算EMA的标量函数 #

假设你的表有自增ID列ID（如果没有，建议先添加一个自增主键）：

CREATE FUNCTION dbo.CalculateEMA12(@ID INT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @EMA FLOAT;
    DECLARE @CurrentClose FLOAT;
    DECLARE @PrevID INT = @ID - 1;

    -- 获取当前行的Close值
    SELECT @CurrentClose = Coinh1d_Close
    FROM YourTableName
    WHERE ID = @ID;

    -- 第一行EMA等于Close，后续递归计算
    IF @ID = 1
        SET @EMA = @CurrentClose;
    ELSE
        SET @EMA = @CurrentClose * (2.0/13) + dbo.CalculateEMA12(@PrevID) * (11.0/13);

    RETURN @EMA;
END;

步骤2：添加计算列到表中 #

ALTER TABLE YourTableName
ADD Coinh1d_EMA12 AS dbo.CalculateEMA12(ID);

这个计算列是非持久化的，每次查询都会重新计算，所以数据量大时会明显拖慢查询速度。

方案三：用窗口函数实现（性能最优，推荐大数据量场景） #

利用EMA的数学展开式，我们可以用窗口函数的累积求和来实现，避免递归，性能大幅提升。公式展开后，EMA可以表示为：
EMA_t = α * Σ( (1-α)^(t-i) * Close_i )（i从1到t）

具体SQL如下：

-- 替换YourTableName和RecordTime为你的实际表名和排序列
WITH DataWithRowNum AS (
    SELECT 
        Coinh1d_Close,
        RecordTime,
        ROW_NUMBER() OVER (ORDER BY RecordTime) AS RowNum
    FROM YourTableName
),
WeightedData AS (
    SELECT 
        Coinh1d_Close,
        RecordTime,
        RowNum,
        -- 计算每个Close值的权重：(1-α)^(当前行号-1)，α=2/13，所以1-α=11/13
        POWER(11.0/13, RowNum - 1) AS Weight
    FROM DataWithRowNum
),
CumulativeWeightedSum AS (
    SELECT 
        RecordTime,
        Coinh1d_Close,
        -- 计算从第一行到当前行的加权累积和
        SUM(Coinh1d_Close * Weight) OVER (ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSum
    FROM WeightedData
)
-- 最终计算EMA值
SELECT 
    RecordTime,
    Coinh1d_Close,
    (2.0/13) * CumulativeSum AS Coinh1d_EMA12
FROM CumulativeWeightedSum
ORDER BY RecordTime;

这个方案不需要递归，全用窗口函数处理，性能最好，适合大数据量的数据分析场景。

重要提醒 #

• 无论用哪种方案，必须有明确的排序列（比如时间、自增ID），EMA是时序依赖的计算，没有固定顺序的话结果完全错误。
• 如果表中的数据会频繁更新，方案二的计算列会自动同步，但性能开销大；方案一和三可以通过视图或定时刷新的方式维护。

内容的提问来源于stack exchange，提问作者Konstantin