Db2 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. A tendência de demanda de anúncios de emprego citando o DB2 como uma proporção de todos os trabalhos de TI com uma correspondência na categoria de Business Intelligence de ampères de banco de dados. Tendência de salários do DB2 Este gráfico fornece a média móvel de 3 meses para os salários cotados em trabalhos permanentes de TI citando o DB2 no Reino Unido. Histograma de salários do DB2 Este gráfico fornece um histograma de salário para os trabalhos de TI citando o DB2 durante os 3 meses até 16 de dezembro de 2016 no Reino Unido. DB2 Top 30 Locais de Trabalho 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 durante os 3 meses até 16 de dezembro de 2016. A coluna de Mudança de Rank fornece uma indicação da mudança na demanda Dentro de cada local com base no mesmo período de 3 meses do ano passado. Rank Mudar no Mesmo Período Último Ano Correspondência Permanent IT Job Ads Salário Médio Últimos 3 MesesExplorando o mundo rico de SQL OLAP funções - Médias móveis por George Baklarz Explorando o rico mundo de SQL funções OLAP - Moving Averages (nota editorial do comitê de conteúdo: a maioria Do que está no artigo é multi-plataforma, exceto os exemplos de LAG / LEAD e FIRSTVALUE / LASTVALUE não estão disponíveis no DB2 para z / OS) Pity o desenvolvedor pobre ou usuário final que nunca teve a chance de usar SQL real. Você sabe, o tipo de SQL que faz mais do que selecionar algumas linhas com possivelmente alguns predicados lançados Bem, alguns podem dizer que pode ser uma coisa boa não ter que saber os detalhes por trás do SQL No entanto, conhecendo alguns dos agrupamentos sofisticados , Ordenação e opções de classificação do SQL pode tornar sua vida mais fácil, e talvez até mesmo resultado em resultados mais rápidos. A sintaxe SQL de expansão A linguagem SQL é rica em funcionalidade e muitas vezes pode ser complexa para navegar. Como chegamos a este ponto na linguagem a partir de nossos simples começos INSERT, UPDATE, DELETE e SELECT Os sistemas de banco de dados relacional precoce carecem de recursos analíticos - por exemplo, a capacidade de calcular desvio padrão ou gerar médias móveis. Esse tipo de cálculo foi normalmente 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 pobres). Executar relatórios localmente em uma estação de trabalho resultou em dois grandes gargalos: Transferência de 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 à linguagem SQL, Para gerar CUBEs e informações de resumo no nível do banco de dados ajudaram a melhorar o desempenho de várias maneiras: O servidor maior no qual o banco de dados estava era mais eficiente na computação dos cubos, resumos e outros cálculos que a estação de trabalho foi desafiada a fazer. Local ao processador assim que nenhuma transferência desnecessária de dados era requerida ao computar o resultado somente as linhas computadas finais seriam retornadas à aplicação Naturalmente, alguns DBAs entre nós indicariam também que esta capacidade nova aumentou a quantidade de dores de cabeça que têm Lidar por causa de excesso entusiasta fanáticos SQL que didnt têm uma pista sobre índices e otimização Às vezes esquecemos que o motor de banco de dados DB2 pode fazer um monte de 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 próximos com sua capacidade de escrever complexas SQL OLAP (On-Line Analytical Processing) As funções OLAP fornecem uma variedade de recursos, incluindo a capacidade de retornar o ranking, a numeração de linhas e as funções de coluna para analisar um intervalo 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 instrução select. Embora existam muitas funções OLAP encontradas no DB2, vamos analisar as médias móveis e como você pode usá-las. Antes de entrarmos nos detalhes das funções OLAP, precisamos criar uma tabela e alguns dados de amostra. O exemplo a seguir supõe que você tenha uma conexão com o banco de dados SAMPLE que normalmente é gerado para você em uma instalação do DB2 para Linux, Unix e Windows. Observação: os exemplos de SQL 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 tê-lo falhar devido a uma vírgula faltando ou nome de coluna com erros ortográficos. 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) eo preço de fechamento para o estoque no final do dia (CLOSEPRICE). Drop table stocks criar estoque de tabela (O SQL a seguir usa recursão para gerar dados para a tabela STOCKS com apenas um estoque (IDUG).O preço da ação é 100 no início do ano e, em seguida, aumentado por um valor de 1 por cada 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 inserir 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 data (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 DAY, S1 CASO QUANDO RAND () gt .50 THEN INT (-10RAND ()) ELSE INT (10 RAND ()) Os dados na tabela de estoque são mostrados abaixo SELECIONAR DE STOCKS SÍMBOLO 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 Vamos começar por olhar para um cálculo da média móvel. As médias móveis nos permitem olhar para uma curva suavizada de pontos de dados. Haverá sempre picos em valores de dados, especialmente com dados associados ao mercado de ações. As médias móveis 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 e ROUND CURRENT. OVER A função OVER é usada em conjunto com uma função como AVG. A palavra-chave OVER instrui o DB2 a calcular a média (ou outra função) sobre um intervalo de valores. Podemos especificar uma partição (ou seja, um símbolo de estoque), que nos informa 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 aos quais a função é aplicada. No exemplo vamos usar a coluna STOCK como a partição. Basicamente, isso significa que cada valor de estoque na tabela terá seu próprio valor de média móvel. Se você não especificar uma partição, todos os valores na tabela serão usados, independentemente do símbolo de 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 a coluna de partição ORDER BY, orderingcolumn. ROWS ENTRE x ROUND ANTERIOR E CURRENT Esta é a cláusula que diz ao DB2 quais valores usar quando calcular o valor médio. O SQL especifica o número de linhas antes (PRECEDING) da linha atual. Portanto, se queremos uma média móvel de 30 dias, especificamos BETWEEN 29 ENTRADA PRÉVIA E CORRENTE. Agora que temos os conceitos básicos de um cálculo da média móvel, aqui está o SQL que nos dará a média móvel de 30 dias de nossa tabela de estoque: 1 COM V1 (SÍMBOLO, TRADINGDATE, MOVINGAVG) COMO 3 SELECT SYMBOL, TRADINGDATE, 4 AVG (CLOSEPRICE) 5 OVER (6 PARTITION BY SYMBOL 7 ORDEM POR TRADINGDATE 8 ROWS ENTRE 29 PRECEDING E CORRENTE RUTA 11 ONDE SIMBOLO IDUG 12) 13 SELECT SÍMBOLO, 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 de ações, a data de negociação e as médias móveis. Esses cálculos serão feitos dentro do corpo da instrução WITH. ORDER BY SYMBOL, TRADINGDATE A instrução SELECT após a cláusula WITH recupera todos os dados da tabela temporária. Você sempre pode adicionar uma cláusula BETWEEN para limitar os resultados a um intervalo específico de dados. WHERE TRADINGDATE ENTRE 2015-01-01 E 2015-01-10 O trabalho real ocorre dentro da cláusula WITH. 3 SELEÇÃO DO SÍMBOLO, TRADINGDATE, 4 AVG (CLOSEPRICE) 5 OVER (6 PARTIÇÃO POR SÍMBOLO 7 ORDEM POR TRADINGDATE 8 ROWS ENTRE 29 ROTA PRECEDENTE E CORRENTE 11 ONDE SÍMBOLO 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 de estoque (pense nisso como onde quebras ocorreria durante o processamento de uma média) Ordenação das linhas deve ser feito por data de negociação para que tenhamos as linhas na adequada Ordem para a média móvel A média deve levar em conta as 29 linhas anteriores da linha atual sendo processada (para um total de 30 dias) A cláusula FROM seleciona os valores de estoque específicos que queremos rastrear, embora possamos remover isso da Exemplo e nós obteríamos médias móveis para todos os estoques na tabela. Correndo a consulta contra a nossa tabela de teste gera o seguinte resultado. CONFIGURAÇÃO DE SÍMBOLOS 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ê poderia adicionar uma torção adicional à seleção Para mostrar o delta entre a média móvel e o valor original. COM V1 (SÍMBOLO, TRADINGDATE, MOVINGAVG) COMO SÍMBOLO SELECIONADO, TRADINGDATE, AVG (CLOSEPRICE) SOBRE (PARTIÇÃO POR SÍMBOLO ORDEM POR TRADINGDATE ROWS ENTRE 29 ROTA PRECEDING E CORRENTE V2 (SÍMBOLO, TRADINGDATE, ORIGINAL, MOVINGAVG, DELTA) SYMBOL, S. TRADINGDATE, S. CLOSEPRICE, FROM V1, STOCKS S WHERE V1.SYMBOLS. SYMBOL E V1.TRADINGDATES. TRADINGDATE SELECT SYMBOL, TRADINGDATE, ORIGINAL, MOVINGAVG, DELTA FROM V2 ORDEM POR SÍMBOLO, TRADINGDATE Há muitas maneiras de gerar Estes resultados, mas uma instrução WITH é uma maneira conveniente de construir o SQL que queremos executar. Os resultados ilustram como a nossa média está sendo calculada sobre o intervalo de valores. SIMBOL 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 2015-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 uma média móvel de 30 dias quando nossos dados não contêm realmente 30 dias de dados antes da Atual. No resultado anterior, você notará que o primeiro de janeiro tem uma média de 100, uma vez que havia apenas um valor envolvido. O 2 º tem uma média de 100,50 porque o nosso total é 201 para os dois dias e, em seguida, dividido por dois. Assim, a função OVER é inteligente o suficiente para criar uma média móvel com base nos valores que estão disponíveis para a função. Os resultados a seguir utilizam uma média móvel de 5 dias, o que mostra que a função média é limitada a 5 dias. O delta será sempre 2 após o 5º dia. SÍMBOLO 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 LAG / LEAD Haverá situações em que os dados podem conter NULL Valores e cuidados devem ser tomados sobre 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 deve eliminá-los do cálculo médio? A seguinte instrução UPDATE será definida no dia 3 de janeiro. 4º. E 10º fim de semana valores do estoque para nulo. 1 de janeiro foi mantida como 100 desde que foi o ponto de partida para a geração de todos os valores. SET CLOSEPRICE NULL WHERE TRADINGDATE IN (2015-01-03,2015-01-04,2015-01-10) Re-emissão da consulta de média móvel de 5 dias dá alguns resultados interessantes. SÍMBOLO 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 Utiliza apenas valores não nulos para determinar a média móvel. Para valores de estoque que são nulos em um dia específico, adicionando todos os valores não nulos e dividindo pela contagem de valores não nulos determinará a média. Por exemplo, a média móvel no décimo é calculada como: Haverá situações onde você quer que a curva de estoque para ser contínua (mesmo que não houve negociação em um feriado ou fim de semana). Em casos como este, você deseja ter o preço de fechamento nulo 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, defaultvalue, 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. Offset A função LAG retorna o valor para a linha que é offset 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 evento em que uma linha não existe no deslocamento (ou seja, após a última linha ou antes da primeira linha). Por exemplo, para a primeira linha em nossa tabela, um deslocamento de 1 não existiria, então este valor padrão seria usado em vez disso. O valor padrão é null. Nulos 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 RESPECT NULLS for usado, então nulos serão considerados. A função LEAD é idêntica à função LAG, mas os valores são tomados 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 da ação existir, seu valor é retornado pela função. Caso contrário, usamos a função LAG para encontrar o primeiro valor não nulo na tabela anterior à linha atual. COM V1 (SÍMBOLO, TRADINGDATE, EODVALUE) COMO SELECIONAR SÍMBOLO, TRADINGDATE, LAG (CLOSEPRICE, 1, CAST (NULL COMO INT), IGNORE NULLS) OVER (PARTITION BY SYMBOL ORDER BY TRADINGDATE