日付順に並べ替え、複数のエントリの最新および前のエントリを取得するには IN を使用します

Question

<p>私の目標: <code>stock_id</code> のリストがあり、各 <code>stock_id< の最後の <code>bid</code> を (日付順に) 取得したいと考えています。 ;/コード> a. </p> <p>画像の場合、これは次のものが必要であることを意味します: </p> <テーブルクラス="s-テーブル"> <頭> <tr> <th>stock_id</th> 入札 </tr> </頭> <みんな> <tr> 3 <td>663.91953</td> </tr> <tr> 1 <td>46.44281</td> </tr> <tr> 2 9.02798 </tr> </tbody> </テーブル> <p>問題の 1 つは、ガスプロムのような株式が取引停止になっているため、最後の相場の 1 つが、たとえば 2021 年 6 月 6 日になる可能性があることです。 </p> <p><code で where >quote_day = DATE(NOW())</code> を実行しても、この場合は機能しません。 </p> <p>最初のクエリにはない最初の下位の日付と同じ日付も必要です。これは 2 番目のクエリで実行できます。 </p> <p>現在のソリューションは PHP を使用しています。これは機能しますが、100 株に 5 秒かかるなど、パフォーマンスは完璧ではありません。 </p> <p>Redis を使用することもできます。これには、入札をどこかに保存するオプションもあります。 </p> <p>現在:</p> <pre class="lang-sql prettyprint-override"><code>`quote_date`、`type` として 'stocks'、id として `bid`、`stock_id` を選択します から （ select t.*, row_number() over(stock_id order by `quote_date` desc) as rn end_day_quotes_AVG から ここで、quote_date <= DATE({$date}) AND ({$val}) のstock_id かつ、currency_id = {$c_id} ) x ここで、rn = 1 </code></pre> <p>前日: </p> <pre class="lang-sql prettyprint-override"><code>`quote_date`、`type` として 'stocks'、id として `bid`、`stock_id` を選択します から （ select t.*, row_number() over(stock_id order by `quote_date` desc) as rn end_day_quotes_AVG から ここで、quote_date < DATE({$date}) AND ({$val}) のstock_id かつ、currency_id = {$c_id} ) x ここで、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> <pre class="brush:php;toolbar:false;">id select_type table type possible_keys key key_len ref rows Extra 1 プライマリ ALL NULL NULL NULL NULL 220896 where の使用 2 DERIVED t ALLstock_id,quote_date NULL NULL NULL 2173105 where を使用します。一時的な</pre>を使用する <p>创建表：</p> <pre class="brush:php;toolbar:false;">CREATE TABLE `end_day_quotes_AVG` ( `id` int(11) NOT NULL、 `quote_date` の日付は NULL ではありません。 `bid` 10 進数 (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 ) ENGINE=InnoDB DEFAULT CHARSET=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、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` 主キー (`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;</pre> <p>生成された充填コメント：</p> <pre class="brush:php;toolbar:false;">`quote_date`、`type` として 'stocks'、id として `bid`、`stock_id` を選択します。 ( select t.*, row_number() over(stock_id order by `quote_date` desc) as rn from end_day_quotes_AVG t where quote_date <= DATE('2023-01-02') AND St​​ock_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 where rn = 1;<

Answer 1

特定の日付の時点での各入札の 2 件の 最新の見積もりをお探しですか?その場合は、行番号 1 と 2 を許可するように最初のクエリを変更するだけです:

リーリー

Answer 2

単一のクエリで各通貨/株式の最後の入札 (特定の日付より前) と最後から 2 番目の入札を取得するには、currency_id、stock_id、quote_date のインデックスを効果的に使用して、これを段階的に行うことができます。 まず、次のようにして日付を見つけます。各通貨/株式の最大値 (はインデックスを使用します)、次に前の日付を見つけます (これもインデックスを使用するのと同じ方法)、そして実際の入札値を見つけます:

リーリー

各銘柄の最新の 2 つの日付以上が必要な場合は、last_dates/next_to_last_dates を日数 (収集したい日数に限定) を含む再帰的な cte に置き換えることができる場合があります。

＃＃＃＃＃＃バイオリン＃＃＃＃＃＃