<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 使用 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>
您是否正在尋找截至給定日期每個出價的兩個最新報價?如果是這樣,您只需修改第一個查詢以允許行號 1 和 2:
要在單一查詢中取得每種貨幣/股票的最後出價(特定日期之前)和倒數第二個出價,並有效地使用currency_id、stock_id、quote_date 上的索引,您可以增量執行此操作:首先找到最大值每種貨幣/股票的日期(將使用索引),然後找到先前的日期(同樣,以使用索引的方式),然後找到實際出價:
如果您想要的不僅僅是每隻股票的兩個最近日期,您可能可以將 last_dates/next_to_last_dates 替換為包含天數的遞歸 cte(僅限於您想要收集的天數)。
小提琴