按日期排序，使用IN获取多个条目的最新和上一个条目

Question

<p>我的目标：我有一个 <code>stock_id</code>s 列表，并且想要获取最后一个 <code>bid</code>s （按日期排序）每个 <code>stock_id</code> 一个。</p> <p>对于图片来说，这意味着我想要：</p> <table class="s-table"> <thead> <tr> <th>stock_id</th> <th>出价</th> </tr> </thead> <tbody> <tr> <td>3</td> <td>663.91953</td> </tr> <tr> <td>1</td> <td>46.44281</td> </tr> <tr> <td>2</td> <td>9.02798</td> </tr> </tbody> </table> <p>一个问题是我们有像俄罗斯天然气工业股份公司这样的股票已停牌，因此最后的报价之一可能是例如 2021-06-06。</p> <p>在 <code 上取一个 where >quote_day = DATE(NOW())</code> 在这种情况下不起作用。</p> <p>我还需要与第一个较低日期相同的日期，该日期不在第一个查询中，这可以通过第二个查询来完成。</p> <p>我当前使用 PHP 的解决方案。这是有效的，但性能并不完美，就像 100 只股票需要 5 秒一样。</p> <p>我可以使用 Redis，它也可以选择将出价保存在某处。</p> <p>当前：</p> <pre class="lang-sql prettyprint-override"><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></pre> <p>前一天：</p> <pre class="lang-sql prettyprint-override"><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></pre> <p><code>Stock_id</code>、<code>quote_date</code> 和 <code>currency_id</code> 是唯一的。</p> <p>我想要使用服务器数据的表：10.9.4-MariaDB-1:10.9.4</p> <p>编辑：</p> <p>解释的查询：</p>

id select_type 表类型 possible_keys key key_len ref rows Extra   
1 PRIMARY <派生2>  ALL NULL NULL NULL NULL 220896 使用其中
2 DERIVED t ALL stock_id,quote_date NULL NULL NULL 2173105 使用 where;使用临时的</pre>
<p>创建表：</p>
 创建表 `end_day_quotes_AVG` (
  `id` int(11) NOT NULL,
  `quote_date` 日期不为空，
  `bid` 小数(15,5) NOT NULL,
  `stock_id` int(11) 默认为 NULL，
  `etf_id` int(11) 默认为 NULL，
  `crypto_id` int(11) 默认为 NULL，
  `certificate_id` int(11) 默认为 NULL，
  `currency_id` int(11) NOT NULL
) 引擎=InnoDB 默认字符集=utf8mb4 COLLATE=utf8mb4_general_ci;

插入 `end_day_quotes_AVG` (`id`、`quote_date`、`bid`、`stock_id`、`etf_id`、`crypto_id`、`certificate_id`、`currency_id`) 值
(10537515, '2023-01-02', '16.48286', 40581, 空, 空, 空, 2),
(10537514, '2023-01-02', '3.66786', 40569, 空, 空, 空, 2),
(10537513, '2023-01-02', '9.38013', 40400, 空, 空, 空, 2),
(10537512, '2023-01-02', '8.54444', 40396, 空, 空, 空, 2),



更改表`end_day_quotes_AVG`
  添加主键（`id`），
  添加键 `stock_id` (`stock_id`,`currency_id`),
  添加密钥 `etf_id` (`etf_id`,`currency_id`),
  添加密钥 `crypto_id` (`crypto_id`,`currency_id`),
  添加密钥 `certificate_id` (`certificate_id`,`currency_id`),
  添加键 `quote_date` (`quote_date`);


更改表`end_day_quotes_AVG`
  修改 `id` int(11) NOT NULL AUTO_INCRMENT, AUTO_INCRMENT=10570526;</pre>
<p>生成的填充查询：</p>
选择`quote_date`、'stocks'作为`type`、`bid`、`stock_id`作为id 
( select t.*, row_number() over(按 stock_id order by `quote_date` desc 分区) as rn 
来自 end_day_quotes_AVG t，其中 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) 和currency_id = 2) x 其中rn = 1;</pre></p>

Answer 1

您是否正在寻找截至给定日期每个出价的两个最新报价？如果是这样，您只需修改第一个查询以允许行号 1 和 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

要在单个查询中获取每种货币/股票的最后出价（特定日期之前）和倒数第二个出价，并有效地使用currency_id、stock_id、quote_date 上的索引，您可以增量执行此操作：首先找到最大值每种货币/股票的日期（将使用索引），然后找到之前的日期（同样，以使用索引的方式），然后查找实际出价：

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)

如果您想要的不仅仅是每只股票的两个最近日期，您可能可以将 last_dates/next_to_last_dates 替换为包含天数的递归 cte（仅限于您想要收集的天数）。

小提琴