Trier par date, utilisez IN pour obtenir l'entrée la plus récente et précédente pour plusieurs entrées

Question

Mon objectif : j'ai une liste de stock_id et je souhaite obtenir le dernier bid (trié par date) pour chaque ;/code>

Pour les images, cela signifie que je veux :

≪/tr> ≪/tête> ≪/tr> ≪/tr> ≪/tr>

Un problème est que nous avons des actions comme Gazprom qui sont suspendues, donc l'une des dernières cotations pourrait être, par exemple, le 2021-06-06.

Prendre un Where >quote_day = DATE(NOW()) sur

J'ai également besoin de la même date que la première date inférieure qui ne figure pas dans la première requête, cela peut être fait avec la deuxième requête.

Ma solution actuelle utilise PHP. Cela fonctionne, mais les performances ne sont pas parfaites, comme 100 actions prennent 5 secondes.

Je pourrais utiliser Redis, qui a également la possibilité d'enregistrer les offres quelque part.

Actuel :

<code> sélectionnez `quote_date`, 'stocks' comme `type`, `bid`, `stock_id` comme identifiant
depuis (
  sélectionnez t.*, row_number()
    over (partition par stock_id ordre par `quote_date` desc) comme rn
  de end_day_quotes_AVG t
  où quote_date <= DATE({$date})
    ET stock_id dans ($val})
    et devise_id = {$c_id}
) x où rn = 1
</code>

La veille :

<code> sélectionnez `quote_date`, 'stocks' comme `type`, `bid`, `stock_id` comme identifiant
depuis (
  sélectionnez t.*, row_number()
    over (partition par stock_id ordre par `quote_date` desc) comme rn
  de end_day_quotes_AVG t
  où quote_date <
    ET stock_id dans ($val})
    et devise_id = {$c_id}
) x où rn = 1
</code>

Stock_id, quote_date et currency_id

编辑：

解释的查询：

id select_type type de table possible_keys key key_len ref rows Extra   
1 PRIMAIRE <derived2>  ALL NULL NULL NULL NULL 220896 Utilisation de Where
2 DERIVED t ALL stock_id,quote_date NULL NULL NULL 2173105 Utilisation de Where ; Utilisation de temporaire

创建表：

CREATE TABLE `end_day_quotes_AVG` (
  `id` int(11) NON NULL,
  `quote_date` date NON NULLe,
  `bid` decimal(15,5) NON NULL,
  `stock_id` int(11) NULL PAR DÉFAUT,
  `etf_id` int(11) NULL PAR DÉFAUT,
  `crypto_id` int(11) NULL PAR DÉFAUT,
  `certificate_id` int(11) NULL PAR DÉFAUT,
  `currency_id` int(11) NON NULL
) MOTEUR=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSÉRER DANS `end_day_quotes_AVG` (`id`, `quote_date`, `bid`, `stock_id`, `etf_id`, `crypto_id`, `certificate_id`, `currency_id`) VALEURS
(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`
  AJOUTER UNE CLÉ PRIMAIRE (`id`),
  AJOUTER LA CLÉ `stock_id` (`stock_id`,`currency_id`),
  AJOUTER UNE CLÉ `etf_id` (`etf_id`,`currency_id`),
  AJOUTER LA CLÉ `crypto_id` (`crypto_id`,`currency_id`),
  AJOUTER LA CLÉ `certificate_id` (`certificate_id`,`currency_id`),
  AJOUTER UNE CLÉ `quote_date` (`quote_date`);


ALTER TABLE `end_day_quotes_AVG`
  MODIFIER `id` int(11) NON NULL AUTO_INCREMENT, AUTO_INCREMENT=10570526;

生成的填充查询：

sélectionnez `quote_date`, 'stocks' comme `type`, `bid`, `stock_id` comme identifiant de 
(sélectionnez t.*, row_number() sur (partition par stock_id ordre par `quote_date` desc) comme rn 
de end_day_quotes_AVG t où quote_date <= DATE('2023-01-02') ET stock_id dans (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) et monnaie_id = 2 ) x où rn = 1;

stock_id	Offre
3	663.91953
1	46,44281
2	9,02798

Answer 1

Recherchez-vous les Deux dernières cotations pour chaque offre à une date donnée ? Si tel est le cas, vous pouvez simplement modifier la première requête pour autoriser les lignes numéros 1 et 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

Pour obtenir la dernière enchère (avant une date spécifique) et l'avant-dernière enchère pour chaque devise/action en une seule requête, et en utilisant efficacement les index sur devises_id, stock_id, quote_date, vous pouvez le faire de manière incrémentielle : recherchez d'abord la date maximale pour chaque devise. /stock ( utilisera l'index), puis recherchez la date précédente (encore une fois, de la même manière qu'en utilisant l'index), puis trouvez l'enchère réelle :

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)

Si vous voulez plus que les deux dates les plus récentes pour chaque stock, vous pourrez peut-être remplacer last_dates/next_to_last_dates par un cte récursif contenant le nombre de jours (limité au nombre de jours que vous souhaitez collecter).

Violon