MySQL查询：日期超出范围时获取最近可用日期的实现需求

问题说明 #

原查询在指定的起始/结束日期超出stocks表的日期范围（示例为2021-01-01至2021-12-31）时会返回空结果，需修改为日期超出范围时自动替换为对应边界日期（或边界附近的最近可用日期），确保涨跌幅计算正常执行。

解决方案 #

以下是两种修改后的查询方案，可根据需求选择：

方案1：固定边界日期（硬编码指定范围）

-- 设置目标起始和结束日期
SET @target_start = '2020-09-15';
SET @target_end = '2022-10-13';

-- 调整日期：超出边界则替换为预设边界值
SET @adjusted_start = IF(@target_start < '2021-01-01', '2021-01-01', @target_start);
SET @adjusted_end = IF(@target_end > '2021-12-31', '2021-12-31', @target_end);

SELECT 
    B.ticker,
    B.date AS S_date,
    B.close AS S_close,
    E.date AS E_date,
    E.close AS E_close,
    ROUND((E.close - B.close)/B.close * 100, 2) AS perc
FROM 
    -- 获取每个ticker对应调整后起始日期的最近可用数据
    (SELECT s1.ticker, s1.date, s1.close
     FROM stocks s1
     WHERE s1.date = (
         SELECT MIN(s2.date)
         FROM stocks s2
         WHERE s2.ticker = s1.ticker
           AND s2.date >= @adjusted_start
     )) B
INNER JOIN 
    -- 获取每个ticker对应调整后结束日期的最近可用数据
    (SELECT s1.ticker, s1.date, s1.close
     FROM stocks s1
     WHERE s1.date = (
         SELECT MAX(s2.date)
         FROM stocks s2
         WHERE s2.ticker = s1.ticker
           AND s2.date <= @adjusted_end
     )) E ON B.ticker = E.ticker
LIMIT 10 OFFSET 0;

方案2：动态获取表的实际日期边界

无需硬编码范围，自动读取stocks表的最小/最大日期作为边界：

-- 设置目标起始和结束日期
SET @target_start = '2020-09-15';
SET @target_end = '2022-10-13';

-- 自动获取表的实际日期边界
SET @min_table_date = (SELECT MIN(date) FROM stocks);
SET @max_table_date = (SELECT MAX(date) FROM stocks);

-- 调整日期：超出表范围则替换为表的边界值
SET @adjusted_start = IF(@target_start < @min_table_date, @min_table_date, @target_start);
SET @adjusted_end = IF(@target_end > @max_table_date, @max_table_date, @target_end);

SELECT 
    B.ticker,
    B.date AS S_date,
    B.close AS S_close,
    E.date AS E_date,
    E.close AS E_close,
    ROUND((E.close - B.close)/B.close * 100, 2) AS perc
FROM 
    (SELECT s1.ticker, s1.date, s1.close
     FROM stocks s1
     WHERE s1.date = (
         SELECT MIN(s2.date)
         FROM stocks s2
         WHERE s2.ticker = s1.ticker
           AND s2.date >= @adjusted_start
     )) B
INNER JOIN 
    (SELECT s1.ticker, s1.date, s1.close
     FROM stocks s1
     WHERE s1.date = (
         SELECT MAX(s2.date)
         FROM stocks s2
         WHERE s2.ticker = s1.ticker
           AND s2.date <= @adjusted_end
     )) E ON B.ticker = E.ticker
LIMIT 10 OFFSET 0;

核心逻辑说明 #

• 日期自动调整：通过IF函数判断目标日期是否超出范围，超出则替换为对应边界值
• 最近数据匹配：子查询用MIN(date)和MAX(date)为每个ticker找到符合条件的最近有效日期，避免因指定日期无数据返回空结果
• 数据完整性保证：内连接确保只有同时拥有起始和结束有效数据的ticker会被输出，保证涨跌幅计算的有效性

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