Neste post, eu mostro um truque para fazer cálculo de média móvel (pode ser estendido para outras operações que exigem funções de janelas) que é super rápido. Muitas vezes, os analistas SAS precisam realizar cálculos de média móvel e existem várias opções pela ordem de preferência: 1. PROC EXPAND 2. DATA PASSO 3. PROC SQL Mas muitos sites podem não licenciados SAS / ETS para usar PROC EXPAND e fazer média móvel Em DATA STEP requer alguma codificação e é propenso a erros. PROC SQL é uma escolha natural para programadores júnior e em muitos casos de negócios a única solução, mas o SAS SQL PROC não possui funções de janelas que estão disponíveis em muitos DBs para facilitar o cálculo da média móvel. Uma técnica que as pessoas costumam usar é CROSS JOIN, que é muito cara e não é uma solução viável para um conjunto de dados de tamanho médio. Neste post, eu mostro um truque para fazer cálculo de média móvel (pode ser estendido para outras operações que exigem funções de janelas) que é super rápido. Considere o cálculo da média móvel mais simples onde as observações K de arrasto estão incluídas no cálculo, a saber MA (K), aqui nós ajustamos K5. Primeiro, geramos um dado de 20 obs, onde o ID da variável deve ser usado para o windowing ea variável X deve ser usada no cálculo do MA, e então aplicamos o CROSS JOIN padrão para examinar primeiro os dados resultantes, Non-Grouped, apenas Para entender como alavancar a estrutura de dados. A partir do conjunto de dados resultante, é difícil encontrar uma pista, agora vamos classificar por quotbidquot coluna neste conjunto de dados: a partir desta triada dados, é claro que nós realmente don39t tem CROSS JOIN todo o conjunto de dados originais, mas em vez disso, Podemos gerar um conjunto de dados de quotoperationquot que contém o valor de diferença e deixar o conjunto de dados original CROSS JOIN com este conjunto de dados de quotoperationquot muito menor e todos os dados que precisamos usar para o cálculo de MA estarão lá. Agora vamos fazer isso: CROSS JOIN dados originais com quotoperationquot dados, classificar por (a. idops), que é realmente quotbid39 no conjunto de dados classificados Note que no código acima, é necessário ter ax multiplicar por b. weight para que os dados Pode ser inter-leaved, caso contrário o mesmo valor X da tabela original será saída e MA cálculo será falha. A variável de peso explícito realmente acrescenta mais flexibilidade ao cálculo de MA inteiro. Ao configurá-lo para ser 1 para todos os obs resultam em um simples cálculo de MA, atribuir pesos diferentes ajudará a resolver MA mais complexa computação, tais como dar outras observações menos peso para um MA decaído. Se for necessário um parâmetro K diferente nos cálculos de MA (K), somente o conjunto de dados de operação precisa ser atualizado, o que é um trabalho trivial. Agora, o modelo de código real para o cálculo do MA (K) será: Com este novo método, é interessante compará-lo com o auto caro CROSS JOIN, bem como a PROC EXPAND. Na minha estação de trabalho (Intel i5 3.8Ghz, 32GB de memória, 1TB 72K HDD), auto CROSS JOIN é proibitivamente longo em tempo de execução (se os dados são grandes), enquanto o novo método usa apenas 2X tanto tempo como PROC EXPAND, ambos os consumos de tempo são Trivial comparando a auto CROSS JOIN. O consumo de tempo mostrado abaixo está em quotsecondquot. Abaixo está o código leitores podem executar e comparar-se. Postado em 10 de maio de 2015 por Liang Xie Programação SAS para Mineração de DadosEstou 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 em 20: 58Como 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 janelas 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: Código SQL Server T-SQL para calcular uma média móvel Por: Dallas Snider Leia comentários Dicas relacionadas: Mais Funções - User Defined UDF Problema Como posso suavizar os dados em um Coluna com uma média móvel em T-SQL Você pode por favor andar através de um exemplo no SQL Server com código T-SQL Como podemos validar os resultados Solução Os dados da série temporal podem ser intrinsecamente ruidosos e uma boa maneira de suavizar os dados é Calcular uma média móvel. Há uma série de maneiras de calcular uma média móvel em T-SQL, mas nesta ponta vamos olhar para uma maneira de calcular uma média móvel que define a janela de média x número de linhas por trás e x número de linhas à frente da corrente Linha de dados. A vantagem disso é que não há atraso no valor médio retornado eo valor da média móvel está na mesma linha com seu valor atual. Vamos começar criando uma tabela e carregando alguns dados usando o T-SQL abaixo. Temos 361 pontos de dados que criam uma onda senoidal barulhenta. Depois de carregar os dados, vamos executar o seguinte código T-SQL para selecionar todas as colunas, juntamente com o valor da média móvel. No código abaixo, o tamanho da janela média móvel é 15 (7 linhas que precedem a linha atual, mais a linha atual, mais as 7 linhas seguintes). A média móvel da coluna DataValue é retornada como a coluna MovingAverageWindowSize15. A cláusula ORDER BY é extremamente importante para manter os dados na ordem classificada adequada. Podemos copiar e colar os resultados no Excel para validar o cálculo está correto. Na imagem abaixo, a janela começa na célula C3 e termina em C17. A média móvel calculada pelo T-SQL nesta dica aparece na célula D10. A média calculada pelo Excel está na parte inferior e é igual ao valor em D10. Na figura abaixo, podemos ver os valores de dados originais plotados em azul com a média móvel plotada em vermelho. Próximas etapas Ajuste o tamanho da janela da média móvel para ver como a plotagem muda. Além disso, certifique-se de verificar essas outras dicas sobre T-SQL de mssqltips: Última atualização: 3/8 / 2016Calculando valores dentro de uma janela de rolagem em Transact SQL Dwain Camps Cálculo de valores dentro de uma janela de rolamento em SQL Qualquer momento 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 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. 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 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
No comments:
Post a Comment