Hace algún tiempo, me encargaron escribir un procedimiento que reduzca las cotizaciones del mercado Forex (más precisamente, los datos de plazos).
La declaración del problema: los datos se ingresan en un intervalo de 1 segundo en este formato:
- Nombre del instrumento (código de par USDEUR, etc.),
- Fecha y hora en formato de hora unix,
- Valor abierto (precio de la primera transacción en el intervalo),
- Alto valor (precio máximo),
- Valor bajo
- Valor de cierre (precio de la última oferta),
- Volumen (volumen o volumen de transacción).
Es necesario asegurar el recálculo y la sincronización de los datos en las tablas: 5 segundos, 15 segundos, 1 minuto, 5 minutos, 15 minutos, etc.
El formato de almacenamiento de datos descrito se llama OHLC u OHLCV (Abrir, Alto, Bajo, Cerrar, Volumen). A menudo se usa, puede construir inmediatamente una tabla de "velas japonesas" en él.
Debajo del corte, describí todas las opciones que se me ocurrieron, cómo diluir (agrandar) los datos recibidos, para el análisis, por ejemplo, el salto invernal en el precio de bitcoin, y de acuerdo con los datos recibidos, inmediatamente construirá un gráfico de "Velas japonesas" (en MS Excel también hay un gráfico de este tipo). ) En la imagen de arriba, este gráfico está construido para el período de tiempo de "1 mes", para la herramienta "bitstampUSD". El cuerpo blanco de la vela indica un aumento en el precio en el intervalo, negro: una disminución en el precio, las mechas superior e inferior indican los precios máximos y mínimos que se alcanzaron en el intervalo. Antecedentes: volumen de transacciones. Se ve claramente que en diciembre de 2017 el precio se acercó a la marca de 20K.
La solución se dará para dos motores de base de datos, para Oracle y MS SQL, que, de alguna manera, permitirán compararlos para esta tarea específica (no generalizaremos la comparación con otras tareas).
Luego resolví el problema de una manera trivial: calculando el adelgazamiento correcto en una tabla temporal y sincronizándolo con la tabla de destino, eliminando filas que existen en la tabla de destino pero que no existen en la tabla temporal y agregando filas que existen en la tabla temporal pero que no existen en el objetivo. En ese momento, el cliente satisfizo la solución y cerré la tarea.
Pero ahora decidí considerar todas las opciones, porque la solución anterior contiene una característica: es difícil de optimizar para dos casos a la vez:
- cuando la tabla de destino está vacía y necesita agregar muchos datos,
- y cuando la tabla de destino es grande, y necesita agregar datos en pequeños fragmentos.
Esto se debe al hecho de que en el procedimiento debe conectar la tabla de destino y la tabla temporal, y debe adjuntarla a la más grande, y no al revés. En los dos casos anteriores, los más grandes / más pequeños se intercambian. El optimizador decidirá el orden de conexión en función de las estadísticas, y las estadísticas pueden estar desactualizadas y la decisión puede tomarse incorrectamente, lo que conducirá a una degradación significativa del rendimiento.
En este artículo, describiré métodos de adelgazamiento únicos que pueden ser útiles para los lectores para el análisis, por ejemplo, el salto invernal en el precio de bitcoin.
Los procedimientos de adelgazamiento en línea se pueden descargar desde github en el enlace al final del artículo.
Hasta el punto ... Mi tarea se estaba reduciendo del período de tiempo de "1 segundo" al siguiente, pero aquí estoy considerando la reducción del nivel de transacción (en la tabla de origen, los campos STOCK_NAME, UT, ID, APRICE, AVOLUME). Porque dichos datos son emitidos por bitcoincharts.com.
En realidad, la eliminación del nivel de transacción al nivel de "1 segundo" se realiza mediante dicho comando (el operador se traduce fácilmente en la reducción del nivel de "1 segundo" a los niveles superiores):
En 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 función
avg () keep (dense_rank primer orden por UT, ID) funciona así: como la solicitud es GROUP BY, cada grupo se calcula independientemente de los demás. Dentro de cada grupo, las cadenas se ordenan por UT e ID, numeradas por
dense_rank . Como sigue la primera función, la línea se selecciona donde
dense_rank devolvió 1 (en otras palabras, se selecciona el mínimo): se selecciona la primera transacción dentro del intervalo. Para este UT mínimo, ID, si hubiera varias líneas, se consideraría el promedio. Pero en nuestro caso se garantizará una línea (debido a la unicidad de la ID), por lo que el valor resultante se devuelve inmediatamente como AOPEN. Es fácil notar que la
primera función reemplaza dos agregadas.
En MS SQLNo hay
primeras / últimas funciones (hay
first_value / last_value , pero no es eso). Por lo tanto, debe conectar la tabla consigo misma.
No daré la solicitud por separado, pero puede verla a continuación en el procedimiento
dbo.THINNING_HABR_CALC . Por supuesto, sin
primero / último no es tan elegante, pero funcionará.
¿Cómo puede resolver este problema un operador? (Aquí, el término "un operador" no significa que el operador será uno, sino que no habrá ciclos que "arrastren" los datos en una línea).
Enumeraré todas las opciones que conozco para resolver este problema:
- SIMP (producto simple, simple, cartesiano),
- CALC (cálculo, adelgazamiento iterativo de los niveles superiores),
- CHIN (forma china, solicitud voluminosa para todos los niveles a la vez),
- UDAF (función agregada definida por el usuario),
- PPTF (función de tabla paralela y canalizada, solución de procedimiento, pero con solo dos cursores, de hecho, dos sentencias SQL),
- MODO (modelo, frase MODELO),
- e IDEA (ideal, una solución ideal que puede no funcionar ahora).
Mirando hacia el futuro, diré que este es el caso raro cuando la solución PPTF de procedimiento es la más efectiva en Oracle.
Descargue archivos de transacciones de
http://api.bitcoincharts.com/v1/csvRecomiendo elegir archivos kraken *. Los archivos localbtc * son muy ruidosos: contienen líneas de distracción con precios poco realistas. Todos los kraken * contienen alrededor de 31 millones de transacciones, recomiendo excluir krakenEUR de allí, luego la transacción se convierte en 11 millones. Este es el volumen más conveniente para las pruebas.
Ejecute un script en Powershell para generar archivos de control para SQLLDR para Oracle y generar una solicitud de importación para 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 "" }
Creemos una tabla de transacciones en 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;
En Oracle, ejecute el
archivo LoadData-Oracle.bat , habiendo
arreglado previamente los parámetros de conexión al comienzo del script Powershell.
Yo trabajo en una máquina virtual. La descarga de todos los archivos de transacciones de 11M en 8 archivos kraken * (omití el archivo EUR) tomó aproximadamente 1 minuto.
Y cree funciones que truncarán las fechas a límites de intervalo:
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;
Considera las opciones. Primero, se proporciona el código de todas las opciones, luego los scripts para iniciar y probar. Primero, la tarea se describe para Oracle, luego para MS SQL
Opción 1 - SIMP (Trivial)
Todo el conjunto de transacciones se multiplica por el producto cartesiano por un conjunto de 10 líneas con números del 1 al 10. Esto es necesario para obtener 10 líneas de una sola línea de transacción con fechas truncadas en los límites de 10 intervalos.
Después de eso, las líneas se agrupan por el número de intervalo y la fecha truncada, y se ejecuta la solicitud anterior.
Crear VISTA:
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);
Opción 2 - CALC (calculado iterativamente)
En esta opción, reducimos iterativamente las transacciones al nivel 1, del nivel 1 al nivel 2, y así sucesivamente.
Crea una tabla:
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 ) parallel 4 pctfree 0 nologging;
Puede crear un índice utilizando el campo STRIPE_ID, pero se ha establecido experimentalmente que es más rentable para 11 millones de transacciones sin un índice. Para cantidades mayores, la situación puede cambiar. O puede dividir la tabla descomentando el bloque en la consulta.
Crea un procedimiento:
create or replace procedure THINNING_HABR_CALC_T is begin rollback; execute immediate 'truncate table QUOTES_CALC'; insert
Por simetría, cree una VISTA simple:
create view THINNING_HABR_CALC_V as select * from QUOTES_CALC;
Opción 3 - CHIN (Código chino)
El método difiere la franqueza brutal del enfoque y es el rechazo del principio de "No te repitas". En este caso, el rechazo de los ciclos.
La opción se proporciona aquí solo para completar.
Mirando hacia el futuro, diré que, en términos de rendimiento en esta tarea en particular, ocupa el segundo lugar.
Gran solicitud 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;
Opción 4 - UDAF
La opción con la función agregada definida por el usuario no se dará aquí, pero se puede ver en github.
Opción 5 - PPTF (función de tabla canalizada y paralela)
Cree una función (en el paquete):
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; /
Crear VISTA:
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)));
Opción 6 - MODO (cláusula modelo)
La opción calcula de forma iterativa el diezmado para los 10 niveles utilizando la frase de la cláusula
MODELO con la frase
ITERATE .
La opción tampoco es práctica porque es lenta. En mi entorno, se calculan 1000 transacciones para 8 instrumentos en 1 minuto. La mayor parte del tiempo se gasta calculando la frase
MODELO .
Aquí doy esta opción solo por razones de integridad y como confirmación del hecho de que en Oracle casi todos los cálculos arbitrariamente complejos se pueden realizar con una consulta, sin usar PL / SQL.
Una de las razones del bajo rendimiento de la frase
MODELO en esta consulta es que la búsqueda a la derecha se realiza para
cada regla que tenemos 6. Las primeras dos reglas se calculan bastante rápido, porque hay un direccionamiento explícito directo, sin comodines. En las cuatro reglas restantes existe la palabra
any : los cálculos son más lentos.
La segunda dificultad es que tienes que calcular el modelo de referencia. Es necesario porque la lista de dimensiones debe conocerse
antes de calcular la frase
MODELO , no podemos calcular nuevas dimensiones dentro de esta frase. Quizás esto se pueda evitar con la ayuda de dos frases MODELO, pero no lo hice debido al bajo rendimiento de una gran cantidad de reglas.
Agrego que sería posible no calcular
UT_OPEN y
UT_CLOSE en el modelo de referencia, sino usar las mismas funciones
avg () keep (dense_rank first / last order by) directamente en la frase
MODEL . Pero habría sucedido aún más lentamente.
Debido a limitaciones de rendimiento, no incluiré esta opción en el procedimiento de prueba.
with
Opción 6 - IDEA (ideal, ideal, pero inoperante)
La solicitud que se describe a continuación sería potencialmente la más eficiente y consumiría una cantidad de recursos igual al mínimo teórico.
Pero ni Oracle ni MS SQL le permiten escribir una consulta de esta forma. Creo que esto es dictado por la norma.
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
Esta consulta corresponde a la siguiente parte de la documentación de Oracle:
Si una subquery_factoring_clause se refiere a su propio query_name en la subconsulta que lo define, entonces subquery_factoring_clause se dice que es recursiva. Una subquery_factoring_clause recursiva debe contener dos bloques de consulta: el primero es el miembro de anclaje y el segundo es el miembro recursivo. El miembro de anclaje debe aparecer antes que el miembro recursivo, y no puede hacer referencia a query_name. El miembro de ancla puede estar compuesto por uno o más bloques de consulta combinados por los operadores establecidos: UNION ALL, UNION, INTERSECT o MINUS. El miembro recursivo debe seguir al miembro ancla y debe hacer referencia a query_name exactamente una vez. Debe combinar el miembro recursivo con el miembro ancla utilizando el operador de conjunto UNION ALL.Pero contradice el siguiente párrafo de la documentación:
El miembro recursivo no puede contener ninguno de los siguientes elementos:
La palabra clave DISTINCT o una cláusula GROUP BY
Una función agregada. Sin embargo, las funciones analíticas están permitidas en la lista de selección.Por lo tanto, en el miembro recursivo, los agregados y la agrupación no están permitidos.
Prueba
Hagámoslo primero para
Oracle .
Realice el procedimiento de cálculo para el método CALC y registre el tiempo de su ejecución:
exec THINNING_HABR_CALC_T
Los resultados del cálculo para los cuatro métodos están en cuatro vistas:
- THINNING_HABR_SIMP_V (realizará el cálculo, causando una SELECCIÓN compleja, por lo que tomará mucho tiempo),
- THINNING_HABR_CALC_V (mostrará los datos de la tabla QUOTES_CALC, por lo que se ejecutará rápidamente)
- THINNING_HABR_CHIN_V (también realizará el cálculo, causando un SELECT complejo, por lo que tomará mucho tiempo),
- THINNING_HABR_PPTF_V (ejecutará la función THINNING_HABR_PPTF).
El tiempo de entrega de todos los métodos ya lo he medido y se muestra en la tabla al final del artículo.
Para el resto de VIEW, ejecutamos las solicitudes y escribimos el tiempo de ejecución:
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
donde XXXX es SIMP, CHIN, PPTF.
Estas vistas calculan el resumen del reclutamiento. Para calcular el resumen, debe buscar todas las líneas y, utilizando el resumen, puede comparar los conjuntos entre sí.
También puede comparar conjuntos utilizando el paquete dbms_sqlhash, pero esto es mucho más lento porque necesita ordenar el conjunto original y el cálculo de hash no es rápido.
También en 12c hay un paquete DBMS_COMPARISON.
Puede verificar la corrección de todos los algoritmos al mismo tiempo. Consideramos los resúmenes como tal solicitud (con 11 millones de entradas en una máquina virtual, esto será relativamente largo, aproximadamente 15 minutos):
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;
Vemos que los resúmenes son los mismos, por lo que todos los algoritmos dieron los mismos resultados.
Ahora vamos a reproducir todo lo mismo en
MS SQL . Probé en la versión 2016.
Primero cree la base de datos DBTEST, luego cree una tabla de transacciones en ella:
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 );
Descargue los datos descargados.
En MSSQL, cree el archivo 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 ""
Y ejecute el script LoadData-MSSQL.sql en SSMS (este script fue generado por el único script de PowerShell proporcionado en la sección de este artículo para Oracle).
Creemos dos funciones:
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
Procedemos a implementar las opciones:Opción 1 - SIMP
Ejecutar: 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);
Las primeras / últimas funciones que faltan se implementan mediante la doble unión de tabla.Opción 2 - CALC
Crear una tabla, procedimiento y vista: 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
No implementé las opciones 3 (CHIN) y 4 (UDAF) en MS SQL.Opción 5 - PPTF
Crear una función de tabla y ver. Esta función es solo una función de tabla, no una función de tabla canalizada paralela, solo la opción ha conservado su nombre histórico de 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;
Calculemos la tabla QUOTES_CALC para el método CALC y escriba el tiempo de ejecución: use DBTEST go exec dbo.THINNING_HABR_CALC
Los resultados del cálculo para los tres métodos están en tres vistas:- THINNING_HABR_SIMP_V (realizará el cálculo, causando una SELECCIÓN compleja, por lo que tomará mucho tiempo),
- THINNING_HABR_CALC_V (mostrará los datos de la tabla QUOTES_CALC, por lo que se ejecutará rápidamente)
- THINNING_HABR_PPTF_V (ejecutará la función THINNING_HABR_PPTF).
Para dos VIEWs, ejecutamos las solicitudes y escribimos el tiempo de ejecución: 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
donde XXXX es SIMP, PPTF.Ahora puede comparar los resultados de cálculo para los tres métodos para MS SQL. Esto se puede hacer en una sola solicitud. Ejecutar: 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 tres líneas coinciden en todos los campos, el resultado del cálculo utilizando los tres métodos es idéntico.Le recomiendo encarecidamente que utilice una pequeña selección en la etapa de prueba, porque el rendimiento de esta tarea en MS SQL es bajo.Si solo tiene el motor MS SQL y desea calcular una mayor cantidad de datos, puede probar el siguiente método de optimización: puede crear índices: 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);
Los resultados de medir el rendimiento en mi máquina virtual son los siguientes:Las secuencias de comandos se pueden descargar desde github : Oracle, el esquema THINNING (las secuencias de comandos de este artículo, el esquema THINNING_LIVE), descargando datos en línea desde bitcoincharts.com y adelgazamiento en línea (pero este sitio solo envía datos de los últimos 5 días en modo en línea) y el script para MS SQL también en este artículo.Conclusión:esta tarea se resuelve más rápido en Oracle que en MS SQL. Con el aumento en el número de transacciones, la brecha se está volviendo más significativa.En Oracle, PPTF era la mejor opción. Aquí el enfoque procesal resultó ser más rentable, esto sucede con poca frecuencia. Otros métodos también mostraron un resultado aceptable: incluso probé el volumen de transacciones de 367 millones en una máquina virtual (el método PPTF calculó el adelgazamiento en una hora y media).En MS SQL, el método de cálculo iterativo (CALC) resultó ser el más productivo.¿Por qué el método PPTF en Oracle resultó ser el líder? Debido a la concurrencia y la arquitectura, una función que se crea como una función de tabla canalizada paralela está incrustada en el medio del plan de consulta: