我的目标是：我有一个stock_id的列表，并且希望每个stock_id只获得最后一个bid（按日期排序）。
对于图片，它意味着我想：
| 库存标识|出价|
| - ------| - ------|
| 三个|小行星663.91953|
| 1个|四十六点四四二八一|
| 第二章|九点零二七九八|
一个问题是我们有像gazproms这样的股票被停牌，所以最后的报价之一可能是2021 - 06 - 06，例如。
在quote_day = DATE(NOW())上执行where在这种情况下不起作用。
我还需要相同的第一个较低的日期，这是不是在第一次查询，这可以在第二次查询完成。
我目前的解决方案与使用PHP。这是工作，但性能不是完美的像100只股票，它需要5秒。
我可以使用Redis，这也将是一个选项，以保存出价的地方。
当前：
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 = 1Stock_id、quote_date和currency_id是唯一的。
"我想要数据的表"使用服务器：10.9.4-玛丽亚数据库-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;
3条答案
按热度按时间ghhaqwfi1#
看起来没有索引被直接使用，这在大的IN列表中经常发生。从值表构造函数改为join，创建一个（currency_id，stock_id，quote_date）索引，它应该可以使用它。
(The选择0/union所在的位置只是为了给予列一个有用的名称，因为mariadb的默认值是非常无用的。）
lyr7nygr2#
我想得到最后出价（按日期排序）只有一个每个股票ID。
我还需要相同的第一个较低的日期，这是不是在第一个查询。
您是否正在查找截至给定日期的每个投标的 * 两个 * 最新报价？如果是，您可以修改第一个查询以允许行号为1 * 和2*：
carvr3hs3#
要在单个查询中获取每种货币/股票的最后出价（在某个日期之前）和倒数第二个出价，并有效地使用currency_id、stock_id、quote_date上的索引，可以逐步执行此操作：首先找到每种货币/股票的最高出价日期（将使用指数），然后找到前一个日期（同样，使用指数），然后查找实际出价：
如果你想要的不仅仅是每只股票最近的两个日期，你可以用一个递归的cte来替换last_dates/next_to_last_dates，其中包含一个天数（限制为你想要收集的天数）。
fiddle