Sortieren Sie nach Datum und verwenden Sie IN, um bei mehreren Einträgen den neuesten und vorherigen Eintrag abzurufen

Question

Mein Ziel: Ich habe eine Liste mit stock_ids und möchte die letzten bids (sortiert nach Datum) für jeden stock_id< ;/code>

Für Bilder bedeutet das, dass ich Folgendes möchte:

stock_id	Gebot
3	663.91953
1	46.44281
2	9.02798

Ein Problem besteht darin, dass wir Aktien wie Gazprom haben, die ausgesetzt sind, sodass eine der letzten Notierungen beispielsweise der 06.06.2021 sein könnte.

Die Verwendung von where >quote_day = DATE(NOW()) für

Ich benötige außerdem das gleiche Datum wie das erste niedrigere Datum, das nicht in der ersten Abfrage enthalten ist. Dies kann mit der zweiten Abfrage erfolgen.

Meine aktuelle Lösung verwendet PHP. Das funktioniert, aber die Leistung ist nicht perfekt, da 100 Aktien 5 Sekunden dauern.

Ich könnte Redis verwenden, das auch die Möglichkeit bietet, die Gebote irgendwo zu speichern.

Aktuell:

<code>wählen Sie „quote_date“, „stocks“ als „type“, „bid“, „stock_id“ als ID
aus (
  select t.*, row_number()
    over(partition by stock_id order by `quote_date` desc) as rn
  von end_day_quotes_AVG t
  wobei quote_date <= DATE({$date})
    UND stock_id in ({$val})
    und Währung_id = {$c_id}
) x wobei rn = 1
</code>

Am Vortag:

<code>wählen Sie „quote_date“, „stocks“ als „type“, „bid“, „stock_id“ als ID
aus (
  select t.*, row_number()
    over(partition by stock_id order by `quote_date` desc) as rn
  von end_day_quotes_AVG t
  wobei quote_date < DATE({$date})
    UND stock_id in ({$val})
    und Währungs-ID = {$c_id}
) x wobei rn = 1
</code>

Stock_id, quote_date und currency_id

我想要使用服务器数据的表:10.9.4-MariaDB-1:10.9.4

编辑：

解释的查询:

id select_type Tabellentyp mögliche_Schlüssel Schlüssel key_len Ref Zeilen Extra   
1 PRIMÄR <abgeleitet2>  ALL NULL NULL NULL NULL 220896 Wo wird verwendet
2 DERIVED t ALL stock_id,quote_date NULL NULL NULL 2173105 Using where; Mit temporärem

创建表:

CREATE TABLE `end_day_quotes_AVG` (
  `id` int(11) NICHT NULL,
  `quote_date` Datum NICHT NULL,
  `bid` decimal(15,5) NOT NULL,
  `stock_id` int(11) STANDARD NULL,
  `etf_id` int(11) STANDARD NULL,
  `crypto_id` int(11) STANDARD NULL,
  `certificate_id` int(11) DEFAULT NULL,
  „currency_id“ int(11) NICHT 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`
  PRIMÄRSCHLÜSSEL („id“) HINZUFÜGEN,
  SCHLÜSSEL „stock_id“ („stock_id“, „currency_id“) hinzufügen,
  SCHLÜSSEL `etf_id` (`etf_id`,`currency_id`) HINZUFÜGEN,
  SCHLÜSSEL `crypto_id` (`crypto_id`,`currency_id`) HINZUFÜGEN,
  SCHLÜSSEL „certificate_id“ („certificate_id“, „currency_id“) hinzufügen,
  SCHLÜSSEL `quote_date` (`quote_date`) HINZUFÜGEN;


ALTER TABLE `end_day_quotes_AVG`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10570526;

生成的填充查询:

wählen Sie „quote_date“, „stocks“ als „type“, „bid“, „stock_id“ als ID von 
( 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) und Währungs-ID = 2 ) x wobei rn = 1;

Answer 1

您是否正在寻找截至给定日期每个出价的两个最新报价？如果是这样，您只需修改第一个查询以允许行号 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

Answer 2

要在单个查询中获取每种货币/股票的最后出价（特定日期之前）和倒数第二个出价，并有效地使用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（仅限于您想要收集的天数）。

小提琴