MySQL: get data set at given date - if it doesn't exist the last exisiting data set before that-CodePudding

My table is structured as followed:

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

To get the latest entries for every symbol (due to national holidays they are not neccessarily the same date) I do:

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;

That works very well, I run that every Friday through a python script. Now to compare this weeks data with last week´s data, I created a second query:

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;

The idea was to to use the last available date and substract 7 days to get last weeks Friday. That also worked well...until it didn't. For some symbols last Fridays data does not exist and so these data sets are skipped. For example one of the missing symbol´s data:

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

Last Date is 2022-06-10, one week before according to aboves query would be 2022-06-03- but there is no data set.

I´d like to modify the 2nd query in a way, that it would take the last available date, if the intervall date is missing (in aboves example it would be 2022-06-01). I have no idea where to put a '<=' relation in above´s query. If it is not doable with the date fields, maybe via the daynumbers, as those are integers? Any hint would be very appreciated!

CodePudding user response：

A possible solution:

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;

Works like a charm.