8
votes

Comment puis-je calculer le top% des changements de prix quotidiens à l'aide de MySQL?

J'ai une table appelée prix code> qui inclut le prix de clôture des stocks que je suive quotidiennement.

Voici le schéma: p>

CREATE TABLE `prices` (
  `id` int(21) NOT NULL auto_increment,
  `ticker` varchar(21) NOT NULL,
  `price` decimal(7,2) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `ticker` (`ticker`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2200 ;

sql mysql

0 commentaires

3 Réponses :

2
votes

Essentiellement, vous pouvez simplement rejoindre la table pour trouver le changement de% donné. Ensuite, commandez par changer code> descendant pour obtenir les plus gros changeurs sur le dessus. Vous pouvez même commander par ABS (changement) code> si vous voulez les plus grandes balançoires.

select
   p_today.ticker,
   p_today.date,
   p_yest.price as open,
   p_today.price as close,
   --Don't have to worry about 0 division here
   (p_today.price - p_yest.price)/p_yest.price as change
from
   prices p_today
   inner join prices p_yest on
       p_today.ticker = p_yest.ticker
       and date(p_today.date) = date(date_add(p_yest.date interval 1 day))
       and p_today.price > 0
       and p_yest.price > 0
       and date(p_today.date) = CURRENT_DATE
order by change desc
limit 10

6 commentaires

Salut Eric. Merci pour la solution. Je reçois une erreur lors de l'exécution de la requête cependant: # 1064 - Vous avez une erreur dans votre syntaxe SQL; Vérifiez le manuel qui correspond à votre version de Server MySQL pour la bonne syntaxe à utiliser à proximité de 'Changement à partir des prix P_TODAY INTERNIER JOIX Prix P_YEST sur P_TODAY.' à la ligne 6

@Knix: hein. Je n'ai pas d'instance MySQL pour essayer cela, mais que se passe-t-il si vous prenez le commentaire, puis la colonne modifier ?


                                                
                                                                                            
                                                    @ERIC: J'ai sorti le commentaire et aussi les 2 lignes qui en ont changé mais obtiennent toujours l'erreur. Je pense que cela n'aime pas le 'et la date (p_today.date) = courant_date' Line "car" date "est en rouge dans le message d'erreur.                                                
                                                
                                                                                            
                                                    Je pense que la fonction Date n'est que dans MySQL (V4.1.1)?                                                
                                                
                                                                                            
                                                     date  a été ajouté en v4.1.1, mais il reste une fonction valide. Je pense que c'était supporté à la date de la mathématique. J'ai ajouté la fonction  date_add .                                                

                                                
                                                                                            
                                                    Je pense que vous avez raison et cela a quelque chose à voir avec la date de mathématique. Il se plaint toujours de la nouvelle fonction «date_add». Je vais regarder dans ça. Merci de me pointer dans la bonne direction.


                        
                                            
                            
                                
                                    4
 votes
                                
                            
                            
                                
                                     Un problème que je vois à droite de la batte utilise un type de données de l'horodatage de la date, cela compliquera votre requête SQL pour deux raisons - vous devrez utiliser une plage ou convertir en une date réelle de votre clause où, mais , plus important encore, puisque vous déclarez que vous êtes intéressé par le prix de clôture d'aujourd'hui et le prix de clôture d'hier, vous devrez garder une trace des jours où le marché est ouvert. Donc, la requête de lundi est différente du mardi et de tous les jours du marché. est fermé pour un jour férié devra être comptabilisé aussi. 
 J'ajouterais une colonne comme mktday et l'incrémentation de chaque jour le marché est ouvert pour les affaires. Une autre approche pourrait être d'inclure une colonne «Exploitation précédente» qui rend votre calcul trivial. Je me rends compte que cela viole la forme normale, mais cela sauve une jointure auto chère dans votre requête.  P> 
 Si vous ne pouvez pas changer la structure, vous ferez une jointure auto pour obtenir la fermeture d'hier et vous pouvez calculer le% de changement Et la commande par ce% change si vous le souhaitez.  p> 
 ci-dessous est le code d'Eric, nettoyé un peu il exécuté sur mon serveur exécutant MySQL 5.0.27  P> 
select
   p_today.`ticker`,
   p_today.`date`,
   p_yest.price as `open`,
   p_today.price as `close`,
   ((p_today.price - p_yest.price)/p_yest.price) as `change`
from
   prices p_today
   inner join prices p_yest on
       p_today.ticker = p_yest.ticker
       and date(p_today.`date`) = date(p_yest.`date`) + INTERVAL 1 DAY

       and p_yest.price > 0
where p_today.price > 0
    and date(p_today.`date`) = CURRENT_DATE
order by `change` desc
limit 10

                                

                                                                    
                                    
                                    
                                        3 commentaires
                                        
                                                                                            
                                                    @Scott: Merci pour votre commentaire. Je peux changer l'horodatage en seulement la date de rendre les choses plus faciles plutôt que de devoir traiter des gammes.                                                
                                                
                                                                                            
                                                    @Knix La fonction de date est assez propre, je ne sais pas à quel point c'est cher, mais certainement votre appel. Il reste encore un problème sur le marché étant fermé le week-end et les jours fériés. La colonne de décharge précédente élimine la jointure de soi, la messilité des journées de marché fermées au détriment des données de duplication et de connaître la fermeture de prev lors de l'insertion de la fermeture d'aujourd'hui.                                                
                                                
                                                                                            
                                                    Merci ... Je vais prendre vos conseils!                                                
                                                
                                                                                    
                                    
                                                            
                        
                        
                                            
                            
                                
                                    3
 votes
                                
                            
                            
                                
                                     Scott fait monter un bon point sur les jours de marché consécutifs. Je recommande de manipuler cela avec une table de connecteur comme:  xxx  pré> 
 comme plus de jours de marché, juste  insérer  code> nouveau  date  code> Valeurs dans la table.  market_day  code> augmentera en conséquence.  p> 
 Lorsque vous insérez les prix  code> des données  code>, consultez le  last_insert_id ()  code> ou la valeur correspondante à une donnée Code> date  code> pour les valeurs passées.  p> 
 comme pour les prix   code> Table même, vous pouvez créer un stockage,  Sélectionnez  CODE> et  INSERT  CODE> Opérations beaucoup plus efficaces avec une clé primaire utile  code> et aucun  auto_incrènement  code> colonne. Dans le schéma ci-dessous, votre clé principale  code> contient des informations intrinsèquement utiles et n'est pas qu'une convention pour identifier des lignes uniques. Utilisation de  MioriMint  code> (3 octets) au lieu de  int  code> (4 octets) enregistre un octet supplémentaire par ligne et plus important encore 2 octets par rangée dans la clé primaire  code > - Tout en offrant toujours plus de 16 millions de dates et de symboles de ticker possibles (chacun).  p>  xxx  pré> 
 dans ce schéma chaque rangée est unique sur chaque paire de  Market_day  Code> et  ticker_id  code>. Ici  Ticker_id  CODE> correspond à une liste de symboles de ticker dans un  Tablettes  CODE> Tableau avec un schéma similaire à la table  Market_days  code> Table:  P> 
SELECT 
  `market_days`.`date`, 
  `tickers`.`ticker_symbol`, 
  `yesterday`.`price` AS `close_yesterday`, 
  `today`.`price` AS `close_today`, 
  (`today`.`price` - `yesterday`.`price`) / (`yesterday`.`price`)  AS `pct_change`
FROM 
  `prices` AS `today`
LEFT JOIN 
  `prices` AS `yesterday` 
  ON /* uses PRIMARY KEY */
    `yesterday`.`market_day` = `today`.`market_day` - 1 /* this will join NULL for `today`.`market_day` = 0 */
    AND
    `yesterday`.`ticker_id` = `today`.`ticker_id` 
INNER JOIN 
  `market_days` /* uses first 3 bytes of PRIMARY KEY */
  ON 
  `market_days`.`market_day` = `today`.`market_day`
INNER JOIN 
  `tickers` /* uses KEY (`ticker_id`) */
  ON 
  `tickers`.`ticker_id` = `today`.`ticker_id`
WHERE 
  `today`.`price` > 0 
  AND 
  `yesterday`.`price` > 0 
;

                                

                                                                    
                                    
                                    
                                        1 commentaires
                                        
                                                                                            
                                                    C'est sérieusement un bon schéma et un ensemble de requêtes