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. Perguntou Oct 28 14 em 20: 58Previamente nós discutimos como escrever médias rolantes em 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. Data) e dateadd (dia, 3, signups. date) Média móvel em T-SQL Um cálculo comum na análise de tendências é a média móvel (ou contínua). 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 2017 e posterior Moving Average Esta versão faz uso de uma função de janela agregada. O que há de novo no SQL 2017 é 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 2017 é 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 2017 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. Publicado por Tomas Lind
No comments:
Post a Comment