Como calcular a média móvel em sql

Usar uma média móvel simples para suavizar os dados é uma técnica bastante popular. É muito ruim o principal exemplo na Ajuda do SQL Anywhere está longe de ser simples: o que torna esse exemplo tão complexo Além da declaração do problema, isto é: calcular a média móvel de todas as vendas de produtos, por mês, no ano 2000. Heres o que faz É complexo: duas referências à função AVG (), um GRUPO BY (que, por si só, faz praticamente qualquer SELECIONAR um scratcher principal),. Uma cláusula WINDOW Stealth uma cláusula WINDOW que nem usa a palavra-chave WINDOW. Então, para os não iniciados (as pessoas que precisam de exemplos mais do que ninguém), não é óbvio que um WINDOW esteja envolvido. Não é apenas qualquer cláusula WINDOW, tenha em atenção você, mas que inclui todos os componentes que você pode codificar em uma cláusula WINDOW: a PARTITION BY, RANGE. Não uma cláusula ROWS simples, mas uma cláusula RANGE completo, uma que possui um relacionamento íntimo com o ORDER BY. Eu sei o que é uma linha, mas o que o redacto é uma GAMA Mas aguarde, há mais: a escolha de GAMA sobre ROWS neste exemplo é fundamental para a operação correta da consulta. (Para uma discussão mais completa sobre este exemplo específico, veja o Exemplo 23 - Computação de uma Média em Movimento no excelente papel branco OLAP de Glenn Paulleys). Agora, vamos voltar à pista: Uma Média Mínima Realmente Simples O seguinte exemplo mostra 10 dias de valor Dados em conjunto com a média móvel do valor de hoje e dos dias de ontem: a cláusula WINDOW nas linhas 21 a 23 define uma janela em movimento que contém duas linhas: a linha de hoje (LUGAR ATUAL) e a linha de ontem (1 PRECEDING): a cláusula WHINDOW ORDER BY determina o que PRECEDING significa (a linha anterior por t. entrydate) ea cláusula ROWS determina o tamanho da janela (sempre duas linhas). A expressão AVG (t. value) OVER twodays na linha 19 refere-se à cláusula WINDOW pelo nome, e informa ao SQL Anywhere que calcula a média dos dois valores de t. value que existem na janela deslizante de 2 filas, para cada um Linha no conjunto de resultados. Então, para 2017-02-02 a média de 10 e 20 é 15.000000, para 2017-02-03 a média de 20 e 10 é 15.000000, para 2017-02-04 a média de 10 e 30 é 20.000000, para 2017- 02-10 a média de 10 e 60 é 35.000000. Ops, e a primeira linha A linha 2017-02-01 não possui uma linha PRECEDING, então qual é a média sobre a janela móvel De acordo com o documento branco de Glenn Paulleys no caso de uma janela em movimento, presume-se que as linhas que contenham Null Os valores existem antes da primeira linha, e após a última linha, na entrada. Isso significa que quando a janela de mudança tem 2017-02-01 como a CORRENTE ATUAL, a 1 linha PRECEDING contém valores NULL. E quando o SQL Anywhere calcula um AVG () que inclui um valor NULL, ele não conta o NULL. Não no numerador ou no denominador ao calcular a média. Sua prova: é por isso que twodayaverage 10.000000 para a primeira linha 2017-02-01. Postado por Breck Carter às 3:47 PM 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 esta seleção. As minhas colunas de exibição são as seguintes: TransactionID é exclusivo. Para cada ID de transação. Gostaria de calcular a média do valor da coluna, em relação aos 250 registros anteriores. Então, para TransactionID 300, colete todos os valores das 250 linhas anteriores (a exibição é ordenada por TransactionID) e, em seguida, na coluna MovAvg, escreva o resultado da média desses valores. Estou procurando coletar dados dentro de uma variedade de registros. Solicitado Oct 28 14 em 20: 58Rolling médias em SQL Server Aggregate funções são convenientes 8211 eles resolvem uma necessidade de negócios e eles facilitam o desenvolvimento. Infelizmente, nem todos os requisitos de negócios são tão fáceis de resolver. Let8217s olham um exemplo: médias rolantes. A média de rolamento Uma média móvel é um conceito simples, uma média é calculada em um subconjunto fixo de dados. Os cálculos médios do rolamento são mais freqüentemente usados ​​com dados da série temporal e ajudam a remover as flutuações de curto prazo, ao mesmo tempo que destacam as tendências a longo prazo. As contas de serviços públicos de 8211 geralmente apresentam uma média móvel do consumo para ajudar o cliente a entender seu uso. Os consumidores não estavam preocupados com o consumo de eletricidade em um dia em agosto, quando houve uma onda de calor que eles queriam ver como seu consumo está mudando ao longo do tempo. Médias rolantes com Expressões da tabela comum: 2005 8211 2008R2 Começando com o SQL Server 2005, tornou-se fácil escrever uma média móvel em uma única declaração T-SQL usando uma Expressão de tabela comum. CTEs rapidamente se tornou popular, uma incrível quantidade de funcionalidades extremamente difíceis foi possível através de CTEs, incluindo consultas recursivas e médias rolantes. Dê uma olhada neste exemplo no banco de dados de exemplo AdventureWorks2017: Embora não seja a abordagem mais direta para a construção de uma média móvel, o CTE consegue fazer o trabalho. Nesta consulta, estamos usando o CTE para criar uma tabela de trabalho e depois realizar uma auto-associação. Esse mesmo tipo de coisa é possível usando uma tabela temporária ou uma variável de tabela, mas o CTE conclui em uma declaração e é, possivelmente, mais fácil de ler. Common Table Expressions também esconde um segredo escuro 8211 SQL Server executa o corpo CTE sempre que a expressão CTE, cte neste exemplo, é referenciada. Quanto mais complexa for a Common Table Expression, mais trabalho deve ser realizado. Executando esta média de rolamento com STATISTICS IO ativado, é fácil ver as múltiplas execuções na forma de duas varreduras em cada tabela. Em um banco de dados, isso é pequeno, isso não apresenta problemas de desempenho importantes, mas isso causará grandes problemas para um tamanho moderado base de dados. Médias rolantes com funções de janela: 2017 e além do SQL Server 2017 forneceram melhor suporte para funções de janelas. Embora o suporte para OVER () já estivesse disponível no SQL Server 2005, o SQL Server 2017 traz consideravelmente mais funcionalidades na tabela. Ao usar a cláusula ROW ou RANGE da função de janela, é possível simplificar a consulta e melhorar o desempenho. Dê uma olhada: embora as duas perguntas sejam notavelmente diferentes, a maior diferença é a introdução do ROWS 12 PRECEDING. Isso toma o lugar da auto-união no exemplo anterior. Em vez de escrever uma junção para nós mesmos, simplesmente dizemos ao SQL Server que we8217d gosta de uma média de st nas últimas 12 linhas ordenadas por ano e mês. Que tipo de efeito isso tem no trabalho, o SQL Server executa 111 leituras em vez de 369 leituras. Claramente, esta alteração proporciona uma melhoria de desempenho substancial para o SQL Server. We8217ve reduziu o número de leituras, eliminou a complexidade de uma consulta e tornou isso um tanto óbvio para os futuros desenvolvedores como eles poderiam modificar ou construir sobre isso no futuro. Alterar a nova consulta para uma média contínua por dia em vez de mês é simples e requer ainda menos linhas de código: Isso executa o número de nome de leitura lógica e física como a média móvel mensal usando uma função de janela. Resumindo Lá, você tem 8211 duas maneiras diferentes de executar uma média móvel no SQL Server. Um método é claramente muito mais fácil do que o outro. Existem várias otimizações no SQL Server 2017 para facilitar a criação desta funcionalidade e melhorar o desempenho do SQL Server ao mesmo tempo. Obrigado Jeremias. Agora estou no meio da migração de um sistema 2008R2 que computa SMAEMAMACD8217s para SQL 2017. Ele está usando a função de janelas R28217s OVER, mas tem que usar o ROWNUMBER e fazer um pouco de matemática extra devido à falta da nova cláusula ROWS SUPERIOR de 2017. EU AMO o novo ROWS PRECEDING E o desempenho é WAY mais rápido em 2017 do que no R2. Obrigado por os exemplos You8217re bem vindo Obrigado por compartilhar um pouco sobre o seu caso de uso 8211 you8217ve atingiu outra técnica que I8217ve usado com algum sucesso moderado no SQL Server 2008R2 e anteriores: combinando ROWNUMBER com uma semi-união para executar SUMAVGetc. Espero que a sua migração seja suave.