7 Day Moving Average In Sql

Tenho uma tabela de produtos de uso horário de produto (quantas vezes o produto é usado) Da mesma forma, tenho os dados de uso para 4 produtos diferentes (ProductId de 1 a 4) armazenados para cada hora na tabela productusage. Como você pode imaginar, ele está crescendo constantemente como o processo nocturno ETL despeja os dados para todo o dia anterior. Se um produto não for usado em qualquer hora do dia, o registro dessa hora não aparecerá nesta tabela. Da mesma forma, se um produto não é usado para o dia inteiro, não haverá qualquer registro para esse dia na tabela. Eu preciso para gerar um relatório que dá o uso diário e 7 dias passado rolando média E assim por diante. Estou planejando criar uma visualização indexada no SQL Server 2014. Você pode pensar em uma consulta SQL eficiente para fazer isso perguntou Sep 18 14 às 21: 08Esta é 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 um do outro 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 perguntado. 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: Anteriormente, discutimos como escrever médias de rolamento no Postgres. Pela demanda popular estavam mostrando-lhe como fazer o mesmo no MySQL e SQL Server. Bem cobrir como anotar gráficos barulhentos como este: Com uma linha de 7 dias precedente média como este: A grande idéia Nosso primeiro gráfico acima é muito 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 possui 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 do MySQL com um auto join. Isso é conceitualmente o mesmo que no MySQL. As únicas traduções são a função dateadd e nomeada explicitamente grupo por colunas. Outras médias Nós nos concentramos na média final de 7 dias neste post. Se nós quisemos olhar para a média 7-dia principal, é tão simples como classificar as datas na outra direção. Se quiséssemos olhar para uma média centrada, use: Postgres: linhas entre 3 precedentes 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) Cálculo de valores dentro de uma janela de rolagem em Transact SQL Dwain Camps Cálculo de valores em uma janela de rolagem em SQL Qualquer tempo que você precisa para combinar valores em várias linhas em SQL, o problema pode ser 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 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. O que é mais rápido e mais eficiente160 tentará responder a esta pergunta neste artigo. Estaremos trabalhando no SQL 2012. 160Se você quiser 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ê encontra-se 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.160 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 as 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 Resolver os Problemas de Total Total e Ordinal Rank .160 Antes de continuar, devemos notar que há aqueles que insistem que o método QU representa um comportamento indocumentado do SQL Server e por isso não é confiável.160 Podemos dizer que a sintaxe é Claramente descrito pela entrada MS Books On Line para a instrução UPDATE para versões SQL 2005, 2008 e 2012.160 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.160 Para os naysayers I8217ll dizer que o comportamento 8220undocumented8221 é pelo menos consistente em todas as versões e há provavelmente pouca razão para Suspeite que será depreciado ou alterado em futuras versões do MS SQL.160 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) .160 As principais, que foram tratadas nessa consulta, podem ser resumidas como: A tabela deve ter um índice agrupado que indique 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 de 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 .160 Basicamente, o que estamos fazendo é acompanhar os últimos doze valores (atrasados), para remover o 12º (onde o Rolling12Months coluna é atribuído) 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 que os resultados são consistentes com as soluções anteriores, e sim esta solução ainda se comporta da mesma forma no SQL 2012.160 Se você estiver comigo até agora, você também pode estar se perguntando o que acontece se eu precisar calcular vários correndo doze Mês totais entre diferentes partições8221160 Isso é relativamente simples para todas as outras soluções apresentadas, mas propõe um pouco de desafio usando o QU.160 A resposta a isso pode ser encontrada no arquivo de recurso anexado: Quirky Update Partitioned. sql. SQL 2012 Soluções Até agora, tudo o que fizemos funcionará no SQL 2008.160 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.160 Agora vamos ver como as novas funcionalidades 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 solução anterior do SQL 2012, no entanto, não estamos particularmente otimistas de 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 são executadas é verificar os tempos reais de execução em um servidor quiescente usando um arnês de teste com muitas linhas.160 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 de tempo decorrido de retornar as linhas para SQL Server Management Studio8217s grade de resultados. 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 transcorridos e os tempos de CPU parecem ser consistentes entre os diferentes métodos em relação à sua ordenação.160 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 testou.160 Estes resultados de teste parecem estar em conformidade com um teste anterior em Running totais em SQL 8220Denali8221 CTP3 pelo Microsoft Certified Master 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.160 É provável que só fique pior se você precisar fazer aritmética de data dentro da cláusula JOIN8217s ON.160 Da mesma forma, usando uma Tabela de Tarefas ou vários LAGs (SQL 2012) certamente Não era o caminho a percorrer. Nós não exploramos soluções baseadas em CURSOR, mas você pode voltar track para o artigo referenciado em totais em execução para ter uma idéia de como eles podem executar neste caso.160 I8217ve também visto algumas soluções que empregam uma expressão de tabela comum recursiva (rCTE) , Mas eu certamente não iria apostar em seu desempenho em comparação com o QU ou janela frame soluções. Há muitas maneiras de calcular os valores dentro de uma janela de rolagem em SQL e há alguns vencedores de desempenho claro entre eles.160 Esperamos que você encontrou 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 dos bancos de dados NoSQL que exploram aspectos do SQL para consulta e estão abraçando a transacionalidade completa, existe o perigo dos modelos de documentos de dados 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 Você deve ficar usando tabelas no SQL Server, em vez de pilhas que têm Nenhum índice agrupado, a menos que você tenha razões bem-consideradas para escolher pilhas. No entanto, há usos para heaps em circunstâncias especiais, e é útil saber o que esses usos são, e quando você deve evitar pilhas. Uwe Ricken explica, e demonstra por que você seria imprudente para usar pilhas em vez de tabelas quando os dados é susceptível de alteração. 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ê precisará alterar o MyTable com o RollingTotalsExample. Obrigado, Sr. Camps Tally método Oi Dwain, eu notei que sua consulta Tally tabela estava causando um operador Table Spool e pensei que você poderia considerar fazer a parte quotTallyquot de uma tabela quotDatesquot 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 (8212 Remover 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 8211 sem pré-visualização) Esta mudança ainda wouldn8217t torná-lo um concorrente, mas faz uma enorme melhoria para que query8230 Obrigado pelos comentários Obrigado Joe e Nic. I8217m feliz que você encontrou 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 8211 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) 8212 Rolando 12 meses totais usando SQL 2012 e uma moldura de janela Se OBJECTID (8216tempdb..PreviousYear8217) NÃO É NULA TABELA DROP AnteriorAnterior Com cte como (SELECT rNum ROWNUMBER () Over (order by Date ) 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 8211 24. EEnd mRNum 8211 12 em AnteriorAnterior De cte, (Selecione mRNum max (rNum) de cte) deMax Onde rNum entre mRNum 8211 23 e mRNum 8211 12 8212 Rolando 12 meses totais usando SQL 2012 e um quadro de janela IF OBJECTID (8216tempdb..ThisYear8217) IS NOT NULL DROP TABLE Este ano com cte como (SELECT rNum ROWNUMBER () Sobre (ordem por data) Data. Valor Rolling12MonthsCASE QUANDO ROWNUMBER () OVER (ORDER BY Date) gt 11 ENTÃO 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 8211 11. EEnd mRNum em ThisYear From cte, (Selecione mRNum max (rNum) de cte) deMax Onde rNum entre mRNum 8211 11 e mRNum Selecione a partir ThisYear ty Left Join AnteriorYear py em ty. tyRowNum py. pyRowNum Estes podem trabalhar I8217m não perto de um comp Acesso sql agora para que eu possa testá-lo (pode haver alguns erros de sintaxe / erros de digitação). 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 aos artigos técnicos, ao nosso boletim informativo da Simple Talk, duas vezes por mês, e às ferramentas SQL gratuitas. . 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