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;

。现在，要将本周数据与上周的数据进行比较，我创建了第二个查询：

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，一个星期根据Aboves查询，将是2022-06-03-，但没有数据集。

我想以某种方式修改第二查询，如果丢失了间隔日期，则需要最后一个可用的日期（在Aboves示例中是2022-06-01）。 我不知道在上述查询中将一个'＆lt; ='关系放在哪里。如果日期字段不可取，也许是通过daynumbers，那是整数？任何提示都将不胜感激！

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;

像魅力一样工作。