N dia exponencial mover média

21 SQL para análise e relatórios Para executar essas operações, as funções analíticas adicionam vários novos elementos ao processamento SQL. Esses elementos se baseiam no SQL existente para permitir expressões de cálculo flexíveis e poderosas. Com poucas exceções, as funções analíticas possuem esses novos elementos. O fluxo de processamento está representado na Figura 21-1. Figura 21-1 Ordem de processamento Os conceitos essenciais utilizados nas funções analíticas são: o processamento de consultas usando funções analíticas ocorre em três etapas. Primeiro, todas as junções, ONDE. As cláusulas GROUP BY e HAVING são realizadas. Em segundo lugar, o conjunto de resultados é disponibilizado para as funções analíticas e todos os seus cálculos ocorrem. Em terceiro lugar, se a consulta tiver uma cláusula ORDER BY no final, a ORDER BY é processada para permitir uma ordem de saída precisa. A ordem de processamento é mostrada na Figura 21-1. Partições de conjunto de resultados As funções analíticas permitem aos usuários dividir os conjuntos de resultados da consulta em grupos de linhas chamados de partições. Observe que o termo partições usadas com funções analíticas não está relacionado com o recurso de partição de tabela. Ao longo deste capítulo, o termo "partições" refere-se apenas ao significado relacionado às funções analíticas. As partições são criadas após os grupos definidos com as cláusulas GROUP BY, por isso estão disponíveis para todos os resultados agregados, como somas e médias. As divisões de partição podem ser baseadas em colunas ou expressões desejadas. Um conjunto de resultados de consulta pode ser dividido em apenas uma partição que contém todas as linhas, algumas partições grandes ou muitas partições pequenas que possuem apenas algumas linhas cada. Para cada linha de uma partição, você pode definir uma janela deslizante de dados. Esta janela determina o intervalo de linhas usadas para executar os cálculos para a linha atual. Os tamanhos das janelas podem ser baseados em um número físico de linhas ou um intervalo lógico, como o tempo. A janela tem uma linha inicial e uma linha final. Dependendo da sua definição, a janela pode se mover em uma ou em ambas as extremidades. Por exemplo, uma janela definida para uma função de soma cumulativa teria sua linha inicial fixada na primeira linha de sua partição, e sua linha final deslizaria desde o ponto de partida até a última linha da partição. Em contraste, uma janela definida para uma média móvel teria seus pontos de partida e de extremidade deslizados para que eles mantenham uma faixa física ou lógica constante. Uma janela pode ser definida como grande como todas as linhas em uma partição ou apenas uma janela deslizante de uma linha dentro de uma partição. Quando uma janela está perto de uma borda, a função retorna resultados apenas para as linhas disponíveis, ao invés de avisá-lo de que os resultados não são o que você deseja. Ao usar as funções da janela, a linha atual está incluída durante os cálculos, portanto, você deve especificar (n -1) quando estiver lidando com n itens. Cada cálculo executado com uma função analítica é baseado em uma linha atual dentro de uma partição. A linha atual serve como o ponto de referência que determina o início e o final da janela. Por exemplo, um cálculo de média móvel centrada poderia ser definido com uma janela que contenha a linha atual, as seis linhas precedentes e as seis linhas seguintes. Isso criaria uma janela deslizante de 13 linhas, como mostrado na Figura 21-2. Figura 21-2 Exemplo de janela deslizante Funções de classificação Uma função de classificação calcula a classificação de um registro em comparação com outros registros no conjunto de dados com base nos valores de um conjunto de medidas. Os tipos de função de classificação são: Exemplo de Cálculo de Regressão Linear Neste exemplo, calculamos uma linha de regressão de mínimos quadrados comuns que expressa a quantidade vendida de um produto como uma função linear do preço da lista de produtos. Os cálculos são agrupados pelo canal de vendas. Os valores SLOPE. INTCPT. RSQR são inclinação, interceptação e coeficiente de determinação da linha de regressão, respectivamente. O valor (número inteiro) COUNT é o número de produtos em cada canal para quem a quantidade vendida e os dados do preço da lista estão disponíveis. Álgebra linear A álgebra linear é um ramo da matemática com uma ampla gama de aplicações práticas. Muitas áreas têm tarefas que podem ser expressas usando álgebra linear, e aqui estão alguns exemplos de vários campos: estatísticas (regressão linear múltipla e análise de componentes principais), mineração de dados (clustering e classificação), bioinformática (análise de dados de microarrays), pesquisa operacional (Cadeia de suprimentos e outros problemas de otimização), econometria (análise de dados da demanda do consumidor) e finanças (problemas de alocação de ativos). Várias bibliotecas para álgebra linear estão disponíveis gratuitamente para qualquer pessoa. O pacote Oracles UTLNLA expõe os tipos de dados matriciais PLSQL e os subprogramas PLSQL do wrapper para duas das mais populares e robustas dessas bibliotecas, BLAS e LAPACK. A álgebra linear depende da manipulação da matriz. A execução de manipulação de matriz em PLSQL no passado exigiu inventar uma representação de matriz baseada em tipos de dados nativos do PLSQL e depois escrever rotinas de manipulação de matriz a partir do zero. Isso exigiu esforço de programação substancial e o desempenho da implementação resultante foi limitado. Se os desenvolvedores optarem por enviar dados para pacotes externos para processamento em vez de criar suas próprias rotinas, a transferência de dados para trás e para frente pode demorar muito tempo. Usando o pacote UTLNLA, os dados permanecem dentro da Oracle, remove o esforço de programação e oferece uma implementação rápida. Exemplo 21-19 Álgebra Linear Aqui está um exemplo de como o suporte de algebra linear Oracles poderia ser usado para análise de negócios. Ele invoca um aplicativo de regressão linear múltipla construído usando o pacote UTLNLA. O aplicativo de regressão múltipla é implementado em um objeto chamado OLSRegression. Observe que os arquivos de amostra para o objeto OLS Regression podem ser encontrados em ORACLEHOMEplsqldemo. Considere o cenário de um varejista analisando a eficácia do seu programa de marketing. Cada uma das suas lojas aloca seu orçamento de marketing nos seguintes programas possíveis: anúncios de mídia (mídia), promoções (promo), cupons de desconto (disct) e endereços diretos (dmail). A análise de regressão cria uma relação linear entre a quantidade de vendas que uma loja média possui em um determinado ano (vendas) e os gastos com os quatro componentes do programa de marketing. Suponha que os dados de marketing estejam armazenados na tabela a seguir: então, você pode construir o seguinte modelo linear de marketing comercial usando coeficientes: Este modelo pode ser implementado como a seguinte visão, que se refere ao objeto de regressão OLS: usando essa visão, um marketing Gerente de programa pode realizar uma análise, como é este modelo de marketing de vendas razoável para o ano de 2004, isto é, é a correlação múltipla maior do que algum valor aceitável, digamos, 0.9 O SQL para tal consulta pode ser o seguinte: Você também pode Resolva questões como o que é a receita de vendas da linha de base esperada de uma loja sem programas de marketing em 2003 ou qual componente do programa de marketing foi o mais efetivo em 2004, ou seja, um aumento de dólar no qual o programa produziu o maior aumento esperado em Vendas Consulte Referência de Pacotes e Tipos do Oracle Database PLSQL para obter mais informações sobre o uso do pacote UTLNLA e álgebra linear. Itens frequentes Em vez de contar com a frequência com que ocorre um determinado evento (por exemplo, com que frequência alguém comprou leite na mercearia), você pode achar útil contar com que frequência vários eventos ocorrem juntos (por exemplo, com que frequência alguém comprou o leite E cereais juntos na mercearia). Você pode contar esses vários eventos usando o que é chamado de conjunto de itens freqüentes, que é, como o nome indica, um conjunto de itens. Alguns exemplos de itens podem ser todos os produtos que um determinado cliente comprou em uma única viagem ao supermercado (comumente chamado de cesta de mercado), as páginas web que um usuário acessou em uma única sessão ou os serviços financeiros que um dado O cliente utiliza. A motivação prática para usar um conjunto de itens freqüentes é encontrar esses conjuntos de itens que ocorrem com mais frequência. Se você analisar os dados do ponto de venda de uma mercearia, você pode, por exemplo, descobrir que o leite e as bananas são os itens de compra mais comum. Os itens de itens freqüentes foram assim utilizados em ambientes de inteligência de negócios por muitos anos, sendo o mais comum para análise de cesta de mercado no setor varejista. Os cálculos freqüentes de conjunto de itens são integrados com o banco de dados, operando em cima de tabelas relacionais e acessados ​​através de SQL. Esta integração fornece os seguintes benefícios principais: as aplicações que anteriormente dependiam de operações freqüentes de conjunto de itens agora se beneficiam de um desempenho significativamente melhorado, bem como de uma implementação mais simples. Os aplicativos baseados em SQL que não utilizavam anteriormente conjuntos de itens freqüentes agora podem ser facilmente estendidos para aproveitar essa funcionalidade. A análise freqüente dos itens é realizada com o pacote PLSQL DBMSFREQUENTITEMSETS. Consulte Referência de Pacotes e Tipos do Oracle Database PLSQL para obter mais informações. Além disso, existe um exemplo de uso freqüente de itens em itens de itens freqüentes. Outras funções estatísticas Oracle apresenta um conjunto de funções estatísticas SQL e um pacote de estatísticas, DBMSSTATFUNCS. Esta seção lista algumas das novas funções, juntamente com a sintaxe básica. Estatísticas descritivas Você pode calcular as seguintes estatísticas descritivas: Mediana de um conjunto de dados Você pode calcular as seguintes estatísticas paramétricas: Spearans rho Coeficiente Kendalls tau-b Coeficiente Além das funções, esta versão possui um novo pacote PLSQL, DBMSSTATFUNCS. Ele contém a função estatística descritiva RESUMO juntamente com funções para suportar o ajuste de distribuição. A função RESUMO resume uma coluna numérica de uma tabela com uma variedade de estatísticas descritivas. As cinco funções de montagem de distribuição suportam distribuição normal, uniforme, Weibull, Poisson e exponencial. Função WIDTHBUCKET Para uma determinada expressão, a função WIDTHBUCKET retorna o número da balde que o resultado dessa expressão será atribuído após a avaliação. Você pode gerar histogramas de equiwidth com esta função. Equiwidth histogramas dividem conjuntos de dados em baldes cujo tamanho de intervalo (valor mais alto para menor valor) é igual. O número de linhas detidas por cada balde variará. Uma função relacionada, NTILE. Cria baldes de equiheight. Equiwidth histogramas só podem ser gerados para tipos numéricos, data ou data-hora. Portanto, os três primeiros parâmetros devem ser todas as expressões numéricas ou todas as expressões de data. Não são permitidos outros tipos de expressões. Se o primeiro parâmetro for NULL. O resultado é NULL. Se o segundo ou o terceiro parâmetro for NULL. Uma mensagem de erro é retornada, pois um valor NULL não pode indicar qualquer ponto final (ou qualquer ponto) para um intervalo em uma data ou uma dimensão de valor numérico. O último parâmetro (número de baldes) deve ser uma expressão numérica que avalie para um valor inteiro positivo 0, NULL. Ou um valor negativo resultará em um erro. Os baldes estão numerados de 0 a (n 1). O balde 0 contém a contagem de valores inferiores ao mínimo. O balde (n 1) contém a contagem de valores maiores ou iguais ao valor máximo especificado. WIDTHBUCKET Sintaxe O WIDTHBUCKET leva quatro expressões como parâmetros. O primeiro parâmetro é a expressão que o histograma equiwidth é para. Os segundo e terceiro parâmetros são expressões que indicam os pontos finais do intervalo aceitável para o primeiro parâmetro. O quarto parâmetro indica o número de baldes. Considere os seguintes dados dos clientes da tabela. Que mostra os limites de crédito de 17 clientes. Estes dados são reunidos na consulta mostrada no Exemplo 21-20. Na tabela de clientes. A coluna custcreditlimit contém valores entre 1500 e 15000, e podemos atribuir os valores a quatro baldes de equiwidth, numerados de 1 a 4, usando WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Idealmente, cada balde é um intervalo fechado aberto da linha do número real, por exemplo, o número 2 do balde é atribuído a pontuações entre 5000.0000 e 9999.9999. Às vezes denotado 5000, 10000) para indicar que 5.000 estão incluídos no intervalo e 10.000 são excluídos. Para acomodar valores fora do intervalo 0, 20.000), valores inferiores a 0 são atribuídos a um balde de fluxo inferior designado que é numerado 0 e valores atribuídos a um balde de transbordo designado que é numerado 5 (baldes numéricos 1 em geral). Veja a Figura 21-3 para obter uma ilustração gráfica de como os baldes são atribuídos. Figura 21-3 Atribuições de balde Você pode especificar os limites na ordem inversa, por exemplo, WIDTHBUCKET (custcreditlimit. 20000. 0. 4). Quando os limites são invertidos, os baldes serão intervalos abertos e fechados. Neste exemplo, o número de balde 1 é (15000,20000, o número de balde 2 é (10000,15000 e o número de balde 4, é (0, 5000. O balde de estouro será numerado 0 (20000. infinito) e a balde subfluxa Será numerado 5 (- infinito. 0. É um erro se o parâmetro de contagem de balde for 0 ou negativo. Exemplo 21-20 WIDTHBUCKET A consulta em seguida mostra os números de balde para os limites de crédito na tabela de clientes para ambos os casos em que o Os limites são especificados em ordem regular ou inversa. Usamos um intervalo de 0 a 20,000. Funções de agregação definidas pelo usuário A Oracle oferece uma facilidade para criar suas próprias funções, chamadas funções agregadas definidas pelo usuário. Essas funções estão escritas em linguagens de programação como o PLSQL , Java e C, e pode ser usado como funções analíticas ou agregados em visões materializadas. Consulte o Guia do Desenvolvedor de Cartuchos de Dados do Banco de Dados Oracle para obter mais informações sobre sintaxe e restrições. As vantagens dessas funções são: Funções altamente complexas ca N seja programado usando uma linguagem totalmente processual. Maior escalabilidade do que outras técnicas quando as funções definidas pelo usuário são programadas para processamento paralelo. Os tipos de dados de objetos podem ser processados. Como um exemplo simples de uma função agregada definida pelo usuário, considere a estatística de desvio. Este cálculo mede se um conjunto de dados tiver uma distribuição desequilibrada sobre o seu significado. Ele irá dizer se uma cauda da distribuição é significativamente maior que a outra. Se você criou um agregado definido pelo usuário chamado udskew e aplicou-o aos dados de limite de crédito no exemplo anterior, a instrução SQL e os resultados podem parecer assim: antes de criar funções agregadas definidas pelo usuário, você deve considerar se suas necessidades podem ser atendidas Em SQL normal. Muitos cálculos complexos são possíveis diretamente no SQL, particularmente usando a expressão CASE. Permanecer com SQL regular permitirá um desenvolvimento mais simples e muitas operações de consulta já estão bem paralelizadas no SQL. Mesmo o exemplo anterior, a estatística de inclinação, pode ser criada usando padrão, embora longo, SQL. CASO Expressões A Oracle agora oferece instruções de CASE simples e procuradas. As declarações CASE são semelhantes em função da declaração DECODE, mas oferecem mais flexibilidade e poder lógico. Eles também são mais fáceis de ler do que as instruções DECODE tradicionais e oferecem um melhor desempenho também. Eles são comumente usados ​​ao romper categorias em baldes como a idade (por exemplo, 20-29, 30-39 e assim por diante). A sintaxe para instruções CASE simples é: teste de expressões CASE simples se o valor expr igual a comparisonexpr. A sintaxe para as afirmações de CASO pesquisadas é: Você pode usar qualquer tipo de condição em uma expressão de CASO pesquisada, e não apenas em um teste de igualdade. Você pode especificar apenas 255 argumentos e cada QUATRO. Então, o par conta como dois argumentos. Para evitar exceder esse limite, você pode aninhar as expressões CASE para que o returnexpr em si seja uma expressão CASE. Exemplo 21-21 CASO Suponha que você desejasse encontrar o salário médio de todos os funcionários da empresa. Se um salário de funcionários for inferior a 2000, você deseja que a consulta use 2000 em vez disso. Sem uma declaração CASE, você pode optar por escrever essa consulta da seguinte maneira: Observe que isso é executado contra o esquema de exemplo hr. Nisto, foo é uma função que retorna sua entrada se a entrada for maior do que 2000 e retorna 2000 caso contrário. A consulta tem implicações de desempenho porque precisa invocar uma função para cada linha. Escrever funções personalizadas também pode adicionar à carga de desenvolvimento. Usando expressões CASE no banco de dados sem PLSQL, esta consulta pode ser reescrita como: Usar uma expressão CASE permite evitar o desenvolvimento de funções personalizadas e também pode ser mais rápido. Exemplo 21-22 CASO para agregação de subconjuntos independentes Usando CASE dentro de funções agregadas é uma maneira conveniente de realizar agregados em vários subconjuntos de dados quando um GRUPO GRUPO simples não será suficiente. Por exemplo, o exemplo anterior poderia ter incluído múltiplas colunas AVG em sua lista SELECT, cada uma com sua própria expressão CASE. Podemos ter uma consulta para encontrar o salário médio para todos os funcionários nas gamas salariais de 0-2000 e 2000-5000. Pareceria: Embora essa consulta coloca os agregados de dados de subconjuntos independentes em colunas separadas, adicionando uma expressão CASE à cláusula GROUP BY, podemos exibir os agregados como as linhas de uma única coluna. A próxima seção mostra a flexibilidade desta abordagem com duas abordagens para criar histogramas com CASE. Criando histogramas com baldes definidos pelo usuário Você pode usar a instrução CASE quando desejar obter histogramas com baldes definidos pelo usuário (tanto no número de baldes quanto na largura de cada balde). Os seguintes são dois exemplos de histogramas criados com instruções CASE. No primeiro exemplo, os totais do histograma são mostrados em várias colunas e uma única linha é retornada. No segundo exemplo, o histograma é mostrado com uma coluna de rótulo e uma única coluna para totais e várias linhas são retornadas. Exemplo 21-23 Histograma Exemplo 1 Exemplo 21-24 Histograma Exemplo 2 Densificação de Dados para Relatórios Os dados são normalmente armazenados em forma esparsa. Ou seja, se nenhum valor existe para uma determinada combinação de valores de dimensão, nenhuma linha existe na tabela de fatos. No entanto, você pode querer visualizar os dados de forma densa, com linhas para todos os valores de dimensão de combinação exibidos mesmo quando não existem dados de fato para eles. Por exemplo, se um produto não vendeu durante um determinado período de tempo, você ainda pode querer ver o produto para esse período de tempo com zero valor de vendas ao lado dele. Além disso, os cálculos das séries temporais podem ser realizados com maior facilidade quando os dados são densos ao longo da dimensão do tempo. Isso ocorre porque os dados densos preencherão um número consistente de linhas para cada período, o que, por sua vez, torna simples usar as funções analíticas de janelas com deslocamentos físicos. A densificação de dados é o processo de conversão de dados dispersos em uma forma densa. Para superar o problema da sparsity, você pode usar uma junção externa particionada para preencher as lacunas em uma série temporal ou qualquer outra dimensão. Essa união alarga a sintaxe de união externa convencional aplicando a junção externa a cada partição lógica definida em uma consulta. Oracle logicamente particiona as linhas em sua consulta com base na expressão que você especifica na cláusula PARTITION BY. O resultado de uma junção externa particionada é uma UNION das junções externas de cada uma das partições na tabela logicamente particionada com a tabela do outro lado da junção. Observe que você pode usar esse tipo de associação para preencher as lacunas em qualquer Dimensão, não apenas a dimensão do tempo. A maioria dos exemplos aqui se concentra na dimensão do tempo porque é a dimensão mais utilizada como base para comparações. Sintaxe de associação de partição A sintaxe para junção externa particionada estende a cláusula ANSI SQL JOIN com a frase PARTITION BY seguida de uma lista de expressões. As expressões na lista especificam o grupo ao qual a associação externa é aplicada. As seguintes são as duas formas de sintaxe normalmente usadas para junção externa particionada: Observe que FULL OUTER JOIN não é suportado com uma associação externa particionada. Amostra de Dados Esparsos Uma situação típica com uma dimensão esparsa é mostrada no exemplo a seguir, que calcula as vendas semanais e as vendas acumuladas no ano para o produto Bounce nas semanas 20 a 30 em 2000 e 2001: neste exemplo, nós Seria de esperar 22 linhas de dados (11 semanas a cada 2 anos) se os dados fossem densos. No entanto, obtemos apenas 18 linhas porque as semanas 25 e 26 estão faltando em 2000 e as semanas 26 e 28 em 2001. Preenchendo lacunas nos dados Podemos tirar dados escassos da consulta anterior e fazer uma junção externa particionada com um denso conjunto de tempo dados. Na consulta a seguir, alias nossa consulta original como v e selecionamos dados da tabela de horários, que alias como t. Aqui recuperamos 22 linhas porque não há lacunas na série. As quatro linhas adicionadas possuem 0 como seu valor de vendas definido como 0 usando a função NVL. Observe que nesta consulta, uma condição WHERE foi colocada por semanas entre 20 e 30 na visualização em linha para a dimensão temporal. Isso foi introduzido para manter o conjunto de resultados pequeno. Lacunas de preenchimento em duas dimensões Os dados de N-dimensional geralmente são exibidos como uma aba transversal bidimensional densa de (n - 2) dimensões da página. Isso requer que todos os valores de dimensão para as duas dimensões que aparecem na aba cruzada sejam preenchidos. O seguinte é outro exemplo em que a capacidade de junção externa particionada pode ser usada para preencher as lacunas em duas dimensões: nessa consulta, a cláusula de factoring WITH subquery A v1 resume os dados de vendas no produto, no país e no nível do ano. Este resultado é esparso, mas os usuários podem querer ver todas as combinações país, ano para cada produto. Para conseguir isso, tomamos cada partição de v1 com base nos valores dos produtos e junte-a externamente na dimensão do país primeiro. Isso nos dará todos os valores do país para cada produto. Em seguida, tomamos esse resultado e particionamos nos valores do produto e do país e, em seguida, juntamos-o na dimensão do tempo. Isso nos dará todos os valores de tempo para cada combinação de produtos e países. Lacunas de preenchimento em uma tabela de inventário Uma tabela de inventário normalmente rastreia a quantidade de unidades disponíveis para vários produtos. Esta tabela é esparsa: apenas armazena uma linha para um produto quando há um evento. Para uma tabela de vendas, o evento é uma venda, e para a tabela de inventário, o evento é uma alteração na quantidade disponível para um produto. Por exemplo, considere a seguinte tabela de inventário: A tabela de inventário agora possui as seguintes linhas: Para fins de relatório, os usuários podem querer ver esses dados de inventário de forma diferente. Por exemplo, eles podem querer ver todos os valores de tempo para cada produto. Isso pode ser feito usando a junção externa particionada. Além disso, para as linhas recém-inseridas de períodos de tempo faltantes, os usuários podem querer ver os valores da quantidade de unidades de coluna a serem transferidas do período de tempo existente mais recente. O último pode ser realizado usando o valor LASTVALUE da função de janela analítica. Aqui está a consulta e a saída desejada: a consulta interna computa uma junção externa particionada no tempo dentro de cada produto. A consulta interna densifica os dados na dimensão do tempo (significando que a dimensão do tempo agora terá uma linha para cada dia da semana). No entanto, a quantidade de coluna de medida terá nulos para as linhas recém-adicionadas (veja a saída na quantidade de coluna nos seguintes resultados. A consulta externa usa a função analítica LASTVALUE. Aplicando esta função, particiona os dados por produto e ordena os dados no Coluna de dimensão de tempo (timeid). Para cada linha, a função encontra o último valor não nulo na janela devido à opção IGNORE NULLS, que você pode usar com LASTVALUE e FIRSTVALUE. Vemos a saída desejada na quantidade repetida da coluna em A seguinte saída: Computação de valores de dados para preencher lacunas Os exemplos na seção anterior ilustram como usar a junção externa particionada para preencher lacunas em uma ou mais dimensões. No entanto, os conjuntos de resultados produzidos por junção externa particionada possuem valores nulos para colunas que não estão incluídas A lista PARTITION BY. Normalmente, estas são colunas de medidas. Os usuários podem usar funções SQL analíticas para substituir esses valores nulos por um valor não nulo. Por exemplo, o seguinte q Utile calcula totais mensais para produtos de 64MB de cartão de memória e discos DVD-R (ID de produto 122 e 136) para o ano 2000. Ele usa partição externa particionada para densificar dados para todos os meses. Para os meses que faltam, ele usa a função SQL analítica AVG para calcular as vendas e as unidades como a média dos meses em que o produto foi vendido. Se estiver trabalhando no SQLPlus, os dois comandos a seguir envolverão os cabeçalhos das colunas para maior legibilidade dos resultados: os Cálculos de séries temporais na Densificação de dados densificados não são apenas para fins de relatório. Também permite certos tipos de cálculos, especialmente cálculos de séries temporais. Os cálculos da série temporal são mais fáceis quando os dados são densos ao longo da dimensão do tempo. Os dados densos têm um número consistente de linhas para cada período de tempo, o que, por sua vez, torna simples usar funções de janela analítica com deslocamentos físicos. Para ilustrar, primeiro façamos o exemplo de preenchimento de lacunas em dados. E vamos adicionar uma função analítica a essa consulta. Na versão aprimorada a seguir, calculamos vendas semanais ao longo do ano, ao lado das vendas semanais. Os valores NULL que a junção externa particionada são inseridos na criação de séries temporais densas são tratados de maneira usual: a função SUM os trata como 0s. Comparação período a período para um nível de tempo: Exemplo Como usamos esse recurso para comparar valores em períodos de tempo Especificamente, como calculamos uma comparação de vendas ano a ano no nível da semana. A seguinte consulta retorna na mesma linha , Para cada produto, as vendas acumuladas a cada semana de 2001 com a de 2000. Observe que, neste exemplo, começamos com uma cláusula WITH. Isso melhora a legibilidade da consulta e nos permite focar a união externa particionada. Se estiver trabalhando no SQLPlus, o comando a seguir envolverá os cabeçalhos das colunas para uma maior legibilidade dos resultados: na cláusula FROM da visualização em linha densesales. Usamos uma junção externa particionada de vista agregada v e exibição de tempo t para preencher lacunas nos dados de vendas ao longo da dimensão do tempo. A saída da união externa particionada é então processada pela função analítica SUM. OVER para calcular as vendas semanais do ano até à data (a coluna weeklyytdsales). Assim, a visão densesales calcula os dados de vendas do ano até à data para cada semana, incluindo aqueles que faltam na visão agregada s. A exibição em linha yearoveryearsales então calcula ano a ano as vendas semanais semestralmente usando a função LAG. A função LAG denominada weeklyytdsalesprioryear especifica uma cláusula PARTITION BY que combina linhas para a mesma semana dos anos 2000 e 2001 em uma única partição. Em seguida, passamos um deslocamento de 1 para a função LAG para obter as vendas do ano semanal para o ano anterior. O bloco de consulta mais externo seleciona dados de yearoveryearsales com a condição yr 2001. e, portanto, a consulta retorna, para cada produto, é semanal Vendas no acumulado do ano nas semanas especificadas dos anos de 2001 e 2000. Comparação Período-Período para vários níveis de tempo: Exemplo Embora o exemplo anterior nos mostre uma maneira de criar comparações para um único nível de tempo, seria ainda mais Útil para lidar com vários níveis de tempo em uma única consulta. Por exemplo, podemos comparar as vendas em relação ao período anterior nos níveis de ano, trimestre, mês e dia. Como podemos criar uma consulta que realiza uma comparação de ano a ano sobre as vendas do acumulado para todos os níveis da hierarquia do tempo. Vamos realizar várias etapas para realizar esta tarefa. O objetivo é uma única consulta com comparações ao nível do dia, semana, mês, trimestre e ano. Os passos são os seguintes: criaremos uma visão chamada cubeprodtime. Que possui um cubo hierárquico de vendas agregado em tempos e produtos. Em seguida, criaremos uma visão da dimensão do tempo para usar como uma ponta do cubo. A borda do tempo, que contém um conjunto completo de datas, será particionada externa juntada aos dados esparsos na vista cubeprodtime. Finalmente, para obter o máximo desempenho, criaremos uma visão materializada, mvprodtime. Construído usando a mesma definição como cubeprodtime. Para obter mais informações sobre cubos hierárquicos, consulte o Capítulo 20, SQL para agregação em data warehouses. A visualização materializada é definida na Etapa 1 na seção a seguir. Passo 1 Crie a visão do cubo hierárquico A visualização materializada mostrada no seguinte pode existir no seu sistema se não, crie-o agora. Se você deve gerá-lo, note que limitamos a consulta apenas a dois produtos para manter o tempo de processamento curto: porque essa visão é limitada a dois produtos, ele retorna um pouco mais de 2200 linhas. Observe que a coluna HierarchicalTime contém representações de seqüência de tempo de todos os níveis da hierarquia de tempo. A expressão CASE usada para a coluna HierarchicalTime anexa um marcador (0, 1.) a cada string de data para indicar o nível de tempo do valor. A 0 representa o nível do ano, 1 é quarto, 2 meses e 3 é o dia. Observe que a cláusula GROUP BY é um ROLLUP concatenado que especifica a hierarquia de rollup para o tempo e as dimensões do produto. A cláusula GROUP BY é o que determina o conteúdo hierárquico do cubo. Passo 2 Crie o tempo de exibição de exibição, que é um conjunto completo de valores de data. Edgetime é a fonte para preencher lacunas de tempo no cubo hierárquico usando uma junção externa particionada. A coluna HierarchicalTime em edgetime será usada em uma junção particionada com a coluna HierarchicalTime na vista cubeprodtime. A seguinte declaração define edgetime: Etapa 3 Crie a visualização materializada mvprodtime para suportar um desempenho mais rápido. A definição de exibição materializada é uma duplicata da vista cubeprodtime definida anteriormente. Como é uma consulta duplicada, as referências ao cubeprodtime serão reescritas para usar a visão materializada do mvprodtime. O material que se segue pode já existir no seu sistema se não, crie-o agora. Se você deve gerá-lo, note que limitamos a consulta apenas a dois produtos para manter o tempo de processamento curto. Passo 4 Crie a consulta de comparação Nós estabelecemos o cenário para a nossa consulta de comparação. Podemos obter cálculos de comparação de período a período em todos os níveis de tempo. Ele requer a aplicação de funções analíticas a um cubo hierárquico com dados densos ao longo da dimensão temporal. Alguns dos cálculos que podemos alcançar para cada nível de tempo são: Soma das vendas para o período anterior em todos os níveis de tempo. Variação nas vendas ao longo do período anterior. Soma das vendas no mesmo período de um ano atrás em todos os níveis de tempo. Variação nas vendas em relação ao mesmo período do ano passado. O seguinte exemplo executa todos os quatro desses cálculos. Ele usa uma junção externa particionada das vistas cubeprodtime e edgetime para criar uma visão em linha de dados densos chamados densecubeprodtime. A consulta então usa a função LAG da mesma maneira que o exemplo anterior de nível único. A cláusula WHERE externa especifica o tempo em três níveis: os dias de agosto de 2001, todo o mês e todo o terceiro trimestre de 2001. Observe que as últimas duas linhas dos resultados contêm as agregações de nível de mês e quarto. Observe que fazer Os resultados são mais fáceis de ler se você estiver usando o SQLPlus, os cabeçalhos das colunas devem ser ajustados com os seguintes comandos. Os comandos dobrarão os cabeçalhos das colunas para reduzir o comprimento da linha: Aqui está a consulta que compara as vendas atuais com vendas anteriores e ano anterior: a primeira função LAG (salespriorperiod) divide os dados no gidp. gato. Subcat. Prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many OLAP tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 22, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Scripting on this page enhances content navigation, but does not change the content in any way. Average True Range Spreadsheet 038 Tutorial Discover how traders use average true range as a stop-loss indicator in buying amp selling strategies, and learn how it is calculated in Excel. A gama de stock8217s é a diferença entre o preço máximo e o preço mínimo em um único dia e é freqüentemente usada como indicador de volatilidade. No entanto, a negociação é muitas vezes interrompida se os preços aumentar ou diminuir em grande quantidade em um único dia. Isso às vezes é observado na negociação de commodities e pode levar a uma lacuna entre os preços de abertura e de fechamento entre dois dias consecutivos. Um intervalo diário não necessariamente capturaria essa informação. J. Welles Wilder introduziu o intervalo verdadeiro e o alcance verdadeiro médio em 1978 para melhor descrever esse comportamento. O verdadeiro alcance captura a diferença entre o fechamento e os preços de abertura entre dois dias consecutivos. O verdadeiro alcance é a maior da diferença entre close-up de yesterday8217s e today8217s baixa, a diferença entre today8217s close e today8217s high a diferença entre today8217s high e today8217s low O valor inicial de true range é simplesmente o diário alto menos a baixa diária. O intervalo verdadeiro médio (ATR) é uma média exponencial de n-dia. E pode ser aproximado por esta equação. Onde n é a janela da média móvel (geralmente 14 dias) e TR é o verdadeiro intervalo. ATR geralmente é inicializado (em t 0) com uma média de trânsito de n-dia de TR. O alcance médio verdadeiro não indica a direção do mercado, mas sim a volatilidade. A equação dá o movimento de preços mais recente, maior significado, portanto, é usado para medir o sentimento do mercado. Geralmente é usado para analisar o risco de assumir uma posição específica no mercado. Uma maneira de fazer isso é prever movimentos diários com base em valores históricos da ATR, e entrar ou sair do mercado de acordo. Por exemplo, uma perda de parada diária pode ser definida em 1,5 ou 2 vezes a faixa real média. Isso dá uma liberdade de preço de ativos para variar naturalmente durante um dia de negociação, mas ainda estabelece uma posição de saída razoável. Além disso, se o intervalo histórico histórico real se contraiga, enquanto os preços tendem para cima, então isso pode indicar que o sentimento do mercado pode virar. Combinado com Bollinger Bands. O verdadeiro alcance médio é uma ferramenta eficaz para estratégias de negociação baseadas em volatilidade. Calcule a faixa verdadeira média no Excel Esta planilha do Excel usa os preços diários das ações da BP nos cinco anos de 2007 (baixados com esta planilha). A planilha é totalmente anotada com equações e comentários para ajudar sua compreensão. A seguinte planilha, no entanto, tem muito mais inteligências. Ele automaticamente, traça o alcance real médio, o índice de força relativa e a volatilidade histórica dos dados que ele automaticamente faz o download do Yahoo Finance. Você insere as seguintes informações um ticker de ações nos períodos de cálculo da data de início e final para o ATR, o RSI e a volatilidade histórica Depois de clicar em um botão, a planilha baixa as cotações de ações da Yahoo Finance (especificamente, os preços diários abertos, fechados, altos e baixos entre As duas datas). Em seguida, traça a faixa verdadeira média e a volatilidade histórica. It8217s muito simples de usar I8217d goste de ouvir o que você pensa ou se você tiver alguma melhoria que você goste. 11 pensamentos sobre ldquo Average True Range Spreadsheet 038 Tutorial rdquo Como o Free Spreadsheets Master Knowledge Base Posts recentes