Quando eu tive um problema semelhante, acabei usando tabelas temporárias por uma variedade de razões, mas isso tornou muito mais fácil o que eu fiz parece muito parecido com o que você está fazendo, tanto quanto o esquema vai. Faça do esquema algo como identidade ID, startdate, enddate, value. Quando você seleciona, faça uma subseqüência média dos 20 anteriores com base na identificação de identidade. Apenas faça isso se você já estiver usando tabelas temporárias por outros motivos embora (eu toquei as mesmas linhas uma e outra vez para métricas diferentes, por isso foi útil ter o pequeno conjunto de dados). Na minha experiência, Mysql a partir de 5.5.x tende a não usar índices de seleção dependente, seja uma subconsulta ou uma associação. Isso pode ter um impacto muito significativo no desempenho onde o critério de seleção dependente muda em cada linha. A média móvel é um exemplo de uma consulta que se enquadra nesta categoria. O tempo de execução pode aumentar com o quadrado das linhas. Para evitar isso, escolha um mecanismo de banco de dados que possa efetuar pesquisas indexadas em opções dependentes. Eu acho que o Postgres trabalha efetivamente para esse problema. Respondeu 2 de julho 14 às 8:01 Sua resposta 2017 Stack Exchange, IncMetrics Maven: Cálculo de uma média móvel no PostgreSQL Em nossa série Metrics Maven, Composes data scientist 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 especificamos ROWS ENTRE 29 ANTERIORES E ESTAÇÃO CORRENTE 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 que copie 2017 ComposePreviamente, discutimos como escrever médias móveis em Postgres. Pela demanda popular, você mostrou como fazer o mesmo no MySQL e no SQL Server. Bem, cubra como anotar gráficos ruidosos como este: Com uma linha média anterior de 7 dias como esta: A grande ideia Nosso primeiro gráfico acima é bastante ruidoso e difícil de obter informações úteis. Podemos suavizá-lo, traçando uma média de 7 dias em cima dos dados subjacentes. Isso pode ser feito com funções de janela, auto-junções ou subconsultas correlacionadas - bem, cubra os dois primeiros. Bem, comece com uma média anterior, o que significa que o ponto médio no 7º do mês é a média dos primeiros sete dias. Visualmente, isso muda os picos no gráfico à direita, como uma grande espiga é calculada a média nos sete dias seguintes. Primeiro, crie uma tabela de contagem intermediária Queremos calcular uma média sobre as inscrições totais para cada dia. Supondo que tenhamos uma tabela típica de usuários com uma linha por usuário novo e um timestamp createdat, podemos criar nossa tabela agregada de inscrições como assim: No Postgres e no SQL Server você pode usar isso como um CTE. No MySQL você pode salvá-lo como uma tabela temporária. A média de roteamento do Postgres Afortunadamente, o Postgres possui funções de janela que são a maneira mais simples de calcular uma média em execução. Essa consulta pressupõe que as datas não possuem lacunas. A consulta está em média nas últimas sete linhas, não nas sete últimas datas. Se seus dados tiverem lacunas, preencha-os com generateseries ou junte-se contra uma tabela com linhas de data densas. MySQL Rolling Average MySQL não possui funções de janela, mas podemos fazer uma computação similar usando auto-junções. Para cada linha da nossa tabela de contagem, nos juntamos a cada linha que foi nos últimos sete dias e leva a média. Esta consulta lida automaticamente com intervalos de data, pois estamos olhando linhas em um intervalo de datas em vez das N linhas anteriores. O SQL Server Rolling Average SQL Server possui funções de janela, portanto, calcular a média móvel pode ser feito no estilo Postgres ou no estilo MySQL. Por simplicidade, estavam usando a versão MySQL com uma auto-união. Isso é conceitualmente o mesmo que no MySQL. As únicas traduções são a função dateadd e explicitamente denominada grupo por colunas. Outras médias Nós focamos a média de 7 dias na seguinte publicação. Se quisermos ver a média líder de 7 dias, é tão simples como classificar as datas na outra direção. Se quisermos olhar para uma média centrada, use: Postgres: linhas entre 3 anteriores e 3 seguintes MySql: entre signups. date - 3 e signups. date 3 no MySQL SQL Server: entre dateadd (dia, -3, inscrições). Data) e dateadd (dia, 3, signups. date)
Comments
Post a Comment