Pl sql média móvel

Houve uma boa pergunta sobre a OTN hoje sobre se há uma função Oracle padrão para calcular a média móvel exponencial. A resposta é que não existe essa função, mas com a cláusula modelo, você pode calcular isso muito facilmente. E é um ótimo exemplo do que quero dizer com um número variável de cálculos com base em valores calculados, escrito na minha terceira parte do tutorial da cláusula modelo. Antes de hoje, nem sabia o que era uma média móvel exponencial. Você pode ler mais sobre isso aqui na Wikipédia ou aqui com um bom exemplo. 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 às observações recentes enquanto ainda não descarta as observações antigas inteiramente. Do segundo link: A fórmula para o cálculo de uma média móvel exponencial (EMA) é: X EMA atual (ou seja, EMA a ser calculado) C Valor de dados original atual K Constante de suavização P EMA anterior (O primeiro EMA no intervalo a ser calculado é Arbitrário e pode ser o valor de dados original correspondente ou, muitas vezes, um valor de média móvel simples. K Constante de suavização 2 (1 n) E esta fórmula é seguida por um exemplo que eu estendi um pouco, usando esta tabela: Os registros do produto A Combine o exemplo no link. Eu compus os números do produto B. Aqui está a consulta de cláusula de modelo que implementa a fórmula. Observe como a fórmula se traduz diretamente na única e única regra da cláusula do modelo. A constante de suavização K está configurada Para .5, com base em uma janela de valores (n) igual a 3. Desafio: experimente isso sem a cláusula do modelo e veja se você pode encontrar algo mais abrangente. 5 comentários: 11.2 recursos em uso com esse como (selecione o produto 39A39 . Data 392009-01-0139 mês, 10 quantidade de dupla união seleciona todos 39A39, data 392009-02-0139, 15 de dupla união seleciona 39A39, data 392009-03-0139, 17 de dupla união seleciona 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 união dupla, todos selecionam 39A39, data 392009-07-0139, 25 de união dupla, todos selecionam 39A39, data 392009-08-0139, 27 de dupla união, todos selecionam 39A39, data 392009-09-0139, 30 de união dupla, todos selecionados 39A39, data 392009-10-0139, 35 de dupla união, todos selecionados 39A39, data 392009-11-0139 , 37 da união dupla, todos selecionam 39A39, data 392009-12-0139, 40 da dupla união, todos os 39B39, data 392009-01-0139, 0 da união dupla, todos selecionam 39B39, data 392009-02-0139, 50 da 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 união dupla todos selecionam 39B39, data 392009-06-0139, 35 de União dupla, selecione 39B39, data 392009-07-0139, 30 de dupla união, selecione 39B39, data 392009-08-0139, 30 de união dupla, selecione 39B39, data 392009-09-0139, 20 de dupla união, selecione tudo 39B39 , Data 392009-10-0139, 20 da união dupla, selecione 39B39, data 392009-11-0139, 20 da união dupla, selecione 39B39, data 392009-12-0139, 20 a partir de dual), rns as (selecione isso. . Rownumber () over (partição por produto ordem por mês) rn -. 2 (1count () over (partição por produto)) k. 0,5 k do dat), res (produto, mês, quantidade, rn, x) como (selecione r. product, r. month, r. amount, r. rn, r. mount x de rns r onde rn 1 union seleciona todos Ns. product, ns. month, ns. amount, ns. rn, ns. k (ns. amount - es. x) es. xx de rns ns, res s onde ns. rn es. rn 1 e ns. product es. produto) selecione o produto, mês, quantidade, rn, rodada (x, 3) EMA a partir de pedido de res por produto, mês após a computação do formulário fechado, surgi com o código a seguir que, se mais parecido com 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 apenas precisam de somas correntes. Há um caso geral e dois casos especiais que são muito mais fáceis: com t1 como (selecionar produto, mês, quantidade, quantidade ci, rownumber () over (partição por ordem do produto por mês) rn, --2 (1 rownumber () over (Partição por ordem de produto por mês)) ki 0,5 ki de vendas), t2 como (selecione produto, mês, quantidade, (caso quando rn 1 e 1 mais ki end ci) ai, caso quando rn 1 e mais 1 (1 - Ki) end bi a partir de t1), t3 como (SELECIONE o produto, MONTH, amount, ai, xmlquery (REPLACE (wmconcat (bi) over (PARTITION BY product ORDEM POR MONTH linhas entre INCLINADO precedente AND ACTUAL), 39,39, 3939 ) VOLTANDO o conteúdo).getnumberval () mi FROM t2), t4 as (selecione o produto, o mês, o montante, o mi) xi do t3) SELECIONE o produto, MONTH, amount, round (mi SUM (xi) over (PARTITION POR produto ORDER BY MONTH filas ENTRE INCLINADO ANTERIOR E LIGAÇÃO ATUAL), 3) ema FROM t4 Caso especial K 0.5: com t1 como (selecionar produto, mês, quantidade, rownumber () sobre (partição por ordem do produto por mês) rn, quantidade Poder (2, nvl (nullif (r (Partição por ordem de produto por mês) - 1, 0), 1)) ci de vendas) selecione produto, mês, quantidade, rodada (soma (ci) sobre (partição por ordem de produto por mês linhas entre ilimitado anterior E linha atual) potência (2, rn), 3) ema de t1 Caso especial K 2 (1 i): com t1 como (selecionar produto, mês, quantidade, rownumber () over (partição por produto ordem por mês) rn, Quantidade de rownumber () por cima (partição por ordem de produto por mês) ci de vendas) selecione produto, mês, quantidade, rodada (soma (ci) sobre (partição por ordem de produto por mês linhas entre linha não limitada precedente e atual) 2 (rn ( Rn 1)), 3) ema de t1 I39ll publicar a prova do formulário fechado se alguém estiver interessado nela. Este é um ótimo exemplo de quotfun com SQLquot :-) Uma combinação de XMLQuery, wmconcat indocumentado e funções analíticas com a cláusula de janela. Eu gosto disso. Embora não seja tão abrangente como a variante da cláusula modelo e os Rafu39s recursivos com um, como você disse a si mesmo. E com certeza, gostaria de ver a prova da forma fechada. Abordei outra questão: como otimizar a constante de suavização SELECT k - suavização constante. Mse - erro quadrado médio FROM (SELECCIONAR DE VENDA MODELO DIMENSÃO POR (produto. ROWNUMBER () OVER (PARTITION BY product ORDER BY month ASC) rn) MEDIDAS (montante - valor das vendas mês a mês 0 COMO C. 0 AS P. 0 AS X. 0 AS SE - erro ao quadrado - - linha de trabalho e atributos - a) linha de trabalho é o produto 39X39, rn 1 - b) os atributos de trabalho são os seguintes:. 0 AS SSE - sum SE para todos os meses de produtos. 0 AS MSE - significa SSE para todos os produtos. 0 AS k - para todos os meses de produtos. 0 AS PreMSE - MSE anterior de k39s para todos os produtos. 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. Xany Rn ORDEM POR 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) qualquer, qualquer. MSE39A39,1 SUM (SE) qualquer, qualquer 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 então - abs (delta39A39, 12) ELSE abs (delta39A39,1) END. Prioritypt39A39,1 K39A39,1)) onde o produto 39A39 e rn 1 K MSE ---------- ---------- .599999237 174.016094 Seu grupo é o que agrega a sua média, e está agrupando toda a tabela (eu suponho que você fez isso para permitir a seleção para tudo). Apenas mova seu avg para outra subconsulta, remova o grupo abrangente e isso deve resolvê-lo. Quando você executa a instrução básica SELECT AVG (custo), ela é naturalmente agrupada pela coluna especificada (custo neste caso), pois é o que você está solicitando. Eu sugeriria ler mais sobre GROUP BY e agregados para obter uma melhor compreensão sobre o conceito. Isso deve ajudá-lo mais do que apenas uma solução simples. A resposta abaixo é, na verdade, da resposta de Davids. Faz uso das funções analíticas. Basicamente, o que está acontecendo é que, em cada chamada do AVG, você está dizendo ao motor o que usar para a função (neste caso, nada). Um writeup decente em funções analíticas pode ser encontrado aqui e aqui e mais com um google no assunto. No entanto, se seu mecanismo SQL permitir variáveis, você poderia facilmente fazer a resposta abaixo. Eu realmente prefiro isso para futura capacidade de manutenção. A razão é que uma variável com um bom nome pode ser muito descritiva para os futuros leitores do código, em comparação com uma função analítica que requer um pouco mais de trabalho para ler (especialmente se você não entender a função excedida). Além disso, esta solução duplica a mesma consulta duas vezes, portanto, pode valer a pena armazenar sua média em uma variável SQL. Então, você altera sua declaração para simplesmente usar essa média global. Isso é variáveis ​​no SQL-Server (você terá que adaptá-lo para sua própria instância de SQL). Esta solução irá ler muito mais limpo para os futuros leitores do seu SQL, também I39m bonita Certamente, a consulta de David é pelo menos tão eficiente e não requer o uso de um bloco PLSQL que requer que você coloque o resultado do SELECT em variáveis ​​- não será exibido da maneira como você o escreveu (na verdade, eu acho que ele nem seria executado) . E, além disso, a consulta duplicada só será executada uma vez pela Oracle. Tenho certeza de que o otimizador é inteligente o suficiente para detectar isso. Ndash ahorrewithononame 10 de março às 16:21 Agradeço sua explicação, mas o bloco PLSQL é simplesmente errado. Essa abordagem nunca funcionará na Oracle. Se você não deseja repetir a consulta avg (), use a abordagem com a função analítica ou a solução de junção cruzada. Ndash ahorrewithononame 10 de março às 16: 29Usando uma média móvel simples para suavizar dados é uma técnica bastante popular. É muito ruim o principal exemplo na Ajuda do SQL Anywhere está longe de ser simples: o que torna esse exemplo tão complexo Além da declaração do problema, isto é: calcular a média móvel de todas as vendas de produtos, por mês, no ano 2000. Heres o que faz É complexo: duas referências à função AVG (), um GROUP BY (que, por si só, faz praticamente qualquer SELECT, um scratcher principal),. Uma cláusula WINDOW Stealth uma cláusula WINDOW que nem sequer usa a palavra-chave WINDOW. Então, para os não iniciados (as pessoas que precisam de exemplos mais do que ninguém), não é óbvio que um WINDOW esteja envolvido. Não é apenas qualquer cláusula WINDOW, tenha em atenção você, mas que inclui todos os componentes que você pode codificar em uma cláusula WINDOW: a PARTITION BY, RANGE. Não é uma cláusula ROWS simples, mas uma cláusula RANGE completa, uma que possui um relacionamento íntimo com o ORDER BY. Eu sei o que é uma linha, mas o que o redacto é uma GAMA Mas aguarde, há mais: a escolha da GAMA sobre ROWS neste exemplo é fundamental para a operação correta da consulta. (Para uma discussão mais completa deste exemplo particular, veja o Exemplo 23 - Computando uma Média em Movimento no excelente papel branco OLAP de Glenn Paulleys). Agora, vamos voltar à pista: Uma Média Mínima Realmente Simples O seguinte exemplo mostra 10 dias de valor Dados em conjunto com a média móvel do valor de hoje e os dias de ontem: a cláusula WINDOW nas linhas 21 a 23 define uma janela em movimento que contém duas linhas: linha de hoje (LIGA ATUAL) e linha de ontem (1 PRECEDING): a cláusula WINDOW ORDER BY determina o que PRECEDING significa (a linha anterior por t. entrydate) ea cláusula ROWS determina o tamanho da janela (sempre duas linhas). A expressão AVG (t. value) OVER twodays na linha 19 refere-se à cláusula WINDOW pelo nome, e informa o SQL Anywhere para calcular a média dos dois valores de t. value que existem na janela deslizante de 2 filas, para cada um Linha no conjunto de resultados. Então, para 2017-02-02 a média de 10 e 20 é 15.000000, para 2017-02-03 a média de 20 e 10 é 15.000000, para 2017-02-04 a média de 10 e 30 é 20.000000, para 2017- 02-10 a média de 10 e 60 é 35.000000. Ops, e a primeira linha A linha 2017-02-01 não tem uma linha PRECEDING, então qual é a média na janela móvel De acordo com o documento branco de Glenn Paulleys no caso de uma janela em movimento, presume-se que as linhas que contenham Null Os valores existem antes da primeira linha, e após a última linha, na entrada. Isso significa que quando a janela de mudança tem 2017-02-01 como a FIÇÃO ATUAL, a linha 1 PRECEDING contém valores NULL. E quando o SQL Anywhere calcula um AVG () que inclui um valor NULL, ele não conta o NULL. Não no numerador ou no denominador ao calcular a média. Sua prova: é por isso que twodayaverage 10.000000 para a primeira linha 2017-02-01. Postado por Breck Carter às 15:47