Ausdünnungszeitraum (Kryptowährung, Forex, Börse)

Vor einiger Zeit wurde ich beauftragt, eine Prozedur zu schreiben, die das Ausdünnen der Forex-Marktkurse (genauer der Zeitrahmendaten) durchführt.

Task Statement: Die Daten werden im Sekundentakt in diesem Format an die Eingabe gesendet:

  • Gerätename (USDEUR-Paarcode usw.),
  • Datum und Uhrzeit im Unix-Zeitformat,
  • Offener Wert (Preis der ersten Transaktion im Intervall),
  • Hoher Wert (Maximalpreis)
  • Niedriger Wert (Mindestpreis)
  • Wert schließen (letzter Transaktionspreis),
  • Volumen (Volumen oder Volumen der Transaktion).

Die Daten müssen in den Tabellen neu berechnet und synchronisiert werden: 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. Sie wird häufig verwendet und es ist sofort möglich, die Grafik "Japanische Kerzen" zu erstellen.

Bild

Unter dem Schnitt habe ich alle Optionen beschrieben, die ich mir vorstellen konnte, wie man die erhaltenen Daten ausdünnen (vergrößern) kann, um beispielsweise den Wintersprung bei den Bitcoin-Preisen zu analysieren, und aus den erhaltenen Daten wird sofort das Diagramm "Japanische Kerzen" erstellt (auch in Excel). ). In der Abbildung oben ist diese Grafik für den Zeitraum von 1 Monat für das Werkzeug „bitstampUSD“ erstellt. Der weiße Körper der Kerze bedeutet einen Preisanstieg im Intervall, schwarz - einen Preisrückgang, der obere und der untere Docht bedeuten die maximalen und minimalen Preise, die im Intervall erreicht wurden. Hintergrund - das Transaktionsvolumen. Es ist deutlich zu sehen, dass der Preis im Dezember 2017 nahe an die 20K-Marke kam.

Die Lösung wird für die beiden 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 nicht mit anderen Aufgaben verallgemeinern).

Dann habe ich das Problem auf triviale Weise gelöst: Die korrekte Ausdünnung in eine temporäre Tabelle berechnen und mit der Zieltabelle synchronisieren - Zeilen löschen, 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 in der Zieltabelle vorhanden sind. Zu dieser Zeit war der Kunde mit der Entscheidung zufrieden und ich habe die Aufgabe geschlossen.

Nun entschied ich mich jedoch, alle Optionen in Betracht zu ziehen, weil die oben genannte Lösung eine Funktion enthält - es ist schwierig, sie 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 Abschnitten hinzufügen müssen.

Dies ist auf die Tatsache zurückzuführen, dass die Prozedur die Zieltabelle und die temporäre Tabelle verbinden muss, und Sie müssen sich mit der größeren Tabelle verbinden, und nicht umgekehrt. In den beiden obigen Fällen werden die größeren / kleineren vertauscht. Das Optimierungsprogramm entscheidet über die Join-Reihenfolge auf der Grundlage von Statistiken. Die Statistiken sind möglicherweise veraltet, und die Entscheidung kann falsch getroffen werden, was zu erheblichen Leistungseinbußen führt.

In diesem Artikel werde ich die Methoden des einmaligen Ausdünnens beschreiben, die für Leser nützlich sein können, um beispielsweise den Wintersprung des Bitcoin-Preises zu analysieren.

Verfahren zum Online-Ausdünnen können Sie über den Link am Ende des Artikels von github herunterladen.

Auf den Punkt ... Meine Aufgabe betraf das Ausdünnen vom "1-sek" -Zeitrahmen bis zum folgenden, aber hier betrachte ich das Ausdünnen von der Transaktionsebene aus (in der Quelltabelle die Felder STOCK_NAME, UT, ID, APRICE, AVOLUME). Denn solche Daten gibt die Site bitcoincharts.com.
Tatsächlich wird das Ausdünnen von der Transaktionsebene auf die Ebene "1 Sekunde" durch einen solchen Befehl ausgeführt (der Operator lässt sich leicht in die Ausdünnung von der Ebene "1 Sekunde" in die oberen Ebenen umwandeln):

Unter Oracle:

select1as STRIPE_ID
     , STOCK_NAME
     , TRUNC_UT (UT, 1)                                     as UT
     , avg (APRICE) keep (dense_rankfirstorderby UT, ID) as AOPEN
     , max (APRICE)                                         as AHIGH
     , min (APRICE)                                         as ALOW
     , avg (APRICE) keep (dense_ranklastorderby UT, ID) as ACLOSE
     , sum (AVOLUME)                                        as AVOLUME
     , sum (APRICE * AVOLUME)                               as AAMOUNT
     , count (*)                                            as ACOUNT
from TRANSACTIONS_RAW
groupby STOCK_NAME, TRUNC_UT (UT, 1);

Die Funktion avg () keep (dense_rank erster Ordnung nach UT, ID) funktioniert folgendermaßen: Da die Abfrage nach GROUP BY gruppiert wird, wird jede Gruppe unabhängig von den anderen berechnet. Innerhalb jeder Gruppe werden die Zeilen nach UT und ID sortiert und mit der Funktion dense_rank nummeriert . Da next die Funktion zuerst ist, wird die Zeile ausgewählt, in der derse_rank 1 zurückgegeben wurde ( dh das Minimum wurde ausgewählt). Die erste Transaktion wird innerhalb des Intervalls ausgewählt. Wenn für diese minimale UT, ID mehrere Zeilen vorhanden wären, würde dies als Durchschnitt angesehen. 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 sehen, dass die erste Funktion die beiden Aggregate ersetzt.

Zu MS SQL

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

Ich werde die Anfrage nicht separat geben, sie kann jedoch unten in der Prozedur dbo.THINNING_HABR_CALC angezeigt werden . 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 es sich um einen Operator handelt, sondern um Zyklen, die die Daten in einer Zeile „ziehen“.)

Ich werde alle Lösungen für dieses Problem auflisten, die ich kenne:

  1. SIMP (einfaches, einfaches, kartesisches Produkt),
  2. CALC (berechnen, iteratives Ausdünnen der oberen Ebenen),
  3. CHIN (China Weg, umständliche Anfrage für alle Ebenen gleichzeitig),
  4. UDAF (benutzerdefinierte Aggregatfunktion),
  5. PPTF (Pipeline- und Paralleltabellenfunktion, prozedurale Lösung, jedoch mit nur zwei Cursorn, tatsächlich zwei SQL-Anweisungen),
  6. MODE (Modell, MODEL-Satz),
  7. und IDEA (ideal, die ideale Lösung, die jetzt nicht funktionieren kann).

Vorausschauend sagen Sie, dass dies der seltene Fall ist, wenn die prozedurale Lösung PPTF bei Oracle die effektivste ist.

Laden Sie die Transaktionsdateien von http://api.bitcoincharts.com/v1/csv herunter.
Ich empfehle, Kraken * -Dateien auszuwählen. Dateien localbtc * sind sehr laut - sie enthalten ablenkende Zeilen mit unrealistischen Preisen. Alle Kraken * enthalten etwa 31 Millionen Transaktionen. Ich empfehle, KrakenEUR von dort auszuschließen. Die Transaktion wird dann zu 11 Millionen. Dies ist das bequemste Volumen zum Testen.

Führen Sie das Skript in Powershell 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 ($FilenameExtin$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 Sie eine Transaktionstabelle in Oracle.

createtable TRANSACTIONS_RAW (
      IDnumbernotnull
    , STOCK_NAME    varchar2 (32)
    , UT            numbernotnull
    , APRICE        numbernotnull
    , AVOLUME       numbernotnull)
pctfree 0parallel4 nologging;

Starten Sie unter Oracle die Datei LoadData-Oracle.bat , nachdem Sie zuvor die Verbindungsparameter am Anfang des Powershell-Skripts korrigiert haben.

Ich arbeite in einer virtuellen Maschine. Das Herunterladen aller 11M-Transaktionsdateien in 8 Kraken * -Dateien (ich habe die EUR-Datei vermisst) dauerte etwa 1 Minute.

Und erstellen Sie Funktionen, die Datumsangaben an den Grenzen der Intervalle abschneiden:

createorreplacefunction TRUNC_UT (p_UT number, p_StripeTypeId number)
returnnumberdeterministicisbeginreturncase p_StripeTypeId
    when1then trunc (p_UT / 1) * 1when2then trunc (p_UT / 10) * 10when3then trunc (p_UT / 60) * 60when4then trunc (p_UT / 600) * 600when5then trunc (p_UT / 3600) * 3600when6then trunc (p_UT / ( 4 * 3600)) * ( 4 * 3600)
    when7then trunc (p_UT / (24 * 3600)) * (24 * 3600)
    when8then trunc ((trunc (date'1970-01-01' + p_UT / 86400, 'Month') - date'1970-01-01') * 86400)
    when9then trunc ((trunc (date'1970-01-01' + p_UT / 86400, 'year')  - date'1970-01-01') * 86400)
    when10then0when11then0end;
end;
createorreplacefunction UT2DATESTR (p_UT number) return varchar2 deterministicisbeginreturn to_char (date'1970-01-01' + p_UT / 86400, 'YYYY.MM.DD HH24:MI:SS');
end;

Betrachten Sie die Optionen. Zuerst wird der Code für alle Optionen bereitgestellt, dann Skripts zum Starten und Testen. Zuerst wird die Aufgabe für Oracle beschrieben, dann für MS SQL

Option 1 - SIMP (Trivial)


Der gesamte Satz von Transaktionen wird mit einem kartesischen Produkt in einen Satz von 10 Zeilen mit Zahlen von 1 bis 10 multipliziert. Dies ist erforderlich, um 10 Zeilen mit Datumsangaben aus einer einzigen Transaktionszeile auf die Grenzen von 10 Intervallen abzuschneiden.

Danach werden die Zeilen nach Intervallnummer und abgeschnittenem Datum gruppiert und die obige Abfrage wird ausgeführt.

Erstellen Sie eine VIEW:

createorreplaceview THINNING_HABR_SIMP_V asselect STRIPE_ID
     , STOCK_NAME
     , TRUNC_UT (UT, STRIPE_ID)                             as UT
     , avg (APRICE) keep (dense_rankfirstorderby UT, ID) as AOPEN
     , max (APRICE)                                         as AHIGH
     , min (APRICE)                                         as ALOW
     , avg (APRICE) keep (dense_ranklastorderby UT, ID) as ACLOSE
     , sum (AVOLUME)                                        as AVOLUME
     , sum (APRICE * AVOLUME)                               as AAMOUNT
     , count (*)                                            as ACOUNT
from TRANSACTIONS_RAW
  , (selectrownumas STRIPE_ID from dual connectbylevel <= 10)
groupby STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID);

Option 2 - CALC (iterativ berechnet)


In dieser Ausführungsform verdünnen wir iterativ von Transaktionen zur Ebene 1, von Ebene 1 zu Ebene 2 usw.

Erstellen Sie eine Tabelle:

createtable QUOTES_CALC (
      STRIPE_ID     numbernotnull
    , STOCK_NAME    varchar2 (128) notnull
    , UT            numbernotnull
    , AOPEN         numbernotnull
    , AHIGH         numbernotnull
    , ALOW          numbernotnull
    , ACLOSE        numbernotnull
    , AVOLUME       numbernotnull
    , AAMOUNT       numbernotnull
    , ACOUNT        numbernotnull
)
/*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)
)*/parallel4 pctfree 0 nologging;

Sie können einen Index für das Feld STRIPE_ID erstellen. Es wurde jedoch experimentell festgestellt, dass sich das 11-Millionen-Volumen von Transaktionen ohne Index als rentabler erweist. Bei der Verwendung auf lshih Zahlen könnte sich ändern. Sie können eine Tabelle partitionieren, indem Sie einen Block in einer Abfrage auskommentieren.

Erstellen Sie eine Prozedur:

createorreplaceprocedure THINNING_HABR_CALC_T isbeginrollback;
    executeimmediate'truncate table QUOTES_CALC';
    insert--+ appendinto QUOTES_CALC
    select1as STRIPE_ID
         , STOCK_NAME
         , UT
         , avg (APRICE) keep (dense_rankfirstorderbyID)
         , max (APRICE)
         , min (APRICE)
         , avg (APRICE) keep (dense_ranklastorderbyID)
         , sum (AVOLUME)
         , sum (APRICE * AVOLUME)
         , count (*)
    from TRANSACTIONS_RAW a
    groupby STOCK_NAME, UT;
    commit;
    for i in 1..9
    loop
        insert--+ appendinto QUOTES_CALC
        select--+ parallel(4)
               STRIPE_ID + 1
             , STOCK_NAME
             , TRUNC_UT (UT, i + 1)
             , avg (AOPEN)   keep (dense_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE)  keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from QUOTES_CALC a
        where STRIPE_ID = i
        groupby STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, i + 1);
        commit;
    endloop;
end;
/

Erstellen Sie für die Symmetrie eine einfache VIEW:

createview THINNING_HABR_CALC_V asselect * from QUOTES_CALC;

Option 3 - CHIN (chinesischer Code)


Die Methode ist ein brutaler, geradliniger Ansatz und besteht darin, das Prinzip "sich nicht wiederholen" aufzugeben. In diesem Fall erfolgt die Ablehnung von Zyklen.

Eine Variante wird hier nur der Vollständigkeit halber angegeben.

Mit Blick auf die Zukunft werde ich sagen, dass die Leistung bei dieser speziellen Aufgabe an zweiter Stelle steht.

Große Bitte
createorreplaceview THINNING_HABR_CHIN_V aswith
  T01 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT)
   as (select1
            , STOCK_NAME
            , UT
            , avg (APRICE) keep (dense_rankfirstorderbyID)
            , max (APRICE)
            , min (APRICE)
            , avg (APRICE) keep (dense_ranklastorderbyID)
            , sum (AVOLUME)
            , sum (APRICE * AVOLUME)
            , count (*)
       from TRANSACTIONS_RAW
       groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T01
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T02
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T03
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T04
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T05
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T06
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T07
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T08
        groupby 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_rankfirstorderby UT)
             , max (AHIGH)
             , min (ALOW)
             , avg (ACLOSE) keep (dense_ranklastorderby UT)
             , sum (AVOLUME)
             , sum (AAMOUNT)
             , sum (ACOUNT)
        from T09
        groupby 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 Variante mit der benutzerdefinierten aggregierten Funktion wird hier nicht angegeben, kann jedoch auf github angezeigt werden.

Option 5 - PPTF (Pipeline- und parallele Tabellenfunktion)


Erstellen Sie eine Funktion (im Paket):

createorreplacepackage THINNING_PPTF_P istype TRANSACTION_RECORD_T isrecord (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;
/
createorreplacepackagebody THINNING_PPTF_P isfunction F (p_cursor CUR_RECORD_T) return QUOTE_LIST_T
pipelinedorder p_cursor by (STOCK_NAME, UT, SEQ_NUM)
parallel_enable (partition p_cursor byhash (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||')');
            endif;
        endif;
        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;
                endif;
            endloop;
        endif;
        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;
                endloop;
            endif;
            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;
            endif;
            if rec.APRICE < QuoteTail(i).ALOW or QuoteTail(i).ALOW is null then QuoteTail(i).ALOW := rec.APRICE; endif;
            if rec.APRICE > QuoteTail(i).AHIGH or QuoteTail(i).AHIGH is null then QuoteTail(i).AHIGH := rec.APRICE; endif;
            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;
        endloop;
        rec_prev := rec;
    endloop;
    for j in 1 .. 10
    loop
        if QuoteTail(j).UT is not null
        then
            pipe row (QuoteTail(j));
        endif;
    endloop;
exception
    when no_data_needed then null;
end;
end;
/

Erstellen Sie eine VIEW:

createorreplaceview THINNING_HABR_PPTF_V asselect * fromtable (THINNING_PPTF_P.F (cursor (select STOCK_NAME, UT, ID, APRICE, AVOLUME from TRANSACTIONS_RAW)));

Option 6 - MODE (Modellklausel)


Die Variante berechnet die Ausdünnung für alle 10 Stufen unter Verwendung der Klausel MODEL- Klausel mit dem Satz ITERATE iterativ .

Die Option ist auch unpraktisch, da sie sich als langsam herausstellt. In meiner Umgebung werden 1000 Transaktionen mit 8 Instrumenten für 1 Minute berechnet. Die meiste Zeit wird für die Berechnung des Ausdrucks MODEL aufgewendet .

Ich gebe diese Option hier nur zur Vollständigkeit und als Bestätigung dafür, dass fast alle komplexen Berechnungen unter Oracle in einer Abfrage ausgeführt werden können, ohne PL / SQL zu verwenden.

Einer der Gründe für die schlechte Leistung des MODEL- Satzes in dieser Abfrage ist, dass die Suche anhand der rechts stehenden Kriterien für jeden durchgeführt wirddie Regeln, die wir haben 6. Die ersten beiden Regeln werden ziemlich schnell berechnet, da es eine direkte explizite Adressierung ohne Joker gibt. In den anderen vier Regeln gibt es das Wort any  - dort werden die Berechnungen langsamer durchgeführt.

Die zweite Schwierigkeit besteht darin, dass das Referenzmodell berechnet werden muss. Dies ist erforderlich, da die Dimensionsliste vor der Berechnung der MODEL- Phrase bekannt sein muss, da innerhalb dieser Phrase keine neuen Dimensionen berechnet werden können. Vielleicht kann dies mit Hilfe von zwei MODEL-Phrasen umgangen werden, aber ich habe dies nicht getan, weil eine Vielzahl von Regeln schlecht ausgeführt wurde.

Ich füge hinzu, dass Sie UT_OPEN und UT_CLOSE nicht im Referenzmodell zählen können, sondern dieselben Funktionen verwenden könntenavg () behält (dense_rank den ersten / letzten Auftrag nach) direkt in der MODEL- Phrase . Aber es wäre noch langsamer gewesen.
Aus Leistungsgründen werde ich diese Option nicht in das Testverfahren aufnehmen.

with-- построение первого уровня прореживания из транзакций
  SOURCETRANS (STRIPE_ID, STOCK_NAME, PARENT_UT, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT)
     as (select1, STOCK_NAME, TRUNC_UT (UT, 2), UT
              , avg (APRICE) keep (dense_rankfirstorderbyID)
              , max (APRICE)
              , min (APRICE)
              , avg (APRICE) keep (dense_ranklastorderbyID)
              , sum (AVOLUME)
              , sum (AVOLUME * APRICE)
              , count (*)
         from TRANSACTIONS_RAW
         whereID <= 1000-- увеличьте значение для каждого интсрумента здесьgroupby 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
          , (selectrownum + 1as STRIPE_ID from dual connectbylevel <= 9) b
        groupby 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, nullfrom 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
modelreturn all rows-- референсная модель содержит карту уровней 2...10reference RM on (select * from REFMOD) dimensionby (STRIPE_ID, STOCK_NAME, UT) measures (UT_OPEN, UT_CLOSE)
main MM partitionby (STOCK_NAME) dimensionby (STRIPE_ID, PARENT_UT, UT) measures (AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT)
rulesiterate (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]
)
orderby1, 2, 3, 4;

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


Die unten beschriebene Abfrage wäre möglicherweise die effizienteste und würde eine Menge Ressourcen erfordern, die dem theoretischen Minimum entspricht.

Weder Oracle noch MS SQL erlauben es Ihnen, eine Abfrage in diesem Formular zu schreiben. Ich denke, das wird vom Standard vorgegeben.

with
  QUOTES_S1 as (select1as STRIPE_ID
                     , STOCK_NAME
                     , TRUNC_UT (UT, 1)                                 as UT
                     , avg (APRICE) keep (dense_rankfirstorderbyID) as AOPEN
                     , max (APRICE)                                     as AHIGH
                     , min (APRICE)                                     as ALOW
                     , avg (APRICE) keep (dense_ranklastorderbyID) as ACLOSE
                     , sum (AVOLUME)                                    as AVOLUME
                     , sum (APRICE * AVOLUME)                           as AAMOUNT
                     , count (*)                                        as ACOUNT
                from TRANSACTIONS_RAW
--                where rownum <= 100groupby STOCK_NAME, TRUNC_UT (UT, 1))
, T1 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT)
     as (select1, 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_rankfirstorderby UT)
              , max (AHIGH)
              , min (ALOW)
              , avg (ACLOSE) keep (dense_ranklastorderby UT)
              , sum (AVOLUME)
              , sum (AAMOUNT)
              , sum (ACOUNT)
         from T1
         where STRIPE_ID < 10groupby STRIPE_ID + 1, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)
         )
select * from T1

Diese Abfrage entspricht dem folgenden Teil der Oracle-Dokumentation:

Wenn sich eine Unterabfrage_Faktorisierungsklausel auf eine Unterabfrage_Faktierklausel bezieht. Eine rekursive subquery_factoring_clause muss zwei Abfrageblöcke enthalten. Kann nicht referenziert werden Referenzname. UNION ALL, UNION, INTERSECT oder MINUS. Das rekursive Member muss genau einmal auf query_name folgen. Sie müssen das rekursive Element mit dem Ankerelement mit dem UNION ALL-Setoperator kombinieren.

Im Gegensatz zu folgendem Absatz der Dokumentation:

rekursive CAN das irgendeine der folgenden Elemente Mitglied der die nicht enthalten:
The das Schlüsselwort distinct oder der Klausel GROUP BY eine Klausel
von An die Aggregatfunktion. In der Auswahlliste sind jedoch analytische Funktionen zulässig.


In einem rekursiven Member sind Aggregate und Gruppierungen daher nicht zulässig.

Testen



Lassen Sie uns zunächst für Oracle ausgeben .

Führen Sie das Berechnungsverfahren für die CALC-Methode aus und notieren Sie sich die Ausführungszeit:

exec THINNING_HABR_CALC_T

Die Berechnungsergebnisse für die vier Methoden liegen in vier Ansichten:

  • THINNING_HABR_SIMP_V (führt die Berechnung durch und führt zu einem komplexen SELECT, sodass es lange dauern wird)
  • THINNING_HABR_CALC_V (zeigt Daten aus der Tabelle QUOTES_CALC an, damit sie schnell ausgeführt werden kann)
  • THINNING_HABR_CHIN_V (führt auch die Berechnung durch, was zu einem komplexen SELECT führt, so dass es lange dauert)
  • THINNING_HABR_PPTF_V (führt die Funktion THINNING_HABR_PPTF aus).

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

Für den Rest von VIEW führen wir Abfragen aus und zeichnen die Ausführungszeit auf:

selectcount (*) 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

Dabei steht XXXX für SIMP, CHIN, PPTF.

Diese VIEW-Zählungseinstellung. Um den Digest zu berechnen, müssen Sie alle Strings abrufen. Mit Hilfe des Digests können Sie Sets miteinander vergleichen.

Sie können Sets auch mit dem Paket dbms_sqlhash vergleichen. Dies ist jedoch viel langsamer, da die anfängliche Menge sortiert werden muss und die Berechnung des Hashwerts nicht schnell ist.
Auch in 12c gibt es ein DBMS_COMPARISON-Paket.

Sie können gleichzeitig die Korrektheit aller Algorithmen überprüfen. Wir berechnen die Digests mit einer solchen Anfrage (bei 11M-Datensätzen auf einer virtuellen Maschine ist diese relativ lang (ca. 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
groupby ALG_NAME;

Wir sehen, dass die Digests identisch sind, so dass alle Algorithmen die gleichen Ergebnisse lieferten.

Jetzt lassen Sie uns dasselbe auf MS SQL reproduzieren . Ich habe die Version 2016 getestet.

Erstellen Sie eine DBTEST-Datenbank vorab und erstellen Sie darin eine Transaktionstabelle:

use DBTEST
gocreatetable TRANSACTIONS_RAW
(
        STOCK_NAME  varchar (32)     notnull
      , UT          intnotnull
      , APRICE      numeric (22, 12) notnull
      , AVOLUME     numeric (22, 12) notnull
      , IDbigintidentitynotnull
);

Laden Sie die heruntergeladenen Daten herunter.

Erstellen Sie in MSSQL ein Dateiformat_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 LoadData-MSSQL.sql-Skript in SSMS aus (dieses Skript wurde von einem einzigen Powershell-Skript generiert, das im Abschnitt dieses Artikels für Oracle angegeben ist).

Erstellen Sie zwei Funktionen:

use DBTEST
gocreateoralterfunction TRUNC_UT (@p_UT bigint, @p_StripeTypeId int) returnsbigintasbeginreturncase @p_StripeTypeId
    when1then @p_UT
    when2then @p_UT / 10 * 10when3then @p_UT / 60 * 60when4then @p_UT / 600 * 600when5then @p_UT / 3600 * 3600when6then @p_UT / 14400 * 14400when7then @p_UT / 86400 * 86400when8thendatediff (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))
    when9thendatediff (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))
    when10then0when11then0end;
end;
go
createoralterfunction UT2DATESTR (@p_UT bigint) returns datetime asbeginreturndateadd(s, @p_UT, cast ('1970-01-01 00:00:00'as datetime));
end;
go

Beginnen wir mit der Implementierung der Optionen:

Option 1 - SIMP


Run:

use DBTEST
gocreateoralterview dbo.THINNING_HABR_SIMP_V aswith
  T1 (STRIPE_ID)
     as (select1union all
         select STRIPE_ID + 1from T1 where STRIPE_ID < 10)
, T2 as (select STRIPE_ID
              , STOCK_NAME
              , dbo.TRUNC_UT (UT, STRIPE_ID)             as UT
              , min (1000000 * cast (UT asbigint) + ID) as AOPEN_UT
              , max (APRICE)                             as AHIGH
              , min (APRICE)                             as ALOW
              , max (1000000 * cast (UT asbigint) + ID) as ACLOSE_UT
              , sum (AVOLUME)                            as AVOLUME
              , sum (APRICE * AVOLUME)                   as AAMOUNT
              , count (*)                                as ACOUNT
         from TRANSACTIONS_RAW, T1
         groupby 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);

Die fehlenden ersten / letzten Funktionen werden durch doppelte sich selbst verbindende Tabellen implementiert.

Option 2 - CALC


Erstellen Sie eine Tabelle, eine Prozedur und eine Sicht:

use DBTEST
gocreatetable dbo.QUOTES_CALC
(
      STRIPE_ID   intnotnull
    , STOCK_NAME  varchar(32) notnull
    , UT          bigintnotnull
    , AOPEN       numeric (22, 12) notnull
    , AHIGH       numeric (22, 12) notnull
    , ALOW        numeric (22, 12) notnull
    , ACLOSE      numeric (22, 12) notnull
    , AVOLUME     numeric (38, 12) notnull
    , AAMOUNT     numeric (38, 12) notnull
    , ACOUNT      intnotnull
);
go
createoralterprocedure dbo.THINNING_HABR_CALC asbeginset nocount on;
    truncatetable 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
             groupby STOCK_NAME, UT)
    insertinto QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT)
    select1, 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)
    beginwith
          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
                 groupby STOCK_NAME, dbo.TRUNC_UT (UT, @StripeId + 1))
        insertinto 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
createoralterview dbo.THINNING_HABR_CALC_V asselect *
from dbo.QUOTES_CALC;
go

Optionen 3 (CHIN) und 4 (UDAF) habe ich nicht in MS SQL implementiert.

Option 5 - PPTF


Erstellen Sie eine Tabellenfunktion und -ansicht. Diese Funktion ist nur ein Tabellenbereich, keine parallele Pipeline-Tabellenfunktion, nur die Variante hat ihren historischen Namen von Oracle beibehalten:

use DBTEST
gocreateoralterfunction dbo.THINNING_HABR_PPTF ()
returns @rettab table (
      STRIPE_ID  bigintnotnull
    , STOCK_NAME varchar(32)      notnull
    , UT         bigintnotnull
    , AOPEN      numeric (22, 12) notnull
    , AHIGH      numeric (22, 12) notnull
    , ALOW       numeric (22, 12) notnull
    , ACLOSE     numeric (22, 12) notnull
    , AVOLUME    numeric (38, 12) notnull
    , AAMOUNT    numeric (38, 12) notnull
    , ACOUNT     bigintnotnull)
asbegindeclare @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  bigintnotnull primary key clustered
        , STOCK_NAME varchar(32)      notnull
        , UT         bigintnotnull
        , AOPEN      numeric (22, 12) notnull
        , AHIGH      numeric (22, 12)
        , ALOW       numeric (22, 12)
        , ACLOSE     numeric (22, 12)
        , AVOLUME    numeric (38, 12) notnull
        , AAMOUNT    numeric (38, 12) notnull
        , ACOUNT     bigintnotnull);
    declare c cursor fast_forward forselect STOCK_NAME, UT, ID, APRICE, AVOLUME
    from TRANSACTIONS_RAW
    orderby 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
    beginif @trans_STOCK_NAME <> @trans_prev_STOCK_NAME or @trans_prev_STOCK_NAME isnullbegininsertinto @rettab select * from @QuoteTail;
            delete @QuoteTail;
        end;
        set @i = 10;
        while @i >= 1
        beginset @tut = dbo.TRUNC_UT (@trans_UT, @i);
            if @tut <> (select UT from @QuoteTail where STRIPE_ID = @i)
            begininsertinto @rettab select * from @QuoteTail where STRIPE_ID <= @i;
                delete @QuoteTail where STRIPE_ID <= @i;
            end;
            if (selectcount (*) from @QuoteTail where STRIPE_ID = @i) = 0begininsertinto @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 = casewhen AHIGH < @trans_APRICE or AHIGH isnullthen @trans_APRICE else AHIGH end
              , ALOW = casewhen ALOW > @trans_APRICE or ALOW isnullthen @trans_APRICE else ALOW end
              , ACLOSE = @trans_APRICE, AVOLUME = AVOLUME + @trans_AVOLUME
              , AAMOUNT = AAMOUNT + @trans_APRICE * @trans_AVOLUME
              , ACOUNT = ACOUNT + 1where 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;
    insertinto @rettab select * from @QuoteTail;
    return;
endgocreateoralterview dbo.THINNING_HABR_PPTF_V asselect *
from dbo.THINNING_HABR_PPTF ();

Führen Sie die Berechnung der Tabelle QUOTES_CALC für die CALC-Methode durch und zeichnen Sie die Ausführungszeit auf:
use DBTEST
go
exec dbo.THINNING_HABR_CALC

Die Berechnungsergebnisse für die drei Methoden liegen in drei Ansichten:

  • THINNING_HABR_SIMP_V (führt die Berechnung durch und führt zu einem komplexen SELECT, sodass es lange dauern wird)
  • THINNING_HABR_CALC_V (zeigt Daten aus der Tabelle QUOTES_CALC an, damit sie schnell ausgeführt werden kann)
  • THINNING_HABR_PPTF_V (führt die Funktion THINNING_HABR_PPTF aus).

Führen Sie für zwei VIEWs die Abfragen aus und schreiben Sie die Ausführungszeit:

selectcount (*) 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

Dabei steht XXXX für SIMP, PPTF.

Jetzt können Sie die Berechnungsergebnisse für die drei Methoden für MS SQL vergleichen. Dies kann in einer Anfrage erfolgen. Run:

use DBTEST
gowith
  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 asbigint)) as STRIPE_ID
     , sum (cast (UT asbigint)) 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 asbigint)) as ACOUNT
from T1
groupby ALG_NAME;

Wenn die drei Linien in allen Feldern übereinstimmen, ist das Ergebnis der Berechnung für die drei Methoden identisch.

Ich empfehle in der Testphase dringend, ein kleines Beispiel zu verwenden, da die Leistung dieser Aufgabe in MS SQL gering ist.

Wenn Sie nur über die MS SQL-Engine verfügen und eine größere Datenmenge berechnen möchten, können Sie die folgende Optimierungsmethode versuchen: Sie können Indizes erstellen:

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

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

Bild

Skripts können von github heruntergeladen werden : Oracle, das THINNING-Schema - die Skripts dieses Artikels, das THINNING_LIVE-Schema - Online - Datendownload von Bitcoincharts.com und Online-Ausdünnung (diese Website stellt jedoch nur Daten für die letzten 5 Tage online bereit ) und für MS SQL auch für diesen Artikel.

Fazit:

Diese Aufgabe wird unter Oracle schneller gelöst als unter MS SQL. Mit zunehmender Anzahl von Transaktionen wird die Lücke immer deutlicher.

Unter Oracle war PPTF die beste Option. Hier erwies sich der prozedurale Ansatz als profitabler, dies geschieht selten. Die übrigen Methoden zeigten ebenfalls ein akzeptables Ergebnis - ich habe sogar das Volumen von 367 Millionen Transaktionen auf einer virtuellen Maschine getestet (die PPTF-Methode berechnete die Ausdünnung in anderthalb Stunden).

Unter MS SQL erwies sich die iterative Berechnungsmethode (CALC) als die produktivste.

Warum hat sich die PPTF-Methode für Oracle als führend erwiesen? Aufgrund der parallelen Ausführung und der Architektur wird eine Funktion, die als parallele Pipeline-Tabellenfunktion erstellt wird, in die Mitte des Abfrageplans eingebaut:

Bild

Jetzt auch beliebt: