🙆🏻 🌱 🔥 Délais d'éclaircissement (crypto-monnaies, forex, échanges) 🦗 🍓 🍳

Il y a quelque temps, j'ai été chargé d'écrire une procédure qui effectue un éclaircissement des cotations du marché Forex (plus précisément, des données de calendrier).

L'énoncé du problème: les données sont entrées à un intervalle de 1 seconde dans ce format:

Nom de l'instrument (code paire USDEUR, etc.),
Date et heure au format Unix,
Valeur ouverte (prix de la première transaction dans l'intervalle),
Valeur élevée (prix maximum),
Valeur faible
Valeur de clôture (prix de la dernière offre),
Volume (volume ou volume de transaction).

Il est nécessaire d'assurer le recalcul et la synchronisation des données dans les tableaux: 5 sec, 15 sec, 1 min, 5 min, 15 min, etc.

Le format de stockage de données décrit est appelé OHLC ou OHLCV (Open, High, Low, Close, Volume). Il est souvent utilisé, vous pouvez immédiatement y construire un tableau de "bougies japonaises".

Sous la coupe, j'ai décrit toutes les options que je pouvais trouver, comment éclaircir (agrandir) les données reçues, pour l'analyse, par exemple, le saut hivernal du prix du bitcoin, et selon les données reçues, vous construirez immédiatement un graphique "Bougies japonaises" (dans MS Excel, il y a aussi un tel graphique ) Dans l'image ci-dessus, ce graphique est construit pour la période de "1 mois", pour l'outil "bitstampUSD". Le corps blanc de la bougie indique une augmentation de prix dans l'intervalle, noir - une diminution de prix, les mèches supérieures et inférieures indiquent les prix maximum et minimum qui ont été atteints dans l'intervalle. Contexte - volume de transactions. On voit clairement qu'en décembre 2017, le prix était proche de la barre des 20K.

La solution sera donnée pour deux moteurs de base de données, pour Oracle et MS SQL, qui, d'une certaine manière, permettront de les comparer pour cette tâche spécifique (nous ne généraliserons pas la comparaison à d'autres tâches).

J'ai ensuite résolu le problème de manière triviale: calcul de l'amincissement correct dans une table temporaire et synchronisation avec la table cible - suppression des lignes qui existent dans la table cible mais n'existent pas dans la table temporaire et ajout de lignes qui existent dans la table temporaire mais n'existent pas dans la cible. À ce moment-là, le client a satisfait la solution et j'ai clôturé la tâche.

Mais maintenant, j'ai décidé de considérer toutes les options, car la solution ci-dessus contient une fonctionnalité - il est difficile d'optimiser pour deux cas à la fois:

lorsque la table cible est vide et que vous devez ajouter un grand nombre de données,
et lorsque la table cible est grande et que vous devez ajouter des données en petits morceaux.

Cela est dû au fait que dans la procédure, vous devez connecter la table cible et la table temporaire, et vous devez vous attacher à la plus grande, et non l'inverse. Dans les deux cas ci-dessus, le plus grand / le plus petit sont échangés. L'optimiseur décidera de l'ordre de connexion en fonction des statistiques, et les statistiques peuvent être obsolètes et la décision peut être prise de manière incorrecte, ce qui entraînera une dégradation significative des performances.

Dans cet article, je décrirai des méthodes d'éclaircissement ponctuelles qui peuvent être utiles aux lecteurs pour l'analyse, par exemple, la hausse hivernale du prix du bitcoin.

Les procédures d'amincissement en ligne peuvent être téléchargées depuis github sur le lien en bas de l'article.

Au point ... Ma tâche consistait à éclaircir le délai de "1 seconde" au suivant, mais ici, j'envisage de réduire le niveau de transaction (dans la table source, les champs STOCK_NAME, UT, ID, APRICE, AVOLUME). Parce que ces données sont émises par bitcoincharts.com.
En fait, la décimation du niveau de transaction au niveau de "1 sec" est effectuée par une telle commande (l'opérateur est facilement traduit en décimation du niveau de "1 sec" aux niveaux supérieurs):

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

La fonction avg () keep (dense_rank de premier ordre par UT, ID) fonctionne comme ceci: puisque la requête est GROUP BY, chaque groupe est calculé indépendamment des autres. Dans chaque groupe, les chaînes sont triées par UT et ID, numérotées par dense_rank . Puisque la première fonction suit, la ligne est sélectionnée où dense_rank a renvoyé 1 (en d'autres termes, le minimum est sélectionné) - la première transaction dans l'intervalle est sélectionnée. Pour cet UT, ID minimum, s'il y avait plusieurs lignes, la moyenne serait considérée. Mais dans notre cas, une ligne sera garantie (en raison de l'unicité de l'ID), de sorte que la valeur résultante est immédiatement retournée sous la forme AOPEN. Il est facile de remarquer que la première fonction remplace deux fonctions agrégées.

Sur MS SQL

Il n'y a pas de première / dernière fonctions (il y a first_value / last_value , mais ce n'est pas ça). Par conséquent, vous devez connecter la table à elle-même.

Je ne donnerai pas la demande séparément, mais vous pouvez la voir ci-dessous dans la procédure dbo.THINNING_HABR_CALC . Bien sûr, sans premier / dernier, ce n'est pas si élégant, mais cela fonctionnera.

Comment ce problème peut-il être résolu par un seul opérateur? (Ici, le terme "un opérateur" signifie non pas que l'opérateur sera un, mais qu'il n'y aura pas de cycles qui "tireront" les données sur une seule ligne.)

Je vais énumérer toutes les options que je connais pour résoudre ce problème:

SIMP (produit simple, simple, cartésien),
CALC (calcul, éclaircissement itératif des niveaux supérieurs),
CHIN (façon porcelaine, demande volumineuse pour tous les niveaux à la fois),
UDAF (fonction d'agrégation définie par l'utilisateur),
PPTF (fonction de table en pipeline et parallèle, solution procédurale, mais avec seulement deux curseurs, en fait, deux instructions SQL),
MODE (modèle, phrase MODÈLE),
et IDEA (idéal, une solution idéale qui peut ne pas fonctionner maintenant).

Pour l'avenir, je dirai que c'est le cas rare où la solution PPTF procédurale est la plus efficace sur Oracle.

Téléchargez les fichiers de transaction depuis http://api.bitcoincharts.com/v1/csv
Je recommande de choisir des fichiers kraken *. Les fichiers localbtc * sont très bruyants - ils contiennent des lignes distrayantes avec des prix irréalistes. Tous les kraken * contiennent environ 31 millions de transactions, je recommande d'exclure krakenEUR de là, puis la transaction devient 11 millions. Il s'agit du volume le plus pratique pour les tests.

Exécutez un script dans Powershell pour générer des fichiers de contrôle pour SQLLDR pour Oracle et pour générer une demande d'importation pour MSSQL.

  # 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 "" }

Créons une table de transactions sur 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;

Sur Oracle, exécutez le fichier LoadData-Oracle.bat , après avoir préalablement fixé les paramètres de connexion au début du script Powershell.

Je travaille dans une machine virtuelle. Le téléchargement de tous les fichiers de transaction 11M dans 8 fichiers kraken * (j'ai ignoré le fichier EUR) a pris environ 1 minute.

Et créez des fonctions qui tronqueront les dates aux limites des intervalles:

 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;

Considérez les options. Tout d'abord, le code de toutes les options est donné, puis les scripts de lancement et de test. Tout d'abord, la tâche est décrite pour Oracle, puis pour MS SQL

Option 1 - SIMP (Trivial)

L'ensemble des transactions est multiplié par le produit cartésien par un ensemble de 10 lignes avec des nombres de 1 à 10. Ceci est nécessaire pour obtenir 10 lignes à partir d'une seule ligne de transaction avec des dates tronquées aux frontières de 10 intervalles.

Après cela, les lignes sont regroupées par numéro d'intervalle et date tronquée, et la demande ci-dessus est exécutée.

Créer une vue:

 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 (calculé itérativement)

Dans cette option, nous réduisons de manière itérative les transactions au niveau 1, du niveau 1 au niveau 2, etc.

Créez une table:

 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;

Vous pouvez créer un index à l'aide du champ STRIPE_ID, mais il a été expérimentalement établi qu'il est plus rentable pour 11 millions de transactions sans index. Pour des quantités plus importantes, la situation peut changer. Ou vous pouvez partitionner la table en supprimant la mise en commentaire du bloc dans la requête.

Créez une procédure:

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

Pour la symétrie, créez une VUE simple:

 create view THINNING_HABR_CALC_V as select * from QUOTES_CALC;

Option 3 - RCIP (code chinois)

La méthode diffère brutalité simple de l'approche, et est le rejet du principe de "Ne vous répétez pas." Dans ce cas, le rejet des cycles.

L'option n'est fournie ici que pour être complète.

Pour l'avenir, je dirai qu'en termes de performances sur cette tâche particulière, il prend la deuxième place.

Grande demande

 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

L'option avec la fonction agrégée définie par l'utilisateur ne sera pas donnée ici, mais elle peut être consultée sur github.

Option 5 - PPTF (fonction de table en pipeline et parallèle)

Créez une fonction (dans le package):

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

Créer une vue:

 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 (clause modèle)

L'option calcule de manière itérative la décimation pour les 10 niveaux à l'aide de la phrase de la clause MODEL avec la phrase ITERATE .

L'option est également peu pratique car elle est lente. Dans mon environnement, 1000 transactions pour 8 instruments sont calculées en 1 minute. La plupart du temps est consacré au calcul de la phrase MODÈLE .

Ici, je donne cette option uniquement par souci d'exhaustivité et comme confirmation du fait que sur Oracle, presque tous les calculs arbitrairement complexes peuvent être effectués avec une seule requête, sans utiliser PL / SQL.

L'une des raisons de la faible performance de la phrase MODEL dans cette requête est que la recherche à droite est effectuée pour chaque règle, que nous avons 6. Les deux premières règles sont calculées assez rapidement, car il existe un adressage explicite direct, sans jokers. Dans les quatre autres règles, il y a le mot any - les calculs sont plus lents.

La deuxième difficulté est que vous devez calculer le modèle de référence. Il est nécessaire car la liste des dimensions doit être connue avant de calculer la phrase MODEL , nous ne pouvons pas calculer de nouvelles dimensions à l'intérieur de cette phrase. Peut-être que cela peut être contourné à l'aide de deux phrases MODÈLE, mais je ne l'ai pas fait en raison des faibles performances d'un grand nombre de règles.

J'ajoute qu'il serait possible de ne pas calculer UT_OPEN et UT_CLOSE dans le modèle de référence, mais d'utiliser les mêmes fonctions avg () keep (dense_rank first / last order by) directement dans la phrase MODEL . Mais cela se serait produit encore plus lentement.
En raison de limitations de performances, je n'inclurai pas cette option dans la procédure de test.

 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 (idéal, idéal, mais inopérant)

La demande décrite ci-dessous serait potentiellement la plus efficace et consommerait un montant de ressources égal au minimum théorique.

Mais ni Oracle ni MS SQL ne vous permettent d'écrire une requête sous cette forme. Je crois que cela est dicté par la norme.

 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

Cette requête correspond à la partie suivante de la documentation Oracle:

Si une subquery_factoring_clause fait référence à son propre query_name dans la sous-requête qui la définit, alors la subquery_factoring_clause est dite récursive. Une subquery_factoring_clause récursive doit contenir deux blocs de requête: le premier est le membre d'ancrage et le second est le membre récursif. Le membre d'ancrage doit apparaître avant le membre récursif et il ne peut pas faire référence à query_name. Le membre d'ancrage peut être composé d'un ou plusieurs blocs de requête combinés par les opérateurs d'ensemble: UNION ALL, UNION, INTERSECT ou MINUS. Le membre récursif doit suivre le membre d'ancrage et doit faire référence une seule fois à query_name. Vous devez combiner le membre récursif avec le membre d'ancrage à l'aide de l'opérateur d'ensemble UNION ALL.

Mais cela contredit le paragraphe suivant de la documentation:

Le membre récursif ne peut contenir aucun des éléments suivants:
Le mot clé DISTINCT ou une clause GROUP BY
Une fonction d'agrégation. Cependant, les fonctions analytiques sont autorisées dans la liste de sélection.

Ainsi, dans le membre récursif, les agrégats et le regroupement ne sont pas autorisés.

Test

Faisons-le d'abord pour Oracle .

Effectuez la procédure de calcul de la méthode CALC et enregistrez l'heure de son exécution:

 exec THINNING_HABR_CALC_T

Les résultats du calcul pour les quatre méthodes sont dans quatre vues:

THINNING_HABR_SIMP_V (effectuera le calcul, provoquant un SELECT complexe, donc cela prendra beaucoup de temps),
THINNING_HABR_CALC_V (affichera les données de la table QUOTES_CALC, donc il s'exécutera rapidement)
THINNING_HABR_CHIN_V (effectuera également le calcul, provoquant un SELECT complexe, cela prendra donc beaucoup de temps),
THINNING_HABR_PPTF_V (exécutera la fonction THINNING_HABR_PPTF).

Le délai de livraison pour toutes les méthodes a déjà été mesuré par moi et est donné dans le tableau en fin d'article.

Pour le reste de VIEW, nous exécutons les requêtes et écrivons le temps d'exécution:

 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

où XXXX est SIMP, CHIN, PPTF.

Ces VUES calculent le résumé du recrutement. Pour calculer le résumé, vous devez récupérer toutes les lignes, et en utilisant le résumé, vous pouvez comparer les ensembles les uns avec les autres.

Vous pouvez également comparer des ensembles à l'aide du package dbms_sqlhash, mais cela est beaucoup plus lent car vous devez trier l'ensemble d'origine et le calcul du hachage n'est pas rapide.
Toujours en 12c, il y a un package DBMS_COMPARISON.

Vous pouvez vérifier l'exactitude de tous les algorithmes en même temps. Nous considérons les résumés comme une telle demande (avec 11M d'entrées dans une machine virtuelle, cela sera relativement long, environ 15 minutes):

 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;

Nous voyons que les résumés sont les mêmes, donc tous les algorithmes ont donné les mêmes résultats.

Nous allons maintenant reproduire tout de même en MS SQL . J'ai testé sur la version 2016.

Créez d'abord la base de données DBTEST, puis créez-y une table de transactions:

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

Téléchargez les données téléchargées.

Dans MSSQL, créez le fichier format_mssql.bcp:

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

Et exécutez le script LoadData-MSSQL.sql dans SSMS (ce script a été généré par le seul script powershell fourni dans la section de cet article pour Oracle).

Créons deux fonctions:

 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

Nous procédons à la mise en œuvre des options:

Option 1 - SIMP

Exécuter:

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

Les premières / dernières fonctions manquantes sont implémentées par l'auto-jointure de table double.

Option 2 - CALC

Créez une table, une procédure et une vue:

 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

Je n'ai pas implémenté les options 3 (CHIN) et 4 (UDAF) sur MS SQL.

Option 5 - PPTF

Créez une fonction de table et une vue. Cette fonction est juste une fonction de table, pas une fonction de table en pipeline parallèle, juste l'option a conservé son nom historique d'Oracle:

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

Calculons la table QUOTES_CALC pour la méthode CALC et écrivons le temps d'exécution:

 use DBTEST go exec dbo.THINNING_HABR_CALC

Les résultats du calcul pour les trois méthodes sont dans trois vues:

THINNING_HABR_SIMP_V (effectuera le calcul, provoquant un SELECT complexe, donc cela prendra beaucoup de temps),
THINNING_HABR_CALC_V (affichera les données de la table QUOTES_CALC, donc il s'exécutera rapidement)
THINNING_HABR_PPTF_V (exécutera la fonction THINNING_HABR_PPTF).

Pour deux VUES, nous exécutons les requêtes et écrivons le temps d'exécution:

 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

où XXXX est SIMP, PPTF.

Vous pouvez maintenant comparer les résultats des calculs pour les trois méthodes pour MS SQL. Cela peut être fait en une seule demande. Exécuter:

 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;

Si trois lignes coïncident dans tous les champs, le résultat du calcul utilisant les trois méthodes est identique.

Je vous conseille fortement d'utiliser une petite sélection au stade des tests, car les performances de cette tâche dans MS SQL sont faibles.

Si vous ne disposez que du moteur MS SQL et souhaitez calculer une plus grande quantité de données, vous pouvez essayer la méthode d'optimisation suivante: vous pouvez créer des index:

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

Les résultats de la mesure des performances sur ma machine virtuelle sont les suivants:

Les scripts peuvent être téléchargés à partir de github : Oracle, le schéma THINNING - les scripts de cet article, le schéma THINNING_LIVE - le téléchargement en ligne de données à partir de bitcoincharts.com et l' éclaircissement en ligne (mais ce site envoie uniquement des données pour les 5 derniers jours en mode en ligne), et le script pour MS SQL également sur cet article.

Conclusion:

Cette tâche est résolue plus rapidement sur Oracle que sur MS SQL. Avec l'augmentation du nombre de transactions, l'écart devient plus important.

Sur Oracle, PPTF était la meilleure option. Ici, l'approche procédurale s'est avérée plus rentable, cela se produit rarement. D'autres méthodes ont également montré un résultat acceptable - j'ai même testé le volume de 367 millions de transactions dans une machine virtuelle (méthode PPTF calculée en éclaircissant en une heure et demie).

Sur MS SQL, la méthode de calcul itérative (CALC) s'est avérée être la plus productive.

Pourquoi la méthode PPTF sur Oracle s'est-elle révélée être le leader? En raison de la concurrence et de l'architecture, une fonction créée en tant que fonction de table en pipeline parallèle est intégrée au milieu du plan de requête:

Délais d'éclaircissement (crypto-monnaies, forex, échanges)