MySQL中基于日度数据计算周度价格指标的实现方案

针对单只股票（以AAPL为例）计算周度价格数据，可通过以下两种方式实现：

方法一：使用窗口函数（MySQL 8.0+ 适用） #

利用窗口函数对每个股票的日期排序，再基于排序序号定位目标区间计算指标：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY stock ORDER BY `Date` DESC) AS rn
    FROM dat
    WHERE stock = 'AAPL'
)
SELECT
    rd.`Date`,
    -- 当前日期往前第5个工作日的开盘价
    (SELECT Open FROM ranked_data WHERE stock = rd.stock AND rn = rd.rn + 4) AS wOpen,
    -- 该5个工作日内的最高价
    (SELECT MAX(High) FROM ranked_data WHERE stock = rd.stock AND rn BETWEEN rd.rn AND rd.rn + 4) AS wHigh,
    -- 该5个工作日内的最低价
    (SELECT MIN(Low) FROM ranked_data WHERE stock = rd.stock AND rn BETWEEN rd.rn AND rd.rn + 4) AS wLow,
    -- 当日收盘价直接复用原字段
    rd.Close AS wClose
FROM ranked_data rd
ORDER BY rd.`Date` DESC;

方法二：关联子查询（兼容旧版MySQL） #

通过子查询定位每个日期对应的最近5个工作日记录，计算所需指标：

SELECT
    d1.`Date`,
    -- 当前日期往前第5个工作日的开盘价：跳过最近4条（含当日）取第5条
    (SELECT d2.Open 
     FROM dat d2 
     WHERE d2.stock = d1.stock 
       AND d2.`Date` <= d1.`Date`
     ORDER BY d2.`Date` DESC 
     LIMIT 4, 1) AS wOpen,
    -- 最近5个工作日内的最高价
    (SELECT MAX(d2.High) 
     FROM dat d2 
     WHERE d2.stock = d1.stock 
       AND d2.`Date` <= d1.`Date`
     ORDER BY d2.`Date` DESC 
     LIMIT 5) AS wHigh,
    -- 最近5个工作日内的最低价
    (SELECT MIN(d2.Low) 
     FROM dat d2 
     WHERE d2.stock = d1.stock 
       AND d2.`Date` <= d1.`Date`
     ORDER BY d2.`Date` DESC 
     LIMIT 5) AS wLow,
    -- 当日收盘价直接复用原字段
    d1.Close AS wClose
FROM dat d1
WHERE d1.stock = 'AAPL'
ORDER BY d1.`Date` DESC;

补充说明 #

• 两种方法都会自动跳过非交易日（节假日），仅基于表中存在的工作日数据计算，符合实际交易场景。
• 若需批量处理所有股票，只需去掉语句中WHERE stock = 'AAPL'的筛选条件即可。

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