Exponencial Moving Average Sql

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 da 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. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. 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.22 SQL for Analysis and Reporting Oracle has enhanced SQLs analytical processing capabilities by introducing a new family of analytic SQL functions. These analytic functions enable you to calculate: Rankings and percentiles Moving window calculations Linear regression statistics Ranking functions include cumulative distributions, percent rank, and N-tiles. Moving window calculations allow you to find moving and cumulative aggregations, such as sums and averages. Laglead analysis enables direct inter-row references so you can calculate period-to-period changes. Firstlast analysis enables you to find the first or last value in an ordered group. Other enhancements to SQL include the CASE expression and partitioned outer join. CASE expressions provide if-then logic useful in many situations. Partitioned outer join is an extension to ANSI outer join syntax that allows users to selectively densify certain dimensions while keeping others sparse. This allows reporting tools to selectively densify dimensions, for example, the ones that appear in their cross-tabular reports while keeping others sparse. To enhance performance, analytic functions can be parallelized: multiple processes can simultaneously execute all of these statements. These capabilities make calculations easier and more efficient, thereby enhancing database performance, scalability, and simplicity. Analytic functions are classified as described in Table 22-1 . Table 22-1 Analytic Functions and Their Uses To perform these operations, the analytic functions add several new elements to SQL processing. These elements build on existing SQL to allow flexible and powerful calculation expressions. With just a few exceptions, the analytic functions have these new elements. The processing flow is represented in Figure 22-1 . Figure 22-1 Processing Order The essential concepts used in analytic functions are: Query processing using analytic functions takes place in three stages. First, all joins, WHERE. GROUP BY and HAVING clauses are performed. Second, the result set is made available to the analytic functions, and all their calculations take place. Third, if the query has an ORDER BY clause at its end, the ORDER BY is processed to allow for precise output ordering. The processing order is shown in Figure 22-1 . Result set partitions The analytic functions allow users to divide query result sets into groups of rows called partitions. Note that the term partitions used with analytic functions is unrelated to the table partitions feature. Throughout this chapter, the term partitions refers to only the meaning related to analytic functions. Partitions are created after the groups defined with GROUP BY clauses, so they are available to any aggregate results such as sums and averages. Partition divisions may be based upon any desired columns or expressions. A query result set may be partitioned into just one partition holding all the rows, a few large partitions, or many small partitions holding just a few rows each. For each row in a partition, you can define a sliding window of data. This window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time. The window has a starting row and an ending row. Depending on its definition, the window may move at one or both ends. For instance, a window defined for a cumulative sum function would have its starting row fixed at the first row of its partition, and its ending row would slide from the starting point all the way to the last row of the partition. In contrast, a window defined for a moving average would have both its starting and end points slide so that they maintain a constant physical or logical range. A window can be set as large as all the rows in a partition or just a sliding window of one row within a partition. When a window is near a border, the function returns results for only the available rows, rather than warning you that the results are not what you want. When using window functions, the current row is included during calculations, so you should only specify ( n -1) when you are dealing with n items. Each calculation performed with an analytic function is based on a current row within a partition. The current row serves as the reference point determining the start and end of the window. For instance, a centered moving average calculation could be defined with a window that holds the current row, the six preceding rows, and the following six rows. This would create a sliding window of 13 rows, as shown in Figure 22-2 . Figure 22-2 Sliding Window Example Ranking, Windowing, and Reporting Functions This section illustrates the basic analytic functions for ranking, windowing, and reporting. Sample Linear Regression Calculation In this example, we compute an ordinary-least-squares regression line that expresses the quantity sold of a product as a linear function of the products list price. The calculations are grouped by sales channel. The values SLOPE. INTCPT. RSQR are slope, intercept, and coefficient of determination of the regression line, respectively. The (integer) value COUNT is the number of products in each channel for whom both quantity sold and list price data are available. Statistical Aggregates Oracle provides a set of SQL statistical functions and a statistics package, DBMSSTATFUNCS. This section lists some of the new functions along with basic syntax. Descriptive Statistics You can calculate the following descriptive statistics: Median of a Data Set Mode of a Data Set You can calculate the following parametric statistics: Spearmans rho Coefficient Kendalls tau-b Coefficient In addition to the functions, this release has a PLSQL package, DBMSSTATFUNCS. It contains the descriptive statistical function SUMMARY along with functions to support distribution fitting. The SUMMARY function summarizes a numerical column of a table with a variety of descriptive statistics. The five distribution fitting functions support normal, uniform, Weibull, Poisson, and exponential distributions. User-Defined Aggregates Oracle offers a facility for creating your own functions, called user-defined aggregate functions. These functions are written in programming languages such as PLSQL, Java, and C, and can be used as analytic functions or aggregates in materialized views. See Oracle Database Data Cartridge Developers Guide for further information regarding syntax and restrictions. The advantages of these functions are: Highly complex functions can be programmed using a fully procedural language. Higher scalability than other techniques when user-defined functions are programmed for parallel processing. Object data types can be processed. As a simple example of a user-defined aggregate function, consider the skew statistic. This calculation measures if a data set has a lopsided distribution about its mean. It will tell you if one tail of the distribution is significantly larger than the other. If you created a user-defined aggregate called udskew and applied it to the credit limit data in the prior example, the SQL statement and results might look like this: Before building user-defined aggregate functions, you should consider if your needs can be met in regular SQL. Many complex calculations are possible directly in SQL, particularly by using the CASE expression. Staying with regular SQL will enable simpler development, and many query operations are already well-parallelized in SQL. Even the earlier example, the skew statistic, can be created using standard, albeit lengthy, SQL. Pivoting Operations The d ata returned by business intelligence queries is often most usable if presented in a crosstabular format. The pivotclause of the SELECT statement lets you write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation. Pivoting is a key technique in data warehouses. In it, you transform multiple rows of input into fewer and generally wider rows in the data warehouse. When pivoting, an aggregation operator is applied for each item in the pivot column value list. The pivot column cannot contain an arbitrary expression. If you need to pivot on an expression, then you should alias the expression in a view before the PIVOT operation. The basic syntax is as follows: To illustrate the use of pivoting, create the following view as a basis for later examples: Example: Pivoting The following statement illustrates a typical pivot on the channel column: Note that the output has created four new aliased columns, DIRECTSALES. INTERNETSALES. CATALOGSALES. and TELESALES. one for each of the pivot values. The output is a sum. If no alias is provided, the column heading will be the values of the IN - list. Pivoting on Multiple Columns You can pivot on more than one column. The following statement illustrates a typical multiple column pivot: Note that this example specifies a multi-column IN - list with column headings designed to match the IN - list members. Pivoting: Multiple Aggregates You can pivot with multiple aggregates, as shown in the following example: Note that the query creates column headings by concatenating the pivot values (or alias) with the alias of the aggregate function, plus an underscore. Distinguishing PIVOT-Generated Nulls from Nulls in Source Data You can distinguish between null values that are generated from the use of PIVOT and those that exist in the source data. The following example illustrates nulls that PIVOT generates. The following query returns rows with 5 columns, column prodid. and pivot resulting columns Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. For each unique value of prodid. Q1COUNTTOTAL returns the total number of rows whose qtr value is Q1. that is, and Q2COUNTTOTAL returns the total number of rows whose qtr value is Q2 . Assume we have a table sales2 of the following structure: From the result, we know that for prodid 100, there are 2 sales rows for quarter Q1. and 1 sales row for quarter Q2 for prodid 200, there is 1 sales row for quarter Q1. and no sales row for quarter Q2.So, in Q2COUNTTOTAL. you can identify that NULLlt1gt comes from a row in the original table whose measure is of null value, while NULLlt2gt is due to no row being present in the original table for prodid 200 in quarter Q2 . Unpivoting Operations An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means. To illustrate unpivoting, first create a pivoted table that includes four columns, for quarters of the year: The tables contents resemble the following: The following UNPIVOT operation rotates the quarter columns into rows. For each product, there will be four rows, one for each quarter. Note the use of INCLUDE NULLS in this example. You can also use EXCLUDE NULLS. which is the default setting. In addition, you can also unpivot using two columns, as in the following: Wildcard and Subquery Pivoting with XML Operations If you want to use a wildcard argument or subquery in your pivoting columns, you can do so with PIVOT XML syntax. With PIVOT XML, the output of the operation is properly formatted XML. The following example illustrates using the wildcard keyword, ANY. It outputs XML that includes all channel values in salesview : Note that the keyword ANY is available in PIVOT operations only as part of an XML operation. This output includes data for cases where the channel exists in the data set. Also note that aggregation functions must specify a GROUP BY clause to return multiple values, yet the pivotclause does not contain an explicit GROUP BY clause. Instead, the pivotclause performs an implicit GROUP BY . The following example illustrates using a subquery. It outputs XML that includes all channel values and the sales data corresponding to each channel: The output densifies the data to include all possible channels for each product. Data Densification for Reporting Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it. Moreover, time series calculations can be performed most easily when data is dense along the time dimension. This is because dense data will fill a consistent number of rows for each period, which in turn makes it simple to use the analytic windowing functions with physical offsets. Data densification is the process of converting sparse data into dense form. To overcome the problem of sparsity, you can use a partitioned outer join to fill the gaps in a time series or any other dimension. Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join. Note that you can use this type of join to fill the gaps in any dimension, not just the time dimension. Most of the examples here focus on the time dimension because it is the dimension most frequently used as a basis for comparisons. Partition Join Syntax The syntax for partitioned outer join extends the ANSI SQL JOIN clause with the phrase PARTITION BY followed by an expression list. The expressions in the list specify the group to which the outer join is applied. The following are the two forms of syntax normally used for partitioned outer join: Note that FULL OUTER JOIN is not supported with a partitioned outer join. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However, we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH subquery factoring clause v1 summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following q uery computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands wraps the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, let us first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command wraps the column headings for greater readability of results: In the FROM clause of the inline view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The inline view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001. and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an inline view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. 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 analytical SQL 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 23, 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. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (a nalysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way.