Mover média consulta 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 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. Perguntou 28 de outubro às 20: 58 Esta é uma pergunta 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 a saída solicitada: respondeu Jan 9 16 às 0:33 Sua resposta 2017 Stack Exchange, IncCalculando Valores dentro de uma Janela Rolante em Transact SQL Dwain Camps Calculando Valores em uma Janela Rolante em SQL Toda vez que você precisa combinar valores em várias linhas em SQL, o problema pode ser um desafio, especialmente quando se trata de desempenho. Nós nos concentraremos no problema de totais 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 alguns aros para criar um método que funciona bem, mas o SQL 2012 oferece alguns recursos novos que o 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. Nós estaremos trabalhando no SQL 2012. Se você quiser seguir, você pode usar o recurso Sample Queries. sql you8217ll em anexo. Configuração de dados e declaração do problema de negócios Muitas vezes, you8217ll encontrar-se com muitas transações dentro de um mês, mas no nosso caso we8217ll assumir que you8217ve já agrupou suas transações por cada mês. Nós atribuímos nossa CHAVE PRIMÁRIA a um tipo de dados de 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 mais tarde para ver como ele se classifica. Solução 5: Usando uma atualização peculiar Se você nunca soube da Quirky Update (QU) e de como ela pode ser aplicada a problemas como a execução de totais, recomendo que você tenha lido este excelente artigo pelo MVP Jeff Moden. Intitulado Resolvendo os Problemas de Posição Total e Ordinal de Corrida. 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 versões SQL 2005, 2008 e 2012. Na verdade, 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, é necessário ter uma nota cuidadosa das muitas regras aplicáveis ​​(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 total acumulado. Quando você executa a atualização, você precisa bloquear a tabela usando a sugestão de consulta TABLOCKX para se certificar de que ninguém mais entra 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), a fim de 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 2012. 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 2012 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 veja quais novos recursos o SQL 2012 tem que pode ser aplicado a esse problema. Solução 6: Usando um quadro de janela Nossa primeira solução de SQL 2012 (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 é muito diferente do que para a solução anterior do SQL 2012 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 executam é 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ários milhões de linhas, é difícil gerar tantos meses únicos, portanto a coluna da tabela de data foi revisada para ser um tipo de dados BIGINT. Para as soluções restantes (2 8211 6), nós representamos graficamente a CPU e os resultados do tempo decorrido das linhas de 1M, embora 4M. Interpretar os resultados decorridos e os tempos da CPU parecem ser consistentes em todos os diferentes métodos em relação à sua ordem. 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. No SQL 2012, a abordagem do quadro de janela é certamente nítida, Compacto e elegante, mas trilha ligeiramente a solução Quirky Update nas linhas que testámos. Esses resultados de teste 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 ambos pareciam estar em uma gravata próxima. Parece que o 8220traditional8221 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, usar uma tabela Tally ou vários LAG s (SQL 2012) 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 na execução de totais para ter uma idéia de como eles podem executar neste caso. I8217ve também vi algumas soluções que utilizam uma Expressão de tabela comum recursiva (rCTE), mas certamente não vou apostar em seu desempenho comparado às 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 esse 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 com alto desempenho. 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 no desenvolvimento de 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 que 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 2012, você deve alterar MyTable com RollingTotalsExample. Muito obrigado, método do Sr. Camps Tally. Olá, Dwain, notei que sua consulta de tabela Tally estava causando um operador de Table Spool e pensou que você 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 ENTÃO 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 a formatação é ndash ruim sem pré-visualização) Esta alteração ainda não seria um concorrente, 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 LAG e 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. 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 a este. Com a ideia 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 (ORDER BY Date) gt 11 LENTO SUM (Valor) OVER (ORDER BY Date 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 instrução Were para refletir se eu quero este ano ou o ano anterior. Meus dados reais têm a data como no Integer 201409, 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 2012 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 (ORDER BY Date) gt 11 LENTO SUM (Valor) OVER (ORDEM POR Data 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 no 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) IST NULL DROP TABLE Este ano com cte como (SELECT rNum ROWNUMBER () Over (ordem por data). Data. Valor. Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDER BY Date) gt 11 LENTO SUM (Valor) OVER (ORDEM POR Data 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 Tarete Lembre-se de Ano anterior em Ty. tyRowNum py. pyRowNum Isso pode 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. DataKey) 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 e simples do SQL 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 riscos e acelerar o ciclo de entrega. Artigos recentes mais bem classificados 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 explique 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 usados ​​de them. hellip 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 Às vezes, você pode fazer uma pergunta estranha sobre dados que não se encaixam perfeitamente Com o modo de pensar SQL. Embora as funções da janela tenham tornado o SQL mais versátil, há momentos em que você precisa alcançar o seu livro favorito de algoritmos para codificar o seu caminho em torno de um relatório incomum, como um arquivo de tarefa. Leia mais copie 2005 - 2017 Red Gate Software Ltd O que você acha do novo Simples Discussão Dê-nos os seus comentários