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

Question

我的目标是，有一份stock_id列表，我想要获取每个stock_id的最新bid（按日期降序排序，即每个stock_id仅取一个最新记录）。

参照表格所示，这意味着我想要：

| stock_id | bid |
|----------|-----------|
| 3 | 663.91953 |
| 1 | 46.44281 |
| 2 | 9.02798 |

存在的问题是，某些股票（如gazproms）可能已被暂停交易，因此最近一条报价记录可能是2021-06-06这样的日期，所以在查询时不能只针对当天数据。

我还想用类似的方式获取最早日期（非当天）的记录，但这可以通过第二个查询实现。

当前我使用PHP实现了这个功能，虽然可行，但性能不是很好，例如处理100只股票就需要大约5秒的时间。我可以利用Redis，将bid数据存储在某个地方以提高性能。

当前使用的SQL查询：

获取指定日期之后的最新记录：

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

获取指定日期之前的最新记录：

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

在这里，stock_id、quote_date和currency_id都是唯一的。

所使用的数据库版本：10.9.4-MariaDB-1:10.9.4

表格图片

（注：根据编辑内容补充了查询计划及表结构）

从查询计划来看，主查询使用了派生表，并且全表扫描了子查询结果，子查询同样进行了全表扫描并使用了临时表。为了优化性能，可以考虑进一步完善索引策略，比如针对（stock_id, quote_date, currency_id）这三个字段建立复合索引。

表结构创建语句如下：

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,
  PRIMARY KEY (`id`),
  KEY `stock_id` (`stock_id`,`currency_id`),
  KEY `etf_id` (`etf_id`,`currency_id`),
  KEY `crypto_id` (`crypto_id`,`currency_id`),
  KEY `certificate_id` (`certificate_id`,`currency_id`),
  KEY `quote_date` (`quote_date`)
) 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
(...);

附带一个生成的实际查询示例：

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 (一系列股票ID)
    AND currency_id = 2
) x WHERE rn = 1;

ysth · Answer

不，您误解了用户的需求。用户想要的是每个stock_id最新的单个bid值以及紧邻其前的较早日期的单个bid值。用户并不是要获取每个stock_id的两个最新的报价记录。

用户需要的第一个查询是获取每个stock_id的最新报价，这已在原问题中的SQL查询中实现：

-- 获取每个stock_id最新的bid
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(?)
    AND stock_id IN (?)
    AND currency_id = ?
) x
WHERE rn = 1;

对于用户还需要的“第一个较低日期”的查询，则需要修改查询以获取每个stock_id在最新日期之前的那个日期的bid，这通常意味着需要第二个独立的查询，类似于：

-- 获取每个stock_id最新日期之前的一个bid
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 < (
      SELECT MAX(`quote_date`) 
      FROM end_day_quotes_AVG 
      WHERE stock_id = t.stock_id AND currency_id = ?
  )
  AND stock_id IN (?)
  AND currency_id = ?
) x
WHERE rn = 1;

Wayne Conrad · Answer

看起来目前查询并未使用索引，这在具有大量IN列表的情况下时常发生。可以改为从值表构造器进行连接，并创建一个(currency_id, stock_id, quote_date)的复合索引，这样数据库就能利用索引来提升查询效率。

select `quote_date`, 'stocks' as `type`, `bid`, `stock_id` as id
from (
    select t.*, row_number() over(partition by currency_id, stock_id order by `quote_date` desc) as rn 
    from (
        -- 创建一个临时表，赋予列一个有意义的名称（这里命名为stock_id）
        select null as stock_id where 0
        union all
        -- 值表构造器，列出所有的stock_id
        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)
    ) as stock_ids
    -- 使用内连接，关联end_day_quotes_AVG表
    join end_day_quotes_AVG t on t.currency_id = 2 and t.stock_id = stock_ids.stock_id and t.quote_date <= date('2023-01-02')
) x where rn = 1

这里的“select where 0/union”是用来给列赋予一个有用的名字，因为MariaDB默认的列名可能会非常难以理解。通过这种方式改写查询，可以使得MySQL/MariaDB更容易利用到(currencu_id, stock_id, quote_date)的复合索引，从而提高查询性能。记得先创建所需的索引。

Scott Boston · Answer

为了在一个查询中高效地获取每个货币/股票在特定日期前的最后一个报价和倒数第二个报价，并充分利用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)

如果你希望获取每个股票超过最近两天的更多日期的报价，很可能可以用一个递归公共表表达式（recursive common table expression, CTE）替代last_dates/next_to_last_dates部分，并添加一个表示天数的编号（限制为你想要收集的天数）。
在线演示链接