id select_type 表格型別 possible_keys key key_len ref rows Extra 1 PRIMARY <派生2> ALL NULL NULL NULL NULL 220896 使用 where 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>
P粉3409802432023-09-04 18:30:57
您是否正在尋找截至給定日期每個出價的兩個最新報價?如果是這樣,您只需修改第一個查詢以允許行號 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
P粉8999507202023-09-04 12:31:54
要在單一查詢中取得每種貨幣/股票的最後出價(特定日期之前)和倒數第二個出價,並有效地使用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(僅限於您想要收集的天數)。