Moving Average Ms Sql

Esta é uma questão Evergreen Joe Celko. Ignoro qual plataforma DBMS é usada. Mas, em qualquer caso, Joe conseguiu responder há mais de 10 anos com o SQL padrão. Joe Celko SQL Puzzles and Answers citação: Essa última tentativa de atualização sugere que poderíamos usar o predicado para construir uma consulta que nos daria uma média móvel: a coluna extra ou a abordagem de consulta melhor. A consulta é tecnicamente melhor porque a abordagem UPDATE Desmoralize o banco de dados. No entanto, se os dados históricos que estão sendo registrados não mudem e o cálculo da média móvel é caro, você pode considerar usar a abordagem da coluna. Consulta SQL Puzzle: por todos os meios uniforme. Você simplesmente joga no balde de peso apropriado dependendo da distância do ponto de tempo atual. Por exemplo, quottake weight1 para datapoints dentro de 24 horas a partir do ponto de dados atual0.5 para pontos de dados dentro de 48hrsquot. Esse caso é importante quantos pontos de dados consecutivos (como 6:12 am e 11:48 pm) estão distantes um do outro Um caso de uso que eu posso pensar seria uma tentativa de alisar o histograma sempre que os pontos de dados não são suficientemente densos. 22:22 Não tenho certeza se o resultado esperado (saída) mostra uma média simples de movimentação simples (rolando) durante 3 dias. Como, por exemplo, o primeiro triplo dos números, por definição, dá: mas você espera 4.360 e é confuso. No entanto, sugiro a seguinte solução, que usa AVG de função de janela. Essa abordagem é muito mais eficiente (clara e menos intensiva em recursos) do que a SELF-JOIN introduzida em outras respostas (e estou surpreso que ninguém tenha dado uma solução melhor). Você vê que o AVG está envolvido com o caso quando rownum gt p. days, em seguida, para forçar NULL s nas primeiras linhas, onde 3 Day Moving Average não tem sentido. Respondeu 23 de fevereiro às 13:12. Podemos aplicar o método de junção externa suja esquerda de Joe Celkos (como citado acima por Diego Scaravaggi) para responder a pergunta conforme foi solicitado. Gera o resultado solicitado: respondeu Jan 9 16 às 0:33 Sua resposta 2017 Stack Exchange, IncMoving média em T-SQL Um cálculo comum na análise de tendências é a média móvel (ou rolando). 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 publicação do blog 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 suavização (linha vermelha) com uma média móvel de 200 dias. As citaçõ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, 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 efetivo. SQL Server 2012 e posterior Moeda em movimento Esta versão faz uso de uma função de janela agregada. O que é novo no SQL 2012 é a possibilidade de restringir o tamanho da janela, especificando quantas linhas que precedem a janela devem conter: as linhas anteriores são 19, pois 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 janelas. 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 média T-SQL. Os resultados dos cálculos em versões antigas do SQL Server são os mesmos, então eles não serão exibidos novamente. SQL Server 2005 8211 2008R2 Média móvel 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 em Movimento antes do SQL Server 2005 A versão pré 2005 usará uma associação externa esquerda para a mesma tabela para obter as últimas 20 linhas. A tabela externa pode ser dita para conter a janela em que queremos calcular uma média: 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 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. Conforme mencionado no início desta publicação, as médias móveis são usadas como uma ferramenta para ilustrar as 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 são 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 Quotes, Ma20, Ma50 e Ma200. Sinais de compra e venda T-SQL Ma20, Ma50, Ma200. Esta publicação no blog faz parte de uma série sobre análise técnica, TA, no SQL Server. Veja as outras publicações aqui. Postado por Tomas LindHi, eu fiquei me perguntando como fazer isso, eu tenho uma consulta que eu executo onde eu quero executar uma média móvel de 10 dias nos valores SUM (a. GBPRevenue), consegui fazer uma soma cumulativa (usando Arquivo b) mas a média móvel é complicada :( SELECT COUNT (DISTINCT a. OrderNumber) AS siteCount, COUNT (DISTINCT dbo. NewOrders. OrderNumber) AS TotalOrderAmountNewOrders, SUM (a. GBPRevenue) COMO GBPRevenue, DATEADD (dia, DATEDIFF (dia , 19000101, a. BatchCompleted), 19000101) AS data, (SELECT SUM (b. GBPRevenue) FROM dbo. Archive b WHERE DATEADD (dia, DATEDIFF (dia, 19000101, b. BatchCompleted), 19000101) lt DATEADD (dia, DATEDIFF) (Dia, 19000101, a. BatchCompleted), 19000101) E (clientID 0 OU clientID clientID) E (Site site OU site 0)) AS cumulativeGBPRevenue GROUP BY DATEADD (dia, DATEDIFF (dia, 19000101, a. BatchCompleted), 19000101) Segunda-feira, 19 de março de 2012 12:18 PM Use CROSS APLICAR-SE com um TOP (10) dentro. N 560439.26quot E 125505.63quot Marcado como resposta por Kalman Toth Moderador Saturda Y, 06 de outubro de 2012 8:27 AM Eu tentei a primeira abordagem como essa: mas as linhas extras retornaram minhas colunas de soma SUM (a. GBPRevenue) COMO GBPRevenue, AVG (t2.GBPRevenue) COMO GBPRevenueMovingAverage de JOIN dbo. Archive t2 ON DATEDIFF (dia, a. BatchCompletedNoTime, t2.BatchCompletedNoTime) ENTRE 0 E 10 declara t tabela (data data não nulo, taxa flutuante não nulo) inserir t selecionar getdate () - 10, 1 união todos seleciona getdate () - 9, 2 união todos selecionam getdate () - 8, 4 união todos selecionam getdate () - 7, 4 união todos selecionam getdate () - 6, 5 união todos selecionam getdate () - 5, 6 union todos selecionam getdate () - 4, 6 union all select getdate () - 3, 8 union all select getdate () - 2, 9 union all select getdate () - 1, 10 union all select getdate () - 0, 11 union all select getdate () 431, 9 Selecione t. date, avg (tt. rate) como simpleMovingAvg de t AS t join ttt DATEDIFF (dia, tt. date, t. date) entre 0 e 2 grupo por ordem t. date por t. date segunda-feira, março 19, 2012 3:05 PM declare t table (date datetime not null, rate float not null) insert t Selecione getdate () - 10, 1 union tudo selecione getdate () - 9, 2 union todos selecionam getdate () - 8, 4 union todos selecionam getdate () - 7, 4 union todos selecionam getdate () - 6, 5 union all Selecione getdate () - 5, 6 união todos selecione getdate () - 4, 6 union todos selecione getdate () - 3, 8 union todos selecionam getdate () - 2, 9 union todos selecionam getdate () - 1, 10 union all Selecione getdate () - 0, 11 union all select getdate () 431, 9 selecione t. date, avg (tt. rate) como simplesMovingAvg de t AS t join ttt em DATEDIFF (dia, tt. date, t. date) Entre 0 e 2 grupo por ordem t. date por t. date - SwePeso SELECIONAR d. Date, AVG (t. Rate) FROM t AS t CROSS APPLY (SELECIONAR 1, DATEADD (DAY, 0, t. Date) UNION ALL SELECIONE 0, DATEADD (DIA, 1, t. Date) UNION ALL SELECT 0, DATEADD (DAY, 2, t. Date)) AS d (Keep, Date) GROUP BY d. Date HAVING MAX (d. Keep) 1 N 560439.26quot E 125505.63quot segunda-feira, 19 de março de 2012 3:24 PM esta seleção aninhada foi feita: bastante simples na verdade (SELECIONE AVG (GBPRevenue) FROM dbo. Archive b WHERE (DATEDIFF (dia, a. BatchCompletedNoTime, b. BatchCompletedNoTime) B ETWEEN 0 AND 10 AND (clientID 0 ou b. clientID clientID) E (site site ou site 0))) COMO GBPRevenueMovingAverage Editado por Quantum Information Segunda-feira, 19 de março de 2012 3:27 PM Marcado como resposta por Quantum Information Segunda-feira, 19 de março , 2012 3:27 PM Não marcado como resposta por Quantum Information Segunda-feira, 19 de março de 2012 5:27 PM Segunda-feira, 19 de março de 2012 15:27 Não é isso que vai dar-lhe uma média móvel de 11 dias (entre 0 e 10) segunda-feira , 19 de março de 2012 3:28 PM Tabelas têm chaves, é isso o que você quis dizer Por que você se importa que as receitas estejam em GBP O que faz disso um tipo de entidade totalmente diferente de outras receitas CREATE TABLE OrderArchives (ordertimestamp DATETIME2 (3) NOT NULL PRIMARY KEY, ordernbr INTEGER NOT NULL, revenueamt DECIMAL (18,2) NOT NULL) INSERT INTO OrderArchives VALUES (2010-06-23 08: 32: 33.670, 606, 2252.68), (2010-06-23 09: 32: 31.453 , 607, 2350.75), (2010-06-23 16: 05: 59.053, 008, 2535.49), (2010-06-27 04: 06: 46.103, 609, 274.54), (2010-06-29 12:25: 50,483, 610, 2254,20), (2010-07 -04 13: 49: 10.460, 601, 255.23), (2010-07-05 18: 40: 03.083, 614, 1000.09) O primeiro problema é que suas especificações não são claras. Dias de dez dias de calendário anteriores Dias de dez dias úteis Dias de dez dias arquivados O próximo problema é que você tenha um timestamp (DATETIME no dialecto de T-SQL) e que deseje dados por dia. Deixe-nos fazer isso em um CTE e obter totais diários. Em seguida, a cláusula da janela 2012 facilita o resto. COMO DailyOrderArchives (orderdate, dailyrevenuetot) AS (SELECT orderdate, SUM (revenueamt) FROM (SELECT CAST (ordertimestamp AS DATE) AS orderdate, revenueamt FROM OrderArchives) AS X GROUP BY orderdate) - aqui é um palpite sem especificações claras SELECT orderdate, AVG (revenueamt) OVER (ORDER BY orderdate ROWS ENTRE 10 PRECEDING AND CURRENT ROW) COMO dailyrevenueamtrunningavg FROM DailyOrderArchives --CELKO-- Livros na série Celko para Morgan-Kaufmann Publishing: analítica e OLAP em dados e bases de dados SQL: conceitos na prática Medições de dados E padrões em SQL SQL para Smarties Estilo de programação SQL Quebra-cabeças e respostas SQL Pensando em conjuntos Árvores e hierarquias em SQL Segunda-feira, 19 de março de 2012 3:37 PM isso basicamente é feito o que eu quero, eu dividi a consulta em apenas 2 partes A coisa é que geralmente está produzindo valores maiores do que deveria para as médias móveis :( DECLARE tbl TABLE (dailyGBPRevenue NUMÉRIC (18, 4), TotalOrderCountProcessedOrders INT, data DATETIME) INSERT INT O tbl SELECT SUM (a. GBPRevenue) COMO GBPRevenue, COUNT (DISTINCT a. OrderNumber), a. BatchCompletedNoTime FROM Archive a GROUP BY a. BatchCompletedNoTime SELECIONE a. date, a. dailyGBPRevenue, AVG (aa. dailyGBPRevenue) AS dailyGBPRevenueMvgAvg, a. TotalOrderCountProcessedOrders, AVG (aa. TotalOrderCountProcessedOrders) AS TotalOrderCountProcessedOrdersMvgAvg FROM tbl a JOIN tbl aa ON DATEDIFF (dia, aa. date, a. date) ENTRE 0 AND 10 WHERE a. date ENTRE FromDate E TODate GROUP BY a. date, a. DailyGBPRevenue, a. TotalOrderCountProcessedOrders ORDEM POR data terça-feira, 20 de março de 2012 4:18 PM há uma maneira fácil de mudar isso para que olhe para as últimas 10 linhas ao invés do intervalo de datas JUNTE-SE a tb Aa ON DATEDIFF (dia, aa. Data, a. date) ENTRE 0 E 10 Quarta-feira, 21 de março de 2012 1:10 PM Use CROSS APLICAR com um TOP (10) dentro. N 560439.26quot E 125505.63quot Marcado como resposta por Kalman Toth Moderador sábado, 6 de outubro de 2012 8:27 AM quarta-feira, 21 de março de 2012 1:22 PM A Microsoft está realizando uma pesquisa on-line para entender sua opinião sobre o site da Msdn. Se você optar por participar, a pesquisa on-line será apresentada quando você deixar o site Msdn. Você gostaria de participar? Ajude-nos a melhorar o MSDN. Visite a nossa página UserVoice para enviar e votar ideias Centros Dev Recursos de aprendizagem