Há algum tempo atrás, fui encarregado de escrever um procedimento que reduz as cotações do mercado Forex (mais precisamente, dados do período).
A declaração do problema: os dados são inseridos em um intervalo de 1 segundo neste formato:
- Nome do instrumento (código de par USDEUR, etc.),
- Data e hora no formato de hora unix,
- Valor em aberto (preço da primeira transação no intervalo),
- Alto valor (preço máximo),
- Valor baixo
- Valor próximo (preço da última oferta),
- Volume (volume ou volume de transação).
É necessário garantir o recálculo e a sincronização dos dados nas tabelas: 5 s, 15 s, 1 min, 5 min, 15 min, etc.
O formato de armazenamento de dados descrito é chamado OHLC ou OHLCV (Aberto, Alto, Baixo, Fechado, Volume). É frequentemente usado, você pode criar imediatamente um gráfico de "velas japonesas" nele.
Abaixo do corte, eu descrevi todas as opções que eu poderia apresentar, como diminuir (ampliar) os dados recebidos, para análise, por exemplo, o salto de inverno no preço do bitcoin e, de acordo com os dados recebidos, você criará imediatamente um gráfico "Japanese Candles" (no MS Excel, também existe um gráfico ) Na figura acima, este gráfico foi desenvolvido para o período de "1 mês", para a ferramenta "bitstampUSD". O corpo branco da vela indica um aumento no preço no intervalo, preto - uma diminuição no preço, as mechas superiores e inferiores indicam os preços máximos e mínimos que foram atingidos no intervalo. Antecedentes - volume de transações. Vê-se claramente que, em dezembro de 2017, o preço chegou perto da marca de 20K.
A solução será fornecida para dois mecanismos de banco de dados, para Oracle e MS SQL, que, de alguma forma, permitirão compará-los para esta tarefa específica (não generalizaremos a comparação para outras tarefas).
Em seguida, resolvi o problema de uma maneira trivial: calculando o afinamento correto em uma tabela temporária e sincronizando com a tabela de destino - excluindo linhas que existem na tabela de destino, mas que não existem na tabela temporária e adicionando linhas que existem na tabela temporária, mas que não existem no destino. Naquele momento, o cliente satisfez a solução e eu encerrei a tarefa.
Mas agora decidi considerar todas as opções, porque a solução acima contém um recurso - é difícil otimizar para dois casos ao mesmo tempo:
- quando a tabela de destino estiver vazia e você precisar adicionar muitos dados,
- e quando a tabela de destino for grande e você precisar adicionar dados em pequenos pedaços.
Isso se deve ao fato de que, no procedimento, você precisa conectar a tabela de destino e a tabela temporária e precisa anexar à tabela maior, e não vice-versa. Nos dois casos acima, os maiores / menores são trocados. O otimizador decidirá sobre a ordem de conexão com base nas estatísticas, e as estatísticas poderão estar desatualizadas e a decisão poderá ser tomada incorretamente, o que levará a uma degradação significativa do desempenho.
Neste artigo, descreverei métodos de diluição únicos que podem ser úteis para os leitores para análise, por exemplo, o salto de inverno no preço do bitcoin.
É possível fazer o download dos procedimentos de desbaste online no github, no link na parte inferior do artigo.
Diretamente ... Minha tarefa foi diminuindo do período de 1 segundo para o próximo, mas aqui estou considerando a redução do nível da transação (na tabela de origem, os campos STOCK_NAME, UT, ID, APRICE, AVOLUME). Porque esses dados são emitidos pelo bitcoincharts.com.
Na verdade, a dizimação do nível da transação para o nível de "1 segundo" é executada por esse comando (o operador é facilmente traduzido em dizimação do nível de "1 segundo" para os níveis superiores):
No 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);
A função
avg () keep (dense_rank de primeira ordem por UT, ID) funciona assim: como a solicitação é GROUP BY, cada grupo é calculado independentemente dos outros. Dentro de cada grupo, as strings são classificadas por UT e ID, numeradas por
dense_rank . Como a primeira função segue, a linha é selecionada onde
dense_rank retornou 1 (em outras palavras, o mínimo é selecionado) - a primeira transação dentro do intervalo é selecionada. Para esse UT mínimo, ID, se houvesse várias linhas, a média seria considerada. Mas, no nosso caso, haverá uma linha garantida (devido à exclusividade do ID), portanto, o valor resultante será retornado imediatamente como AOPEN. É fácil perceber que a
primeira função substitui duas agregadas.
No MS SQLNão há
primeira / última função (há
first_value / last_value , mas não é isso). Portanto, você precisa conectar a tabela a si mesma.
Não darei a solicitação separadamente, mas você pode vê-la abaixo no procedimento
dbo.THINNING_HABR_CALC . Obviamente, sem o
primeiro / último não é tão elegante, mas funcionará.
Como esse problema pode ser resolvido por um operador? (Aqui, o termo "um operador" significa não que o operador será um, mas que não haverá ciclos que "puxem" os dados em uma linha.)
Vou listar todas as opções conhecidas para resolver esse problema:
- SIMP (simples, simples, produto cartesiano),
- CALC (cálculo, afinamento iterativo dos níveis superiores),
- CHIN (forma chinesa, pedido volumoso para todos os níveis de uma só vez)
- UDAF (função agregada definida pelo usuário),
- PPTF (função de tabela em pipeline e paralela, solução processual, mas com apenas dois cursores, de fato, duas instruções SQL),
- MODE (modelo, frase MODEL),
- e IDEA (ideal, uma solução ideal que pode não funcionar agora).
Olhando para o futuro, direi que esse é o caso raro em que a solução PPTF processual é a mais eficaz no Oracle.
Faça o download dos arquivos de transação em
http://api.bitcoincharts.com/v1/csvEu recomendo escolher arquivos kraken *. Os arquivos localbtc * são muito barulhentos - eles contêm linhas perturbadoras com preços irrealistas. Todos os kraken * contêm cerca de 31 milhões de transações, eu recomendo excluir o krakenEUR de lá e a transação se torna 11 milhões. Este é o volume mais conveniente para teste.
Execute um script no Powershell para gerar arquivos de controle para SQLLDR for Oracle e para gerar uma solicitação de importação 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 "" }
Vamos criar uma tabela de transações no 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;
No Oracle, execute o
arquivo LoadData-Oracle.bat , tendo
corrigido anteriormente os parâmetros de conexão no início do script do Powershell.
Eu trabalho em uma máquina virtual. O download de todos os arquivos de transação 11M em 8 arquivos kraken * (ignorei o arquivo EUR) levou cerca de 1 minuto.
E crie funções que truncarão datas para os limites do 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;
Considere as opções. Primeiro, o código de todas as opções é fornecido e, em seguida, os scripts para inicialização e teste. Primeiro, a tarefa é descrita para Oracle, depois para MS SQL
Opção 1 - SIMP (Trivial)
Todo o conjunto de transações é multiplicado pelo produto cartesiano por um conjunto de 10 linhas com números de 1 a 10. Isso é necessário para obter 10 linhas de uma única linha de transação com datas truncadas nas bordas de 10 intervalos.
Depois disso, as linhas são agrupadas pelo número do intervalo e pela data truncada, e a solicitação acima é executada.
Criar 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);
Opção 2 - CALC (calculado iterativamente)
Nesta opção, reduzimos iterativamente as transações para o nível 1, do nível 1 ao nível 2 e assim por diante.
Crie uma tabela:
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;
Você pode criar um índice usando o campo STRIPE_ID, mas foi estabelecido experimentalmente que é mais lucrativo para transações de 11 milhões sem um índice. Para quantidades maiores, a situação pode mudar. Ou você pode particionar a tabela descomentando o bloco na consulta.
Crie um procedimento:
create or replace procedure THINNING_HABR_CALC_T is begin rollback; execute immediate 'truncate table QUOTES_CALC'; insert
Para simetria, crie uma VIEW simples:
create view THINNING_HABR_CALC_V as select * from QUOTES_CALC;
Opção 3 - CHIN (Código Chinês)
O método difere a franqueza brutal da abordagem e é a rejeição do princípio de "Não se repita". Neste caso, a rejeição de ciclos.
A opção é fornecida aqui apenas para completar.
Olhando para o futuro, direi que, em termos de desempenho nessa tarefa específica, ocupa o segundo lugar.
Grande pedido 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;
Opção 4 - UDAF
A opção com Função agregada definida pelo usuário não será fornecida aqui, mas pode ser visualizada no github.
Opção 5 - PPTF (função de tabela em pipeline e paralela)
Crie uma função (no pacote):
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; /
Criar 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)));
Opção 6 - MODO (cláusula de modelo)
A opção calcula iterativamente a dizimação para todos os 10 níveis usando a frase da cláusula
MODEL com a frase
ITERATE .
A opção também é impraticável porque é lenta. No meu ambiente, 1000 transações para 8 instrumentos são calculadas em 1 minuto. Na maioria das vezes, é gasto o cálculo da frase
MODEL .
Aqui, ofereço esta opção apenas por uma questão de integridade e como confirmação do fato de que no Oracle quase todos os cálculos arbitrariamente complexos podem ser realizados com uma consulta, sem o uso de PL / SQL.
Uma das razões para o baixo desempenho da frase
MODEL nesta consulta é que a pesquisa à direita é realizada para
cada regra, que temos 6. As duas primeiras regras são calculadas rapidamente, porque existe endereçamento explícito direto, sem curingas. Nas quatro regras restantes, existe a palavra
any - existem cálculos mais lentos.
A segunda dificuldade é que você precisa calcular o modelo de referência. É necessário porque a lista de dimensões deve ser conhecida
antes de calcular a frase
MODEL , não podemos calcular novas dimensões dentro dessa frase. Talvez isso possa ser contornado com a ajuda de duas frases MODEL, mas eu não fiz isso por causa do baixo desempenho de um grande número de regras.
Acrescento que seria possível não calcular
UT_OPEN e
UT_CLOSE no modelo de referência, mas usar as mesmas funções
avg () keep (dense_rank primeira / última ordem por) diretamente na frase
MODEL . Mas isso teria acontecido ainda mais devagar.
Devido a limitações de desempenho, não incluirei esta opção no procedimento de teste.
with
Opção 6 - IDEA (ideal, ideal, mas inoperante)
A solicitação descrita abaixo seria potencialmente a mais eficiente e consumiria a quantidade de recursos igual ao mínimo teórico.
Mas nem o Oracle nem o MS SQL permitem que você escreva uma consulta neste formulário. Eu acredito que isso é ditado pelo padrão.
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 à seguinte parte da documentação do Oracle:
Se uma subquery_factoring_clause se referir ao seu próprio query_name na subconsulta que a define, a subquery_factoring_clause será recursiva. Uma subquery_factoring_clause recursiva deve conter dois blocos de consulta: o primeiro é o membro âncora e o segundo é o membro recursivo. O membro âncora deve aparecer antes do membro recursivo e não pode fazer referência a query_name. O membro âncora pode ser composto por um ou mais blocos de consulta combinados pelos operadores definidos: UNION ALL, UNION, INTERSECT ou MINUS. O membro recursivo deve seguir o membro âncora e deve fazer referência ao query_name exatamente uma vez. Você deve combinar o membro recursivo com o membro âncora usando o operador de conjunto UNION ALL.Mas contradiz o seguinte parágrafo da documentação:
O membro recursivo não pode conter nenhum dos seguintes elementos:
A palavra-chave DISTINCT ou uma cláusula GROUP BY
Uma função agregada. No entanto, funções analíticas são permitidas na lista de seleção.Assim, no membro recursivo, agregados e agrupamentos não são permitidos.
Teste
Vamos fazer primeiro pela
Oracle .
Execute o procedimento de cálculo para o método CALC e registre o tempo de sua execução:
exec THINNING_HABR_CALC_T
Os resultados do cálculo para os quatro métodos estão em quatro visualizações:
- THINNING_HABR_SIMP_V (executará o cálculo, causando um SELECT complexo, por isso levará muito tempo),
- THINNING_HABR_CALC_V (exibirá os dados da tabela QUOTES_CALC, para que seja executado rapidamente)
- THINNING_HABR_CHIN_V (também executará o cálculo, causando um SELECT complexo, por isso levará muito tempo),
- THINNING_HABR_PPTF_V (executará a função THINNING_HABR_PPTF).
O lead time de todos os métodos já foi medido por mim e é apresentado na tabela no final do artigo.
Para o restante do VIEW, executamos as solicitações e escrevemos o tempo de execução:
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
onde XXXX é SIMP, CHIN, PPTF.
Essas visualizações calculam o resumo do recrutamento. Para calcular o resumo, é necessário buscar todas as linhas e, usando o resumo, você pode comparar os conjuntos.
Você também pode comparar conjuntos usando o pacote dbms_sqlhash, mas isso é muito mais lento porque você precisa classificar o conjunto original e o cálculo de hash não é rápido.
Também no 12c existe um pacote DBMS_COMPARISON.
Você pode verificar a correção de todos os algoritmos ao mesmo tempo. Consideramos os resumos como uma solicitação (com entradas de 11 milhões em uma máquina virtual, isso será relativamente longo, cerca de 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 os resumos são os mesmos, então todos os algoritmos deram os mesmos resultados.
Agora vamos reproduzir tudo a mesma coisa no
MS SQL . Eu testei na versão 2016.
Primeiro, crie o banco de dados DBTEST e, em seguida, crie uma tabela de transações:
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 );
Faça o download dos dados baixados.
No MSSQL, crie o arquivo 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 ""
E execute o script LoadData-MSSQL.sql no SSMS (esse script foi gerado pelo único script do PowerShell fornecido na seção deste artigo para Oracle).
Vamos criar duas funções:
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
Prosseguimos para implementar as opções:Opção 1 - SIMP
Execute: 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);
As primeiras / últimas funções ausentes são implementadas pela junção automática da tabela dupla.Opção 2 - CALC
Crie uma tabela, procedimento e visualize: 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
Não implementei as opções 3 (CHIN) e 4 (UDAF) no MS SQL.Opção 5 - PPTF
Crie uma função de tabela e visualize. Esta função é apenas uma função de tabela, não uma função de tabela em pipeline paralela, apenas a opção manteve seu nome histórico do 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;
Vamos calcular a tabela QUOTES_CALC para o método CALC e escrever o tempo de execução: use DBTEST go exec dbo.THINNING_HABR_CALC
Os resultados do cálculo para os três métodos estão em três visualizações:- THINNING_HABR_SIMP_V (executará o cálculo, causando um SELECT complexo, por isso levará muito tempo),
- THINNING_HABR_CALC_V (exibirá dados da tabela QUOTES_CALC, para que seja executado rapidamente)
- THINNING_HABR_PPTF_V (executará a função THINNING_HABR_PPTF).
Para duas VIEWs, executamos as solicitações e escrevemos o tempo de execução: 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
onde XXXX é SIMP, PPTF.Agora você pode comparar os resultados do cálculo para os três métodos para o MS SQL. Isso pode ser feito em uma solicitação. Execute: 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;
Se três linhas coincidirem em todos os campos, o resultado do cálculo usando os três métodos é idêntico.Eu recomendo fortemente que você use uma pequena seleção no estágio de teste, porque o desempenho desta tarefa no MS SQL é baixo.Se você possui apenas o mecanismo MS SQL e deseja calcular uma quantidade maior de dados, tente o seguinte método de otimização: é possível criar í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);
Os resultados da medição de desempenho na minha máquina virtual são os seguintes:É possível fazer o download de scripts no github : Oracle, o esquema THINNING - os scripts deste artigo, o esquema THINNING_LIVE - download online de dados do bitcoincharts.com e do thinning online (mas este site envia dados apenas nos últimos 5 dias no modo online) e o script para MS SQL também neste artigo.Conclusão:Esta tarefa é resolvida mais rapidamente no Oracle do que no MS SQL. Com o aumento do número de transações, a diferença está se tornando mais significativa.No Oracle, o PPTF era a melhor opção. Aqui a abordagem processual acabou sendo mais lucrativa, isso acontece com pouca frequência. Outros métodos também mostraram um resultado aceitável - até testei o volume de transações de 367M em uma máquina virtual (o método PPTF calculou o desbaste em uma hora e meia).No MS SQL, o método de cálculo iterativo (CALC) acabou sendo o mais produtivo.Por que o método PPTF no Oracle se tornou o líder? Devido à simultaneidade e à arquitetura, uma função criada como uma função de tabela em pipeline paralela é incorporada no meio do plano de consulta: