Moving Average Postgresql

Em um banco de dados de transações abrangendo 1.000 pessoas de entidades ao longo de 18 meses, eu gostaria de executar uma consulta para agrupar todos os períodos de 30 dias possíveis por entidade com uma SOM dos seus valores de transação e COUNT de suas transações nesse período de 30 dias, E devolver os dados de uma forma que eu possa consultar. Depois de muitos testes, este código cumpre muito do que eu quero: e vou usar em uma consulta maior estruturada como: O caso que esta consulta não cobre é quando a contagem da transação abrangerá vários meses, mas ainda será dentro de 30 dias de cada um. Este tipo de consulta é possível com o Postgres. Se assim for, eu aceito qualquer entrada. Muitos dos outros tópicos abordam a execução de agregados, não rolando. O script CREATE TABLE: a saída ideal incluiria SUM (quantidade) e COUNT () de todas as transações durante um período de 30 dias. Veja esta imagem, por exemplo: O resumo de data verde indica o que está sendo incluído na minha consulta. O destaque da linha amarela indica registros do que eu gostaria de fazer parte do conjunto. Por todos os períodos de 30 dias possíveis por entidade, você quer dizer que o período pode começar qualquer dia, então 365 períodos possíveis em um ano (sem salto) Ou você só quer considerar dias com uma transação real como o início de um período individualmente para qualquer Entityid. De qualquer forma, forneça a definição da sua tabela, a versão do Postgres, alguns dados de amostra e o resultado esperado para a amostra. Ndash Erwin Brandstetter 20 de julho 15 às 7:18 Em teoria, eu quis dizer qualquer dia, mas na prática não há necessidade de considerar dias em que não haja transações. Eu publiquei os dados de amostra e a definição da tabela. Ndash tufelkinder 20 de julho 15 às 14:50 Então você deseja acumular linhas do mesmo entidade em uma janela de 30 dias a partir de cada transação real. Pode haver várias transações para o mesmo (transdate, entityid) ou é essa combinação definida unicamente. Sua definição de tabela não possui restrição UNIQUE ou PK, mas restrições parecem estar faltando. Ndash Erwin Brandstetter 20 de julho 15 às 15:44 A consulta que você possui Você poderia simplificar sua consulta usando uma cláusula WINDOW, mas isso é apenas encurtar a sintaxe, não alterando o plano de consulta. Também usando a contagem ligeiramente mais rápida (). Uma vez que id é definitivamente definido NÃO NULL E você não precisa ORDER BY entityid já que você já é PARTITION BY entityid Você pode simplificar ainda mais: Não adicione ORDER BY para a definição da janela, não é relevante para sua consulta. Então você não precisa definir um quadro de janela personalizado, seja: mais simples, mais rápido, mas ainda é uma versão melhor do que você tem. Com meses estáticos. A consulta que você pode querer não está claramente definida, portanto, eu construo com base nessas premissas: Contagem de transações e montante por cada período de 30 dias dentro da primeira e última transação de qualquer entidade. Exclua os períodos iniciais e de saída sem atividade, mas inclua todos os períodos possíveis de 30 dias dentro desses limites externos. Isso lista todos os períodos de 30 dias para cada entidade com seus agregados e com transdate sendo o primeiro dia (inclusive) do período. Para obter valores para cada linha individual, junte-se à tabela de base mais uma vez. A dificuldade básica é a mesma que discutida aqui: a definição do quadro de uma janela não pode depender dos valores da linha atual. A consulta que você realmente deseja Após a atualização e a discussão da pergunta: Acumule linhas do mesmo entidade em uma janela de 30 dias começando em cada transação real. Como seus dados são distribuídos escasso, ele deve ser mais eficiente para executar uma associação automática com uma condição de alcance. Ainda mais desde o Postgres 9.1 não tem ligações LATERAL, no entanto: uma janela rotativa só pode ter sentido (com relação ao desempenho) com dados para a maioria dos dias. Isso não agrega duplicatas em (transdate, entityid) por dia, mas todas as linhas do mesmo dia estão sempre incluídas na janela de 30 dias. Para uma mesa grande, um índice abrangente como esse poderia ajudar um pouco: o último montante da coluna só é útil se você conseguir ver apenas o índice. Deixa-o também. Mas não vai ser usado enquanto você seleciona toda a tabela de qualquer maneira. Ele apoiaria consultas para um pequeno subconjunto. Metrics Maven: Cálculo de uma média móvel no PostgreSQL Em nossa série Metrics Maven, Composes data cientifico compartilha recursos, dicas, truques e código do banco de dados que você pode usar para obter as métricas que você precisa de seus dados. Neste artigo, veja bem como calcular uma média móvel no PostgreSQL. Este artigo baseia-se nos nossos dois artigos anteriores sobre Funções de Janelas e Quadros de Janelas no PostgreSQL. Bem, aproveite os recursos de janelas que discutimos anteriormente para calcular uma média móvel e, também, olhar para um método alternativo. O que é uma média móvel Uma média móvel é exatamente o que parece - uma média que se move continuamente com base na mudança de entrada. Por exemplo, você pode querer levar a média de algum valor para as 100 principais entradas ou nos 30 dias anteriores. Porque você receberá novas entradas em seu banco de dados ou porque cada novo dia é outra data, a média mudará. O termo média móvel também é sinônimo de média móvel ou média de corrida, mas existem alguns tipos diferentes de médias móveis. Neste artigo, iriam se concentrar na média móvel simples para que nossos pés fiquem molhados e, bem, também revisem brevemente a média móvel acumulada no final do artigo. Um futuro artigo abrangerá as médias móveis ponderadas e exponenciais. A razão para usar uma média móvel para suas métricas é facilitar a localização das tendências. É uma técnica comumente utilizada em finanças e análise de negócios para suavizar os mergulhos e espigas que podem ocorrer nos dados para que as verdadeiras tendências possam ser identificadas ao longo da série em mudança. Descobrir como executar o cálculo como as mudanças de dados pode ser um pouco assustador, no entanto, se você nunca fez isso. Uma vez que você aprende um método que você gosta, (bem, cubra dois) é fácil de fazer e você encontrará muitos usos para isso em seu rastreamento e relatórios. Vamos lá. Primeiras coisas primeiro: bem, precisa de uma tabela que contenha os valores que queremos prover. Na prática no Compose, muitas vezes achamos que os dados básicos que precisamos também não estão claramente definidos em uma tabela. Por essa razão, temos algumas tabelas agregadas que extraem os dados que precisamos juntos. Estas são as tabelas de base para as quais aplicaremos cálculos mais avançados, como uma média móvel. Em alguns casos, estas são tabelas derivadas que existem temporariamente para a execução da consulta principal. Em outros casos, podemos usar uma visão ou uma visão materializada. Então, no entanto, você entendeu, você precisará de uma tabela contendo os valores que deseja em média e qualquer dimensão que você deseja ordenar os dados. Para o nosso exemplo, digamos que foi solicitado a criar uma média móvel de 30 dias para downloads de aplicativos do Exemplo Co. Os dados de download do aplicativo são preenchidos diariamente para uma tabela chamada appdownloadsbydate e a parte mais recente é assim: neste exemplo, O pedido por data será importante, pois queremos calcular uma média móvel de 30 dias em relação às séries de datas anteriores. Por isso, é importante que tenhamos uma linha para cada data. No nosso caso, sim, se você tiver lacunas em seus dados onde não há valores para certas datas, você pode usar generateseries ao construir sua tabela base para garantir que você tenha todas as linhas que você precisará. Observe como esse intervalo de datas contém downloads flutuantes de downloads de aplicativos de 35 para 7. É muito difícil ver uma tendência a partir desses dados: Digite a média móvel. Usando quadros de janelas para uma média móvel simples Se você se lembra do nosso artigo anterior nesta série. Os quadros das janelas são usados ​​para indicar o número de linhas em torno da linha atual que a função da janela deve incluir. Eles criam um subconjunto de dados para a função da janela para operar. Dependendo de seus dados e suas necessidades, o cálculo da média móvel pode incluir linhas que precedem e seguem a linha atual, mas para nossos propósitos, nossa média móvel usará linhas precedentes e a linha atual porque queremos gerar um novo valor médio móvel para Cada nova data. Nossa consulta é assim: usamos ORDER BY no nosso campo de datas para garantir que nossos dados estejam na ordem em que esperamos e nós incluímos ROWS ENTRE 29 NORMAS PRECEDENTES E ATUAIS para configurar o quadro da janela para o cálculo do AVG. À medida que o quadro da janela avança para cada data, apenas as 29 linhas precedentes e a atual (30 dias totais) são usadas para o cálculo: Como não mostramos datas em nossa tabela básica antes de 26 de maio para este exemplo, vamos focar nossa revisão Dos resultados nas datas em que mostramos as 29 linhas precedentes. Vamos levar 30 de junho, por exemplo. Nosso quadro de janela concentra nossa agregação AVG nos downloads de aplicativos apenas a partir de junho, esta parte da nossa tabela de base: então, agora, se classificarmos a média de rotação calculada, podemos ver que os dados são alisados ​​e há uma tendência ascendente Durante a primeira semana de junho, depois uma tendência de queda mais volátil depois disso: uma vez que isso mostra apenas um mês de dados, não é muito importante para um relatório analítico, mas espero que ele ajude você a entender como calcular uma média móvel pode ser útil para negócios análise. Uma dica sobre não incluir a linha atual Se, por algum motivo, você não quiser incluir a linha atual para a sua função de janela e você estiver usando apenas as configurações anteriores ou apenas SEGUINDO para o seu quadro de janela, uma maneira fácil de fazer isso é usar x PRECEDING ou E SEGUINDO duas vezes em suas FILETAS ENTRE. cláusula. Por exemplo, digamos que queríamos usar 30 linhas anteriores à nossa linha atual, mas não incluímos a linha atual no quadro da janela. Poderíamos escrever essa cláusula como esta: ROTHOS ENTRE 30 ANÁLISES E 1 ANTERIOR. Da mesma forma, podemos excluir a linha atual, mas faz 30 linhas seguindo assim: ROUPAS ENTRE 1 SEGUINTE E 30 SIGUIENTES. Um método alternativo para uma média móvel simples Antes do PostgreSQL 9.0, não possuímos as opções de quadro de janela x PRECEDING ou y SEGURAS disponíveis para nós. Para calcular uma média móvel sem usar um quadro de janela, podemos usar dois alias de tabela de nossa tabela base. Bem use um alias para operar sobre o outro usando um intervalo de data. Confira: usando este método, podemos alcançar os mesmos resultados descritos acima com o quadro da janela. Se você estiver operando em grandes quantidades de dados, a opção da janela será mais eficiente, mas esta alternativa existe se você quiser usá-la. Calculando uma média móvel cumulativa Agora que analisamos alguns métodos para calcular uma média móvel simples, bem, altere nosso exemplo de quadro de janela para mostrar como você também pode fazer uma média móvel cumulativa. Os mesmos princípios se aplicam, mas ao invés de ter um quadro de janela continuamente deslocando para um intervalo, o quadro da janela simplesmente se estende. Por exemplo, em vez de fazer uma média móvel de 30 dias, calculariam uma média móvel de até o ano. Para cada nova data, seu valor é simplesmente incluído no cálculo médio de todas as datas anteriores. Vamos dar uma olhada neste exemplo: porque a nossa tabela base começa em 1º de janeiro para o ano atual, estavam usando UNBOUNDED PRECEDING para configurar o nosso quadro de janela. Os resultados que recuperamos para este cálculo cumulativo são assim: se classificarmos esses resultados, você pode ver que a vantagem da média móvel acumulada é um alisamento adicional dos dados, de modo que somente as mudanças significativas de dados aparecem como tendências. Nós vemos agora que há uma ligeira tendência ascendente de acordo com o ano: Deslocamento Agora que você conhece alguns tipos diferentes de médias móveis que você pode usar e alguns métodos diferentes para o cálculo, você pode realizar análises mais profundas e criar mais Relatórios efetivos. Em nosso próximo artigo Metrics Maven, observe algumas opções para como tornar os dados bonitos, de modo que em vez de valores como 20.4184782608695652, veja bem 20.42. Vejo você na próxima vez copiar 2017 ComposePostgreSQL Cálculo de uma média móvel Em sex, 21 de janeiro de 2005 às 12:53:45 AM -0500, Greg Stark escreveu: QuotVanole, Mikequot ltMike. Vanolecingulargt escreve: Preciso calcular uma média móvel e eu gostaria Para fazê-lo com SQL, ou uma função Pg criada para este fim. I39m no Pg 7.4. Isso é possível em Pg sem um monte de auto-junções, ou existe uma funcionalidade disponível Infelizmente, as médias móveis se enquadram em uma classe de funções, chamadas funções analíticas (pelo menos aquilo que Oracle chama) que são intrinsecamente difíceis de modelar eficientemente em SQL. O Postgres não tem nenhum suporte especial para este conjunto de funções, então você está preso a fazê-lo nas formas ineficientes que o SQL padrão permite. Eu acho que isso ainda é difícil de implementar corretamente usando o suporte de função extremamente extensível do Postgres39. Mesmo se você o implementou em Perl ou Python, não acho que haja alguma maneira de alocar uma área de armazenamento estática temporária para um determinado site de chamadas. Portanto, sua função de média móvel se comportaria estranhamente se você a chamou duas vezes em uma determinada consulta. Mas se você pode trabalhar dentro dessa ressalva, deve ser direto implementá-lo de forma eficiente em Perl ou Python. Alternativamente, você pode escrever uma função plpgsql para calcular a média móvel específica que você precisa, que faz a própria seleção. Se você se sentir aventureiro, você pode olhar para a documentação da Oracle39 em suas funções analíticas e ver se você pode encontrar algo genérico para o PostgreSQL. Mesmo que você apenas faça uma função média móvel, seria um bom começo. - Jim C. Nasby, consultor de banco de dados decibeldecibel. org Dê ao seu computador alguns doces de cérebro distributed. net Team 1828 Windows: quot Onde você quer ir hoje Linux: quê em que você quer ir tomorrowquot FreeBSD: quot Você está vindo ou o que? Greg Stark Eu acho que a parte mais difícil de fazer até mesmo uma implementação simples é precisamente o ponto que eu criei sobre fazê-lo em Perl ou Python. De alguma forma, você deve alocar uma área de armazenamento estático específica para o site de chamadas. Isso é como uma chamada de função agregada, exceto, é claro, que você retornará um dado para cada registro. Para uma implementação mais completa, há muito mais detalhes. Se eu entendo corretamente na Oracle, você precisa especificar uma cláusula ORDER BY e o equivalente a uma cláusula GROUP BY no A menos que o I39m mal entendido o problema, acho que um gatilho escrito no PLpgsql funcionaria bem. Algo assim: CRIAR TABELA foo (chave principal SERIAL de FOoid, foo TEXT) BARRA CREATE TABLE (fofo INTEGER referências foo, barid chave primária SERIAL, barra DOUBLE PRECISION NOT NULL) CREAR TABELA baravg (fooid INTEGER chave primária referências foo, baravg DOUBLE PRECISION ) CRIAR OU REEMBARRAR A FUNÇÃO getbaravg () RETORNAR O TRIGGER COMO 39 DECLARAR o registro de barramento RECORD x INTEGER e DOUBLE PRECISION: 0 COMEÇAR SE TGOP 3939INSERT3939 ENTÃO y. Y. NOVO. bar x: 1 PARA o barracão IN SELECT FROM bar LOOP SE NOVO. foid barrecord. fooid ENTÃO y: y barrecord. bar x: x 1 END SE FIM LOOP y: yx SE EXISTE (SELECE DE BARAVG ONDE FOCO NOVO. fooid) ENTÃO ACTUALIZAÇÃO baravg SET baravg. baravg y WHERE FOIDE NOVO. Oloxo INSERIR EM BARAVg VALORES (NOVO. foide, y) END SE VOLTAR NOVO ELSIF TGOP 3939DELETE3939 ENTÃO x: 0 PARA BARREIR IN SELECT FROM bar LOOP SE VELHO. Foid barrecord. fooid AND OLD. barid ltgt barrecord. barid THEN y: y barrecord. bar x: x 1 END IF END LOOP SE x ltgt 0 LENTO y: yx END SE UPDATE baravg SET bara Vg. baravg y WHERE FOIDE OLD. fooid RETURN OLD ELSE y: y NEW. bar x: 1 PARA o barracord IN SELECT FROM bar LOOP SE VELHO. barid ltgt barrecord. barid THEN y: y barrecord. bar x: x 1 END IF END LOOP y: yx UPDATE baravg SET baravg. baravg y WHERE fooid OLD. fooid VOLTAR NOVO END SE FIM 39 IDIOMA plpgsql CRIAR TRIGGER getbaravg ANTES DE INSERIR OU DELETE OU UPDATE ON BAR PARA CADA PROCEDIMENTO DE EXECUÇÃO DE FILEIRA getbaravg () Isso deve funcionar, citando quimim. Nasbyquot ltdecibeldecibel. orggt: No sex, 21 de janeiro de 2005 às 12:53:45 AM -0500, Greg Stark escreveu: QuotVanole, Mikequot ltMike. Vanolecingulargt escreve: Preciso calcular uma média móvel e eu gostaria de fazê-lo com ou Uma função Pg criada para esse fim. I39m no Pg 7.4. É isso em Pg sem um monte de auto-junções, ou há uma funcionalidade disponível. Infelizmente, as médias móveis se enquadram em uma classe de funções, chamadas funções analíticas (pelo menos aquelas que o Oracle chama) que são intrinsecamente difíceis de modelar eficientemente em SQL. O Postgres não tem nenhum suporte especial para este conjunto de funções, então você está preso a fazê-lo nas formas ineficientes que o SQL padrão permite. Eu acho que isso ainda é difícil de implementar corretamente usando o suporte de função extremamente extensível do Postgres39. Mesmo se você o implementou em Perl ou Python, não acho que haja alguma maneira de alocar uma área de armazenamento estática temporária para um determinado site de chamadas. Portanto, sua função de média móvel se comportaria estranhamente se você a chamou duas vezes em uma determinada consulta. Mas se você pode trabalhar dentro dessa ressalva, deve ser direto implementá-lo de forma eficiente em Perl ou Python. Alternativamente, você pode escrever uma função plpgsql para calcular a média móvel específica que você precisa, que faz a própria seleção. Se você se sentir aventureiro, você pode olhar para a documentação da Oracle39 em suas funções analíticas e ver se você pode encontrar algo genérico para o PostgreSQL. Mesmo que você apenas faça uma função média móvel, seria um bom começo. - Jim C. Nasby, consultor de banco de dados decibeldecibel. org Dê ao seu computador alguns doces de cérebro distributed. net Team 1828 Windows: quot Onde você quer ir hoje Linux: quê em que você quer ir tomorrowquot FreeBSD: quot Você está vindo ou o que? --------------------------- (fim da transmissão) ------------------ --------- TIP 2: você pode desativar todas as listas de uma vez com o comando de cancelamento do registro (envie quotunregister YourEmailAddressHerequot para majordomopostgresql. org) Alban Hertroys Eu vejo as pessoas fazer isso de tempos em tempos. Apenas por curiosidade, isso é considerado um bom estilo de codificação, ou é considerado quotlazynessquick. Não tenho certeza do que pensar. Se eu escrevesse isso, haveria 3 disparadores no controle da TGOP. Existe uma desvantagem importante para fazer isso. Existe algum documento no estilo de codificação quotpreferredquot em PLPGSQL Sim, I39m um pouco de um purista. - Recentemente, Alban Hertroys MAG Productions T: 31 (0) 53 4346874 F: 31 (0) 53 4346876 E: Eu uso pessoalmente 3 disparadores separados na maioria das ocasiões, dependendo de quão diferente é a ação para cada ação separada, é só Mais fácil para mim e para as pessoas para distinguir as funções de maneira lógica, mas o exemplo na documentação 7.4 para gatilhos é fornecido usando o formulário em que escrevi essa função e é útil quando os procedimentos de disparo para todas as 3 ações fazem aproximadamente o mesmo Coisa, também torna a edição do código um pouco mais fácil nesta situação. Citando Russell Smith ltmr-russpws. augt: No dia 24 jan 2005 20:32, Alban Hertroys escreveu: mstoryuchicago. edu escreveu: CRIAR OU SUBSTITUIR FUNÇÃO getbaravg () DEVOLUÇÕES TRIGGER COMO 39 DECLARAR o registro de barramento RECORD x INTEGER y DOUBLE PRECISION: 0 INICIAR SE TGOP 3939INSERT3939 ENTÃO y: y NEW. bar. VOLTAR NOVO ELSIF TGOP 3939DELETE3939 ENTÃO x: 0. VOLTAR VELHO ELE: y NEW. bar. VOLTAR NOVO END SE FIM 39 IDIOMA plpgsql Eu vejo as pessoas fazer isso de vez em quando. Apenas por curiosidade, isso é considerado um bom estilo de codificação, ou é considerado quotlazynessquick. Não tenho certeza do que pensar. Se eu escrevesse isso, haveria 3 disparadores no controle da TGOP. Existe uma desvantagem importante para fazer isso. Existe algum documento no estilo de codificação quotpreferredquot em PLPGSQL Sim, I39m um pouco de um purista. Dado que você precisa definir uma função para cada gatilho, minha visão é por que escreve mais funções. Juntamente com isso. Como um programador C, faria mais alguns testes IF em uma função, em vez de escrever outro. Acho que disparadores como este são um bloco funcional e todos seguem juntos. Então, quando você atualiza a função, tudo está em um só lugar. Outros podem ter muitas razões para o fato de fazê-lo da maneira que eles fazem. Mas eles são meus. --------------------------- (fim da transmissão) ------------------ --------- TIP 7: não se esqueça de aumentar suas configurações de mapa de espaço livre No qua 26 de janeiro de 2005 às 23:12:25 0100, o PFC escreveu: Como você faz uma auto-união em uma subseleção Como SELECT a. xb. x FROM (subseleccionar) a, (subselect) b ONDE a. id b. id10 mas sem executar a subselecção duas vezes. Você deve escrever a subselecção duas vezes se quiser combinar linhas diferentes. Em alguns casos, vale a pena fazer uma cópia em uma tabela temporária. Para subselects simples, onde existe um índice de identificação, deixá-lo como deveria funcionar bem.