MySQL : récupère l'ensemble de données pour une date donnée - le dernier ensemble de données existant avant celle-ci s'il n'en existe pas

Question

La structure de ma table est la suivante :

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

Pour avoir la dernière entrée pour chaque symbole (ce ne sont pas forcément la même date en raison des jours fériés), je fais ceci :

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;

Cela fonctionne si bien que je l'exécute via un script python tous les vendredis. Maintenant, pour comparer les données de cette semaine avec celles de la semaine dernière, j'ai créé une deuxième requête :

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;

L'idée est d'utiliser la dernière date disponible et de soustraire 7 jours pour obtenir le vendredi de la semaine dernière. Cela fonctionne également bien... jusqu'à ce que ce ne soit plus le cas. Pour certains symboles, les données du vendredi dernier n'existent pas, ces ensembles de données sont donc ignorés. Par exemple l'un des symboles manquants dans les données :

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

La dernière date est le 10/06/2022, et selon la requête ci-dessus, la semaine précédente est le 03/06/2022, mais il n'y a pas d'ensemble de données.

Je souhaite modifier la deuxième requête de telle sorte que si la date d'intervalle est manquante (01/06/2022 dans l'exemple ci-dessus), elle prendra la dernière date disponible. Je ne sais pas où placer la relation "<=" dans la requête ci-dessus. Si ce n'est pas possible avec les champs de date, peut-être transmettre les numéros de date puisqu'ils sont des nombres entiers ? Tous les conseils seraient grandement appréciés !

Answer 1

Solutions possibles :

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;

Fonctionne à merveille.