Média móvel db2

Esta é uma questão Evergreen Joe Celko. Ignoro qual plataforma DBMS é usada. Mas, em qualquer caso, Joe conseguiu responder há mais de 10 anos com o SQL padrão. Joe Celko SQL Puzzles and Answers 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 Desmoralize o banco de dados. No entanto, se os dados históricos que estão sendo registrados não mudem e o cálculo da média móvel é caro, você pode considerar usar a abordagem da coluna. Consulta SQL Puzzle: por todos os meios uniforme. Você simplesmente joga no balde de peso apropriado dependendo da distância do ponto de tempo atual. Por exemplo, quottake weight1 para datapoints dentro de 24 horas a partir do ponto de dados atual0.5 para pontos de dados dentro de 48hrsquot. Esse caso é importante quantos pontos de dados consecutivos (como 6:12 am e 11:48 pm) estão distantes um do outro Um caso de uso que eu posso pensar seria uma tentativa de alisar o histograma sempre que os pontos de dados não são suficientemente densos. 22:22 Não tenho certeza se o resultado esperado (saída) mostra uma média simples de movimentação simples (rolando) durante 3 dias. Como, por exemplo, o primeiro triplo dos números, por definição, dá: mas você espera 4.360 e é confuso. No entanto, sugiro a seguinte solução, que usa AVG de função de janela. Essa abordagem é muito mais eficiente (clara e menos intensiva em recursos) do que a SELF-JOIN introduzida em outras respostas (e estou surpreso que ninguém tenha dado uma solução melhor). Você vê que o AVG está envolvido com o caso quando rownum gt p. days, em seguida, para forçar NULL s nas primeiras linhas, onde 3 Day Moving Average não tem sentido. Respondeu 23 de fevereiro às 13:12. Podemos aplicar o método de junção externa suja esquerda de Joe Celkos (como citado acima por Diego Scaravaggi) para responder a pergunta conforme foi solicitado. Gera a saída solicitada: respondeu Jan 9 16 às 0:33 Sua resposta 2017 Stack Exchange, Inc A tendência da demanda de anúncios de trabalho citando o DB2 como uma proporção de todos os trabalhos de TI com uma correspondência na categoria Banco de dados de Business Intelligence. Tendência Salarial do DB2 Este gráfico fornece a média móvel de 3 meses para os salários citados em trabalhos de TI permanentes citando o DB2 no Reino Unido. Histograma de Salário do DB2 Este gráfico fornece um histograma de salário para trabalhos de TI citando o DB2 nos 3 meses até 13 de janeiro de 2017 no Reino Unido. Localizações de trabalho do Top 30 do DB2 A tabela abaixo analisa a demanda e fornece um guia para os salários médios citados em trabalhos de TI citando o DB2 no Reino Unido nos 3 meses até 13 de janeiro de 2017. A coluna de Mudança de Rank fornece uma indicação da mudança de demanda Dentro de cada local com base no mesmo período de 3 meses do ano passado. Mudança de classificação no mesmo período Último ano Correspondência Permanent IT Job Ads Salário médio Últimos 3 meses Explicando o mundo rico de funções SQL OLAP - Médias móveis de George Baklarz Explorando o mundo rico de funções SQL OLAP - Médias móveis (nota editorial do comitê de conteúdo: a maioria O que está no artigo é de plataforma cruzada, exceto que os exemplos de LAGLEAD e FIRSTVALUELASTVALUE não estão disponíveis no DB2 para zOS) Pense no desenvolvedor ou usuário final que nunca teve a chance de usar o SQL real. Você sabe, o tipo de SQL que faz mais do que selecionar algumas linhas com possivelmente alguns predicados lançados no Bem, alguns podem dizer que pode ser uma coisa boa não ter que conhecer os detalhes por trás do SQL No entanto, conhecendo alguns dos grupos sofisticados , Ordenar e classificar as opções do SQL pode tornar a sua vida mais fácil e talvez resultar em resultados mais rápidos. A sintaxe SQL expansível O idioma SQL é rico em funcionalidades e muitas vezes pode ser complexo para navegar. Como chegamos a este ponto no idioma em nossos começos simples de INSERT, UPDATE, DELETE e SELECT Os sistemas de banco de dados relacionais precoce não possuíam recursos analíticos - por exemplo, a capacidade de calcular desvio padrão ou gerar médias móveis. Esse tipo de cálculo geralmente era executado em uma planilha ou em um aplicativo. Há um grande número de produtos no mercado que geram seus próprios cubos e relatórios extraindo dados de um banco de dados (e muitas vezes usando algumas técnicas de extração muito fracas). A execução de relatórios localmente em uma estação de trabalho resultou em dois grandes estrangulamentos: Transferindo grandes quantidades de dados para uma estação de trabalho em uma rede limitada Cálculos intensivos em computação sendo executados em processadores lentos com pouca memória e disco Além de adicionar mais riqueza ao idioma SQL, a capacidade Para gerar CUBEs e informações de resumo no nível do banco de dados ajudou a melhorar o desempenho de várias maneiras: o servidor maior em que o banco de dados estava ligado foi mais eficiente na computação dos cubos, resumos e outros cálculos que a estação de trabalho foi desafiada a fazer. Os dados foram Local para o processador, portanto, nenhuma transferência desnecessária de dados foi necessária ao calcular o resultado. Apenas as linhas calculadas finais seriam devolvidas à aplicação. Claro, alguns DBAs entre nós também apontaram que essa nova capacidade aumentou a quantidade de dores de cabeça que eles têm para Lidar com os fanáticos SQL exagerados que não tinham idéia sobre índices e otimização às vezes Esqueça que o mecanismo de banco de dados DB2 pode fazer muito trabalho em nosso nome, em vez de confiar em nossa planilha favorita para calcular alguns desses valores. Você pode achar que o uso dessas funções pode acelerar seus relatórios. Ou você pode impressionar seus amigos íntimos com sua capacidade de escrever funções SQL OLAP (SQL On-Line Analytical Processing) SQL complexas fornecem uma variedade de recursos, incluindo a capacidade de retornar a classificação, numeração de linhas e modificar funções de coluna para analisar uma gama de valores Dentro de uma coluna em vez de todo o conjunto de resultados. Normalmente, uma função OLAP pode ser incluída em expressões em uma lista de seleção ou na cláusula ORDER BY de uma declaração de seleção. Embora existam muitas funções OLAP encontradas no DB2, vamos analisar as médias móveis e como você pode usá-las. Antes de entrar nos detalhes das funções OLAP, precisamos criar uma tabela e alguns dados de amostra. O exemplo a seguir pressupõe que você possui uma conexão com o banco de dados SAMPLE geralmente gerado para você em uma instalação do DB2 para Linux, Unix e Windows. Nota: Os exemplos de SQL que são encontrados neste artigo estão disponíveis como um arquivo de texto para download para você usar. Nada é pior do que ter que digitar um conjunto de SQL e fazê-lo falhar devido a uma vírgula ou um nome de coluna com erros ortográficos em falta. Um link para o arquivo é fornecido no final do artigo. Primeiro, precisamos criar a tabela STOCKS, que contém o símbolo de ações (SYMBOL), a data de negociação (TRADINGDATE) e o preço de fechamento do estoque no final do dia (CLOSEPRICE). Os estoques de tabela de baixa criam ações de tabela (o SQL a seguir usa a recursão para gerar dados para a tabela STOCKS com apenas um estoque (IDUG). O preço das ações é de 100 no início do ano e, em seguida, aumentado em um valor de 1 por dia até 10 de janeiro. Os números foram mantidos simples para que você possa ver como os cálculos são feitos nos exemplos. Insira em ações com temp1 (nm, tx, s1) como valores (IDUG, data (2015-01-01), 100 ) Selecione IDUG, tx 1 dia, s11 onde tx lt date (2015-01-10) selecione de temp1 Se você deseja gerar números aleatórios para seus estoques, você pode modificar a instrução SELECT para incluir a seguinte instrução CASE. SELECT IDUG, TX 1 DIA, S1 CASO QUANDO RAND () gt .50 ENTÃO INT (-10RAND ()) ELSE INT (10 RAND ()) Os dados na tabela de estoque são mostrados abaixo. SELECIONE DE STOCKS SYMBOL TRADINGDATE CLOSEPRICE IDUG 2015-01- 01-00.00.00 100 IDUG 2015-01-02-00.00.00 101 IDUG 2015-01-03-00.00.00 102 IDUG 2015-01-04-00.00.00 103 IDUG 2015-01-05-00.00.00 104 IDUG 2015-01-06-00.00.00 105 IDUG 2015-01-07-00.00.00 106 IDUG 2015-01-08-00.00.00 107 IDUG 2015-01-09-00.00.00 108 IDUG 2015-01-10-00.00.00 109 Média móvel de 30 dias Começaremos por analisar um cálculo da média móvel. As médias móveis permitem-nos observar uma curva suavizada de pontos de dados. Sempre haverá picos em valores de dados, especialmente com dados associados ao mercado de ações. As médias em movimento podem nos dar uma visão melhor da tendência de longo prazo dos dados ao invés de olhar para pontos de dados individuais. O formato geral de uma média móvel envolve o uso de três operadores dentro da instrução SQL: OVER, PARTITION e ROWS ENTRE Hellip PRECEEDING AND CURRENT ROW. OVER A função OVER é usada em conjunto com uma função como o AVG. A palavra-chave OVER instrui DB2 para calcular a média (ou outra função) em uma variedade de valores. Podemos especificar uma partição (ou seja, um símbolo de estoque), que nos diz qual coluna será usada para parar a média móvel. PARTIÇÃO A cláusula de partição é usada durante o processamento para limitar o cálculo. Uma partição pode ser pensada em uma janela nos dados que a função é aplicada. No exemplo, vamos usar a coluna STOCK como a partição. Basicamente isso significa que todo valor de estoque na tabela terá seu próprio valor médio móvel. Se você não especificar uma partição, todos os valores na tabela serão usados, independentemente do símbolo do estoque. ORDER BY ORDER BY deve ser especificado dentro da cláusula OVER para que os dados sejam ordenados para a função ROWS. Você pode obter os mesmos resultados de particionamento especificando ORDER BY partitioncolumn, orderingcolumn. ROWS ENTRE x FILE PRECEDING E CURRENT Esta é a cláusula que informa ao DB2 quais valores usar para calcular o valor médio. O SQL especifica o número de linhas antes (PRECEDING) da linha atual. Então, se queremos uma média móvel de 30 dias, especificamos entre 29 precedentes e atuais. Agora que temos os conceitos básicos de um cálculo de média móvel, aqui está o SQL que nos dará a média móvel de 30 dias da nossa tabela de estoque: 1 COM V1 (SYMBOL, TRADINGDATE, MOVINGAVG) AS 3 SELECT SYMBOL, TRADINGDATE, 4 AVG (CLOSEPRICE) 5 MAIS (6 PARTIÇÕES POR SÍMBOLO 7 ORDEM POR TRADINGDATE 8 FILAS ENTRADAS 29 PRIMEIRAS E ESTAÇÃO ATUAL 11 ONDE SYMBOL IDUG 12) 13 SÍMBOLO DE SELECÇÃO, TRADINGDATE, DEC (MOVINGAVG, 5,2) DE V1 14 ORDEM POR SÍMBOLO, TRADINGDATE A primeira parte do SQL cria uma tabela temporária chamada V1 que contém o símbolo do estoque, a data de negociação e as médias móveis. Esses cálculos serão feitos dentro do corpo da instrução WITH. 1 COM V1 (SÍMBOLO, TRADINGDATE, MOVINGAVG) COMO SÍMBOLO DE SÍMBOLO 13, TRADINGDATE, DEC (MOVINGAVG, 5,2) DE V1 14 ORDEM POR SÍMBOLO, TRADINGDATE A instrução SELECT após a cláusula WITH recuperar todos os dados da tabela temporária. Você sempre pode adicionar uma cláusula BETWEEN para limitar os resultados a um intervalo de dados específico. ONDE TRADINGDATE ENTRE 2015-01-01 E 2015-01-10 O trabalho real ocorre dentro da cláusula WITH. 3 SELECCIONAR SÍMBOLO, TRADINGDATE, 4 AVG (CLOSEPRICE) 5 OVER (6 PARTITION BY SYMBOL 7 ORDEM POR TRADINGDATE 8 ROWS ENTRE 29 NÍVEL ANTERIOR E ATUAL 11 WHERE SYMBOL IDUG A função média (AVG) será aplicada à coluna CLOSEPRICE. A função OVER especifica o seguinte: A partição é baseada no símbolo do estoque (pense nisso como onde as quebras ocorreriam durante o processamento de uma média) O pedido das linhas deve ser feito por data de negociação para que possamos as linhas no próprio Ordem para a média móvel A média deve levar em consideração as 29 linhas precedentes da linha atual em processamento (por um total de 30 dias) A cláusula FROM seleciona os valores de estoque específicos que queremos rastrear, embora possamos remover isso do Exemplo e nós obteríamos médias móveis para todos os estoques na tabela. Executando a consulta em nossa tabela de teste gera o seguinte resultado. SYMBOL TRADINGDATE 3 IDUG 2015-01-01-00.00.00 100.00 IDUG 2015-01-02-00.00 .00 100.50 IDUG 2015-01-03-00.00.00 101.00 IDUG 2015-01-04-00.00.00 101.50 IDUG 2015-01-05-00.00.00 102.00 IDUG 2015-01-06-00.00.00 102.50 IDUG 2015-01-07 -00.00.00 103.00 IDUG 2015-01-08-00.00.00 103.50 IDUG 2015-01-09-00.00.00 104.00 IDUG 2015-01-10-00.00.00 104.50 Você pode adicionar um toque adicional à instrução selecionada para mostrar O delta entre a média móvel e o valor original. COM V1 (SÍMBOLO, TRADINGDATE, MOVINGAVG) COMO SÍMBOLO DE SELECÇÃO, TRADINGDATE, AVG (CLOSEPRICE) OVER (PARTIÇÃO POR ORDEM DE SÍMBOLO POR TRADINGDATE ROWS ENTRE 29 PRECEDING E ATUAL V2 (SYMBOL, TRADINGDATE, ORIGINAL, MOVINGAVG, DELTA) COMO SELECIONAR S. SYMBOL, S. TRADINGDATE, S. CLOSEPRICE, DE V1, STOCKS S ONDE V1.SYMBOLS. SYMBOL e V1.TRADINGDATES. TRADINGDATE SELECIONAR SÍMBOLO, TRADINGDATE, ORIGINAL, MOVINGAVG, DELTA DA V2 ORDEM POR SÍMBOLO, TRADINGDATE Existem muitas maneiras de gerar Esses resultados, mas uma instrução WITH é uma maneira conveniente de construir o SQL que queremos executar. Os resultados ilustram como nossa média está sendo computada na gama de valores. SYMBOL TRADINGDATE ORIGINAL MOVINGAVG DELTA IDUG 2015-01-01-00.00 .00 100 100,00 0,00 IDUG 2015-01-02-00.00.00 101 100.50 0.50 IDUG 2015-01-03-00.00.00 102 101.00 1.00 IDUG 2015-01-04-00.00.00 103 101.50 1.50 IDUG 2015-01-05 -00.00.00 104 102.00 2.00 IDUG 2015-01-06-00.00.00 105 102.50 2.50 IDUG 2015-01-07-00.00.00 106 103.00 3.00 IDUG 20 15-01-08-00.00.00 107 103.50 3.50 IDUG 2015-01-09-00.00.00 108 104.00 4.00 IDUG 2015-01-10-00.00.00 109 104.50 4.50 Alguns de vocês podem ter se perguntado como podemos ter um movimento Média de 30 dias, quando nossos dados realmente não contêm 30 dias de dados anteriores à linha atual. No resultado anterior, você notará que o primeiro de janeiro tem uma média de 100, uma vez que houve apenas um valor envolvido. O 2º tem uma média de 100,50 porque nosso total é de 201 nos dois dias e depois dividido por dois. Portanto, a função OVER é inteligente o suficiente para criar uma média móvel com base nos valores disponíveis para a função. Os resultados a seguir usam uma média móvel de 5 dias, o que mostra que a função média é limitada a 5 dias. O delta sempre será 2 após o 5º dia. SYMBOL TRADINGDATE ORIGINAL MOVINGAVG DELTA IDUG 2015-01-01-00.00.00 100 100.00 0.00 IDUG 2015-01-02-00.00.00 101 100.50 0.50 IDUG 2015-01-03-00.00.00 102 101.00 1.00 IDUG 2015-01-04 -00.00.00 103 101.50 1.50 IDUG 2015-01-05-00.00.00 104 102.00 2.00 IDUG 2015-01-06-00.00.00 105 103.00 2.00 IDUG 2015-01-07-00.00.00 106 104.00 2.00 IDUG 2015-01 -08-00.00.00 107 105.00 2.00 IDUG 2015-01-09-00.00.00 108 106.00 2.00 IDUG 2015-01-10-00.00.00 109 107.00 2.00 A função LAGLEAD Haverá situações em que os dados podem conter valores NULL e Deve-se ter cuidado com a forma como estes valores são utilizados durante o cálculo da média móvel. Os valores NULL devem ser tratados como zeros, ou a nossa média móvel os elimina do nosso cálculo médio. A seguinte instrução UPDATE será definida em 3 de janeiro. 4º. E 10º valores de fim de semana do estoque para nulo. O primeiro de janeiro foi mantido como 100, pois esse era o ponto de partida para gerar todos os valores. SET CLOSEPRICE NULL ONDE TRADINGDATE IN (2015-01-03,2015-01-04,2015-01-10) Re-emitir a consulta de média móvel de 5 dias dá alguns resultados interessantes. SYMBOL TRADINGDATE ORIGINAL MOVINGAVG DELTA IDUG 2015-01-01-00.00.00 100 100.00 0.00 IDUG 2015-01-02-00.00.00 101 100.50 0.50 IDUG 2015-01-05-00.00.00 104 101.67 2.33 IDUG 2015-01-06 -00.00.00 105 103.33 1.67 IDUG 2015-01-07-00.00.00 106 105.00 1.00 IDUG 2015-01-08-00.00.00 107 105.50 1.50 IDUG 2015-01-09-00.00.00 108 106.00 2.00 A função de média móvel Usa apenas valores não nulos para determinar a média móvel. Para os valores de estoque que são nulos em um determinado dia, adicionar todos os valores não nulos e dividir pela contagem de valores não nulos determinará a média. Por exemplo, a média móvel no 10º é calculada como: Haverá situações em que você deseja que a curva de estoque seja contínua (mesmo que não haja negociação em feriado ou fim de semana). Em casos como este, você deseja que o preço de fechamento nulo seja alterado para o último preço de fechamento válido. Em vez de modificar nossos dados, podemos usar a função LAG para obter os valores anteriores usados ​​em um cálculo. A função LAG tem a seguinte sintaxe: LAG (ou LEAD) (coluna, offset, valor padrão, RESPECT NULLS IGNORE NULLS) Coluna Este campo é o nome da coluna da qual queremos o valor ou uma expressão que inclui o nome do Coluna nele. Deslocamento A função LAG retorna o valor da linha que está deslocada n número de linhas antes da linha atual. Um valor de zero seria a linha atual, 1 seria a linha anterior, e assim por diante. O valor padrão para o deslocamento é 1. Valor padrão O valor padrão é usado no caso de uma linha não existir no deslocamento (ou seja, após a última linha ou antes da primeira linha). Por exemplo, para a primeira linha em nossa tabela, um desvio de 1 não existiria, então esse valor padrão seria usado. O valor padrão é nulo. Nulls Você pode especificar como NULLS deve ser tratado durante o processamento dos valores. Se IGNORE NULLS for especificado, todas as linhas com valores nulos serão ignoradas durante o cálculo. Se o RESPEITO NULLS for utilizado, serão considerados nulos. A função LEAD é idêntica à função LAG, mas os valores são retirados das seguintes linhas em oposição aos anteriores. O SQL a seguir criará um conjunto de resultados intermediários que preenche os valores nulos com o último preço de estoque final válido. A função COALESCE retorna o primeiro resultado não nulo em uma lista. Se o valor de estoque existe, seu valor é retornado pela função. Caso contrário, usamos a função LAG para encontrar o primeiro valor não nulo na tabela antes da linha atual. COM V1 (SYMBOL, TRADINGDATE, EODVALUE) COMO SÍMBOLO DE SELECÇÃO, TRADINGDATE, LAG (CLOSEPRICE, 1, CAST (NULL COMO INT), IGNORE NULLS) SOBRE (PARTITION BY SYMBOL ORDER BY TRADINGDATE