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) for each stock_id a.

For images, this means I want:

stock_id	Bid
3	663.91953
1	46.44281
2	9.02798

One problem is that we have stocks like Gazprom that are suspended, so one of the last quotes could be, for example, 2021-06-06.

Taking a where >quote_day = DATE(NOW()) on

I also need the same date as the first lower date which is not in the first query, this can be done with the second query.

My current solution is using PHP. This works, but the performance isn't perfect, like 100 stocks takes 5 seconds.

I could use Redis, which also has the option to save the bids somewhere.

Current:

<code>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
  from end_day_quotes_AVG t
  where quote_date <= DATE({$date})
    AND stock_id in ({$val})
    and currency_id = {$c_id}
) x where rn = 1
</code>

The day before:

<code>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
  from end_day_quotes_AVG t
  where quote_date < DATE({$date})
    AND stock_id in ({$val})
    and currency_id = {$c_id}
) x where rn = 1
</code>

Stock_id, quote_date and currency_id are unique.

我想要使用服务器数据的表：10.9.4-MariaDB-1:10.9.4

编辑：

解释的查询：

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    220896  Using where
2   DERIVED     t   ALL     stock_id,quote_date     NULL    NULL    NULL    2173105     Using where; Using temporary

创建表：

CREATE TABLE `end_day_quotes_AVG` (
  `id` int(11) NOT NULL,
  `quote_date` date NOT NULL,
  `bid` decimal(15,5) NOT NULL,
  `stock_id` int(11) DEFAULT NULL,
  `etf_id` int(11) DEFAULT NULL,
  `crypto_id` int(11) DEFAULT NULL,
  `certificate_id` int(11) DEFAULT NULL,
  `currency_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `end_day_quotes_AVG` (`id`, `quote_date`, `bid`, `stock_id`, `etf_id`, `crypto_id`, `certificate_id`, `currency_id`) VALUES
(10537515, '2023-01-02', '16.48286', 40581, NULL, NULL, NULL, 2),
(10537514, '2023-01-02', '3.66786', 40569, NULL, NULL, NULL, 2),
(10537513, '2023-01-02', '9.38013', 40400, NULL, NULL, NULL, 2),
(10537512, '2023-01-02', '8.54444', 40396, NULL, NULL, NULL, 2),



ALTER TABLE `end_day_quotes_AVG`
  ADD PRIMARY KEY (`id`),
  ADD KEY `stock_id` (`stock_id`,`currency_id`),
  ADD KEY `etf_id` (`etf_id`,`currency_id`),
  ADD KEY `crypto_id` (`crypto_id`,`currency_id`),
  ADD KEY `certificate_id` (`certificate_id`,`currency_id`),
  ADD KEY `quote_date` (`quote_date`);


ALTER TABLE `end_day_quotes_AVG`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10570526;

生成的填充查询：

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 
from end_day_quotes_AVG t where quote_date <= DATE('2023-01-02') AND stock_id in (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) and currency_id = 2 ) x where rn = 1;

Answer 1

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

Answer 2

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