Wie kann ich die prozentualen täglichen Preisänderungen mit MySQL berechnen?

Question

Ich habe eine Tabelle namens prices die den Schlusskurs der Aktien enthält, die ich täglich verfolge.

Hier ist das Schema:

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 ;

Ich versuche, den prozentualen Preisrückgang für alles zu berechnen, was einen Preiswert größer als 0 für heute und gestern hat. Mit der Zeit wird diese Tabelle riesig, und ich mache mir Sorgen um die Leistung. Ich nehme an, dass dies eher auf der MySQL-Seite als in PHP geschehen muss, weil LIMIT wird hier benötigt.

Wie nehme ich die letzten 2 Daten und mache die % Drop-Berechnung in MySQL?

Für jeden Ratschlag wären wir Ihnen sehr dankbar.

Answer 1

Ein Problem, das ich auf Anhieb sehe, ist die Verwendung eines Zeitstempeldatentyps für das Datum. Dies erschwert Ihre SQL-Abfrage aus zwei Gründen - Sie müssen einen Bereich verwenden oder in ein tatsächliches Datum in Ihrer Where-Klausel konvertieren, aber, was noch wichtiger ist, da Sie angeben, dass Sie sich für den heutigen Schlusskurs und den gestrigen Schlusskurs interessieren, müssen Sie die Tage im Auge behalten, an denen der Markt geöffnet ist - so ist die Abfrage für Montag anders als für Dienstag bis Freitag, und jeder Tag, an dem der Markt wegen eines Feiertags geschlossen ist, muss ebenfalls berücksichtigt werden.

Ich würde eine Spalte wie mktDay hinzufügen und sie an jedem Tag, an dem der Markt geöffnet ist, inkrementieren. Ein anderer Ansatz könnte darin bestehen, eine Spalte "previousClose" einzufügen, die Ihre Berechnung trivial macht. Ich weiß, dass dies gegen die Normalform verstößt, aber es spart eine teure Selbstverknüpfung in Ihrer Abfrage.

Wenn Sie die Struktur nicht ändern können, führen Sie einen Self-Join durch, um den gestrigen Schlusskurs zu erhalten, und Sie können die prozentuale Veränderung berechnen und nach dieser prozentualen Veränderung bestellen, wenn Sie möchten.

Nachfolgend ist Erics Code, ein wenig bereinigt, der auf meinem Server mit mysql 5.0.27 ausgeführt wird

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_today.price > 0
       and p_yest.price > 0
       and date(p_today.`date`) = CURRENT_DATE
order by `change` desc
limit 10

Beachten Sie die Rückwärtshaken, da einige Ihrer Spaltennamen und Erics Aliasnamen reservierte Wörter waren.

Beachten Sie auch, dass die Verwendung einer Where-Klausel für die erste Tabelle eine weniger kostspielige Abfrage wäre - die Where-Klausel wird zuerst ausgeführt und muss nur versuchen, sich mit den Zeilen zu verbinden, die größer als Null sind und das heutige Datum haben

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

Answer 2

Scott weist auf einen wichtigen Punkt bezüglich der aufeinanderfolgenden Markttage hin. Ich empfehle, dies mit einer Verbindungstabelle wie der folgenden zu behandeln:

CREATE TABLE `market_days` ( 
  `market_day` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY USING BTREE (`market_day`), 
  UNIQUE KEY USING BTREE (`date`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 
;

Je mehr Markttage vergehen, desto INSERT neu date Werte in der Tabelle. market_day wird entsprechend erhöht.

Beim Einfügen prices Daten, suchen Sie die LAST_INSERT_ID() oder den entsprechenden Wert für einen bestimmten date für vergangene Werte.

Was die prices Tabelle selbst, können Sie Ablagemöglichkeiten schaffen, SELECT y INSERT viel effizienter mit einem nützlichen PRIMARY KEY und keine AUTO_INCREMENT Spalte. In dem unten stehenden Schema ist Ihre PRIMARY KEY enthält an sich nützliche Informationen und ist nicht nur eine Konvention zur Kennzeichnung eindeutiger Zeilen. Verwendung von MEDIUMINT (3 Bytes) anstelle von INT (4 Byte) spart ein zusätzliches Byte pro Zeile und, was noch wichtiger ist, 2 Byte pro Zeile in der PRIMARY KEY - und bietet dennoch über 16 Millionen mögliche Daten und Tickersymbole (jeweils).

CREATE TABLE `prices` ( 
  `market_day` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  `ticker_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0', 
  `price` decimal (7,2) NOT NULL DEFAULT '00000.00', 
  PRIMARY KEY USING BTREE (`market_day`,`ticker_id`), 
  KEY `ticker_id` USING BTREE (`ticker_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

In diesem Schema ist jede Zeile eindeutig für jedes Paar von market_day y ticker_id . Hier ticker_id entspricht einer Liste von Tickersymbolen in einer tickers Tabelle mit einem ähnlichen Schema wie die market_days Tisch:

CREATE TABLE `tickers` ( 
  `ticker_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticker_symbol` VARCHAR(5),
  `company_name` VARCHAR(50), 
  /* etc */
  PRIMARY KEY USING BTREE (`ticker_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 
;

Das Ergebnis ist eine ähnliche Abfrage wie bei anderen Vorschlägen, jedoch mit zwei wichtigen Unterschieden: 1) Es gibt keine funktionale Transformation der Datumsspalte, was die Fähigkeit von MySQL zerstört, Schlüssel in der Verknüpfung zu benutzen; in der folgenden Anfrage benutzt MySQL einen Teil der PRIMARY KEY zum Beitritt am market_day . 2) MySQL kann nur einen Schlüssel pro JOIN o WHERE Klausel. In dieser Anfrage verwendet MySQL die volle Breite der PRIMARY KEY ( market_day y ticker_id ), während in der vorherigen Abfrage nur einer verwendet werden konnte (MySQL wählt normalerweise den selektiveren der beiden aus).

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 
;

Ein weiterer Punkt ist die Notwendigkeit, sich auch gegen tickers y market_days zur Anzeige der aktuellen ticker_symbol y date aber diese Operationen sind sehr schnell, da sie Schlüssel verwenden.

Answer 3

Im Wesentlichen können Sie die Tabelle einfach mit sich selbst verknüpfen, um die angegebene prozentuale Veränderung zu ermitteln. Dann ordnen Sie nach change absteigend, um die größten Wechsler nach oben zu bekommen. Sie können auch nach abs(change) wenn Sie die größten Ausschläge wünschen.

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