Calculate Exponential Moving Average In Sql

Estou tentando implementar uma média móvel exponencial (EMA) no postgres, mas como eu verificar a documentação e pensar sobre isso, mais eu tento o mais confuso que eu sou. A fórmula para EMA (x) é: Parece ser perfeito para um agregador, mantendo o resultado do último elemento calculado é exatamente o que tem que ser feito aqui. No entanto, um agregador produz um único resultado (como reduzir ou dobrar) e aqui precisamos de uma lista (uma coluna) de resultados (como mapa). Tenho verificado como os procedimentos e funções funcionam, mas AFAIK produzem uma única saída, não uma coluna. Tenho visto muitos procedimentos e funções, mas eu realmente não consigo descobrir como isso interage com a álgebra relacional, especialmente ao fazer algo assim, um EMA. Eu não tive sorte pesquisando o Internet até agora. Mas a definição para um EMA é bastante simples, espero que seja possível traduzir esta definição em algo que funciona no postgres e é simples e eficiente, porque mover para o NoSQL vai ser excessivo no meu contexto. Isto está calculando a agregação que produz o resultado em cada linha para cada sublista dos dados de entrada Porque parece que está usando o agregador até a linha n, retornando o resultado e indo para a linha 0 para calcular a agregação até a linha n1 novamente. Existe alguma maneira de usar a acumulação ou alguma variável estática (como em C) para que isso tem que ser calculado uma vez Obrigado. Ndash Trylks Jan 20 12 at 11:59 Não, ele está usando o valor acumulado. Se você executar a consulta com o comando quotraise infoquot descomentado, você poderá ver que a função é chamada apenas uma vez para cada saída de linha. O Postgresql fornece o valor de estado em cada linha (se houvesse um finalfunc definido, que seria chamado para transformar o estado em um valor de saída). Ndash araqnid Jan 20 12 at 12:04 ErwinBrandstetter: Eu revertei a maioria das mudanças - no caso do formato da primeira parte (âncora) da consulta, EMA (x1) pode ser claramente representado com uma única linha - este Corespnds para a única linha que o define na pergunta. No caso da parte recursiva da consulta, usei mn - 1 na condição join para indicar a equivalência da relação com EMA (xn - 1) na pergunta, mesmo que esta seja menos performante se o desempenho for um O OP pode alterar a condição de junção para ser como sugerido. Ndash Mark Bannister Jan 16 12 às 9: 27SQL Server Código T-SQL para calcular uma média móvel Por: Dallas Snider Leia comentários Dicas relacionadas: Mais Funções - User Defined UDF Como posso suavizar os dados em uma coluna com uma média móvel em T - SQL Você pode, por favor, caminhar através de um exemplo no SQL Server com código T-SQL Como podemos validar os resultados Os dados de séries temporais podem ser intrinsecamente ruidosos e uma boa maneira de suavizar os dados é calcular uma média móvel. Há uma série de maneiras de calcular uma média móvel em T-SQL, mas nesta ponta vamos olhar para uma maneira de calcular uma média móvel que define a janela de média x número de linhas por trás e x número de linhas à frente da corrente Linha de dados. A vantagem disso é que não há atraso no valor médio retornado eo valor da média móvel está na mesma linha com seu valor atual. Vamos começar criando uma tabela e carregando alguns dados usando o T-SQL abaixo. Temos 361 pontos de dados que criam uma onda senoidal barulhenta. Depois de carregar os dados, vamos executar o seguinte código T-SQL para selecionar todas as colunas, juntamente com o valor da média móvel. No código abaixo, o tamanho da janela média móvel é 15 (7 linhas que precedem a linha atual, mais a linha atual, mais as 7 linhas seguintes). A média móvel da coluna DataValue é retornada como a coluna MovingAverageWindowSize15. A cláusula ORDER BY é extremamente importante para manter os dados na ordem classificada adequada. Podemos copiar e colar os resultados no Excel para validar o cálculo está correto. Na imagem abaixo, a janela começa na célula C3 e termina em C17. A média móvel calculada pelo T-SQL nesta dica aparece na célula D10. A média calculada pelo Excel está na parte inferior e é igual ao valor em D10. Na figura abaixo, podemos ver os valores de dados originais traçados em azul com a média móvel plotada em vermelho. Próximas etapas Ajuste o tamanho da janela da média móvel para ver como a plotagem muda. Além disso, certifique-se de verificar essas outras dicas sobre T-SQL de mssqltips: Última atualização: 382016Moving média em T-SQL Um cálculo comum na análise de tendência é a média móvel (ou rolling). Uma média móvel é a média das, por exemplo, as últimas 10 linhas. A média móvel mostra uma curva mais suave do que os valores reais, mais ainda com um período mais longo para a média móvel, tornando-se uma boa ferramenta para análise de tendências. Esta postagem do blog irá mostrar como calcular a média móvel em T-SQL. Métodos diferentes serão usados ​​dependendo da versão do SQL Server. O gráfico abaixo demonstra o efeito de alisamento (linha vermelha) com uma média móvel de 200 dias. As cotações de ações são a linha azul. A tendência a longo prazo é claramente visível. T-SQL Moving Avergage 200 dias A demonstração abaixo requer o banco de dados TAdb que pode ser criado com o script localizado aqui. No próximo exemplo vamos calcular uma média móvel para os últimos 20 dias. Dependendo da versão do SQL Server, haverá um método diferente para fazer o cálculo. E, como veremos mais adiante, as versões mais recentes do SQL Server possuem funções que permitem um cálculo muito mais efetivo. SQL Server 2012 e posterior Moving Average Esta versão faz uso de uma função de janela agregada. O que há de novo no SQL 2012 é a possibilidade de restringir o tamanho da janela, especificando quantas linhas que precedem a janela devem conter: Linhas precedentes é 19, porque incluiremos a linha atual também no cálculo. Como você pode ver, o cálculo da média móvel no SQL Server 2012 é bastante simples. A figura abaixo demonstra o princípio de janela. A linha atual é marcada com amarelo. A janela é marcada com um fundo azul. A média móvel é simplesmente a média de QuoteClose nas linhas azuis: Janela de média móvel T-SQL. Os resultados dos cálculos em versões mais antigas do SQL Server são os mesmos, para que eles não serão mostrados novamente. SQL Server 2005 8211 2008R2 Moving Average Esta versão faz uso de uma expressão de tabela comum. O CTE é auto referenciado para obter as últimas 20 linhas para cada linha: Movendo Média antes do SQL Server 2005 A versão anterior a 2005 usará uma junção externa esquerda na mesma tabela para obter as últimas 20 linhas. Comparação de desempenho Se executamos os três métodos diferentes simultaneamente e verificamos o plano de execução resultante, há uma diferença dramática no desempenho entre os métodos: Comparação de três Diferentes métodos para calcular a média móvel Como você pode ver, as melhorias na janela de função no SQL 2012 faz uma enorme diferença no desempenho. Como mencionado no início deste post, as médias móveis são usadas como uma ferramenta para ilustrar tendências. Uma abordagem comum é combinar médias móveis de diferentes comprimentos, a fim de detectar alterações nas tendências a curto, médio e longo prazo, respectivamente. De particular interesse são a passagem de linhas de tendência. Por exemplo, quando a tendência curta se move sobre a tendência de longo ou médio, isso pode ser interpretado como um sinal de compra em análise técnica. E quando a tendência curta se move sob uma linha de tendência mais longa, isso pode ser interpretado como um sinal de venda. O gráfico abaixo mostra Cotações, Ma20, Ma50 e Ma200. T-SQL Ma20, Ma50, Ma200 comprar e vender sinais. Este blog é parte de uma série sobre análise técnica, TA, no SQL Server. Veja os outros posts aqui. As médias móveis exponenciais são semelhantes às médias móveis ponderadas, pois atribuem menos peso às mudanças há muito tempo e mais peso às mudanças recentes. As médias móveis ponderadas são lineares, mas as médias móveis exponenciais são exponenciais. Ou seja, o peso pode ser expresso como uma curva: Existe uma ótima maneira de calcular médias exponenciais em T-SQL usando um recurso não documentado sobre variáveis ​​e totais em execução no SQL Server. Nesta postagem do blog vou mostrar como usar esse método para calcular a média móvel exponencial em T-SQL, mas também apresentarei um método que está usando recursos padrão no SQL Server. Infelizmente, isso significa usar um loop. Nos exemplos vou calcular uma média móvel exponencial de 9 dias. Os exemplos usam o banco de dados TAdb. Um script para criar TAdb pode ser encontrado aqui. A teoria por trás dos recursos totais em atualizações é descrita em detalhes por Jeff Moden em seu artigo Resolvendo os Problemas de Total Corrente e Ordinal Rank. Outros recursos que descrevem a utilização deste método para calcular EMA são o blog Calculando Médias Móveis com T-SQL por Gabriel Priester e o post do fórum Exponential Moving Average Challenge. Ambos no SQL Server Central. Basicamente, no T-SQL você pode atualizar variáveis, bem como colunas em uma instrução de atualização. As atualizações são feitas linha a linha internamente pelo SQL Server. Esse comportamento linha a linha é o que torna o cálculo de um total em execução possível. Este exemplo mostra como ele funciona: Observe que 8220ColumnRunningTotal8221 é um total de execução de 8220ColumnToSum8221. Usando este método, podemos calcular EMA9 com este T-SQL: O cálculo de EMA é bastante simples. Usamos a linha atual e a anterior, mas com mais peso para a linha atual. O peso é calculado pela fórmula 2 (19), onde 822098221 é o parâmetro para o comprimento do EMA. Para calcular EMA9 para a linha 10 acima, o cálculo é: Neste caso, a linha atual recebe 20 do peso (2 (19) 0,2) ea linha anterior recebe 80 do peso (1-2 (19) 0,8). Você encontra esse cálculo na instrução acima na instrução CASE: MOVIMENTAÇÃO EXPRESSIVA (EMA): Método de loop Até onde eu sei, exceto para o método de totais em execução descrito acima, não há maneira de calcular EMA usando uma instrução SQL baseada em conjunto . Portanto, o T-SQL abaixo está usando um loop while para calcular EMA9: Os resultados são os mesmos que no exemplo de totais em execução acima. Desempenho Como esperado, o conjunto baseado execução totais versão é maneira mais rápida do que a versão de loop. Na minha máquina a solução baseada em conjunto foi de cerca de 300 ms, em comparação com cerca de 1200 com a versão de loop. A versão do laço é mais conforme aos padrões do SQL entretanto. Portanto, a escolha entre os métodos depende do que é mais importante para você, desempenho ou padrões. A média móvel exponencial pode ser utilizada na análise de tendências, como nos outros tipos de médias móveis, média móvel simples (SMA) e média móvel ponderada (WMA). Há também outros cálculos na análise técnica que usa o EMA, MACD por exemplo. Este blog é parte de uma série sobre análise técnica, TA, no SQL Server. Veja os outros posts aqui. Postado por Tomas Lind Tomas Lind - Serviços de consultoria como SQL Server DBA e desenvolvedor de banco de dados na High Coast Database Solutions AB.