Pl sql moving average

Esta é uma pergunta de 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 perguntado. Gera a saída solicitada: respondida Jan 9 at 0:33 Sua resposta 2017 Stack Exchange, IncExponential Moving Average Houve uma pergunta agradável sobre OTN hoje sobre se há uma função padrão do Oracle para calcular a média móvel exponencial. A resposta é que não há tal função, mas com a cláusula modelo, você pode calculá-lo muito fácil. E é um ótimo exemplo do que eu quero dizer com o número variável de cálculos baseados em valores calculados, escrito na minha terceira parte do tutorial de cláusula modelo. Antes de hoje, eu nem sabia o que uma média móvel exponencial era exatamente. Você pode ler mais sobre isso aqui na Wikipedia ou aqui com um bom exemplo. A partir do primeiro link: Uma média móvel exponencial (EMA), aplica fatores de ponderação que diminuem exponencialmente. A ponderação para cada ponto de dados mais antigo diminui exponencialmente, dando muito mais importância a observações recentes, enquanto ainda não descarta observações mais antigas inteiramente. A partir do segundo link: A fórmula para calcular uma média móvel exponencial (EMA) é: X EMA atual (ou seja, EMA a ser calculada) C Valor de dados original atual K Smoothing Constant P EMA anterior (O primeiro EMA no intervalo a ser calculado é Arbitrária e pode ser o valor de dados original correspondente ou, muitas vezes, um valor de média móvel simples K Smoothing Constant 2 / (1 n) E esta fórmula é seguida por um exemplo que eu estendi um pouco, usando esta tabela: Os registros do produto A corresponde ao exemplo no link. Eu fiz os números do produto B. Aqui está a consulta de cláusula modelo que implementa a fórmula. Nota como a fórmula traduzir diretamente para a única regra da cláusula do modelo. A constante de suavização K é Definido como .5, com base em uma janela de valores (n) igual a 3. Desafio: tente isso sem a cláusula do modelo e veja se você pode vir acima com algo mais abrangente 6 comentários: 11.2 recursos em uso com dat como (selecione 39A39 Data do produto 392009-01-0139, 10 quantidade da união dupla todos selecionam 39A39, data 392009-02-0139, 15 da união dupla todos selecionam 39A39, data 392009-03-0139, 17 da união dupla todos selecionam 39A39, data 392009-04-0139, 20 de dupla união todos selecionam 39A39, data 392009-05-0139, 22 de dupla união todos selecionam 39A39, data 392009-06-0139, 20 de dupla união todos selecionam 39A39, data 392009-07-0139 , 25 de dupla união todos selecionam 39A39, data 392009-08-0139, 27 de dupla união todos selecionam 39A39, data 392009-09-0139, 30 de dupla união todos selecionam 39A39, data 392009-10-0139, 35 de dual union Todos selecionam 39A39, data 392009-11-0139, 37 de união dupla todos selecionam 39A39, data 392009-12-0139, 40 de união dupla todos selecionam 39B39, data 392009-01-0139, 0 de união dupla todos selecionam 39B39, data 392009-02-0139, 50 de dupla união todos selecionam 39B39, data 392009-03-0139, 10 de dupla união todos selecionam 39B39, data 392009-04-0139, 40 de dupla união todos selecionam 39B39, data 392009-05-0139 , 15 de dupla união todos selecionam 39B39, data 392009-06-0139, 35 de dupla união todos selecionam 39B39, data 392009-07-0139, 30 de dupla união todos selecionam 39B39, data 392009-08-0139, 30 de dupla união Todos selecionar 39B39, data 392009-09-0139, 20 de união dupla todos selecionar 39B39, data 392009-10-0139, 20 de união dupla todos selecionar 39B39, data 392009-11-0139, 20 de dupla união todos selecionar 39B39, data 392009-12-0139, 20 a partir de dual), rns as (select dat. . Rownumber () over (partição por ordem de produto por mês) rn -. 2 / (1count () sobre (partição por produto)) k. 0,5 k de dat), res (produto, mês, quantidade, rn, x) como (selecione r. produto, r. month, r. amount, r. rn, r. amount x de rns r onde rn 1 union all select Ns. product, ns. month, ns. amount, ns. rn, ns. k (ns. amount - es. x) es. xx de rns ns, res es onde ns. rn es. rn 1 e ns. product es Produto, mês, quantidade, rn, rodada (x, 3) EMA de res ordem por produto, mês depois de computar o formulário fechado Eu vim com o seguinte código que se mais como uma ofuscação do que qualquer coisa abrangente. A idéia é criar múltiplos em execução usando uma concatenação de string e a funcionalidade xml-eval. As formas fechadas dos casos especiais só precisam de somas correntes. Há um caso geral e dois casos especiais que são muito mais fáceis: com t1 como (selecione o produto, o mês, o montante, o montante ci, o número do rownumber () sobre (partição por ordem de produto por mês) rn, --2 / (1 rownumber () Ki 0,5 ki das vendas), t2 como (produto selecionado, mês, quantidade, (caso quando rn 1 e 1 ki end ci) ai, caso quando rn 1 e 1 else (1 (RETIRAR (wmconcat (bi) sobre (PARTIÇÃO POR produto ORDEM POR MÊS linhas entre ENTRADA não precedida E CORRENTE ACTUAL), 39,39, 3939) RETORNAR conteúdo).getnumberval () mi FROM t2), t4 como (selecione produto, mês, quantidade, mi, (ai / mi) xi de t3) SELECT produto, MONTH, (PARTITION BY product ORDEM POR MÊS linhas entre ENTRADA SEM FIM E CURRENTE), 3) ema FROM t4 Caso especial K 0.5: com t1 como (selecione produto, mês, quantidade, rownumber () sobre (partição por produto ordem por mês) rn , Quantidade de energia (2, nvl (nullif (rownumber () sobre (partição por produto ordem por mês) - 1, 0), 1) (2, rn), 3) ema de t1 Caso especial K 2 / (1 i): com t1 como (selecione o produto, o mês, a quantidade, o número de rownumber ) Sobre (partição por produto ordem por mês) rn, quantidade rownumber () sobre (partição por produto ordem por mês) ci de vendas) selecione produto, mês, montante, Linhas entre a linha não precedida e a linha atual) 2 / (rn (rn 1)), 3) ema de t1 I39ll postar a prova da forma fechada se alguém estiver interessado nela. Este é um ótimo exemplo de quotfun com SQLquot :-) Uma combinação de XMLQuery, o wmconcat não documentado, e funções analíticas com a cláusula windowing. Eu gosto disso. Embora não seja tão abrangente quanto a variante de cláusula modelo e o recursivo de Rafu com um, como você mesmo disse. E claro, gostaria de ver a prova da forma fechada. Abordei outra questão: como otimizar a constante de suavização SELECT k - constante de suavização. Mse - erro quadrado médio FROM (SELECT FROM vendas MODEL DIMENSION BY (produto. ROWNUMBER () OVER (PARTITION BY produto ORDEM POR mês ASC) rn) AS P - 0 AS X. 0 AS SE - erro quadrado - - linha de trabalho e atributos - a) linha de trabalho é produto 39X39, rn 1 - b) atributos de trabalho são os seguintes:. 0 AS SSE - soma SE para todos os produtos / meses. 0 AS MSE - significa SSE para todos os produtos / meses. 0 AS k - para todos os produtos / meses. 0 AS PreMSE - MSE prévio de k39s para todos os produtos / meses. 0 AS diff - entre MSE atual e anterior. 0.1 AS delta - incremento inicial. 0 AS priorpt - ponto de partida inicial) REGRAS ITERATE (99) ATÉ (abs (diff39A39,1) lt 0,00010) (Cany, rn amountcv (), cv () K39A39,1 priorpt39A39,1 delta39A39,1. , Rn ORDER BY produto, rn ASC COALESCE (K39A39,1 Ccv (), cv () (1-K39A39,1) Xcv (), cv () - 1, Ccv (), cv ()) Pproduct, rn Xcv (), Cv () - 1. SEproduct, rn POWER (Ccv (), cv () - Xcv (), cv () - 1, 2) SSE39A39,1 SUM (SE) any, any MSE39A39,1 SUM (SE) any, any / 24. diff39A39,1 CASE iterationnumber QUANDO 0 então NULL ELSE preMSE39A39,1 - MSE39A39,1 END PreMSE39A39,1 MSE39A39,1 delta39A39,1 CASO QUANDO diff39A39,1 lt 0 THEN - abs (delta39A39 , 1/2) ELSE abs (delta39A39,1) ENDPT39A39,1 K39A39,1)) onde o produto 39A39 e rn 1 / K MSE ---------- --------- - .599999237 174.016094AVG (Transact-SQL) ALL Aplica a função agregada a todos os valores. ALL é o padrão. DISTINCT Especifica que o AVG seja executado somente em cada instância exclusiva de um valor, independentemente do número de vezes que o valor ocorrer. Expressão É uma expressão da categoria de tipo de dados numérico exata ou aproximada, exceto para o tipo de dados bit. Não são permitidas funções agregadas e subconsultas. OVER (partitionbyclause orderbyclause) partitionbyclause divide o conjunto de resultados produzido pela cláusula FROM em partições às quais a função é aplicada. Se não for especificado, a função trata todas as linhas do conjunto de resultados da consulta como um único grupo. Orderbyclause determina a ordem lógica na qual a operação é executada. Pedido por cláusula é necessária. Para obter mais informações, consulte Cláusula OVER (Transact-SQL). O tipo de retorno é determinado pelo tipo de resultado avaliado da expressão. Decimal category (p, s) Se o tipo de dados de expressão é um tipo de dados de alias, o tipo de retorno também é do tipo de dados de alias. No entanto, se o tipo de dados base do tipo de dados de alias é promovido, por exemplo de tinyint para int. O valor de retorno é do tipo de dados promovido e não o tipo de dados alias. AVG () calcula a média de um conjunto de valores dividindo a soma desses valores pela contagem de valores nonnull. Se a soma exceder o valor máximo para o tipo de dados do valor de retorno um erro será retornado. O AVG é uma função determinística quando usado sem as cláusulas OVER e ORDER BY. Ele é não-determinístico quando especificado com as cláusulas OVER e ORDER BY. Para obter mais informações, consulte Funções determinísticas e não determinísticas. A. Usando as funções SUM e AVG para cálculos O exemplo a seguir calcula as horas de férias médias e a soma das horas de folga que os vice-presidentes de Ciclos de Trabalho Aventuras usaram. Cada uma dessas funções agregadas produz um único valor de resumo para todas as linhas recuperadas. O exemplo usa o banco de dados AdventureWorks2017.