Isih mengikut tarikh, gunakan IN untuk mendapatkan entri terkini dan sebelumnya untuk berbilang entri

Question

Matlamat saya: Saya mempunyai senarai stock_ids dan ingin mendapatkan bids (diisih mengikut tarikh) untuk setiap ;/kod>

Untuk imej, ini bermakna saya mahu:

stock_id	Bida
3	663.91953
1	46.44281
2	9.02798

Satu masalah ialah kami mempunyai stok seperti Gazprom yang digantung, jadi salah satu petikan terakhir mungkin, sebagai contoh, 2021-06-06.

Mengambil tempat >quote_day = TARIKH(SEKARANG()) pada

Saya juga memerlukan tarikh yang sama dengan tarikh bawah pertama yang tiada dalam pertanyaan pertama, ini boleh dilakukan dengan pertanyaan kedua.

Penyelesaian semasa saya menggunakan PHP. Ini berfungsi, tetapi prestasinya tidak sempurna, seperti 100 stok mengambil masa 5 saat.

Saya boleh menggunakan Redis, yang juga mempunyai pilihan untuk menyimpan bida di suatu tempat.

Semasa:

<kod>pilih `tarikh_sebut harga`, 'saham' sebagai `jenis`, `bida`, `id_saham` sebagai id
daripada (
  pilih t.*, row_number()
    over(partition by stock_id order by `quote_date` desc) as rn
  daripada end_day_quotes_AVG t
  di mana quote_date <= DATE({$date})
    DAN stok_id dalam ({$val})
    dan currency_id = {$c_id}
) x di mana rn = 1
</code>

Sehari sebelumnya:

<kod>pilih `tarikh_sebut harga`, 'saham' sebagai `jenis`, `bida`, `id_saham` sebagai id
daripada (
  pilih t.*, row_number()
    over(partition by stock_id order by `quote_date` desc) as rn
  daripada end_day_quotes_AVG t
  di mana quote_date < DATE({$date})
    DAN stok_id dalam ({$val})
    dan currency_id = {$c_id}
) x di mana rn = 1
</code>

Stock_id, quote_date dan currency_id

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

编辑：

解释的查询：

id select_type table type possible_keys key key_len ref rows Tambahan   
1 UTAMA <berasal2>  ALL NULL NULL NULL NULL 220896 Menggunakan mana
2 TERUSKAN t SEMUA stock_id,quote_date NULL NULL NULL 2173105 Menggunakan mana; Menggunakan sementara

创建表：

BUAT JADUAL `tanda_akhir_hari_AVG` (
  `id` int(11) BUKAN NULL,
  `tarikh_petik` tarikh BUKAN NULL,
  `bida` perpuluhan(15,5) BUKAN NULL,
  `id_saham` int(11) LALAI NULL,
  `etf_id` int(11) LALAI NULL,
  `id_kripto` int(11) LALAI NULL,
  `id_sijil` int(11) LALAI NULL,
  `id_mata wang` int(11) BUKAN NULL
) ENJIN=CHARSET LALAI InnoDB=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IN TO `end_day_quotes_AVG` (`id`, `quote_date`, `bid`, `stock_id`, `etf_id`, `crypto_id`, `certificate_id`, `currency_id`) NILAI
(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 JADUAL `petikan_akhir_hari_AVG`
  TAMBAHKAN KUNCI UTAMA (`id`),
  TAMBAHKAN KUNCI `stock_id` (`stock_id`,`currency_id`),
  TAMBAHKAN KUNCI `etf_id` (`etf_id`,`currency_id`),
  TAMBAHKAN KUNCI `crypto_id` (`crypto_id`,`currency_id`),
  TAMBAHKAN KUNCI `id_sijil` (`id_sijil`,`id_mata wang`),
  TAMBAHKAN KUNCI `tarikh_petikan` (`tarikh_petikan`);


ALTER JADUAL `petikan_akhir_hari_AVG`
  UBAHSUAI `id` int(11) BUKAN NULL AUTO_INCREMENT, AUTO_INCREMENT=10570526;

生成的填充查询：

pilih `quote_date`, 'stok' sebagai `type`, `bid`, `stock_id` sebagai id daripada 
( pilih t.*, row_number() over(partition by stock_id order by `quote_date` desc) as rn 
dari tarikh_akhir_hari_AVG t di mana tarikh_sebut harga <= TARIKH('2023-01-02') DAN stok_id dalam (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,367964,36796
29659,40367,27821,24912,36654,21125,22569,22201,
23133,40373,36697,36718,26340,36653,47,34019,36847,36694) dan currency_id = 2 ) x dengan rn = 1;

Answer 1

Adakah anda sedang mencari Dua sebut harga terkini untuk setiap tawaran pada tarikh tertentu? Jika ya, anda hanya boleh mengubah suai pertanyaan pertama untuk membenarkan nombor baris 1 dan 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

Untuk mendapatkan bida terakhir (sebelum tarikh tertentu) dan bida kedua terakhir untuk setiap mata wang/saham dalam satu pertanyaan, dan dengan cekap menggunakan indeks pada currency_id, stock_id, quote_date, anda boleh melakukan ini secara berperingkat: mula-mula cari Tarikh maksimum untuk setiap mata wang /saham ( akan menggunakan indeks), kemudian cari tarikh sebelumnya (sekali lagi, dengan cara yang sama seperti menggunakan indeks), kemudian cari bida sebenar:

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)

Jika anda mahukan lebih daripada sekadar dua tarikh terbaharu untuk setiap stok, anda mungkin boleh menggantikan tarikh_akhir/seterusnya_tarikh_akhir dengan cte rekursif yang mengandungi bilangan hari (terhad kepada bilangan hari yang anda mahu kumpulkan).

Violin