Geekly articles weekly

Ausdünnende Zeitrahmen (Kryptowährungen, Devisen, Börsen)

Vor einiger Zeit wurde ich beauftragt, ein Verfahren zu schreiben, das das Ausdünnen von Forex-Marktnotierungen (genauer Zeitrahmendaten) durchführt.

Die Erklärung des Problems: Die Daten werden in einem Intervall von 1 Sekunde in diesem Format eingegeben:

  • Instrumentenname (USDEUR-Paarcode usw.),
  • Datum und Uhrzeit im Unix-Zeitformat,
  • Offener Wert (Preis der ersten Transaktion im Intervall),
  • Hoher Wert (Maximalpreis),
  • Niedriger Wert
  • Schlusswert (Preis des letzten Geschäfts),
  • Volumen (Volumen oder Transaktionsvolumen).

Es ist notwendig, die Neuberechnung und Synchronisation der Daten in den Tabellen sicherzustellen: 5 Sekunden, 15 Sekunden, 1 Minute, 5 Minuten, 15 Minuten usw.

Das beschriebene Datenspeicherformat wird als OHLC oder OHLCV (Open, High, Low, Close, Volume) bezeichnet. Es wird oft verwendet, Sie können sofort eine Tabelle mit "japanischen Kerzen" darauf erstellen.

Bild

Im Rahmen des Schnitts habe ich alle Optionen beschrieben, die mir einfallen könnten, um die empfangenen Daten auszudünnen (zu vergrößern), um beispielsweise den Wintersprung des Bitcoin-Preises zu analysieren. Entsprechend den empfangenen Daten erstellen Sie sofort ein Diagramm „Japanische Kerzen“ (in MS Excel gibt es auch ein solches Diagramm ) In der Abbildung oben wurde dieses Diagramm für den Zeitraum „1 Monat“ für das Tool „bitstampUSD“ erstellt. Der weiße Körper der Kerze zeigt eine Preiserhöhung im Intervall an, schwarz - eine Preissenkung, der obere und untere Docht zeigen die maximalen und minimalen Preise an, die in dem Intervall erreicht wurden. Hintergrund - Transaktionsvolumen. Es ist deutlich zu sehen, dass der Preis im Dezember 2017 nahe an die Marke von 20.000 kam.

Die Lösung wird für zwei Datenbank-Engines für Oracle und MS SQL angegeben, die es in gewisser Weise ermöglichen, sie für diese bestimmte Aufgabe zu vergleichen (wir werden den Vergleich mit anderen Aufgaben nicht verallgemeinern).

Dann habe ich das Problem auf triviale Weise gelöst: Berechnen der korrekten Ausdünnung in einer temporären Tabelle und Synchronisieren mit der Zieltabelle - Löschen von Zeilen, die in der Zieltabelle vorhanden sind, aber nicht in der temporären Tabelle vorhanden sind, und Hinzufügen von Zeilen, die in der temporären Tabelle vorhanden sind, aber nicht im Ziel vorhanden sind. Zu diesem Zeitpunkt war der Kunde mit der Lösung zufrieden und ich schloss die Aufgabe.

Aber jetzt habe ich beschlossen, alle Optionen in Betracht zu ziehen, da die obige Lösung eine Funktion enthält - es ist schwierig, für zwei Fälle gleichzeitig zu optimieren:

  • Wenn die Zieltabelle leer ist und Sie viele Daten hinzufügen müssen,
  • und wenn die Zieltabelle groß ist und Sie Daten in kleinen Blöcken hinzufügen müssen.

Dies liegt an der Tatsache, dass Sie in der Prozedur die Zieltabelle und die temporäre Tabelle verbinden müssen und eine Verbindung zur größeren Tabelle herstellen müssen und nicht umgekehrt. In den beiden oben genannten Fällen werden die größeren / kleineren vertauscht. Das Optimierungsprogramm entscheidet anhand von Statistiken über die Verbindungsreihenfolge. Die Statistiken sind möglicherweise veraltet und die Entscheidung wird möglicherweise falsch getroffen, was zu einer erheblichen Leistungsverschlechterung führt.

In diesem Artikel werde ich einmalige Ausdünnungsmethoden beschreiben, die für Leser zur Analyse nützlich sein können, beispielsweise den Wintersprung des Bitcoin-Preises.

Online-Ausdünnungsverfahren können von Github unter dem Link am Ende des Artikels heruntergeladen werden.

Auf den Punkt gebracht ... Meine Aufgabe war das Ausdünnen vom Zeitrahmen "1 Sekunde" zum nächsten, aber hier erwäge ich das Ausdünnen von der Transaktionsebene (in der Quelltabelle die Felder STOCK_NAME, UT, ID, APRICE, AVOLUME). Weil solche Daten von bitcoincharts.com ausgegeben werden.
Tatsächlich wird die Dezimierung von der Transaktionsebene auf die Ebene von "1 Sek." Durch einen solchen Befehl durchgeführt (der Operator kann leicht in eine Dezimierung von der Ebene von "1 Sek." Auf die oberen Ebenen übersetzt werden):

Unter Oracle:

select 1 as STRIPE_ID , STOCK_NAME , TRUNC_UT (UT, 1) as UT , avg (APRICE) keep (dense_rank first order by UT, ID) as AOPEN , max (APRICE) as AHIGH , min (APRICE) as ALOW , avg (APRICE) keep (dense_rank last order by UT, ID) as ACLOSE , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW group by STOCK_NAME, TRUNC_UT (UT, 1);

Die Funktion avg () keep (dens_rank erster Ordnung nach UT, ID) funktioniert folgendermaßen: Da die Anforderung GROUP BY lautet, wird jede Gruppe unabhängig von den anderen berechnet. Innerhalb jeder Gruppe werden Zeichenfolgen nach UT und ID sortiert und nach dens_rank nummeriert. Da die erste Funktion folgt, wird die Zeile ausgewählt, in der dens_rank 1 zurückgegeben hat (mit anderen Worten, das Minimum ist ausgewählt) - die erste Transaktion innerhalb des Intervalls wird ausgewählt. Wenn für diese minimale UT, ID mehrere Zeilen vorhanden wären, würde der Durchschnitt berücksichtigt. In unserem Fall wird jedoch eine Zeile garantiert (aufgrund der Eindeutigkeit der ID), sodass der resultierende Wert sofort als AOPEN zurückgegeben wird. Es ist leicht zu bemerken, dass die erste Funktion zwei aggregierte ersetzt.

Unter MS SQL

Es gibt keine ersten / letzten Funktionen (es gibt first_value / last_value , aber das ist es nicht). Daher müssen Sie die Tabelle mit sich selbst verbinden.

Ich werde die Anfrage nicht separat geben, aber Sie können sie unten in der Prozedur dbo.THINNING_HABR_CALC sehen . Natürlich ist es ohne First / Last nicht so elegant, aber es wird funktionieren.

Wie kann dieses Problem von einem Bediener gelöst werden? (Hier bedeutet der Begriff "ein Operator" nicht, dass der Operator einer sein wird, sondern dass es keine Zyklen gibt, die die Daten in einer Zeile "ziehen".)

Ich werde alle mir bekannten Optionen zur Lösung dieses Problems auflisten:

  1. SIMP (einfaches, einfaches, kartesisches Produkt),
  2. CALC (berechnen, iterative Ausdünnung der oberen Ebenen),
  3. CHIN (China Way, sperrige Anfrage für alle Ebenen gleichzeitig),
  4. UDAF (benutzerdefinierte Aggregatfunktion),
  5. PPTF (Pipeline- und Paralleltabellenfunktion, prozedurale Lösung, aber mit nur zwei Cursorn, tatsächlich zwei SQL-Anweisungen),
  6. MODE (Modell, Phrase MODEL),
  7. und IDEA (ideal, eine ideale Lösung, die jetzt möglicherweise nicht funktioniert).

Mit Blick auf die Zukunft werde ich sagen, dass dies der seltene Fall ist, wenn die prozedurale PPTF-Lösung unter Oracle am effektivsten ist.

Laden Sie die Transaktionsdateien von http://api.bitcoincharts.com/v1/csv herunter
Ich empfehle die Auswahl von Kraken * -Dateien. Die localbtc * -Dateien sind sehr verrauscht - sie enthalten störende Zeilen mit unrealistischen Preisen. Alle kraken * enthalten ungefähr 31 Millionen Transaktionen. Ich empfehle, krakenEUR von dort auszuschließen, dann wird die Transaktion 11 Millionen. Dies ist das bequemste Volumen zum Testen.

Führen Sie in Powershell ein Skript aus, um Steuerdateien für SQLLDR für Oracle und eine Importanforderung für MSSQL zu generieren.

  # MODIFY PARAMETERS THERE $OracleConnectString = "THINNING/aaa@P-ORA11/ORCL" # For Oracle $PathToCSV = "Z:\10" # without trailing slash $filenames = Get-ChildItem -name *.csv Remove-Item *.ctl -ErrorAction SilentlyContinue Remove-Item *.log -ErrorAction SilentlyContinue Remove-Item *.bad -ErrorAction SilentlyContinue Remove-Item *.dsc -ErrorAction SilentlyContinue Remove-Item LoadData-Oracle.bat -ErrorAction SilentlyContinue Remove-Item LoadData-MSSQL.sql -ErrorAction SilentlyContinue ForEach ($FilenameExt in $Filenames) { Write-Host "Processing file: "$FilenameExt $StockName = $FilenameExt.substring(1, $FilenameExt.Length-5) $FilenameCtl = '.'+$Stockname+'.ctl' Add-Content -Path $FilenameCtl -Value "OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, ROWS=1000000, SKIP_INDEX_MAINTENANCE=Y)" Add-Content -Path $FilenameCtl -Value "UNRECOVERABLE" Add-Content -Path $FilenameCtl -Value "LOAD DATA" Add-Content -Path $FilenameCtl -Value "INFILE '.$StockName.csv'" Add-Content -Path $FilenameCtl -Value "BADFILE '.$StockName.bad'" Add-Content -Path $FilenameCtl -Value "DISCARDFILE '.$StockName.dsc'" Add-Content -Path $FilenameCtl -Value "INTO TABLE TRANSACTIONS_RAW" Add-Content -Path $FilenameCtl -Value "APPEND" Add-Content -Path $FilenameCtl -Value "FIELDS TERMINATED BY ','" Add-Content -Path $FilenameCtl -Value "(ID SEQUENCE (0), STOCK_NAME constant '$StockName', UT, APRICE, AVOLUME)" Add-Content -Path LoadData-Oracle.bat -Value "sqlldr $OracleConnectString control=$FilenameCtl" Add-Content -Path LoadData-MSSQL.sql -Value "insert into TRANSACTIONS_RAW (STOCK_NAME, UT, APRICE, AVOLUME)" Add-Content -Path LoadData-MSSQL.sql -Value "select '$StockName' as STOCK_NAME, UT, APRICE, AVOLUME" Add-Content -Path LoadData-MSSQL.sql -Value "from openrowset (bulk '$PathToCSV\$FilenameExt', formatfile = '$PathToCSV\format_mssql.bcp') as T1;" Add-Content -Path LoadData-MSSQL.sql -Value "" }

Erstellen wir eine Transaktionstabelle unter Oracle.

 create table TRANSACTIONS_RAW ( ID number not null , STOCK_NAME varchar2 (32) , UT number not null , APRICE number not null , AVOLUME number not null) pctfree 0 parallel 4 nologging;

Führen Sie unter Oracle die Datei LoadData-Oracle.bat aus , nachdem Sie zuvor die Verbindungsparameter am Anfang des Powershell-Skripts festgelegt haben.

Ich arbeite in einer virtuellen Maschine. Das Herunterladen aller 11 Millionen Transaktionsdateien in 8 kraken * -Dateien (ich habe die EUR-Datei übersprungen) dauerte ungefähr 1 Minute.

Und erstellen Sie Funktionen, mit denen Datumsangaben auf Intervallgrenzen gekürzt werden:

 create or replace function TRUNC_UT (p_UT number, p_StripeTypeId number) return number deterministic is begin return case p_StripeTypeId when 1 then trunc (p_UT / 1) * 1 when 2 then trunc (p_UT / 10) * 10 when 3 then trunc (p_UT / 60) * 60 when 4 then trunc (p_UT / 600) * 600 when 5 then trunc (p_UT / 3600) * 3600 when 6 then trunc (p_UT / ( 4 * 3600)) * ( 4 * 3600) when 7 then trunc (p_UT / (24 * 3600)) * (24 * 3600) when 8 then trunc ((trunc (date '1970-01-01' + p_UT / 86400, 'Month') - date '1970-01-01') * 86400) when 9 then trunc ((trunc (date '1970-01-01' + p_UT / 86400, 'year') - date '1970-01-01') * 86400) when 10 then 0 when 11 then 0 end; end; create or replace function UT2DATESTR (p_UT number) return varchar2 deterministic is begin return to_char (date '1970-01-01' + p_UT / 86400, 'YYYY.MM.DD HH24:MI:SS'); end;

Betrachten Sie die Optionen. Zuerst wird der Code aller Optionen angegeben, dann Skripte zum Starten und Testen. Zuerst wird die Aufgabe für Oracle und dann für MS SQL beschrieben

Option 1 - SIMP (Trivial)


Der gesamte Transaktionssatz wird mit dem kartesischen Produkt mit einem Satz von 10 Zeilen mit Zahlen von 1 bis 10 multipliziert. Dies ist erforderlich, um 10 Zeilen aus einer einzelnen Transaktionszeile mit Datumsangaben zu erhalten, die auf die Grenzen von 10 Intervallen abgeschnitten sind.

Danach werden die Zeilen nach der Intervallnummer und dem abgeschnittenen Datum gruppiert, und die obige Anforderung wird ausgeführt.

ANSICHT erstellen:

 create or replace view THINNING_HABR_SIMP_V as select STRIPE_ID , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID) as UT , avg (APRICE) keep (dense_rank first order by UT, ID) as AOPEN , max (APRICE) as AHIGH , min (APRICE) as ALOW , avg (APRICE) keep (dense_rank last order by UT, ID) as ACLOSE , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW , (select rownum as STRIPE_ID from dual connect by level <= 10) group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID);

Option 2 - CALC (iterativ berechnet)


Bei dieser Option werden Transaktionen iterativ auf Ebene 1, von Ebene 1 auf Ebene 2 usw. reduziert.

Erstellen Sie eine Tabelle:

 create table QUOTES_CALC ( STRIPE_ID number not null , STOCK_NAME varchar2 (128) not null , UT number not null , AOPEN number not null , AHIGH number not null , ALOW number not null , ACLOSE number not null , AVOLUME number not null , AAMOUNT number not null , ACOUNT number not null ) /*partition by list (STRIPE_ID) ( partition P01 values (1) , partition P02 values (2) , partition P03 values (3) , partition P04 values (4) , partition P05 values (5) , partition P06 values (6) , partition P07 values (7) , partition P08 values (8) , partition P09 values (9) , partition P10 values (10) )*/ parallel 4 pctfree 0 nologging;

Sie können einen Index mithilfe des Felds STRIPE_ID erstellen. Es wurde jedoch experimentell festgestellt, dass er für 11 Millionen Transaktionen ohne Index rentabler ist. Bei größeren Mengen kann sich die Situation ändern. Oder Sie können die Tabelle partitionieren, indem Sie den Block in der Abfrage auskommentieren.

Erstellen Sie eine Prozedur:

 create or replace procedure THINNING_HABR_CALC_T is begin rollback; execute immediate 'truncate table QUOTES_CALC'; insert --+ append into QUOTES_CALC select 1 as STRIPE_ID , STOCK_NAME , UT , avg (APRICE) keep (dense_rank first order by ID) , max (APRICE) , min (APRICE) , avg (APRICE) keep (dense_rank last order by ID) , sum (AVOLUME) , sum (APRICE * AVOLUME) , count (*) from TRANSACTIONS_RAW a group by STOCK_NAME, UT; commit; for i in 1..9 loop insert --+ append into QUOTES_CALC select --+ parallel(4) STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, i + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from QUOTES_CALC a where STRIPE_ID = i group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, i + 1); commit; end loop; end; /

Erstellen Sie aus Gründen der Symmetrie eine einfache ANSICHT:

 create view THINNING_HABR_CALC_V as select * from QUOTES_CALC;

Option 3 - CHIN (chinesischer Code)


Die Methode unterscheidet sich von der brutalen Geradlinigkeit des Ansatzes und ist die Ablehnung des Prinzips "Wiederhole dich nicht". In diesem Fall die Ablehnung von Zyklen.

Die Option wird hier nur der Vollständigkeit halber bereitgestellt.

Mit Blick auf die Zukunft werde ich sagen, dass es in Bezug auf die Leistung bei dieser speziellen Aufgabe den zweiten Platz einnimmt.

Große Anfrage
 create or replace view THINNING_HABR_CHIN_V as with T01 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select 1 , STOCK_NAME , UT , avg (APRICE) keep (dense_rank first order by ID) , max (APRICE) , min (APRICE) , avg (APRICE) keep (dense_rank last order by ID) , sum (AVOLUME) , sum (APRICE * AVOLUME) , count (*) from TRANSACTIONS_RAW group by STOCK_NAME, UT) , T02 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T01 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T03 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T02 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T04 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T03 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T05 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T04 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T06 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T05 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T07 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T06 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T08 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T07 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T09 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T08 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T10 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T09 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) select * from T01 union all select * from T02 union all select * from T03 union all select * from T04 union all select * from T05 union all select * from T06 union all select * from T07 union all select * from T08 union all select * from T09 union all select * from T10;


Option 4 - UDAF


Die Option mit benutzerdefinierter aggregierter Funktion wird hier nicht angegeben, kann jedoch auf github angezeigt werden.

Option 5 - PPTF (Pipeline- und Paralleltabellenfunktion)


Erstellen Sie eine Funktion (im Paket):

 create or replace package THINNING_PPTF_P is type TRANSACTION_RECORD_T is record (STOCK_NAME varchar2(128), UT number, SEQ_NUM number, APRICE number, AVOLUME number); type CUR_RECORD_T is ref cursor return TRANSACTION_RECORD_T; type QUOTE_T is record (STRIPE_ID number, STOCK_NAME varchar2(128), UT number , AOPEN number, AHIGH number, ALOW number, ACLOSE number, AVOLUME number , AAMOUNT number, ACOUNT number); type QUOTE_LIST_T is table of QUOTE_T; function F (p_cursor CUR_RECORD_T) return QUOTE_LIST_T pipelined order p_cursor by (STOCK_NAME, UT, SEQ_NUM) parallel_enable (partition p_cursor by hash (STOCK_NAME)); end; / create or replace package body THINNING_PPTF_P is function F (p_cursor CUR_RECORD_T) return QUOTE_LIST_T pipelined order p_cursor by (STOCK_NAME, UT, SEQ_NUM) parallel_enable (partition p_cursor by hash (STOCK_NAME)) is QuoteTail QUOTE_LIST_T := QUOTE_LIST_T() ; rec TRANSACTION_RECORD_T; rec_prev TRANSACTION_RECORD_T; type ut_T is table of number index by pls_integer; ut number; begin QuoteTail.extend(10); loop fetch p_cursor into rec; exit when p_cursor%notfound; if rec_prev.STOCK_NAME = rec.STOCK_NAME then if (rec.STOCK_NAME = rec_prev.STOCK_NAME and rec.UT < rec_prev.UT) or (rec.STOCK_NAME = rec_prev.STOCK_NAME and rec.UT = rec_prev.UT and rec.SEQ_NUM < rec_prev.SEQ_NUM) then raise_application_error (-20010, 'Rowset must be ordered, ('||rec_prev.STOCK_NAME||','||rec_prev.UT||','||rec_prev.SEQ_NUM||') > ('||rec.STOCK_NAME||','||rec.UT||','||rec.SEQ_NUM||')'); end if; end if; if rec.STOCK_NAME <> rec_prev.STOCK_NAME or rec_prev.STOCK_NAME is null then for j in 1 .. 10 loop if QuoteTail(j).UT is not null then pipe row (QuoteTail(j)); QuoteTail(j) := null; end if; end loop; end if; for i in reverse 1..10 loop ut := TRUNC_UT (rec.UT, i); if QuoteTail(i).UT <> ut then for j in 1..i loop pipe row (QuoteTail(j)); QuoteTail(j) := null; end loop; end if; if QuoteTail(i).UT is null then QuoteTail(i).STRIPE_ID := i; QuoteTail(i).STOCK_NAME := rec.STOCK_NAME; QuoteTail(i).UT := ut; QuoteTail(i).AOPEN := rec.APRICE; end if; if rec.APRICE < QuoteTail(i).ALOW or QuoteTail(i).ALOW is null then QuoteTail(i).ALOW := rec.APRICE; end if; if rec.APRICE > QuoteTail(i).AHIGH or QuoteTail(i).AHIGH is null then QuoteTail(i).AHIGH := rec.APRICE; end if; QuoteTail(i).AVOLUME := nvl (QuoteTail(i).AVOLUME, 0) + rec.AVOLUME; QuoteTail(i).AAMOUNT := nvl (QuoteTail(i).AAMOUNT, 0) + rec.AVOLUME * rec.APRICE; QuoteTail(i).ACOUNT := nvl (QuoteTail(i).ACOUNT, 0) + 1; QuoteTail(i).ACLOSE := rec.APRICE; end loop; rec_prev := rec; end loop; for j in 1 .. 10 loop if QuoteTail(j).UT is not null then pipe row (QuoteTail(j)); end if; end loop; exception when no_data_needed then null; end; end; /

ANSICHT erstellen:

 create or replace view THINNING_HABR_PPTF_V as select * from table (THINNING_PPTF_P.F (cursor (select STOCK_NAME, UT, ID, APRICE, AVOLUME from TRANSACTIONS_RAW)));

Option 6 - MODE (Modellklausel)


Die Option berechnet iterativ die Dezimierung für alle 10 Ebenen unter Verwendung der MODEL- Klauselphrase mit der Phrase ITERATE .

Die Option ist auch unpraktisch, weil sie langsam ist. In meiner Umgebung werden 1000 Transaktionen für 8 Instrumente in 1 Minute berechnet. Die meiste Zeit wird für die Berechnung der MODEL- Phrase aufgewendet.

Hier gebe ich diese Option nur der Vollständigkeit halber und als Bestätigung dafür an, dass unter Oracle fast alle beliebig komplexen Berechnungen mit einer Abfrage ohne Verwendung von PL / SQL durchgeführt werden können.

Einer der Gründe für die geringe Leistung der MODEL- Phrase in dieser Abfrage ist, dass die Suche auf der rechten Seite für jede Regel durchgeführt wird, die wir haben 6. Die ersten beiden Regeln werden ziemlich schnell berechnet, da es eine direkte explizite Adressierung ohne Joker gibt. In den verbleibenden vier Regeln gibt es das Wort any - dort sind die Berechnungen langsamer.

Die zweite Schwierigkeit besteht darin, dass Sie das Referenzmodell berechnen müssen. Dies ist erforderlich, da die Dimensionsliste vor der Berechnung der MODEL- Phrase bekannt sein muss. Wir können keine neuen Dimensionen innerhalb dieser Phrase berechnen. Vielleicht kann dies mit Hilfe von zwei MODEL-Phrasen umgangen werden, aber ich habe dies wegen der geringen Leistung einer großen Anzahl von Regeln nicht getan.

Ich füge hinzu, dass es möglich wäre, UT_OPEN und UT_CLOSE nicht im Referenzmodell zu berechnen, sondern dieselben Funktionen zu verwenden. Avg () keep (dens_rank first / last order by) direkt in der MODEL- Phrase. Aber es wäre noch langsamer gegangen.
Aufgrund von Leistungsbeschränkungen werde ich diese Option nicht in das Testverfahren einbeziehen.

 with --       SOURCETRANS (STRIPE_ID, STOCK_NAME, PARENT_UT, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select 1, STOCK_NAME, TRUNC_UT (UT, 2), UT , avg (APRICE) keep (dense_rank first order by ID) , max (APRICE) , min (APRICE) , avg (APRICE) keep (dense_rank last order by ID) , sum (AVOLUME) , sum (AVOLUME * APRICE) , count (*) from TRANSACTIONS_RAW where ID <= 1000 --       group by STOCK_NAME, UT) --   PARENT_UT, UT  2...10    UT_OPEN, UT_CLOSE --    , REFMOD (STRIPE_ID, STOCK_NAME, PARENT_UT, UT, UT_OPEN, UT_CLOSE) as (select b.STRIPE_ID , a.STOCK_NAME , TRUNC_UT (UT, b.STRIPE_ID + 1) , TRUNC_UT (UT, b.STRIPE_ID) , min (TRUNC_UT (UT, b.STRIPE_ID - 1)) , max (TRUNC_UT (UT, b.STRIPE_ID - 1)) from SOURCETRANS a , (select rownum + 1 as STRIPE_ID from dual connect by level <= 9) b group by b.STRIPE_ID , a.STOCK_NAME , TRUNC_UT (UT, b.STRIPE_ID + 1) , TRUNC_UT (UT, b.STRIPE_ID)) --        , MAINTAB as ( select STRIPE_ID, STOCK_NAME, PARENT_UT, UT, AOPEN , AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT, null, null from SOURCETRANS union all select STRIPE_ID, STOCK_NAME, PARENT_UT, UT, null , null, null, null, null, null, null, UT_OPEN, UT_CLOSE from REFMOD) select STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT from MAINTAB model return all rows --      2...10 reference RM on (select * from REFMOD) dimension by (STRIPE_ID, STOCK_NAME, UT) measures (UT_OPEN, UT_CLOSE) main MM partition by (STOCK_NAME) dimension by (STRIPE_ID, PARENT_UT, UT) measures (AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) rules iterate (9) ( AOPEN [iteration_number + 2, any, any] = AOPEN [cv (STRIPE_ID) - 1, cv (UT) , rm.UT_OPEN [cv (STRIPE_ID), cv (STOCK_NAME), cv (UT)]] , ACLOSE [iteration_number + 2, any, any] = ACLOSE [cv (STRIPE_ID) - 1, cv (UT) , rm.UT_CLOSE[cv (STRIPE_ID), cv (STOCK_NAME), cv (UT)]] , AHIGH [iteration_number + 2, any, any] = max (AHIGH)[cv (STRIPE_ID) - 1, cv (UT), any] , ALOW [iteration_number + 2, any, any] = min (ALOW)[cv (STRIPE_ID) - 1, cv (UT), any] , AVOLUME [iteration_number + 2, any, any] = sum (AVOLUME)[cv (STRIPE_ID) - 1, cv (UT), any] , AAMOUNT [iteration_number + 2, any, any] = sum (AAMOUNT)[cv (STRIPE_ID) - 1, cv (UT), any] , ACOUNT [iteration_number + 2, any, any] = sum (ACOUNT)[cv (STRIPE_ID) - 1, cv (UT), any] ) order by 1, 2, 3, 4;

Option 6 - IDEE (ideal, ideal, aber nicht funktionsfähig)


Die unten beschriebene Anforderung wäre möglicherweise die effizienteste und würde die Menge an Ressourcen verbrauchen, die dem theoretischen Minimum entspricht.

Weder Oracle noch MS SQL erlauben es Ihnen, eine Abfrage in dieser Form zu schreiben. Ich glaube, das wird vom Standard vorgegeben.

 with QUOTES_S1 as (select 1 as STRIPE_ID , STOCK_NAME , TRUNC_UT (UT, 1) as UT , avg (APRICE) keep (dense_rank first order by ID) as AOPEN , max (APRICE) as AHIGH , min (APRICE) as ALOW , avg (APRICE) keep (dense_rank last order by ID) as ACLOSE , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW -- where rownum <= 100 group by STOCK_NAME, TRUNC_UT (UT, 1)) , T1 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select 1, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT from QUOTES_S1 union all select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T1 where STRIPE_ID < 10 group by STRIPE_ID + 1, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1) ) select * from T1

Diese Abfrage entspricht dem folgenden Teil der Oracle-Dokumentation:

Wenn eine Unterabfrage_Faktorklausel auf ihren eigenen Abfragenamen in der Unterabfrage verweist, die sie definiert, wird die Unterabfrage_Faktorklausel als rekursiv bezeichnet. Eine rekursive subquery_factoring_clause muss zwei Abfrageblöcke enthalten: Der erste ist das Ankerelement und der zweite ist das rekursive Element. Das Ankerelement muss vor dem rekursiven Element erscheinen und kann nicht auf den Abfragenamen verweisen. Das Ankerelement kann aus einem oder mehreren Abfrageblöcken bestehen, die von den Set-Operatoren kombiniert werden: UNION ALL, UNION, INTERSECT oder MINUS. Das rekursive Element muss dem Ankerelement folgen und genau einmal auf query_name verweisen. Sie müssen das rekursive Element mit dem Ankerelement mithilfe des Set-Operators UNION ALL kombinieren.

Dies widerspricht jedoch dem folgenden Absatz der Dokumentation:

Das rekursive Element darf keines der folgenden Elemente enthalten:
Das Schlüsselwort DISTINCT oder eine GROUP BY-Klausel
Eine Aggregatfunktion. In der Auswahlliste sind jedoch Analysefunktionen zulässig.

Daher sind im rekursiven Element Aggregate und Gruppierungen nicht zulässig.

Testen



Machen wir es zuerst für Oracle .

Führen Sie das Berechnungsverfahren für die CALC-Methode durch und notieren Sie den Zeitpunkt ihrer Ausführung:

 exec THINNING_HABR_CALC_T

Die Berechnungsergebnisse für die vier Methoden sind in vier Ansichten dargestellt:

  • THINNING_HABR_SIMP_V (führt die Berechnung durch und verursacht ein komplexes SELECT, daher dauert es lange),
  • THINNING_HABR_CALC_V (zeigt die Daten aus der Tabelle QUOTES_CALC an, sodass sie schnell ausgeführt werden können)
  • THINNING_HABR_CHIN_V (führt auch die Berechnung durch und verursacht ein komplexes SELECT, daher dauert es lange),
  • THINNING_HABR_PPTF_V (führt die Funktion THINNING_HABR_PPTF aus).

Die Vorlaufzeit für alle Methoden wurde bereits von mir gemessen und ist in der Tabelle am Ende des Artikels angegeben.

Für den Rest von VIEW führen wir die Anforderungen aus und schreiben die Ausführungszeit:

 select count (*) as CNT , sum (STRIPE_ID) as S_STRIPE_ID, sum (UT) as S_UT , sum (AOPEN) as S_AOPEN, sum (AHIGH) as S_AHIGH, sum (ALOW) as S_ALOW , sum (ACLOSE) as S_ACLOSE, sum (AVOLUME) as S_AVOLUME , sum (AAMOUNT) as S_AAMOUNT, sum (ACOUNT) as S_ACOUNT from THINNING_HABR_XXXX_V

wobei XXXX SIMP, CHIN, PPTF ist.

Diese VIEWs berechnen die Rekrutierungsübersicht. Um den Digest zu berechnen, müssen Sie alle Zeilen abrufen und mit dem Digest können Sie die Sets miteinander vergleichen.

Sie können Mengen auch mit dem Paket dbms_sqlhash vergleichen. Dies ist jedoch viel langsamer, da Sie die ursprüngliche Menge sortieren müssen und die Hash-Berechnung nicht schnell ist.
Auch in 12c gibt es ein Paket DBMS_COMPARISON.

Sie können die Richtigkeit aller Algorithmen gleichzeitig überprüfen. Wir betrachten die Digests als eine solche Anforderung (bei 11 Millionen Einträgen in einer virtuellen Maschine ist dies relativ lang, etwa 15 Minuten):

 with T1 as (select 'SIMP' as ALG_NAME, a.* from THINNING_HABR_SIMP_V a union all select 'CALC', a.* from THINNING_HABR_CALC_V a union all select 'CHIN', a.* from THINNING_HABR_CHIN_V a union all select 'PPTF', a.* from THINNING_HABR_PPTF_V a) select ALG_NAME , count (*) as CNT , sum (STRIPE_ID) as S_STRIPE_ID, sum (UT) as S_UT , sum (AOPEN) as S_AOPEN, sum (AHIGH) as S_AHIGH, sum (ALOW) as S_ALOW , sum (ACLOSE) as S_ACLOSE, sum (AVOLUME) as S_AVOLUME , sum (AAMOUNT) as S_AAMOUNT, sum (ACOUNT) as S_ACOUNT from T1 group by ALG_NAME;

Wir sehen, dass die Digests gleich sind, sodass alle Algorithmen die gleichen Ergebnisse lieferten.

Jetzt werden wir trotzdem in MS SQL reproduzieren. Ich habe auf Version 2016 getestet.

Erstellen Sie zuerst die DBTEST-Datenbank und anschließend eine Transaktionstabelle:

 use DBTEST go create table TRANSACTIONS_RAW ( STOCK_NAME varchar (32) not null , UT int not null , APRICE numeric (22, 12) not null , AVOLUME numeric (22, 12) not null , ID bigint identity not null );

Laden Sie die heruntergeladenen Daten herunter.

Erstellen Sie in MSSQL die Datei format_mssql.bcp:

 12.0 3 1 SQLCHAR 0 0 "," 3 UT "" 2 SQLCHAR 0 0 "," 4 APRICE "" 3 SQLCHAR 0 0 "\n" 5 AVOLUME ""

Führen Sie das Skript LoadData-MSSQL.sql in SSMS aus (dieses Skript wurde von dem einzigen Powershell-Skript generiert, das im Abschnitt dieses Artikels für Oracle bereitgestellt wird).

Lassen Sie uns zwei Funktionen erstellen:

 use DBTEST go create or alter function TRUNC_UT (@p_UT bigint, @p_StripeTypeId int) returns bigint as begin return case @p_StripeTypeId when 1 then @p_UT when 2 then @p_UT / 10 * 10 when 3 then @p_UT / 60 * 60 when 4 then @p_UT / 600 * 600 when 5 then @p_UT / 3600 * 3600 when 6 then @p_UT / 14400 * 14400 when 7 then @p_UT / 86400 * 86400 when 8 then datediff (second, cast ('1970-01-01 00:00:00' as datetime), dateadd(m, datediff (m, 0, dateadd (second, @p_UT, cast ('1970-01-01 00:00:00' as datetime))), 0)) when 9 then datediff (second, cast ('1970-01-01 00:00:00' as datetime), dateadd(yy, datediff (yy, 0, dateadd (second, @p_UT, cast ('1970-01-01 00:00:00' as datetime))), 0)) when 10 then 0 when 11 then 0 end; end; go create or alter function UT2DATESTR (@p_UT bigint) returns datetime as begin return dateadd(s, @p_UT, cast ('1970-01-01 00:00:00' as datetime)); end; go

:

1 — SIMP


:

 use DBTEST go create or alter view dbo.THINNING_HABR_SIMP_V as with T1 (STRIPE_ID) as (select 1 union all select STRIPE_ID + 1 from T1 where STRIPE_ID < 10) , T2 as (select STRIPE_ID , STOCK_NAME , dbo.TRUNC_UT (UT, STRIPE_ID) as UT , min (1000000 * cast (UT as bigint) + ID) as AOPEN_UT , max (APRICE) as AHIGH , min (APRICE) as ALOW , max (1000000 * cast (UT as bigint) + ID) as ACLOSE_UT , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW, T1 group by STRIPE_ID, STOCK_NAME, dbo.TRUNC_UT (UT, STRIPE_ID)) select t.STRIPE_ID, t.STOCK_NAME, t.UT, t_op.APRICE as AOPEN, t.AHIGH , t.ALOW, t_cl.APRICE as ACLOSE, t.AVOLUME, t.AAMOUNT, t.ACOUNT from T2 t join TRANSACTIONS_RAW t_op on (t.STOCK_NAME = t_op.STOCK_NAME and t.AOPEN_UT / 1000000 = t_op.UT and t.AOPEN_UT % 1000000 = t_op.ID) join TRANSACTIONS_RAW t_cl on (t.STOCK_NAME = t_cl.STOCK_NAME and t.ACLOSE_UT / 1000000 = t_cl.UT and t.ACLOSE_UT % 1000000 = t_cl.ID);

first/last .

2 — CALC


, view:

 use DBTEST go create table dbo.QUOTES_CALC ( STRIPE_ID int not null , STOCK_NAME varchar(32) not null , UT bigint not null , AOPEN numeric (22, 12) not null , AHIGH numeric (22, 12) not null , ALOW numeric (22, 12) not null , ACLOSE numeric (22, 12) not null , AVOLUME numeric (38, 12) not null , AAMOUNT numeric (38, 12) not null , ACOUNT int not null ); go create or alter procedure dbo.THINNING_HABR_CALC as begin set nocount on; truncate table QUOTES_CALC; declare @StripeId int; with T1 as (select STOCK_NAME , UT , min (ID) as AOPEN_ID , max (APRICE) as AHIGH , min (APRICE) as ALOW , max (ID) as ACLOSE_ID , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW group by STOCK_NAME, UT) insert into QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) select 1, t.STOCK_NAME, t.UT, t_op.APRICE, t.AHIGH, t.ALOW, t_cl.APRICE, t.AVOLUME, t.AAMOUNT, t.ACOUNT from T1 t join TRANSACTIONS_RAW t_op on (t.STOCK_NAME = t_op.STOCK_NAME and t.UT = t_op.UT and t.AOPEN_ID = t_op.ID) join TRANSACTIONS_RAW t_cl on (t.STOCK_NAME = t_cl.STOCK_NAME and t.UT = t_cl.UT and t.ACLOSE_ID = t_cl.ID); set @StripeId = 1; while (@StripeId <= 9) begin with T1 as (select STOCK_NAME , dbo.TRUNC_UT (UT, @StripeId + 1) as UT , min (UT) as AOPEN_UT , max (AHIGH) as AHIGH , min (ALOW) as ALOW , max (UT) as ACLOSE_UT , sum (AVOLUME) as AVOLUME , sum (AAMOUNT) as AAMOUNT , sum (ACOUNT) as ACOUNT from QUOTES_CALC where STRIPE_ID = @StripeId group by STOCK_NAME, dbo.TRUNC_UT (UT, @StripeId + 1)) insert into QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) select @StripeId + 1, t.STOCK_NAME, t.UT, t_op.AOPEN, t.AHIGH, t.ALOW, t_cl.ACLOSE, t.AVOLUME, t.AAMOUNT, t.ACOUNT from T1 t join QUOTES_CALC t_op on (t.STOCK_NAME = t_op.STOCK_NAME and t.AOPEN_UT = t_op.UT) join QUOTES_CALC t_cl on (t.STOCK_NAME = t_cl.STOCK_NAME and t.ACLOSE_UT = t_cl.UT) where t_op.STRIPE_ID = @StripeId and t_cl.STRIPE_ID = @StripeId; set @StripeId = @StripeId + 1; end; end; go create or alter view dbo.THINNING_HABR_CALC_V as select * from dbo.QUOTES_CALC; go

3 (CHIN) 4 (UDAF) MS SQL.

5 — PPTF


view. , parallel pipelined table function, Oracle:

 use DBTEST go create or alter function dbo.THINNING_HABR_PPTF () returns @rettab table ( STRIPE_ID bigint not null , STOCK_NAME varchar(32) not null , UT bigint not null , AOPEN numeric (22, 12) not null , AHIGH numeric (22, 12) not null , ALOW numeric (22, 12) not null , ACLOSE numeric (22, 12) not null , AVOLUME numeric (38, 12) not null , AAMOUNT numeric (38, 12) not null , ACOUNT bigint not null) as begin declare @i tinyint; declare @tut int; declare @trans_STOCK_NAME varchar(32); declare @trans_UT int; declare @trans_ID int; declare @trans_APRICE numeric (22,12); declare @trans_AVOLUME numeric (22,12); declare @trans_prev_STOCK_NAME varchar(32); declare @trans_prev_UT int; declare @trans_prev_ID int; declare @trans_prev_APRICE numeric (22,12); declare @trans_prev_AVOLUME numeric (22,12); declare @QuoteTail table ( STRIPE_ID bigint not null primary key clustered , STOCK_NAME varchar(32) not null , UT bigint not null , AOPEN numeric (22, 12) not null , AHIGH numeric (22, 12) , ALOW numeric (22, 12) , ACLOSE numeric (22, 12) , AVOLUME numeric (38, 12) not null , AAMOUNT numeric (38, 12) not null , ACOUNT bigint not null); declare c cursor fast_forward for select STOCK_NAME, UT, ID, APRICE, AVOLUME from TRANSACTIONS_RAW order by STOCK_NAME, UT, ID; -- THIS ORDERING (STOCK_NAME, UT, ID) IS MANDATORY open c; fetch next from c into @trans_STOCK_NAME, @trans_UT, @trans_ID, @trans_APRICE, @trans_AVOLUME; while @@fetch_status = 0 begin if @trans_STOCK_NAME <> @trans_prev_STOCK_NAME or @trans_prev_STOCK_NAME is null begin insert into @rettab select * from @QuoteTail; delete @QuoteTail; end; set @i = 10; while @i >= 1 begin set @tut = dbo.TRUNC_UT (@trans_UT, @i); if @tut <> (select UT from @QuoteTail where STRIPE_ID = @i) begin insert into @rettab select * from @QuoteTail where STRIPE_ID <= @i; delete @QuoteTail where STRIPE_ID <= @i; end; if (select count (*) from @QuoteTail where STRIPE_ID = @i) = 0 begin insert into @QuoteTail (STRIPE_ID, STOCK_NAME, UT, AOPEN, AVOLUME, AAMOUNT, ACOUNT) values (@i, @trans_STOCK_NAME, @tut, @trans_APRICE, 0, 0, 0); end; update @QuoteTail set AHIGH = case when AHIGH < @trans_APRICE or AHIGH is null then @trans_APRICE else AHIGH end , ALOW = case when ALOW > @trans_APRICE or ALOW is null then @trans_APRICE else ALOW end , ACLOSE = @trans_APRICE, AVOLUME = AVOLUME + @trans_AVOLUME , AAMOUNT = AAMOUNT + @trans_APRICE * @trans_AVOLUME , ACOUNT = ACOUNT + 1 where STRIPE_ID = @i; set @i = @i - 1; end; set @trans_prev_STOCK_NAME = @trans_STOCK_NAME; set @trans_prev_UT = @trans_UT; set @trans_prev_ID = @trans_ID; set @trans_prev_APRICE = @trans_APRICE; set @trans_prev_AVOLUME = @trans_AVOLUME; fetch next from c into @trans_STOCK_NAME, @trans_UT, @trans_ID, @trans_APRICE, @trans_AVOLUME; end; close c; deallocate c; insert into @rettab select * from @QuoteTail; return; end go create or alter view dbo.THINNING_HABR_PPTF_V as select * from dbo.THINNING_HABR_PPTF ();

QUOTES_CALC CALC :
 use DBTEST go exec dbo.THINNING_HABR_CALC

view:

  • THINNING_HABR_SIMP_V ( , SELECT, ),
  • THINNING_HABR_CALC_V ( QUOTES_CALC, )
  • THINNING_HABR_PPTF_V ( THINNING_HABR_PPTF).

VIEW :

 select count (*) as CNT , sum (STRIPE_ID) as S_STRIPE_ID, sum (UT) as S_UT , sum (AOPEN) as S_AOPEN, sum (AHIGH) as S_AHIGH, sum (ALOW) as S_ALOW , sum (ACLOSE) as S_ACLOSE, sum (AVOLUME) as S_AVOLUME , sum (AAMOUNT) as S_AAMOUNT, sum (ACOUNT) as S_ACOUNT from THINNING_HABR_XXXX_V

XXXX — SIMP, PPTF.

MS SQL. . :

 use DBTEST go with T1 as (select 'SIMP' as ALG_NAME, a.* from THINNING_HABR_SIMP_V a union all select 'CALC', a.* from THINNING_HABR_CALC_V a union all select 'PPTF', a.* from THINNING_HABR_PPTF_V a) select ALG_NAME , count (*) as CNT, sum (cast (STRIPE_ID as bigint)) as STRIPE_ID , sum (cast (UT as bigint)) as UT, sum (AOPEN) as AOPEN , sum (AHIGH) as AHIGH, sum (ALOW) as ALOW, sum (ACLOSE) as ACLOSE, sum (AVOLUME) as AVOLUME , sum (AAMOUNT) as AAMOUNT, sum (cast (ACOUNT as bigint)) as ACOUNT from T1 group by ALG_NAME;

— .

, , MS SQL .

MS SQL, , : :

 create unique clustered index TRANSACTIONS_RAW_I1 on TRANSACTIONS_RAW (STOCK_NAME, UT, ID); create unique clustered index QUOTES_CALC_I1 on QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT);

Die Ergebnisse der Leistungsmessung auf meiner virtuellen Maschine lauten wie folgt:

Bild

github : Oracle, THINNING — , THINNING_LIVE — - bitcoincharts.com - ( - 5 ), MS SQL .

:

Oracle, MS SQL. .

Oracle PPTF. , . — 367 ( PPTF ).

MS SQL (CALC).

PPTF Oracle ? - - — , parallel pipelined table function, :

Bild

Source: https://habr.com/ru/post/de418757/

More articles:


All Articles