Cálculo da média móvel t sql

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 o TransactionID 300, colete todos os valores das 250 linhas anteriores (a exibição é ordenada, decrescente por TransactionID) e depois na coluna MovAvg escreva o resultado da média desses valores. Estou procurando coletar dados em uma variedade de registros. Perguntou 28 de outubro às 20: 58Menção 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 2017 e posterior Moeda em movimento Esta versão faz uso de uma função de janela agregada. O que é novo no SQL 2017 é 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 2017 é 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 2017 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. T-SQL Ma20, Ma50, Ma200 vendem e vendem sinais. 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 LindCalculando Valores dentro de uma Janela Rolante em Transact SQL Dwain Camps Calculando Valores em uma Janela Rolante em SQL Sempre que você precisar combinar valores em várias linhas em SQL, o problema pode ser desafiador, particularmente quando se trata de desempenho. Vamos nos concentrar no problema dos totais de doze meses, mas nossos métodos podem ser aplicados a qualquer janela de tempo (por exemplo, 3 meses) ou a médias e outras agregações em todas essas janelas de tempo. Um total contínuo de um mês é o total desse mês mais os meses anteriores dentro da janela de tempo, ou NULL se você não tiver os valores de todos os meses anteriores dentro da janela de tempo. Nas versões anteriores do SQL Server, você teve que saltar através de algumas argolas para criar um método que funcione bem, mas o SQL 2017 oferece alguns recursos novos que simplificam. Em ambos os casos, existem várias soluções válidas. O que é mais rápido e eficiente We8217ll tenta responder a esta pergunta neste artigo. Estaremos trabalhando no SQL 2017. Se você quiser acompanhar, você pode usar o recurso Sample Queries. sql you8217ll em anexo. Configuração de dados e declaração do problema comercial Muitas vezes o you8217ll se encontra com muitas transações dentro de um mês, mas no nosso caso, we8217ll assumiu que you8217ve já agrupou suas transações por cada mês. Nós atribuímos nossa CHAVE PRIMÁRIA a um tipo de dados DATA e incluímos alguns valores sobre os quais queremos acumular toques de doze meses. Isso também produz um plano de consulta ligeiramente diferente para que possamos estar interessados ​​em ver como seus resultados de desempenho se comparam a outras soluções propostas até agora. Tanto para soluções tradicionais e minhas desculpas, se eu esqueci um de seus favoritos, mas sinta-se livre para codificá-lo e adicioná-lo ao arnês de teste de desempenho que apresentamos para ver o que é bom. Solução 5: Usando uma atualização peculiar Se você nunca ouviu falar sobre a Quirky Update (QU) e como ela pode ser aplicada a problemas como a execução de totais, eu recomendo que você tenha lido este excelente artigo pelo MVP Jeff Moden. Intitulado Resolvendo os Problemas Total e Ordinal de Rank Running. Antes de continuar, devemos notar que existem aqueles que insistem que o método QU representa um comportamento indocumentado do SQL Server e, portanto, não é confiável. Podemos dizer que a sintaxe é claramente descrita pela entrada do MS Books On Line para a instrução UPDATE para as versões SQL 2005, 2008 e 2017. De fato, ela volta mais longe do que isso. Eu usei com sucesso no SQL Server 2000, mas foi herdado da Sybase e estava na primeira versão do SQL Server lançada. Para os naysayers I8217ll dizer que o comportamento 8220undocumented8221 é pelo menos consistente em todas as versões e provavelmente há poucas razões para suspeitar que ele será obsoleto ou será alterado em futuras versões do MS SQL. Considere-se avisado Se você considerar o uso de um QU para resolver qualquer problema, você deve tomar nota cuidadosa das muitas regras que se aplicam (também incluídas no artigo referenciado por Jeff). Os principais, que I8217ve manipulados nesta consulta, podem ser resumidos como: A tabela deve ter um índice agrupado que indique o pedido das linhas de origem pelo período como você deseja que seja percorrido. A tabela deve ter uma coluna na qual você pode colocar o total acumulado. Quando você executa a atualização, você precisa bloquear a tabela usando a sugestão da consulta TABLOCKX para garantir que ninguém mais entre em INSERT s, DELETE s ou UPDATE s antes de you8217re. Você deve impedir o SQL de tentar paralelizar a consulta usando a dica OPTION (MAXDOP 1). Uma vez que uma média de doze meses é simplesmente um total executado disfarçado, podemos adicionar uma coluna à nossa tabela e aplicar uma consulta QU para fazer nosso cálculo. Devo confessar que isso parece um pouco confuso, com todas as variáveis ​​que você precisa DECLARAR. Basicamente, o que fazemos é acompanhar os últimos doze valores (atrasados), para remover o 12º (onde a coluna Rolling12Months é atribuída) do que de outra forma é um total executável do QU como descrito no artigo Jeff8217s. Temos grandes esperanças de sua velocidade, dado que é conhecido pelo método mais rápido para resolver o problema dos totais em execução. Mais uma vez, você deve convencer-se de que os resultados são consistentes com as soluções anteriores, e sim, esta solução ainda se comporta igual no SQL 2017. Se você estiver comigo até agora, você também pode se perguntar o que acontece se eu precisar calcular várias corridas Totais de doze meses em diferentes partições8221 Isso é relativamente simples para todas as outras soluções apresentadas, mas propõe um pouco de desafio usando o QU. A resposta a isso pode ser encontrada no arquivo de recursos anexado: Quirky Update Partitioned. sql. Soluções SQL 2017 Até agora, tudo o que fizemos funcionará no SQL 2008. A única coisa que we8217ve feito que não é suportada no SQL 2005 é a inicialização das variáveis ​​que DECLARE d na abordagem QU. Agora let8217s ver quais novos recursos o SQL 2017 tem que pode ser aplicado a esse problema. Solução 6: Usando um quadro de janela Nossa primeira solução de SQL 2017 (6) mostra como usar um quadro de janela que inicia 11 linhas antes da linha atual, até a linha atual para SUMAR os resultados desejados. Mais uma vez, os resultados retornados são os mesmos, mas o plano de consulta é bastante diferente do que para a solução anterior do SQL 2017 no entanto, não é particularmente otimista que essa abordagem produza uma alternativa razoavelmente eficiente devido ao número de 8220look-backs8221 necessários para fazê-lo funcionar . Comparação de desempenho dos métodos O teste real para ver como as múltiplas soluções funcionam é verificar os tempos de execução reais em um servidor quiescente usando um arnês de teste com muitas linhas. O nosso arnês de teste é mostrado, juntamente com a forma como as Soluções 1 e 2 foram modificadas (consulte os comentários no código) para: Inserir os resultados em uma tabela temporária, para evitar o impacto do tempo decorrido de retornar as linhas aos resultados do SQL Server Management Studio8217s grade. Remova a aritmética DATA, porque ao gerar arneses de teste de várias milhões de linhas, é difícil gerar tantos meses únicos, então a coluna da tabela de data foi revisada para ser um tipo de dados BIGINT. Para as soluções restantes (2 8211 6), graficamos a CPU e os resultados do tempo decorrido de linhas de 1M, embora 4M. Interpretar os resultados decorridos e os tempos de CPU parecem ser consistentes em todos os métodos em relação à sua ordem. Todos parecem escalar de forma linear. A Quirky Update, supondo que você possa entendê-lo e todas as suas regras associadas, parece ser a solução mais rápida disponível para resolver esse problema, mesmo considerando os novos recursos disponíveis no SQL 2017. No SQL 2017, a abordagem do quadro de janela é certamente impecável, Compacto e elegante, mas trilha ligeiramente a solução Quirky Update nas linhas que testamos. Esses resultados de testes parecem estar de acordo com um teste anterior sobre Running Totals no SQL 8220Denali8221 CTP3 pelo Microsoft Certified Master Wayne Sheffield em seu blog. Se você estiver preso com uma versão anterior do SQL (2005 ou 2008) e por algum motivo você pode usar uma Atualização Quirky (por exemplo, se você não confia neste comportamento indocumentado), as soluções mais rápidas disponíveis para você são o CROSS APPLY TOP ou Usando uma sub-consulta correlacionada, já que ambas pareciam estar em uma gravata próxima. Parece que o 8220tradicional8221 INNER JOIN é algo a ser evitado. Provavelmente só piorará se você precisar fazer aritmética de data dentro da cláusula JOIN8217s ON. Da mesma forma, usando uma tabela Tally ou vários LAG s (SQL 2017) certamente não era o caminho a seguir. Nós não exploramos as soluções baseadas no CURSOR, mas você pode acompanhar o artigo referenciado ao executar totais para ter uma idéia de como eles podem realizar neste caso. I8217we também viu algumas soluções que utilizam uma Expressão de tabela comum recursiva (rCTE), mas certamente não vou apostar em seu desempenho em comparação com as soluções de quadro de janela ou QU. Existem muitas maneiras de calcular valores dentro de uma janela rotativa em SQL e há alguns vencedores de desempenho claros entre eles. Esperamos que você tenha encontrado este guia para os métodos disponíveis, interessante e informativo. Total: 31 Média: 4.65 Dwain Camps tem sido gerente de projeto há muitos anos. Como o desempenho das aplicações pode ser um fator de sucesso crítico para projetos, ele tem evangelizado sobre a necessidade de desenvolver SQL altamente performante. Ao sugerir e criar artigos sobre o SQL, ele espera treinar uma geração futura de engenheiros de software nas maneiras correta e errada de entregar o código SQL. Ele também tem um interesse especial em desenvolver soluções para problemas complexos e intensivos em dados, usando SQL de alto desempenho, porque a natureza declarativa do SQL permite o desenvolvimento de soluções algorítmicamente únicas às quais as linguagens processuais podem não ser capazes. Siga Dwain no Twitter Muito bom Excelente artigo Fiquei surpreso que o LAG () tenha feito tão mal. Eu acho que cada invocação é feita separadamente, em vez de fatorado e otimizado como uma janela. Grande explicação, eu concordo, esta é uma ótima explicação de diferentes maneiras de calcular valores dentro de uma janela rotativa. Se você testar esses exemplos no SQL 2017, você deve alterar MyTable com RollingTotalsExample. Muito obrigado, método do Sr. Camps Tally. Oi Dwain, notei que sua consulta de tabela Tally estava causando um operador de Table Spool e pensou que poderia considerar fazer a parte Tally de uma tabela de Datas como esta: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date ENTÃO a. Value END), Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDER BY GroupingDate) lt 12 ENCONTRO NULL ELSE SUM (Valor) END INTO ResultsSoln2 FROM RollingTotalsExample a CROSS APPLY (mdash Remove the DATE arithmetic values ​​(Date), (Date1), (Date2), (Date3), (Date4), (Date5), (Date6), (Date7), (Date8), (Date9), (Date10), (Date11)) c (GroupingDate) GROUP BY GroupingDate HAVING GroupingDate lt MAX (Data) ORDER BY GroupingDate (Desculpe-se se a formatação for ruim ndash sem visualização) Esta mudança ainda não o tornaria um candidato, mas faz uma melhoria enorme para esse queryhellip Obrigado pelos Comentários Obrigado Joe e Nic. Irsquom prazer que você achou o artigo interessante. Joe: Eu também fui um pouco surpreso com os resultados do GAL e isso me faz saber qual seria o ponto de equilíbrio. Talvez 3 meses possam não ser tão ruins, mas ainda é difícil acreditar que seja mais rápido do que o QU. Tally Tables MM: Por algum motivo, eu tenho uma preferência pessoal para mesas Tally em linha, mas seus resultados são interessantes se apenas para considerar para outros casos. Assistência com Moving Annual Total My first post. Eu preciso calcular o total anual móvel do valor acima nos 12 meses anteriores, sendo este mês o mês 12. Então eu preciso obter o total anual móvel para os 12 meses anteriores. Com a idéia de comparar MAT para este mês com o mês correspondente no ano passado e para cada mês anterior. Minha tentativa me deu isso: com cte como (SELECT rNum ROWNUMBER () Over (ordem por data). Data. Valor Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDEM POR Data) gt 11 LENTO SUM (Valor) OVER (ORDEM POR Data ROWS ENTRE 11 PRECEDING AND CURRENT ROW) END FROM RollingTotalsExample) Selecione From cte, (Selecione mRNum max (rNum) de cte) deMax Onde rNum entre mRNum ndash 23 e mRNum Com a capacidade de alterar a declaração Were para refletir se eu quero este ano ou o ano anterior. Meus dados reais têm a data como no Integer 201709, que eu acho que vai me facilitar a vida, pois eu posso subtrair 100 para obter o ano anterior. Excelente artigo e qualquer ajuda serão apreciados. Esta é a minha solução de trabalho (com algum ruído) mdash Rolando totais de 12 meses usando o SQL 2017 e um quadro de janela IF OBJECTID (lsquotempdb..PreviousYearrsquo) IS NOT NULL DROP TABLE Anterior Ano com cte como (SELECT rNum ROWNUMBER () Over (ordem por data ). Data. Valor Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDEM POR Data) gt 11 LENTO SUM (Valor) OVER (ORDER BY Date ROWS ENTRE 11 PRECEDING AND CURRENT ROW) END FROM RollingTotalsExample) Selecione pyRowNum ROWNUMBER () Over (ordem por mRNum ). . SStart mRNum ndash 24. EEnd mRNum ndash 12 em PreviousYear From cte, (Selecione mRNum max (rNum) de cte) deMax Onde rNum entre mRNum ndash 23 e mRNum ndash 12 mdash Rolando 12 meses totais usando SQL 2017 e um quadro de janela IF OBJECTID (lsquotempdb..ThisYearrsquo) NÃO É TABELA DE DESLOCAMENTO NUM. Este ano com cte como (SELECIONAR RNUM ROWNUMBER () Sobre (ordem por data). Data. Valor. Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDER BY Date) gt 11 LENTO SUM (Valor) OVER (ORDER BY Date ROWS ENTRE 11 ANÁLISES ANTERIORES E CORRENTES) END FROM RollingTotalsExample) Selecione tyRowNum ROWNUMBER () Over (ordem por mRNum). . SStart mRNum ndash 11. EEnd mRNum em ThisYear From cte, (Selecione mRNum max (rNum) de cte) deMax Onde rNum entre mRNum ndash 11 e mRNum Selecione a partir deste ano ty Left Join PreviousYear py on ty. tyRowNum py. pyRowNum Estes podem funcionar Irsquom não perto de um comp com Acesso sql agora para que eu possa testá-lo (pode haver alguns erros de erros de digitação). SELECIONE T. DateKey, AVG (T. ValueField) OVER (ODER BY T. DateKey ASC ENTRE 365 PRECEDING AND AND CURRENT ROW) COMO YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC Caso o AVG seja uma das funções agregadas não suportadas Com BETWEEN range (eu sei que SUM é suportado). SELECIONE T. DateKey, SUM (T. ValueField) OVER (ODER BY T. DateKey ASC ENTRE 365 PRECEDING AND AND CURRENT ROW) CASO QUANDO DATEDIFF (DAY, StartDate, T. DateKey) lt 365 THEN DATEDIFF (DAY, StartDate, T. DateKey) ELSE 365 END COMO YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC Join Simple Talk Junte-se a mais de 200.000 profissionais da Microsoft e obtenha acesso completo e gratuito a artigos técnicos, nosso boletim de notícias simples de Discussão Simples e ferramentas SQL gratuitas. Visite nossa biblioteca de artigos para descobrir os padrões e práticas que você precisa para avançar para métodos mais ágeis de entrega de banco de dados. Descubra como automatizar o processo de construção, teste e implantação de mudanças no banco de dados para reduzir o risco e acelerar o ciclo de entrega. Artigos mais recentes e mais votados na programação T-SQL Artigos relacionados Também em SQL Com o aumento de bancos de dados NoSQL que estão explorando aspectos do SQL para consultas e estão abraçando a transação completa, existe o perigo de a natureza hierárquica dos modelos de documentos de dados causar uma base fundamental Conflito com a teoria relacional Pedimos ao nosso especialista relacional, Hugh Bin-Haad, que exponha uma área difícil para os teóricos da base de dados. Bookip Leia mais Também no SQL Server Todo o programador do banco de dados SQL Server precisa estar familiarizado com as funções do sistema. Estes variam desde o sublime (como a contagem de renda ou a identidade) até o ridículo (IsNumeric ()) Robert Sheldon fornece uma visão geral dos mais comumente usados ​​deles. Bookip Leia mais Também na programação T-SQL Para poder usar plenamente O catálogo do sistema para saber mais sobre um banco de dados, você precisa estar familiarizado com as funções de metadados. Eles economizam muito tempo e digitando quando questionam os metadados. Uma vez que você obtém o jeito dessas funções, o catálogo do sistema de repente parece simples de usar, como Robert Sheldon demonstra neste artigo. Bookip Leia mais Também na programação T-SQL Se você está trabalhando em uma linguagem processual como o PowerShell ou no T-SQL , Há algo um pouco incômodo sobre ter que lidar com parâmetros que são listas, ou pior com intervalos entre os valores. Na verdade, uma vez que você tem uma maneira de lidar com eles, eles podem ser convenientes, especialmente quando superando o abismo entre o aplicativo eo banco de dados. Phil Factor mostra como lidar com them. hellip Leia mais copie 2005 - 2017 Red Gate Software Ltd O que você acha do novo Simple Talk Dê-nos seus comentários