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:

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 (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 ($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 Sie eine Transaktionstabelle in 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;

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:

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 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:

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)


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

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 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:

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 für die Symmetrie eine einfache VIEW:

create view THINNING_HABR_CALC_V as
select * 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
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 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):

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;
/

Erstellen Sie eine VIEW:

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 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 (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 - 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 (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 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:

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

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
group by 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
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 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
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

Beginnen wir mit der Implementierung der Optionen:

Option 1 - SIMP


Run:

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);

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
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

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
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 ();

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:

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

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
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;

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:

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 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: