MySQL: Ruft den Datensatz für ein bestimmtes Datum ab – den letzten vorhandenen Datensatz davor, falls noch keiner vorhanden ist

Question

Mein Tabellenaufbau ist wie folgt:

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`));

Um den neuesten Eintrag für jedes Symbol zu erhalten (aufgrund nationaler Feiertage handelt es sich nicht unbedingt um dasselbe Datum), mache ich Folgendes:

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;

Es funktioniert so gut, dass ich es jeden Freitag über ein Python-Skript laufen lasse. Um nun die Daten dieser Woche mit den Daten der letzten Woche zu vergleichen, habe ich eine zweite Abfrage erstellt:

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;

Die Idee besteht darin, das letzte verfügbare Datum zu verwenden und 7 Tage abzuziehen, um den Freitag der letzten Woche zu erhalten. Das funktioniert auch gut ... bis es nicht mehr funktioniert. Für einige Symbole sind keine Daten für den letzten Freitag vorhanden, daher werden diese Datensätze übersprungen. Zum Beispiel eines der fehlenden Datensymbole:

| 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 |

Das letzte Datum ist der 10.06.2022, und laut der obigen Abfrage ist das eine Woche frühere der 03.06.2022, es gibt jedoch keinen Datensatz.

Ich möchte die zweite Abfrage so ändern, dass bei fehlendem Intervalldatum (im Beispiel oben 01.06.2022) das letzte verfügbare Datum verwendet wird. Ich weiß nicht, wo ich die Beziehung „<=“ in der obigen Abfrage einfügen soll. Wenn das bei Datumsfeldern nicht möglich ist, übergeben Sie vielleicht Datumszahlen, da es sich um Ganzzahlen handelt? Für jeden Tipp wäre ich sehr dankbar!

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;

就像魅力一样。