Moving Average Query Access

Eu tenho uma produção de tabela que contém a seguinte estrutura: Eu tenho dados para cada representante de 1/1/2011 a 8/1/2013. O que eu quero ser capaz de fazer é criar uma média móvel de 12 meses a partir de 1/1/2012 para cada representante, da seguinte forma: onde cada linha representa a média móvel de 12 meses para o referido representante no momento indicado. Eu encontrei alguns exemplos que estavam vagamente perto e eu tentei-los sem sucesso. Parece que a adição de um grupo por componente rep é a principal saída de outros exemplos. Isso é quase tão longe quanto eu tenho: Essa consulta parece puxar uma média geral ou soma, uma vez que não há agrupamento na subconsulta correlacionada. Quando tento agrupar por, recebo um erro que ele só pode retornar no máximo uma linha. Como 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: Esta é 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. Gera a saída solicitada: respondida Jan 9 at 0:33 Sua resposta 2016 Stack Exchange, IncCalculating totais atuais e médias móveis no Microsoft Access com estatísticas de acesso total O Microsoft Access não oferece recursos internos para calcular totais em execução. Os totais em execução são resumos sobre um número definido de registros. Por exemplo, uma média móvel de 30 dias que você gostaria de calcular para cada registro com base em seu valor e seus 29 registros anteriores. Felizmente, o Total Access Statistics realiza uma ampla gama de cálculos de total de execução em seus registros: Execute resumos para um conjunto de registros em movimento para calcular somas, médias, mínimo, máximo, intervalo, mediana, desvio padrão, etc. Na sua fonte de dados Analisar os dados em suas tabelas e consultas do Access (incluindo dados vinculados) Agrupamento de suporte em valores de campo para que você possa executar várias análises em uma única passagem Totais atuais foram adicionados ao Total Access Statistics para Microsoft Access 2007 e X.7 Versões para o Access 2003 e versões anteriores. Visão geral dos totais em execução Os totais em execução são cálculos para um número definido de registros através de sua tabela ou consulta. Gerar facilmente resultados como somas em execução, médias, mínimo, máximo, mediana, etc. e colocá-los em campos em sua fonte de dados. Esses cálculos estão disponíveis: Média (média) Contagem Observações Sum Sum Squared Mínimo Máximo Gama Desvio padrão Variância Coeficiente de variância Erro padrão Modo mediano Modo Contagem Média geométrica Média harmônica Média raiz Quadratura Skewness Kurtosis Erro padrão de skewness Erro padrão de curtose Até cinco campos Em sua tabela podem ser atualizados ao mesmo tempo. Como calcular valores totais são calculados Por exemplo, você pode querer calcular a média dos últimos 10 registros. Essa média móvel é determinada com base na ordem de classificação de seus dados, calculando a média desses 10 registros e colocando-a em um campo designado pelo registro 10. Para o registro 11, o registro 1 é ignorado eo cálculo é executado para os registros 2 a 11, etc. Como os outros recursos, você pode especificar campos de grupo para que cada grupo tenha seus próprios totais em execução. Este recurso não cria uma tabela separada. Os resultados são colocados nos campos especificados na sua origem de dados. Executar Seleção de Campos de Totais A tela de seleção de campos para Executar Totais permite especificar os campos a ordenar e os campos para colocar os resultados: Os Campos de Grupo permitem que você gere um conjunto separado de análise para cada combinação de valores exclusivos entre os campos de grupo. Os Campos de classificação determinam a ordem de classificação de seus registros para calcular os valores em execução. Especifique pelo menos um campo de classificação. Isso pode incluir o campo que você está realizando os cálculos. Os campos são classificados em ordem crescente. Especifique até cinco Campos a Atualizar com seus totais. Verifique se os campos são de tipos de dados que podem conter os dados que você espera colocar neles. Executar as opções totais Depois de seleccionar os campos, são apresentadas as opções Totais em curso: Campo a analisar Especifique o campo em que os cálculos são baseados. Este campo pode ser um campo que foi especificado como um campo de classificação. Por exemplo, você pode querer calcular a média corrente baseada em vendas ordenadas em ordem crescente. Número de registros para calcular totais acima Especifica o número de registros no total móvel. Se você especificar 0, os totais são calculados para cada registro. Se você inserir um número específico, os cálculos são baseados nesse número de registros. O primeiro registro é descartado quando o próximo é adicionado, e os cálculos são baseados nesse conjunto de registros em movimento. Conjunto inicial de registros Se você especificar o número de registros para calcular os totais, há uma opção para determinar o que acontece antes de alcançar esse número de registros. Os totais devem ser ignorados ou calculados com base nos registros processados. Escolha Calcular para ver os totais em execução independentemente do número de registros em movimento ser atingido. Escolha deixar em branco para ter somente totais em execução quando o número especificado de registros é alcançado sem quaisquer cálculos para menor número de registros. Tipo de Cálculo Existem muitos tipos de cálculo disponíveis. Especifique o que você deseja para cada um dos campos de atualização selecionados. Resultados totais em execução Os resultados totais em execução são colocados nos campos de actualização especificados: Exemplo de totais actuais colocados nos quatro campos direitos desta tabela do Microsoft Access Neste exemplo, os dados foram ordenados por Data e ID da encomenda, com o cálculo no campo Vendas campo. Observe os valores no campo RunningCount aumentando de 1 a 10. Uma vez que atinge 10, permanece em 10 porque esse é o número máximo de registros no total móvel. O campo RunningTotal mostra a soma de vendas sobre os registros no conjunto de registros em movimento. Como a opção de calcular os valores para o conjunto inicial de registros foi selecionada (antes de atingir 10 registros), os valores são exibidos. Caso contrário, os primeiros 9 registros teriam valores nulos. Assistente Interativo e Interfaces Programáticas do VBA O Total Access Statistics permite gerar interativamente seus cálculos através de sua interface de assistente para selecionar facilmente a fonte de dados, campos e análise sem programação. Depois de especificar suas seleções, elas são salvas automaticamente como um cenário no banco de dados para que você possa executá-las novamente no futuro quando seus dados forem alterados. Uma função de estatísticas VBA também está disponível para que você possa gerar esses resultados de código. Você pode facilmente executar qualquer cenário salvo chamando um único procedimento na biblioteca Total Access Statistics VBA. A biblioteca inclui uma licença de tempo de execução livre de royalties para que você possa incluí-la em seu banco de dados do Access e distribuí-la para proprietários que não sejam Total Access Statistics. Chamar a função de um evento OnClick botões ou outro processo, e os resultados são gerados para você. Você pode até mesmo esconder a interface interna do usuário para que seus usuários nem sequer sabem que o Total Access Statistics está em execução. Eles serão surpreendidos com os recursos de análise estatística Detalhes adicionais de cálculos disponíveis nas estatísticas de acesso total Aqui estão alguns recursos e detalhes adicionais sobre alguns dos dados de análise que você pode executar em seus dados do Microsoft Access com estatísticas de acesso total: Fluxos e taxas de juros para gerar o Valor Presente Líquido (VPN), Valor Presente (PV), Valor Futuro (VF), Taxa Interna de Retorno (TIR), Taxa Interna Modificada de Retorno (MIRR) para pagamentos e recibos periódicos e dependentes da data. Cálculos de Percentilos Calcule diferentes tipos de percentis: mediana, quartis, quintis, octiles, deciles, percentis, cada percentil X.5 e coloque-os em uma tabela ou atualize um campo existente com o valor de percentil de registros. Totais Correntes e Médias Móveis Resumir um conjunto de registros em movimento para calcular somas correntes, médias móveis, mínimo, máximo, intervalo, mediana, desvio padrão, etc. Normalização de Dados (Transposição de Dados) Transponha dados não normalizados para que você possa facilmente analisar e manter isto. Regressões Regressões simples, múltiplas e polinomiais com cálculo dos coeficientes de equação, ANOVA e tabela residual Tabelas cruzadas e tabelas cruzadas Chi-Square com porcentagem de linha, coluna e totais exibidos como registros ou colunasValoração média na consulta Re: Média móvel na consulta Você Significa apenas os dados mais recentes de quatro semanas (por isso está mudando / movendo a cada semana) O que os dados em seu campo YearWeek parecem É formatado como Texto do Número DICAS PARA ENCONTRAR AS SOLUÇÕES EXCEL 1. Use a Ajuda embutida que vem Com Excel / Access 2. Use a funcionalidade de pesquisa nesta placa 3. Um monte de código VBA pode ser adquirido usando o gravador de macro. Dê a um homem um peixe, alimente-o por um dia. Ensinar um homem a pescar, alimentá-lo para a vida Gerrit. B disse: 06 de maio de 2012 16:31 Re: Movendo Média em Query Joe4, Não eu quero calcular a média móvel em 4 semanas. Campo YearWeek parece 201201 para a semana 1 de 2012, e é formatado como número. Re: Movendo Média em Query OK, independentemente de suas respostas às perguntas anteriores, acho que tenho uma solução que deve funcionar para qualquer instância. Primeiro, criei uma função definida pelo usuário que converte seu número de semana para um número total de semanas. Leva o ano e multiplica por 52, e depois adiciona os meses nas últimas duas posições de número. Esse UDF se parece com isto: Note, não é necessário usar um UDF, como você pode usar esta fórmula diretamente em suas consultas, mas desde que eu estou reutilizando, é muitas vezes mais fácil e torna suas fórmulas mais legível para fazê-lo deste jeito. Em seguida, criei uma consulta para calcular o máximo YearWeek para cada combinação de área / produto ID. Acabei de agrupar os campos Área e ID do produto e tomou o valor máximo de Anual (se você não estiver familiarizado com Consultas de Agregação, eu recomendo que eles sejam verificados nos arquivos de Ajuda embutidos do Access). Assumindo um nome de tabela de MyTable, aqui está o que o código SQL daquela consulta se parece com: Eu chamei essa consulta MyMaxQuery. Eu, em seguida, criei uma nova consulta em que juntamos a tabela original (MyTable) a esta consulta nos campos Área e ID do produto. Eu então criei um campo calculado chamado Check, onde verifico para ver se o campo YearMonth é maior ou igual ao valor máximo de YearWeek (a partir da consulta anterior) menos 3. Se for, eu retornar um 1. Eu, em seguida, adicionar critérios Para essa consulta para retornar apenas os valores iguais a 1. Assim, isso limita os dados para os últimos 4 meses. Agora, eu mudo esta consulta para uma consulta de agregação e pegue a média da quantidade para obter o que você deseja. O código SQL para essa consulta é semelhante a: Dicas para encontrar soluções Excel 1. Use a Ajuda embutida que vem com o Excel / Access 2. Use a funcionalidade de pesquisa nesta placa 3. Um monte de código VBA pode ser adquirido usando o Gravador De Macro. Dê a um homem um peixe, alimente-o por um dia. Ensine um homem a pescar, alimente-o para a vida