MySQL用窗口函数统计一段期间股票的涨幅

要使用窗口函数统计一段期间股票的涨幅，我们可以使用LEAD()或LAG()函数来获取股票在特定时间点前后的价格，然后计算涨幅。涨幅通常定义为当前价格与之前某个时间点价格的差值，除以之前的价格。

假设我们有一个名为stock_prices的表，其中包含以下列：

• date：日期
• stock_symbol：股票代码
• price：股票价格

1.前一天

我们想要计算每只股票从前一天到当前日期的涨幅。以下是如何使用LAG()窗口函数来实现这一点的SQL示例：

SELECT
    stock_symbol,
    date,
    price,
    LAG(price) OVER (PARTITION BY stock_symbol ORDER BY date) AS previous_day_price,
    (price - LAG(price) OVER (PARTITION BY stock_symbol ORDER BY date)) / 
    LAG(price) OVER (PARTITION BY stock_symbol ORDER BY date) AS percentage_change
FROM
    stock_prices;

在这个查询中：

• PARTITION BY stock_symbol：表示我们对每只股票分别进行计算。
• ORDER BY date：表示我们按照日期对数据进行排序。
• LAG(price) OVER (...)：获取当前行的前一天的价格。
• price - LAG(price) OVER (...)：计算当前价格与前一天价格的差值。
• (price - LAG(price) OVER (...)) / LAG(price) OVER (...)：计算涨幅百分比。

请注意，这个查询会为每行返回前一天的价格和涨幅百分比。对于第一行（即每只股票的第一个记录），LAG()函数将返回NULL，因为没有前一天的价格。

2.特定期间

如果你想要计算从特定开始日期到结束日期的总涨幅，你可以先计算出开始和结束日期的价格，然后计算涨幅。例如：

SELECT
    stock_symbol,
    start_price,
    end_price,
    (end_price - start_price) / start_price AS percentage_change
FROM (
    SELECT
        stock_symbol,
        MIN(price) OVER (PARTITION BY stock_symbol) AS start_price,
        MAX(price) OVER (PARTITION BY stock_symbol) AS end_price
    FROM
        stock_prices
    WHERE
        date BETWEEN '2024-01-01' AND '2024-12-31'
) AS subquery;

在这个查询中：

• 我们首先筛选出特定日期范围内的股票价格。
• 使用MIN(price) OVER (...)和MAX(price) OVER (...)分别计算每只股票在这段时间内的最低和最高价格。
• 然后在外层查询中计算涨幅百分比。

3.滑动窗口

使用滑动窗口实现15天和30天的涨幅，我们可以使用SUM()和AVG()窗口函数来计算指定期间内的价格总和和平均值，然后计算涨幅。以下是如何使用这些函数的SQL示例：

SELECT
    stock_symbol,
    date,
    price,
    -- 计算15天涨幅
    (price - SUM(price) OVER (PARTITION BY stock_symbol ORDER BY date ROWS BETWEEN 14 PRECEDING AND CURRENT ROW)) / 
    SUM(price) OVER (PARTITION BY stock_symbol ORDER BY date ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) AS percentage_change_15d,
    -- 计算30天涨幅
    (price - SUM(price) OVER (PARTITION BY stock_symbol ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)) / 
    SUM(price) OVER (PARTITION BY stock_symbol ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS percentage_change_30d
FROM
    stock_prices;

在这个查询中：

• PARTITION BY stock_symbol：表示我们对每只股票分别进行计算。
• ORDER BY date：表示我们按照日期对数据进行排序。
• ROWS BETWEEN 14 PRECEDING AND CURRENT ROW：定义了一个滑动窗口，包括当前行和之前的14行（对于15天涨幅）或29行（对于30天涨幅）。
• SUM(price) OVER (...)：计算指定窗口内的价格总和。
• price - SUM(price) OVER (...)：计算当前价格与窗口内价格总和的差值。
• (price - SUM(price) OVER (...)) / SUM(price) OVER (...)：计算涨幅百分比。

请注意，这个查询会为每行返回15天和30天的涨幅百分比。对于每个股票的前14行和前29行，SUM()函数将只计算到可用的数据点，因此涨幅百分比可能不准确。