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;

就像魅力一樣。