Calculate Moving Average In Sql Server

Estou trabalhando com o SQL Server 2008 R2, tentando calcular uma média móvel. Para cada registro na minha opinião, gostaria de coletar os valores dos 250 registros anteriores e, em seguida, calcular a média para essa seleção. As colunas de exibição são as seguintes: TransactionID é exclusivo. Para cada TransactionID. Eu gostaria de calcular a média para o valor da coluna, sobre os anteriores 250 registros. Assim para TransactionID 300, coletar todos os valores de 250 linhas anteriores (exibição é classificada decrescente por TransactionID) e, em seguida, na coluna MovAvg gravar o resultado da média desses valores. Eu estou olhando para coletar dados dentro de um intervalo de registros. As médias móveis exponenciais são semelhantes às médias móveis ponderadas, uma vez que 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 em High Coast Database Solutions AB. Anteriormente, discutimos como escrever médias rolando no Postgres. Pela demanda popular estavam mostrando-lhe como fazer o mesmo no MySQL e SQL Server. Bem, cubra como anotar gráficos barulhentos como este: Com uma linha média anterior de 7 dias como esta: A grande idéia Nosso primeiro gráfico acima é bastante ruidoso e difícil de obter informações úteis. Podemos suavizar-lo traçando uma média de 7 dias sobre os dados subjacentes. Isso pode ser feito com funções de janela, self-joins, ou subconsultas correlacionadas - bem cobrir os dois primeiros. Bem começar com uma média anterior, o que significa que o ponto médio no dia 7 do mês é a média dos primeiros sete dias. Visualmente isso desloca os picos no gráfico para a direita, uma vez que um grande pico é calculado nos sete dias seguintes. Primeiro, Crie uma Tabela de Contagem Intermediária Queremos calcular uma média sobre as inscrições totais para cada dia. Assumindo que temos uma tabela típica de usuários com uma linha por novo usuário e um timestamp createdat, podemos criar nossa tabela agregados de inscrições da seguinte forma: No Postgres e SQL Server você pode usar isso como um CTE. No MySQL você pode salvá-lo como uma tabela temporária. Postgres Rolling Average Felizmente Postgres tem funções de janela que são a maneira mais simples de calcular uma média em execução. Esta consulta assume que as datas não têm lacunas. A consulta é a média das últimas sete linhas, não as últimas sete datas. Se seus dados tiverem lacunas, preencha-as com generateseries ou junção contra uma tabela com linhas de data densas. MySQL Rolling Average O MySQL não tem funções de janela, mas podemos fazer um cálculo semelhante usando auto-uniões. Para cada linha em nossa tabela de contagem, juntamos cada linha que estava nos últimos sete dias e tomamos a média. Esta consulta trata automaticamente as lacunas de data, uma vez que estamos a olhar para linhas dentro de um intervalo de datas em vez das N linhas precedentes. SQL Server Rolling Average O SQL Server possui funções de janela, portanto, calcular a média móvel pode ser feita no estilo Postgres ou no estilo MySQL. Para simplificar, estavam usando a versão MySQL com um auto join. Isso é conceitualmente o mesmo que no MySQL. As únicas traduções são a função dateadd e nomeado explicitamente grupo por colunas. Outras médias Nós nos concentramos na média final de 7 dias neste post. Se quiséssemos olhar para a média de 7 dias, é tão simples como classificar as datas na outra direção. Se queremos olhar para uma média centrada, use wed: Postgres: linhas entre 3 precedente e 3 seguindo MySQL: entre signups. date - 3 e signups. date 3 no MySQL SQL Server: entre dateadd (dia, -3, inscrições. Date) e dateadd (dia, 3, signups. date) Im não completamente familiarizado com SQL ainda: Eu respondi muito rapidamente desculpe. Eu tentei alterar o seu código para adicionar uma nova média móvel, mas eu falhei, você poderia ter um olhar sobre ele e me diga o que está faltando COM MA (SELECT ma. TradeDate, ma. Opening, ROWNUMBER () OVER (ORDER BY TradeDate ) AS Ordem FROM EurostoxxBase1 ma) SELECT ma. TradeDate, ma. Opening, ma2.Opening, ma3.Opening, ((ma. Opening 43 ma2.Opening) 2) AS MovingAverage2, ((ma. Opening 43 ma2.Opening 43 ma3 ) 3) AS MovingAverage3, ((ma. Opening 43 ma2.Opening 43 ma3.Opening) 43 ma4.Opening) 4) AS MovingAverage4 FROM MA ma LEFT OUTER JOIN MA ma2 ON ma. Order ma2.Order 43 1 LEFT OUTER JOIN MA ma3 ON ma2.Order ma3.Order 43 1 LEFT OUTER JOIN MA ma4 ON ma3.Order ma4.Order 43 1 Quando eu executo seu script ele cria uma tabela temporária, o que eu gostaria de adicionar essas novas colunas (MovingAverage1 MovingAverage2 . ) para a mesa. O que você tem para uma média móvel de 4 dias parece bom, exceto que você tem um parêntese de esquerda extra depois de ma3.Opening quarta) 43 ma4.Opening) quot Retire o um após ma3.Opening. Para ter médias móveis na sua tabela base, os gatilhos terão que estar envolvidos, a menos que outra pessoa tenha outra idéia - diferente de que você poderia criar uma exibição para encapsular a instrução select e, em seguida, usar a exibição em vez da tabela base. Terça-feira, abril 27, 2010 1:29 PM Você poderia me dizer mais sobre como usar gatilho neste caso. Eu preciso ter as diferentes médias móveis na minha mesa, eu tenho que fazer algum cálculo. Eu preciso adicionar essas médias móveis em minha tabela. Terça-feira, abril 27, 2010 2:14 PM Triggers irá adicionar em uma grande quantidade de sobrecarga. Por exemplo, se você mantivesse 4 dias de médias móveis em sua tabela base, quando um item fosse inserido ou atualizado, o insertupdate ocorreria e, em seguida, até 4 atualizações mais por item, então 5 no total para um único insertupdate. Para alguns itens você não vai ter muita contenção, no entanto, se houvesse muitos insertsupdates um segundo minuto isso poderia levar a muitas questões incluídas deadlocking. Eu não aconselho manter valores computados em tabelas de base, eles devem ser mantidos em pontos de vista ou usando serviços de análise. Gostaria de mudar a arquitetura de aplicação antes que as outras questões rastejar e causar estragos mais tarde. No entanto, você pode ler sobre Triggers AQUI. Terça-feira, 27 de abril de 2010 14:32 O valor armazenado não vai mudar por qualquer motivo. São dados históricos. As Médias Móveis não mudam nem. O que eu acho que é crate uma segunda tabela (não temporária) e, em seguida, juntar Minha tabela inicial com dados históricos ea tabela com calculadas médias móveis. Todos esses dados são fixos, sem atualização, sem inserção, sem exclusão. A única questão que eu posso imaginar é quando eu vou ter que adicionar mais dados, por exemplo, vou ter que adicionar abril de 2010 nos primeiros dias de maio, eu não quero voltar a executar todo o processo, mas basta adicionar para o específico Valores e eu acho que posso gerenciar isso. O que você acha, Estou completamente errada juntando-se a essas 2 tabelas Terça-feira, 27 de abril de 2010 14:46 Mas parece que há um problema com a tabela que eu criei: CREATE TABLE MovingAverage (TradeDateAvg SMALLdatetime, OpeningAvg FLOAT, B1MAvg2 FLOAT, B1MAvg3 FLOAT, B1MAvg4 FLOAT COM MA AS (SELECCIONAR ma. TradeDate, ma. Opening, ROWNUMBER () OVER (ORDER BY TradeDate) AS Ordem FROM EurostoxxBase1 ma) SELECT ma. TradeDate, maOpening, ma2.Opening, ma3.Opening, ma4.Opening, ((ma. Opening 43 ma2.Opening) 2) AS B1MAvg2, ((ma. Opening 43 ma2.Opening 43 ma3.Opening) 3) AS B1MAvg3 FROM MA ma LEFT OUTER JOIN MA ma2 ON ma. Order ma2. Ordem 43 1 LEFT OUTER JOIN MA ma3 ON ma2.Order ma3.Order 43 1 LEFT OUTER JOIN MA ma4 ON ma3.Order ma4.Order Funciona perfeitamente, posso ver todos os valores Mas a tabela está vazia, quero dizer, quando eu corro. Selecione de MovingAverage Dá-me apenas os nomes das colunas, nenhum valor em todos Alguma idéia o que eu fiz errado Muito obrigado, eu modifiquei meu código: CREATE TABLE MovingAverage (TradeDateAvg SMALLdat Etime, OpeningAvg FLOAT, B1MAvg2 FLOAT, B1MAvg3 FLOAT, B1MAvg4 FLOAT COM MA AS (SELECT ma. TradeDate, ma. Opening, ROWNUMBER () OVER (ORDER BY TradeDate) AS Ordem FROM EurostoxxBase1 ma) INSERIR EM MovingAverage (TradeDateAvg, OpeningAvg, B1MAvg2 , B1MAvg3, B1MAvg4) SELECT ma. TradeDate, ma. Opening, ((ma. Opening 43 ma2.Opening) 2) AS B1MAvg2, ((ma. Opening 43 ma2.Opening 43 ma3.Opening) 3) AS B1MAvg3, (( Ma. Opening 43 ma2.Opening 43 ma3.Opening 43 ma4.Opening) 4) AS B1Mavg4 FROM MA ma LEFT OUTER JOIN MA ma2 ON ma. Order ma2.Order 43 1 LEFT OUTER JOIN MA ma3 ON ma2.Order ma3.Order 43 1 LEFT OUTER JOIN MA ma4 ON ma3.Order ma4.Order 43 1 Mas nada acontece A solicitação ainda está em andamento, sem mensagem de erro, mas parece que não cria nada. Qualquer idéia do que eu sinto falta novamente