如何在SQL Server 2019中基于多只股票日价表生成自定义股票指数

本方案适配SQL Server 2019版本，符合你提出的计算规则，具备以下特性：

• 支持自定义计算起始日期、初始指数基准值
• 可轻松扩展到3只及以上股票合并计算
• 自动过滤缺漏个股交易数据的交易日，确保计算准确性

完整实现代码 #

-- 1. 自定义参数配置，可根据实际需求修改
DECLARE 
    @StartDate DATE = '2021-08-19', -- 指数计算起始日期
    @InitIndex DECIMAL(18,4) = 100, -- 起始日初始指数基准值
    @StockCount INT = 2; -- 参与计算的股票总数量

-- 2. 合并所有参与计算的股票日涨跌幅数据
-- 扩展多只股票时，只需在这里新增UNION ALL对应的股票表即可
WITH AllStockData AS (
    SELECT Date, DailyChange FROM dbo.StockA_DailyPrice
    UNION ALL
    SELECT Date, DailyChange FROM dbo.StockB_DailyPrice
    -- 新增第三只股票示例：UNION ALL SELECT Date, DailyChange FROM dbo.StockC_DailyPrice
),
-- 3. 按日期计算每日平均涨跌幅，过滤掉缺股票数据的交易日
DailyAvgChange AS (
    SELECT 
        Date,
        AVG(DailyChange) AS AvgChange,
        COUNT(1) AS DailyStockCnt
    FROM AllStockData
    WHERE Date >= @StartDate
    GROUP BY Date
    HAVING COUNT(1) = @StockCount -- 确保当日所有参与股票都有交易数据
),
-- 4. 给日期排序，为递归计算指数做准备
OrderedDates AS (
    SELECT 
        Date,
        AvgChange,
        ROW_NUMBER() OVER (ORDER BY Date ASC) AS RowNum
    FROM DailyAvgChange
),
-- 5. 递归计算每日指数
IndexCalculation AS (
    -- 锚点成员：计算起始日指数
    SELECT 
        Date,
        AvgChange,
        RowNum,
        CAST(@InitIndex * (1 + AvgChange) AS DECIMAL(18,4)) AS CustomIndex
    FROM OrderedDates
    WHERE RowNum = 1
    UNION ALL
    -- 递归成员：用前一日指数计算当日指数
    SELECT 
        curr.Date,
        curr.AvgChange,
        curr.RowNum,
        CAST(prev.CustomIndex * (1 + curr.AvgChange) AS DECIMAL(18,4)) AS CustomIndex
    FROM IndexCalculation prev
    INNER JOIN OrderedDates curr ON curr.RowNum = prev.RowNum + 1
)
-- 6. 结果输出，可改为INTO直接存入新表
SELECT Date, CustomIndex 
-- INTO dbo.CustomStockIndex -- 取消注释即可将结果存入新表
FROM IndexCalculation
ORDER BY Date DESC;

结果验证 #

使用你提供的样例数据，参数设置为起始日期2021-08-19、初始值100、2只股票时，输出结果和你给出的示例完全匹配：

扩展说明 #

• 新增个股只需在AllStockData的CTE里新增UNION ALL对应股票表的查询即可，同时修改@StockCount参数为总股票数
• 如果要调整指数精度，修改代码中DECIMAL(18,4)的位数即可
• 取消代码中INTO dbo.CustomStockIndex的注释，即可将计算结果直接持久化到新表

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