Sort by date, use IN to get latest and previous entry for multiple entries

Question

My goal: I have a list of stock_ids and want to get the last bids (sorted by date) one for each stock_id. For the picture this means I want: stock_id bid 3663.91953146.4428129.02798 One problem is that we have stocks like Gazprom that are suspended, so one of the last bids could be for example 2021-06-06. The quote_day=DATE(NOW()) does not work in this case. I also need to work with

P粉340980243 · Answer

Are you looking for the two latest quotes for each bid as of a given date? If so, you can simply modify the first query to allow row numbers 1 and 2:

select `quote_date`, 'stocks' as `type`, `bid`, `stock_id` as id 
from ( 
    select t.*, row_number() over(partition by stock_id order by quote_date desc) as rn f
    from end_day_quotes_AVG t 
    where quote_date <= DATE(?) AND stock_id in (?)  and currency_id = ? 
) x 
where rn <= 2  -- the latest two

P粉899950720 · Answer

To get the last bid (before a specific date) and the penultimate bid for each currency/stock in a single query, and effectively using indexes on currency_id, stock_id, quote_date, you can do this incrementally: First find the date with the maximum value for each currency/stock (willuse the index), then find the previous date (again, in the same way as using the index), then find the actual bid:

with stock_ids(stock_id) as (
    values (2),(23),(19),(41),(40),(26),(9),(43),
           (22),(44),(28),(32),(30),(34),(20),(10),
           (13),(17),(27),(35),(8),(29),(39),(16),
           (33),(5),(36589),(25),(18),(6),(38),(37),
           (3),(45),(7),(21),(46),(15),(4),(24),
           (31),(36),(38423),(40313),(22561),(36787),(35770),(36600),
           (35766),(42),(22567),(40581),(40569),(29528),(22896),(24760),
           (40369),(40396),(40400),(40374),(36799),(1),(27863),(29659),
           (40367),(27821),(24912),(36654),(21125),(22569),(22201),(23133),
           (40373),(36697),(36718),(26340),(36653),(47),(34019),(36847),
           (36694)
),
last_dates as (
    select t.currency_id, t.stock_id, max(t.quote_date) as quote_date
    from stock_ids
    join end_day_quotes_AVG t on
        t.currency_id=2 and
        t.stock_id=stock_ids.stock_id and
        t.quote_date <= '2023-01-31'
    group by t.currency_id,t.stock_id
),
next_to_last_dates as (
    select t.currency_id, t.stock_id, max(t.quote_date) as quote_date
    from last_dates l
    join end_day_quotes_AVG t on
        t.currency_id=l.currency_id and
        t.stock_id=l.stock_id and
        t.quote_date < l.quote_date
    group by t.currency_id,t.stock_id
)
select 'last' as 'when', currency_id, stock_id, quote_date, bid
from last_dates
join end_day_quotes_AVG using (currency_id, stock_id, quote_date)
union all
select 'next-to-last', currency_id, stock_id, quote_date, bid
from next_to_last_dates
join end_day_quotes_AVG using (currency_id, stock_id, quote_date)

If you want more than just the two most recent dates for each stock, you might be able to replace last_dates/next_to_last_dates with a recursive cte containing the number of days (limited to the number of days you want to collect).

violin