MySQL：获取给定日期的数据集 - 如果不存在则在此之前的最后一个现有数据集

Question

我的表格结构如下：

CREATE TABLE `market_trend_record` (
`date` date NOT NULL,
`symbol` CHAR(40),
`identifier` CHAR(20),
`trend` CHAR(9),
`duration` int,
`daynr` int,
`price_quote` decimal(16,6),
PRIMARY KEY (`date` , `symbol` , `identifier`));

要获取每个符号的最新条目（由于国家假日，它们不一定是同一日期），我这样做：

SELECT market_trend_record.symbol, market_trend_record.date, market_trend_record.trend, 
market_trend_record.duration, market_trend_record.price_quote 
    FROM (select symbol, MAX(date) 
       AS date FROM market_trend_record GROUP BY symbol) AS latest_record INNER JOIN 
       market_trend_record ON market_trend_record.symbol = latest_record.symbol AND 
       market_trend_record.date = latest_record.date;

效果非常好，我每周五都通过 python 脚本运行它。现在为了将本周的数据与上周的数据进行比较，我创建了第二个查询：

select market_trend_record.symbol, market_trend_record.date, market_trend_record.trend, 
market_trend_record.duration, market_trend_record.price_quote FROM (select symbol, MAX(date) - 
INTERVAL 7 DAY AS date 
    FROM market_trend_record GROUP BY symbol) AS latest_record INNER JOIN market_trend_record 
    ON market_trend_record.symbol = latest_record.symbol AND market_trend_record.date = 
    latest_record.date;

我们的想法是使用最后一个可用日期并减去 7 天来得到上周的星期五。这也很有效......直到它不起作用。对于某些交易品种，上周五的数据不存在，因此这些数据集被跳过。例如缺少符号的数据之一：

| 2022-05-31 | FTSE 100 | FTSE       | uptrend   |        7 | 44711 | 7607.660156 |
| 2022-06-01 | FTSE 100 | FTSE       | uptrend   |        8 | 44712 | 7532.950195 |
| 2022-06-06 | FTSE 100 | FTSE       | uptrend   |        9 | 44717 | 7608.220215 |
| 2022-06-07 | FTSE 100 | FTSE       | uptrend   |       10 | 44718 | 7598.930176 |
| 2022-06-08 | FTSE 100 | FTSE       | uptrend   |       11 | 44719 | 7593.000000 |
| 2022-06-09 | FTSE 100 | FTSE       | sideways  |        1 | 44720 | 7476.209961 |
| 2022-06-10 | FTSE 100 | FTSE       | sideways  |        2 | 44721 | 7317.520020 |

最后日期是 2022-06-10，根据上面的查询，提前一周是 2022-06-03，但没有数据集。

我想以某种方式修改第二个查询，如果间隔日期丢失（在上面的示例中为 2022-06-01），它将采用最后一个可用日期。 我不知道在上面的查询中将“<=”关系放在哪里。如果日期字段无法实现，也许可以通过日期数字，因为它们是整数？任何提示将不胜感激！

Answer 1

可能的解决方案：

SELECT MTR.symbol, MTR.date, MTR.trend, MTR.duration, MTR.price_quote
FROM (
    SELECT DT.*,
           RANK() OVER (PARTITION BY DT.symbol ORDER BY DT.date Desc) record_number
    FROM market_trend_record DT
    INNER JOIN (
        SELECT symbol,
        MAX(date) - INTERVAL 7 DAY AS date
        FROM market_trend_record
        GROUP BY symbol
    ) AS DT2 ON DT.symbol = DT2.symbol AND DT.date <= DT2.date
) AS MTR
WHERE MTR.record_number = 1;

就像魅力一样。