Moving average in sql

Eu 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 dessa seleção. Minhas 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 250 registros anteriores. Portanto, para TransactionID 300, colete todos os valores das 250 linhas anteriores (a visualização é classificada como decrescente por TransactionID) e, na coluna MovAvg, grave o resultado da média desses valores. Eu estou olhando para coletar dados dentro de um intervalo de registros. perguntou 28 de outubro às 20: 58Movendo média em T-SQL Um cálculo comum na análise de tendência é a média móvel (ou rolamento). Uma média móvel é a média das últimas 10 linhas, por exemplo. 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 mostrará como calcular a média móvel em T-SQL. Diferentes métodos serão usados ​​dependendo da versão do SQL Server. O gráfico abaixo demonstra o efeito de suavização (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 de longo prazo é claramente visível. T-SQL Movendo 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, calcularemos uma média móvel nos ú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 têm funções que permitem um cálculo muito mais eficaz. SQL Server 2012 e posterior Média Móvel 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 antes da janela deve conter: As linhas anteriores são 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 é bem simples. A figura abaixo demonstra o princípio de janelamento. A linha atual está marcada com amarelo. A janela está marcada com um fundo azul. A média móvel é simplesmente a média de QuoteClose nas linhas azuis: T-SQL Janela média móvel. Os resultados dos cálculos em versões mais antigas do SQL Server são os mesmos, portanto, eles não serão mostrados novamente. Mover Média do SQL Server 2005 8211 2008R2 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: Média Móvel antes do SQL Server 2005 A versão anterior a 2005 usará uma associação externa esquerda para a mesma tabela para obter as últimas 20 linhas. Pode-se dizer que a tabela externa contém a janela na qual queremos calcular uma média: Comparação de desempenho Se executarmos os três métodos diferentes simultaneamente e verificarmos o plano de execução resultante, haverá uma diferença dramática no desempenho entre os métodos: Comparação de três métodos diferentes para calcular a média móvel Como você pode ver, as melhorias na função de janelas no SQL 2012 fazem uma enorme diferença no desempenho. Como mencionado no início deste post, 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 mudanças nas tendências de curto, médio e longo prazo, respectivamente. De particular interesse é o cruzamento de linhas de tendência. Por exemplo, quando a tendência curta se move sobre a tendência longa ou média, isso pode ser interpretado como um sinal de compra na 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. Esta postagem de blog é parte de uma série sobre análise técnica, TA, no SQL Server. Veja os outros posts aqui. Postado por Tomas LindAnteriormente discutimos como escrever médias contínuas no Postgres. Por demanda popular estavam mostrando como fazer o mesmo no MySQL e no 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 barulhento e difícil de obter informações úteis. Podemos suavizá-lo plotando uma média de 7 dias sobre os dados subjacentes. Isso pode ser feito com funções de janela, auto-uniões ou subconsultas correlacionadas - bem, cubra as duas primeiras. Bem comece 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 altera os picos no gráfico para a direita, pois um grande pico é calculado nos sete dias seguintes. Primeiro, crie uma tabela de contagem intermediária Queremos calcular uma média sobre o total de inscrições para cada dia. Supondo que temos uma tabela típica de usuários com uma linha por novo usuário e um registro de data e hora criado, podemos criar nossa tabela de cadastros da seguinte forma: No Postgres e no SQL Server, você pode usar isso como 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 pressupõe que as datas não possuem intervalos. A consulta tem média nas últimas sete linhas, não nas últimas sete datas. Se os dados tiverem intervalos, preencha-os com gereries ou junte-se a uma tabela com linhas de datas densas. MySQL Rolling Average O MySQL não possui funções de janela, mas podemos fazer um cálculo similar usando self-joins. Para cada linha na nossa tabela de contagem, nós nos juntamos a cada linha que estava nos últimos sete dias e pegamos a média. Essa consulta trata automaticamente dos intervalos de datas, pois estamos analisando as linhas dentro de um período, em vez das linhas N anteriores. Média de rolagem do SQL Server O SQL Server possui funções de janela, portanto, calcular a média de rolagem pode ser feito no estilo Postgres ou no estilo MySQL. Por simplicidade, estavam usando a versão do MySQL com um self join. Isso é conceitualmente o mesmo que no MySQL. As únicas traduções são a função dateadd e explicitamente nomeadas grupo por colunas. Outras Médias Nós nos concentramos na média móvel de 7 dias neste post. Se quiséssemos olhar para a média de 7 dias, é tão simples quanto ordenar as datas na outra direção. Se quisermos olhar para uma média centralizada, devemos usar: Postgres: linhas entre 3 precedentes 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, inscrições. datas)