Moving Average Query Sql

Estou trabalhando com o SQL Server 2008 R2, tentando calcular uma média móvel. Para cada registro na minha opinião, eu gostaria de coletar os valores dos 250 registros anteriores e, em seguida, calcular a média para esta 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. Portanto, para TransactionID 300, coletar todos os valores de 250 linhas anteriores (exibição é ordenada decrescente por TransactionID) e, em seguida, na coluna MovAvg escrever o resultado da média desses valores. Eu estou olhando para coletar dados dentro de um intervalo de registros. Perguntou Oct 28 14 at 20: 58Esta é uma pergunta Evergreen Joe Celko. Eu ignoro qual plataforma DBMS é usada. Mas em qualquer caso Joe foi capaz de responder há mais de 10 anos com SQL padrão. Joe Celko SQL Quebra-Cabeças e Respostas 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 Desnormalizar o banco de dados. No entanto, se os dados históricos que estão sendo gravados não vai mudar ea computação da média móvel é cara, você pode considerar usar a abordagem de coluna. SQL consulta quebra-cabeça: por todos os meios uniforme. Você apenas joga para o balde de peso apropriado, dependendo da distância do ponto de tempo atual. Por exemplo quottake weight1 para datapoints dentro de 24hrs de datapoint atual weight0.5 para datapoints dentro de 48hrsquot. Esse caso importa quanto pontos de dados consecutivos (como 6:12 am e 11:48 pm) estão distantes uns dos outros Um caso de uso que eu posso pensar seria uma tentativa de suavizar o histograma onde quer que os pontos de dados não são densos o suficiente ndash msciwoj May 27 15 at 22:22 Eu não tenho certeza que seu resultado esperado (saída) mostra clássico simples movendo (rolando) média de 3 dias. Porque, por exemplo, o primeiro triplo de números por definição dá: mas você espera 4.360 e sua confusão. No entanto, sugiro a seguinte solução, que usa a função de janela AVG. Essa abordagem é muito mais eficiente (clara e menos intensiva em recursos) do que o SELF-JOIN introduzido em outras respostas (e estou surpreso que ninguém tenha dado uma solução melhor). Você vê que o AVG é envolvido com o caso quando rownum gt p. days, em seguida, para forçar NULL s nas primeiras linhas, onde 3 dias Moving Average é sem sentido. Respondeu Feb 23 at 13:12 Podemos aplicar Joe Celkos suja deixada externa juntar método (como citado acima por Diego Scaravaggi) para responder à pergunta como foi solicitado. Gerar a saída solicitada: respondida Jan 9 at 0:33 Sua resposta 2016 Stack Exchange, IncComo calcular uma média móvel SQL sem uma atualização de cursor: Se você estiver trabalhando com as versões mais recentes do SQL Server, você pode usar as funções de janela para realizar a mesma coisa. Eu postei o código atualizado no final do post. Para este vídeo, eu ainda gosto do processo de pensamento de ancorar a uma data. Vídeo: média móvel de 3 dias em SQL Uma maneira eficiente de calcular uma média móvel em SQL usando alguns truques para definir âncoras de data. Há debates sobre a melhor maneira de fazer um SQL Moving Average no SQL Server. Algumas pessoas pensam que há momentos em que um cursor é mais eficiente. Outros acham que você pode fazer tudo de uma maneira baseada em set sem o cursor. No outro dia eu estava indo para calcular uma média móvel e meu primeiro pensamento foi usar um cursor. Eu fiz algumas pesquisas rápidas e encontrei esta pergunta do fórum: Moving Average no TSQL Há uma postagem que mostra uma subconsulta com uma data de âncora para ajudar a encontrar o offset de 1 e 2 dias. Aqui está o script que você pode usar para testar o resultado final do SQL Moving Average de 3 dias. Aqui está a consulta final. Aqui está a consulta que você usaria com o SQL Server 2012. Compartilhe isso: Assumindo que você tem dimensões de data no seu modelo e com base no seu nome curto do mês com o ano. Aqui você pode criar uma medida calculada para a média móvel em seu cenário Deixe-me saber para quaisquer detalhes Proposta como resposta por Charlie Liao Pessoal contingente Microsoft, Moderador sexta-feira, 07 de fevereiro de 2014 6:28 Marcado como resposta por Charlie Liao Pessoal contingente Microsoft, Em estatísticas, uma média móvel (média móvel ou média corrente) é um cálculo para analisar pontos de dados, criando uma série de médias de diferentes subconjuntos Do conjunto de dados completo. Inclui média móvel simples, média móvel acumulada e média móvel ponderada. Em seu cenário, você pode criar um terceiro campo que calcula a média móvel para os últimos 6 meses usando média móvel simples. Aqui está uma consulta de exemplo do Adventure Works para sua referência. Charlie Liao Suporte Técnico Comunidade TechNet Sexta-feira, 7 de fevereiro de 2014 6:28 Todas as respostas Assumindo que você tem dimensões de data em seu modelo e com base no seu nome curto do mês com ano. Aqui você pode criar uma medida calculada para a média móvel em seu cenário Deixe-me saber para quaisquer detalhes Proposta como resposta por Charlie Liao Pessoal contingente Microsoft, Moderador sexta-feira, 07 de fevereiro de 2014 6:28 Marcado como resposta por Charlie Liao Pessoal contingente Microsoft, Em estatísticas, uma média móvel (média móvel ou média corrente) é um cálculo para analisar pontos de dados, criando uma série de médias de diferentes subconjuntos Do conjunto de dados completo. Inclui média móvel simples, média móvel acumulada e média móvel ponderada. Em seu cenário, você pode criar um terceiro campo que calcula a média móvel para os últimos 6 meses usando média móvel simples. Aqui está uma consulta de exemplo do Adventure Works para sua referência. Charlie Liao Suporte Comunidade TechNet sexta-feira, 7 de fevereiro de 2014 6:28 A Microsoft está conduzindo uma pesquisa on-line para entender sua opinião sobre o site da Msdn. Se você optar por participar, o questionário on-line será apresentado a você quando você deixar o site Msdn. Gostaria de participar 2016 Microsoft. Todos os direitos reservados. Signup Log in Menu Início SQL Cloud Sysadmin Opinião Livros Blogs Entrar Registrar-se Pesquisar 17 de outubro de 2013 Cálculo de valores dentro de uma janela rolante em Transact SQL Antes das funções SQL Window foram implementadas, era complicado calcular totais rolantes ou mover Médias de forma eficiente no SQL Server. Agora há uma série de técnicas, mas que tem o melhor desempenho Dwain Camps sai o cronômetro metafórico. Dwain Camps Cálculo de valores dentro de uma janela de rolagem em SQL Qualquer hora que você precisa para 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 acumulados em 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 também. Um total de rolamento para um mês é o total para esse mês mais os meses anteriores dentro da janela de tempo, ou NULL se você don8217t tem os valores para todos os meses anteriores dentro da janela de tempo. Nas versões anteriores do SQL Server, você teve que saltar através de alguns aros para chegar a um método que executa bem, mas SQL 2012 oferece alguns novos recursos que tornam mais simples. Em ambos os casos, existem várias soluções válidas. Que é mais rápido e mais eficiente We8217ll tentar responder a esta pergunta neste artigo. Nós estaremos trabalhando no SQL 2012. Se você gostaria de acompanhar, você pode usar o exemplo de consulta Queries. sql you8217ll encontrar anexado. Configuração de Dados e Declaração do Problema de Negócio Muitas vezes você se encontra com muitas transações dentro de um mês, mas no nosso caso nós vamos assumir que você já agrupou suas transações para cada mês. We8217ll atribuir a nossa PRIMARY KEY a um tipo de dados DATE e incluir alguns valores sobre os quais queremos acumular rolando doze meses totais. Isso também produz um plano de consulta ligeiramente diferente, de modo que estaremos 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 passou a ignorar um dos seus favoritos, mas sinta-se livre para codificá-lo e adicioná-lo ao teste de desempenho arnês we8217ll presente mais tarde para ver como ele tarifas. Solução 5: Usando uma Atualização Quirky Se você nunca ouviu falar da Quirky Update (QU) e como ela pode ser aplicada a problemas como a execução de totais, eu recomendo fortemente que você tenha uma leitura deste notável artigo por MVP do SQL Jeff Moden. Intitulado Resolvendo os Problemas de Total Total e Ordinal Rank. Antes de continuar, devemos observar 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 MS Books On Line para a instrução UPDATE para versões SQL 2005, 2008 e 2012. Na verdade, ele vai para trás mais do que isso. Eu tenho usado com êxito no SQL Server 2000, mas ele foi herdado da Sybase e estava na primeira versão do SQL Server já lançado. Para os naysayers I8217ll dizer que o 8220undocumented8221 comportamento é, pelo menos, consistente em todas as versões e há provavelmente pouca razão para suspeitar que será depreciado ou alterar em versões futuras do MS SQL. Considere-se avisado Se você considerar usar uma QU para resolver qualquer problema, você precisa tomar cuidado com as 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 indica a ordenação das linhas de origem pelo período como você deseja que ele seja percorrido. A tabela deve ter uma coluna na qual você pode colocar o total agregado em execução. Quando você executar a atualização, você precisará bloquear a tabela usando a dica de consulta TABLOCKX para certificar-se de que ninguém mais obtém em qualquer INSERT s, DELETE s ou UPDATE s antes you8217re através. 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 de rolamento é simplesmente um total em andamento 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 para DECLARAR. Basicamente, o que estamos fazendo é manter o controle dos últimos doze (atraso) valores, a fim de remover o 12 º um (onde a coluna Rolling12Months é atribuído) a partir do que é, de outra forma um QU executando total como descrito no artigo Jeff8217s. Temos grandes esperanças para a sua velocidade, dado que é conhecido por ser o método mais rápido para resolver o problema de 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 o mesmo no SQL 2012. Se você estiver comigo até agora, você também pode estar se perguntando o que acontece se eu precisar calcular vários execução 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 para isso pode ser encontrada no arquivo de recurso anexado: Quirky Update Partitioned. sql. Soluções SQL 2012 Até agora, tudo o que fizemos funcionará no SQL 2008. A única coisa que we8217ve feito que não é suportada no SQL 2005 é as inicializações das variáveis ​​que DECLARE d na abordagem QU. Agora let8217s ver que novos recursos SQL 2012 tem que pode ser aplicado a este problema. Solução 6: Usando um Moldura de Janela Nossa primeira solução de SQL 2012 (6) mostra como usar uma moldura de janela que começa 11 linhas antes da linha atual, até a linha atual para SUM nossos resultados desejados. Mais uma vez, os resultados retornados são os mesmos, mas o plano de consulta é bastante diferente do que para a anterior solução SQL 2012, porém não é particularmente otimista que esta abordagem irá produzir uma alternativa razoavelmente eficiente devido ao número de 8220look-backs8221 necessário para fazê-lo funcionar . Comparação de desempenho dos métodos O teste real para ver como várias soluções executar é verificar os tempos de execução real em um servidor quiescent usando um arnês de teste com muitas linhas. Nosso arnês de teste é mostrado, juntamente com a forma como a Solução 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 tempo decorrido de retornar as linhas para SQL Server Management Studio8217s resultados grade. Remova a aritmética DATE, porque ao gerar arnês de teste de multi-milhões de linhas, é difícil gerar muitos meses exclusivos, portanto a coluna da tabela Data foi revisada para ser um tipo de dados BIGINT. Para as soluções restantes (2 8211 6), temos graficado a CPU e os resultados de tempo decorrido de 1M, apesar de linhas de 4M. Interpretando os resultados decorridos e os tempos de CPU parecem ser consistentes entre os diferentes métodos em relação à sua ordenação. Todos parecem escalar de forma linear. A Quirky Update, supondo que você possa entendê-la 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 2012. Em SQL 2012, Compacto e elegante, mas ligeiramente trilhas a solução Quirky Update em todas as linhas que testamos. Esses resultados de teste parecem estar em conformidade com um teste anterior em totais em execução em SQL 8220Denali8221 CTP3 pelo mestre Microsoft Certified Wayne Sheffield em seu blog. Se você estiver preso a uma versão anterior do SQL (2005 ou 2008), e por algum motivo você não pode usar uma Atualização Quirky (por exemplo, se você não confiar nesse comportamento indocumentado), as soluções mais rápidas disponíveis são a CROSS APPLY TOP ou Usando uma correlacionada sub-consulta, como ambos os que parecia estar em um laço estreito em toda a linha. Parece que o 8220traditional8221 INNER JOIN é algo a ser evitado. Ele provavelmente só vai piorar se você precisa fazer aritmética de data dentro da cláusula JOIN8217s ON. Da mesma forma, usando uma tabela de Tally ou vários LAGs (SQL 2012) certamente não era o caminho a percorrer. Não exploramos soluções baseadas em CURSOR, mas você pode fazer o acompanhamento do artigo referenciado em totais em execução para ter uma idéia de como eles podem executar neste caso. Também vi algumas soluções que empregam uma Expressão de Tabela Comum recursiva (rCTE), mas eu certamente não apostaria em seu desempenho em comparação com as soluções QU ou window frame. Há muitas maneiras de calcular valores dentro de uma janela de rolagem em SQL e há alguns vencedores de desempenho claro entre eles. Esperamos que você tenha encontrado este guia para os métodos disponíveis interessante e informativo. Downloads Total: 30 Média: 4.6 / 5 Dwain Camps é gerente de projetos há muitos anos. Como o desempenho das aplicações pode ser um fator crítico de sucesso para os projetos, ele vem evangelizando sobre a necessidade de desenvolver um SQL de alta performance. Por mentoring e criação de artigos sobre SQL, ele espera treinar uma futura geração de engenheiros de software sobre as maneiras corretas e erradas para entregar código SQL. Ele também tem um interesse especial no desenvolvimento de soluções para problemas complexos e de uso intensivo de dados usando SQL de alto desempenho porque a natureza declarativa do SQL permite o desenvolvimento de soluções algoritmicamente únicas que as linguagens procedurais podem não ser capazes de fazer. Siga Dwain no Twitter Artigos relacionados Também em SQL Com a ascensão de bases de dados NoSQL que estão explorando aspectos de SQL para consultar e estão abraçando a transacionalidade completa, há um perigo dos modelos de documento-documento natureza hierárquica causando um conflito fundamental com a teoria relacional Nós Perguntou o nosso especialista relacional, Hugh Bin-Haad para expor uma área difícil para theorists. hellip banco de dados Leia mais Também no SQL Server Cada SQL Server Database programador precisa estar familiarizado com as funções do sistema. Estes vão desde o sublime (como rowcount ou identidade) para o ridículo (IsNumeric ()) Robert Sheldon fornece uma visão geral do mais comumente utilizado deles. Shellip Leia mais Também em T-SQL Programação Para poder fazer pleno uso de 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 digitam ao consultar os metadados. Uma vez que você pegar o jeito dessas funções, o catálogo do sistema de repente parece simples de usar, como Robert Sheldon demonstra neste article. hellip Leia mais Também na programação T-SQL A forma como você formatar o código T-SQL pode afetar a produtividade do Pessoas que têm que posteriormente manter seu trabalho. Nunca é uma boa experiência ver o Código SQL, gritar Quem o diabo escreveu este código, e então perceber que era você. Grant dá alguns exemplos de má formatação e explica por que você nunca deve check-in mal formatado SQL code. hellip Leia mais Muito bom Grande artigo Fiquei surpreso que LAG () fez tão mal. Eu acho que cada invocação é feita separadamente em vez de fatorada para fora e otimizado como uma janela. Grande explicação Concordo, esta é uma ótima explicação de diferentes maneiras de calcular valores dentro de uma janela de rolamento. Se você testar esses exemplos no SQL 2012, você deve alterar o MyTable com o RollingTotalsExample. Obrigado, Sr. Camps Tally método Oi Dwain, eu notei que sua consulta tabela Tally estava causando um operador Table Spool e pensei que você pode considerar fazer a Tally parte de uma tabela Datas como este: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date THEN aValue END), Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDER BY GroupingDate) lt 12 THEN NULL ELSE SUM (Valor) END INTO ResultsSoln2 FROM RollingTotalsExample a CROSS APPLY (mdash Remove os valores aritméticos DATE (Date) (Date2), (Date3), (Date4), (Date5), (Date6), (Date7), (Date8), (Date9), (Date10), (Date11)) c (GroupingDate) GROUP BY GroupingDate HAVING GroupingDate lt MAX (Data) ORDER BY GroupingDate (Desculpas se a formatação é ruim ndash sem pré-visualização) Esta mudança ainda wouldnrsquot torná-lo um concorrente, mas faz uma enorme melhoria para que queryhellip Obrigado pelos comentários Obrigado Joe e Nic. Irsquom feliz por você ter encontrado o artigo interessante. Joe: Eu também fiquei um pouco surpreso com os resultados do LAG e isso me faz pensar no que seria o ponto de equilíbrio. Talvez 3 meses pode não ser tão ruim, mas ainda é difícil acreditar que pode ser mais rápido do que o QU. Tally Tables MM: Por alguma razão, eu tenho uma preferência pessoal para tabelas de Tally in-line, mas seus resultados são interessantes se apenas para considerar para outros casos. Assistência com Mudança Anual Total Meu primeiro post. Eu preciso calcular o Total Anual Movente para o valor acima para os 12 meses precedentes, com este mês que é mês 12. Eu então necessito começar o total anual movente para os 12 meses antes deste. Com a idéia de ser comparar MAT para este mês com o mês correspondente no ano passado, e para cada mês anterior. Minha tentativa me deu isto: Com cte como (SELECT rNum ROWNUMBER () Sobre (order by Date). Date. Valor Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDER BY Date) gt 11 ENTÃO SUM (Valor) OVER (ORDER BY Date ROWS ENTRE (Selecione mRNum max (rNum) de cte) deMax Onde rNum entre mRNum ndash 23 e mRNum Com a capacidade de alterar a instrução Were para refletir se eu quero este ano ou o ano anterior. Meus dados reais tem a data como em Integer 201409 que eu acho que vai tornar a vida mais fácil para mim como eu posso subtrair 100 para obter o ano anterior. Excelente artigo e qualquer ajuda seria apreciada. Esta é a minha solução de trabalho (com algum ruído) mdash Rolling 12 meses totais usando SQL 2012 e uma moldura de janela IF OBJECTID (lsquotempdb..PreviousYearrsquo) IS NOT NULL DROP TABELA AnteriorAnterior Com cte como (SELECT rNum ROWNUMBER () Sobre (ordem por Data ) Valor Valor Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDER BY Date) gt 11 THEN SUM (Valor) OVER (ORDER BY Date ROWS ENTRE 11 ROUND ANTERIOR E ATUAL) END FROM RollingTotalsExample) Selecione pyRowNum ROWNUMBER () Over (order by 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 2012 e um quadro de janela IF OBJECTID (lsquotempdb..ThisYearrsquo) IS NOT NULL DROP TABLE Este ano com cte como (SELECT rNum ROWNUMBER () Sobre (ordem por data) Data. Valor Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ordem por data) gt 11 THEN SUM (Valor) OVER (ORDER BY Date ROWS ENTRE 11 LINHAS PRECEDENTES 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 ThisYear ty Left Join AnteriorYear py em ty. tyRowNum py. pyRowNum Estes podem funcionar Irsquom não perto de um comp Sql acesso agora para que eu canrsquot testá-lo (pode haver alguns erros de digitação / sintaxe). SELECT T. DateKey, AVG (T. ValueField) OVER (ODER BY T. DateKey ASC ENTRE 365 PREFERIDOS E E CORRENTE LINHA) AS YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC No caso AVG é uma das funções agregadas não suportado Com BETWEEN intervalo (eu sei SUM é suportado). SELECT T. DateKey, SUM (T. ValueField) OVER (ODER BY T. DateKey ASC entre 365 e precedente) / CASO QUANDO DATEDIFF (DAY, StartDate, T. DateKey) lt 365 THEN DATEDIFF (DAY, StartDate, T Junte-se a mais de 200.000 profissionais da Microsoft e obtenha acesso completo e gratuito a artigos técnicos, o nosso boletim informativo de duas mensagens mensais do Simple Talk e ferramentas SQL grátis. Visite nossa biblioteca de padrões e práticas para saber mais sobre o gerenciamento do ciclo de vida do banco de dados. Descubra como automatizar o processo de criação, teste e implantação de alterações no banco de dados para reduzir o risco e tornar possíveis lançamentos rápidos. Últimos artigos avaliados em T-SQL Programming