Moving Average Sql Oracle

Funções SQL As funções SQL são incorporadas ao Oracle Database e estão disponíveis para uso em várias instruções SQL apropriadas. Não confunda funções SQL com funções definidas pelo usuário escritas em PLSQL. Se você chamar uma função SQL com um argumento de um tipo de dados diferente do tipo de dados esperado pela função SQL, o Oracle tentará converter o argumento para o tipo de dados esperado antes de executar a função SQL. Se você chamar uma função SQL com um argumento nulo, a função SQL retornará automaticamente nulo. As únicas funções SQL que não seguem necessariamente este comportamento são CONCAT. NVL. SUBSTITUIR. E REGEXPREPLACE. Os valores combinados das configurações NLSCOMP e NLSSORT determinam as regras pelas quais os caracteres são classificados e comparados. Se NLSCOMP estiver definido como LINGUISTIC para o banco de dados, todas as entidades neste capítulo serão interpretadas de acordo com as regras especificadas pelo parâmetro NLSSORT. Se NLSCOMP não estiver definido para LINGUISTIC. Então as funções são interpretadas sem considerar a configuração NLSSORT. NLSSORT pode ser explicitamente definido. Se não for definido explicitamente, ele é derivado de NLSLANGUAGE. Consulte o Oracle Database Globalization Support Guide para obter mais informações sobre essas configurações. Nos diagramas de sintaxe para funções SQL, os argumentos são indicados pelos seus tipos de dados. Quando a função de parâmetro aparecer na sintaxe SQL, substitua-a por uma das funções descritas nesta seção. As funções são agrupadas pelos tipos de dados de seus argumentos e seus valores de retorno. Quando você aplica funções SQL a colunas LOB, o Oracle Database cria LOBs temporários durante o processamento SQL e PLSQL. Você deve garantir que a cota de espaço de tabela temporária é suficiente para armazenar esses LOBs temporários para seu aplicativo. Funções Definidas pelo Usuário para obter informações sobre funções de usuário e Conversão de dados para conversão implícita de tipos de dados Referência de Texto do Oracle para obter informações sobre funções usadas com o Oracle Text Guia de Desenvolvedores de Aplicativos do Oracle Data Mining para obter informações sobre funções freqüentes de conjuntos de itens usadas com o Oracle Data Mining A sintaxe mostrando as categorias Das funções a seguir: As seções a seguir listam as funções SQL incorporadas em cada um dos grupos ilustrados nos diagramas anteriores, exceto as funções definidas pelo usuário. Todas as funções SQL incorporadas são descritas em ordem alfabética. Funções de linha única As funções de linha única retornam uma única linha de resultado para cada linha de uma tabela ou exibição consultada. Essas funções podem aparecer em listas de seleção, cláusulas WHERE, cláusulas START WITH e CONNECT BY e cláusulas HAVING. Funções numéricas As funções numéricas aceitam entrada numérica e retornam valores numéricos. A maioria das funções numéricas que retornam NUMBER valores que são precisos para 38 dígitos decimais. As funções transcendentais. EXP. LN. REGISTRO. PECADO. SINH. SQRT. BRONZEADO. E TANH são precisos para 36 dígitos decimais. As funções transcendentais ACOS. COMO EM. UM BRONZEADO. E ATAN2 são precisos para 30 dígitos decimais. As funções numéricas são: Funções de Caracteres Devolvendo Valores de Caracteres As funções de caractere que retornam valores de caracteres retornam valores dos seguintes tipos de dados, a menos que documentado de outra forma: Se o argumento de entrada for CHAR ou VARCHAR2. Então o valor retornado é VARCHAR2. Se o argumento de entrada é NCHAR ou NVARCHAR2. Então o valor retornado é NVARCHAR2. O comprimento do valor retornado pela função é limitado pelo comprimento máximo do tipo de dados retornado. Para funções que retornam CHAR ou VARCHAR2. Se o comprimento do valor de retorno exceder o limite, o Oracle Database trunca e retorna o resultado sem uma mensagem de erro. Para funções que retornam valores CLOB, se o comprimento dos valores de retorno exceder o limite, o Oracle levanta um erro e retorna nenhum dado. As funções de caractere que retornam valores de caractere são: Funções de caractere NLS As funções de caractere NLS retornam informações sobre o conjunto de caracteres. As funções de caractere NLS são: Funções de Caracteres Devolvendo Valores de Número As funções de caractere que retornam valores de número podem tomar como argumento qualquer tipo de dado de caractere. As funções de caractere que retornam valores de número são: Funções de data e hora Funções de hora de data operam em data (DATA), timestamp (TIMESTAMP, TIMESTAMP COM ZONA DE HORA e TIMESTAMP COM ZONA DE HORA LOCAL) e intervalo (INTERVALO DE DIA PARA SEGUNDO INTERVALO DE ANO PARA MÊS) Valores. Algumas das funções datetime foram projetadas para o tipo de dados Oracle DATE (ADDMONTHS, CURRENTDATE, LASTDAY, NEWTIME e NEXTDAY). Se você fornecer um valor timestamp como seu argumento, o Oracle Database converte internamente o tipo de entrada em um valor DATE e retorna um valor DATE. As exceções são a função MONTHSBETWEEN, que retorna um número, e as funções ROUND e TRUNC, que não aceitam valores de timestamp ou intervalos. As restantes funções de data e hora foram concebidas para aceitar qualquer um dos três tipos de dados (data, hora e intervalo) e para devolver um valor de um destes tipos. As funções de data e hora são: Funções gerais de comparação As funções gerais de comparação determinam o maior e o menor valor de um conjunto de valores. As funções gerais de comparação são: Funções de conversão As funções de conversão convertem um valor de um tipo de dados para outro. Geralmente, a forma dos nomes de função segue o tipo de dados de convenção de tipo de dados TO. O primeiro tipo de dados é o tipo de dados de entrada. O segundo tipo de dados é o tipo de dados de saída. As funções de conversão SQL são: Funções de Objeto Grande As funções de objeto grande operam em LOBs. As funções de objeto grandes são: Funções de coleção As funções de coleção operam em tabelas aninhadas e varrays. As funções de coleção do SQL são: Função hierárquica A função hierárquica aplica informações de caminho hierárquico a um conjunto de resultados. Funções de Mineração de Dados As funções de mineração de dados operam em modelos que foram construídos usando o pacote DBMSDATAMINING ou a API Java do Oracle Data Mining. As funções de mineração de dados do SQL são: Funções XML As funções XML operam sobre ou retornam documentos ou fragmentos XML. Para obter mais informações sobre como selecionar e consultar dados XML usando essas funções, incluindo informações sobre formatação de saída, consulte Oracle XML DB Developers Guide. As funções SQL XML são: Funções de codificação e decodificação As funções de codificação e decodificação permitem inspecionar e decodificar dados no banco de dados. Funções relacionadas com NULL As funções relacionadas com NULL facilitam o processamento nulo. As funções relacionadas com NULL são: Funções de Ambiente e Identificador As funções de ambiente e identificador fornecem informações sobre a instância ea sessão. Essas funções são: Funções de agregação As funções de agregação retornam uma única linha de resultado com base em grupos de linhas, em vez de em linhas únicas. As funções de agregação podem aparecer em listas de seleção e em cláusulas ORDER BY e HAVING. Eles são comumente usados ​​com a cláusula GROUP BY em uma instrução SELECT, em que o Oracle Database divide as linhas de uma tabela ou exibição consultada em grupos. Em uma consulta que contém uma cláusula GROUP BY, os elementos da lista de seleção podem ser funções agregadas, expressões GROUP BY, constantes ou expressões que envolvam uma dessas. A Oracle aplica as funções agregadas a cada grupo de linhas e retorna uma única linha de resultado para cada grupo. Se você omitir a cláusula GROUP BY, a Oracle aplicará funções de agregação na lista de seleção a todas as linhas na tabela ou exibição consultada. Você usa funções de agregação na cláusula HAVING para eliminar grupos da saída com base nos resultados das funções de agregação, em vez de nos valores das linhas individuais da tabela ou exibição consultada. Usando a Cláusula GROUP BY: Exemplos ea Cláusula HAVING para obter mais informações sobre a cláusula GROUP BY e as cláusulas HAVING em consultas e subconsultas Muitas (mas não todas) funções agregadas que aceitam um único argumento aceitam essas cláusulas: DISTINCT faz com que uma função agregada considere Apenas valores distintos da expressão do argumento. ALL faz com que uma função agregada considere todos os valores, incluindo todos os duplicados. Por exemplo, a média DISTINCT de 1, 1, 1 e 3 é 2. A média ALL é 1,5. Se você especificar nem, então o padrão é ALL. Todas as funções de agregação, exceto COUNT () e GROUPING ignoram os nulos. Você pode usar a função NVL no argumento para uma função agregada para substituir um valor por um nulo. COUNT nunca retorna null, mas retorna um número ou zero. Para todas as funções de agregação restantes, se o conjunto de dados não contém linhas ou contém apenas linhas com nulos como argumentos para a função de agregação, a função retorna null. As funções agregadas MIN. MAX. SOMA. AVG. CONTAGEM. VARIÂNCIA. E STDDEV. Quando seguido pela palavra-chave KEEP, pode ser usado em conjunto com a função FIRST ou LAST para operar em um conjunto de valores de um conjunto de linhas que se classificam como FIRST ou LAST em relação a uma dada especificação de classificação. Consulte FIRST para obter mais informações. Você pode aninhar funções agregadas. Por exemplo, o exemplo a seguir calcula a média dos salários máximos de todos os departamentos do esquema de amostra hr: Este cálculo avalia o agregado interno (MAX (salário)) para cada grupo definido pela cláusula GROUP BY (departmentid) e os agregados Os resultados novamente. As funções agregadas são: Funções analíticas As funções analíticas calculam um valor agregado com base em um grupo de linhas. Eles diferem das funções de agregação em que retornam várias linhas para cada grupo. O grupo de linhas é chamado de janela e é definido pela cláusula analítica. Para cada linha, uma janela deslizante de linhas é definida. A 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 em um intervalo lógico, como o tempo. Funções analíticas são o último conjunto de operações executadas em uma consulta, exceto para a cláusula ORDER BY final. Todas as junções e todos os WHERE. GRUPO POR. E as cláusulas HAVING são concluídas antes que as funções analíticas sejam processadas. Portanto, as funções analíticas podem aparecer somente na lista de seleção ou na cláusula ORDER BY. Funções analíticas são comumente usadas para computar acumulativo, movendo, centrado e agregados de relatório. Eu fiz alguns Googling e não poderia encontrar uma resposta a esta pergunta mais recente do que alguns anos há, assim que eu pensei a pergunta da identificação. O recurso Oracles RAC oferece balanceamento de carga para transações de leitura e gravação, bem como scale-out e alta disponibilidade sem tempo de inatividade (pelo menos, como eu entendo - estavam prestes a implantar nossos primeiros bancos de dados que usam RAC, para ver bem como Ele vai). Existe um conjunto de recursos do SQL Server (ou um componente de terceiros que você poderia instalar no topo) que oferece funcionalidade equivalente. Sempre usamos o cluster do Windows, onde um evento de failover causa cerca de 20-30 segundos de inatividade do SQL - sempre tolerável, mas não ideal. Agora, com o AlwaysOn no SQL 2012, o SQL Server encolhe isso para cerca de 15 segundos e adiciona o conceito de bancos de dados somente leitura, mas eles ainda exigem que as transações de gravação sejam sufocadas por um único ponto de conexão (muito melhor, já que muitas transações são Apenas ler, mas ainda não realmente balanceamento de carga), e no caso de uma falha de nó ou a necessidade de patch, há ainda tempo de inatividade. Suponho que é apenas mais curiosidade - eu sinto que esta é a única área que o SQL Server cai para trás Oracle (pelo menos entre os recursos que eu pessoalmente vi usado). Eu queria ver se existem algumas opções lá fora, para fechar essa lacuna e, possivelmente, melhorar a nossa própria implantação do SQL Server, enquanto esperamos para Microsofts equivalente recurso a ser adicionado - talvez em SQL 20142015 pediu Jul 13 12 at 22:04 rwmnau novamente, eu Sugerem que você mantenha seu louvor brilhante do RAC até que você o implemente e o tenha usado por alguns meses. -) Você pode estar certo pode ser tudo o que promete e trabalhar perfeitamente para você. Mas você pode ser enganado pelo brilho brilhante na caixa. -) ndash Aaron Bertrand 9830 Jul 14 12 at 2: 5122 SQL para análise e relatórios O Oracle aprimorou as capacidades de processamento analítico do SQLs, introduzindo uma nova família de funções SQL analíticas. Estas funções analíticas permitem calcular: Rankings e percentis Cálculos de janela em movimento Estatísticas de regressão linear As funções de classificação incluem distribuições cumulativas, classificação por cento e N-tiles. Os cálculos de janelas móveis permitem encontrar agregações móveis e cumulativas, como somas e médias. A análise Laglead permite referências diretas entre linhas para que você possa calcular as alterações período-período. A primeira análise permite que você encontre o primeiro ou último valor em um grupo ordenado. Outros aprimoramentos para SQL incluem a expressão CASE e partição externa particionada. As expressões CASE fornecem a lógica if-then útil em muitas situações. A junção externa particionada é uma extensão da sintaxe de junção externa ANSI que permite aos usuários densificar seletivamente certas dimensões enquanto mantém outras escassas. Isso permite que as ferramentas de relatório seletivamente densifiquem dimensões, por exemplo, as que aparecem em seus relatórios de tabelas cruzadas, mantendo outras escassas. Para melhorar o desempenho, as funções analíticas podem ser paralelizadas: vários processos podem executar simultaneamente todas essas instruções. Essas capacidades tornam os cálculos mais fáceis e eficientes, aumentando o desempenho, escalabilidade e simplicidade do banco de dados. As funções analíticas são classificadas conforme descrito na Tabela 22-1. Tabela 22-1 Funções analíticas e seus usos Para executar essas operações, as funções analíticas adicionam vários novos elementos ao processamento SQL. Esses elementos baseiam-se no SQL existente para permitir expressões de cálculo flexíveis e poderosas. Com poucas exceções, as funções analíticas têm esses novos elementos. O fluxo de processamento é representado na Figura 22-1. Figura 22-1 Ordem de Processamento Os conceitos essenciais utilizados nas funções analíticas são: Processamento de consultas usando funções analíticas ocorre em três estágios. Primeiro, todas as junções, ONDE. As cláusulas GROUP BY e HAVING são executadas. 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 tem uma cláusula ORDER BY no seu final, o ORDER BY é processado para permitir a ordenação de saída precisa. A ordem de processamento é mostrada na Figura 22-1. Partições de conjunto de resultados As funções analíticas permitem aos usuários dividir os conjuntos de resultados de consulta em grupos de linhas chamadas partições. Observe que o termo partições usadas com funções analíticas não está relacionado ao recurso de partições 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, de modo que estão disponíveis para todos os resultados agregados, como somas e médias. As divisões de partição podem basear-se em quaisquer colunas ou expressões desejadas. Um conjunto de resultados de consulta pode ser particionado em apenas uma partição que contém todas as linhas, algumas grandes partições ou muitas pequenas partições com apenas algumas linhas cada. Para cada linha em uma partição, você pode definir uma janela deslizante de dados. Esta janela determina o intervalo de linhas usado 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 em 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 mover-se 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 do ponto de partida até a última linha da partição. Em contraste, uma janela definida para uma média móvel teria ambos os seus pontos inicial e final deslizar para que eles mantenham uma constante físico ou lógico intervalo. 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 para apenas as linhas disponíveis, em vez de avisar que os resultados não são o que você deseja. Ao usar funções de janela, a linha atual é incluída durante os cálculos, portanto, você deve especificar apenas (n -1) quando estiver lidando com n itens. Cada cálculo realizado com uma função analítica é baseado em uma linha atual dentro de uma partição. A linha atual serve como ponto de referência que determina o início eo fim da janela. Por exemplo, um cálculo de média móvel centrada pode ser definido com uma janela que contém a linha atual, as seis linhas precedentes e as seis linhas seguintes. Isso criaria uma janela deslizante de 13 linhas, como mostrado na Figura 22-2. Figura 22-2 Janela deslizante Exemplo de classificação, janelas e funções de relatório Esta seção ilustra as funções analíticas básicas para classificação, janelas e relatórios. Exemplo de Cálculo de Regressão Linear Neste exemplo, calculamos uma linha de regressão de mínimos quadrados comuns que expresse a quantidade vendida de um produto como uma função linear do preço de lista de produtos. Os cálculos são agrupados por 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 (inteiro) COUNT é o número de produtos em cada canal para os quais estão disponíveis os dados de quantidade vendida e de preço de tabela. Agregados estatísticos O Oracle fornece 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 modo de conjunto de dados de um conjunto de dados Você pode calcular as seguintes estatísticas paramétricas: Spearmans rho Coeficiente Kendalls tau-b Coeficiente Além das funções, esta versão possui um pacote PLSQL, DBMSSTATFUNCS . Contém a função estatística descritiva RESUMO juntamente com funções para suportar o encaixe 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 ajuste de distribuição suportam distribuições normais, uniformes, Weibull, Poisson e exponenciais. Agregados definidos pelo usuário O Oracle oferece uma facilidade para criar suas próprias funções, chamadas funções de agregação definidas pelo usuário. Estas funções são escritas em linguagens de programação como PLSQL, Java e C, e podem ser usadas 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 destas funções são: Funções altamente complexas podem ser programadas usando uma linguagem totalmente processual. Maior escalabilidade que outras técnicas quando funções definidas pelo usuário são programadas para processamento paralelo. Tipos de dados de objeto podem ser processados. Como um exemplo simples de uma função agregada definida pelo usuário, considere a estatística de inclinação. Este cálculo mede se um conjunto de dados tem uma distribuição desequilibrada sobre sua média. Ele irá dizer-lhe se uma cauda da distribuição é significativamente maior do que o outro. 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 ser assim: Antes de criar funções de agregação definidas pelo usuário, você deve considerar se suas necessidades podem ser atendidas Em SQL regular. 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 em SQL. Mesmo o exemplo anterior, a estatística de inclinação, pode ser criado usando padrão, embora longo, SQL. Operações dinâmicas O dado retornado por consultas de inteligência de negócios geralmente é mais útil se apresentado em um formato de tabela cruzada. O pivotclause da instrução SELECT permite que você escreva consultas crosstabulation que rodam linhas em colunas, agregando dados no processo de rotação. O pivô é uma técnica-chave nos data warehouses. Nela, você transforma várias linhas de entrada em menos e em linhas geralmente mais amplas no data warehouse. Ao girar, um operador de agregação é aplicado para cada item na lista de valores da coluna de pivô. A coluna pivot não pode conter uma expressão arbitrária. Se você precisar girar em uma expressão, então você deve alias a expressão em uma visão antes da operação PIVOT. A sintaxe básica é a seguinte: Para ilustrar o uso do pivô, crie a seguinte visão como base para exemplos posteriores: Exemplo: Pivotando A seguinte instrução ilustra um pivô típico na coluna do canal: Note que a saída criou quatro novas colunas de alias , VENDAS DIRETAS. INTERNETSALES. CATALOGSALES. E TELESALES. Um para cada um dos valores de pivô. A saída é uma soma. Se nenhum alias for fornecido, o cabeçalho da coluna será os valores do IN-list. Como girar em várias colunas Você pode girar em mais de uma coluna. A instrução a seguir ilustra um pivô de várias colunas típico: Observe que esse exemplo especifica uma lista de várias colunas IN com cabeçalhos de coluna projetados para coincidir com os membros IN-list. Pivoting: Multiple Aggregates Você pode girar com vários agregados, como mostrado no exemplo a seguir: Observe que a consulta cria cabeçalhos de coluna, concatenando os valores de pivô (ou alias) com o alias da função de agregação, mais um sublinhado. Distinguindo Nulos Gerados por PIVOT de Nulos em Dados de Origem Você pode distinguir entre valores nulos que são gerados a partir do uso de PIVOT e aqueles que existem nos dados de origem. O exemplo a seguir ilustra nulos gerados pelo PIVOT. A consulta a seguir retorna linhas com 5 colunas, coluna prodid. E articular as colunas Q1 resultantes. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Para cada valor único de prodídio. Q1COUNTTOTAL retorna o número total de linhas cujo valor qtr é Q1. Isto é, e Q2COUNTTOTAL retorna o número total de linhas cujo valor qtr é Q2. Suponhamos que temos uma tabela sales2 da seguinte estrutura: Do resultado, sabemos que para 100 pródigos, há 2 linhas de vendas para trimestre Q1. E 1 linha de vendas para o trimestre Q2 para 200 prodid, há 1 linha de vendas para trimestre Q1. E nenhuma fila das vendas para o quarto Q2. Assim, em Q2COUNTTOTAL. Você pode identificar que NULLlt1gt vem de uma linha na tabela original cuja medida é de valor nulo, enquanto NULLlt2gt é devido a nenhuma linha estar presente na tabela original para 200 pródido no trimestre Q2. Unpivot Unpivot não inverte uma operação PIVOT. Em vez disso, ele roda dados de colunas em linhas. Se você estiver trabalhando com dados articulados, uma operação UNPIVOT não pode reverter quaisquer agregações que foram feitas pela PIVOT ou por qualquer outro meio. Para ilustrar unpivoting, primeiro crie uma tabela girada que inclui quatro colunas, para trimestres do ano: O conteúdo de tabelas é semelhante ao seguinte: A operação UNPIVOT seguinte roda as colunas de trimestre em linhas. Para cada produto, haverá quatro linhas, uma para cada trimestre. Observe o uso de INCLUDE NULL neste exemplo. Você também pode usar EXCLUDE NULLS. Que é a configuração padrão. Além disso, você também pode unpivot usando duas colunas, como no seguinte: Wildcard e subconsulta girando com operações XML Se você quiser usar um argumento curinga ou subconsulta em suas colunas giratórias, você pode fazer isso com sintaxe XML PIVOT. Com PIVOT XML, a saída da operação é formatada corretamente XML. O exemplo a seguir ilustra o uso da palavra-chave curinga, ANY. Ele fornece XML que inclui todos os valores de canal em salesview: Note que a palavra chave ANY está disponível em operações PIVOT apenas como parte de uma operação XML. Esta saída inclui dados para casos em que o canal existe no conjunto de dados. Observe também que as funções de agregação devem especificar uma cláusula GROUP BY para retornar vários valores, mas a articulação não contém uma cláusula GROUP BY explícita. Em vez disso, o pivotclause executa um GROUP BY implícito. O exemplo a seguir ilustra o uso de uma subconsulta. Ele exibe XML que inclui todos os valores de canal e os dados de vendas correspondentes a cada canal: A saída densifica os dados para incluir todos os canais possíveis para cada produto. 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 exibir os dados em forma densa, com linhas para todas as combinações de valores de dimensão exibidos mesmo quando não existem dados de fato para eles. Por exemplo, se um produto não foi vendido durante um período de tempo específico, você ainda pode querer ver o produto para esse período de tempo com valor de venda zero próximo a ele. Além disso, os cálculos de séries temporais podem ser realizados com maior facilidade quando os dados são densos ao longo da dimensão temporal. 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 o uso das funções de janelas analíticas com deslocamentos físicos. A densificação de dados é o processo de conversão de dados escassos em forma densa. Para superar o problema de sparsity, você pode usar uma junção externa particionada para preencher as lacunas em uma série de tempo ou em qualquer outra dimensão. Essa junção estende a sintaxe de junçã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 especificada na cláusula PARTITION BY. O resultado de uma junção externa particionada é um UNION das junções externas de cada uma das partições na tabela logicamente particionada com a tabela no 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 de tempo. A maioria dos exemplos aqui se concentram na dimensão de tempo porque é a dimensão mais freqüentemente usada como base para comparações. Sintaxe de associação de partição A sintaxe para partição externa particionada estende a cláusula ANSI SQL JOIN com a frase PARTITION BY seguida por uma lista de expressão. As expressões na lista especificam o grupo ao qual a associação externa é aplicada. A seguir estão as duas formas de sintaxe normalmente usadas para partição externa particionada: Observe que FULL OUTER JOIN não é suportado com uma junção externa particionada. Exemplo 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 do ano para o produto Bounce para as semanas 20-30 em 2000 e 2001: Neste exemplo, nós Seria esperar 22 filas de dados (11 semanas cada de 2 anos), se os dados eram densos. No entanto, obtemos apenas 18 linhas porque as semanas 25 e 26 estão ausentes em 2000 e as semanas 26 e 28 em 2001. Preenchendo lacunas em dados Podemos tomar os dados esparsos da consulta anterior e fazer uma junção externa particionada com um conjunto denso de Tempo. Na consulta a seguir, alias nossa consulta original como v e selecionamos dados da tabela de tempos, que alias como t. Aqui recuperamos 22 linhas porque não há lacunas na série. As quatro linhas adicionadas têm cada 0 como seu valor de Vendas definido como 0 usando a função NVL. Observe que, nesta consulta, uma condição WHERE foi colocada para semanas entre 20 e 30 na exibição em linha para a dimensão de tempo. Isso foi introduzido para manter o conjunto de resultados pequeno. Preenchendo lacunas em duas dimensões Os dados N-dimensionais são exibidos tipicamente como uma aba cruzada bidimensional densa de (n - 2) dimensões de página. Isso requer que todos os valores de dimensão para as duas dimensões que aparecem na guia cruzada sejam preenchidos. O seguinte é outro exemplo onde a capacidade de união externa particionada pode ser usada para preencher as lacunas em duas dimensões: Nesta consulta, a cláusula WITH V1 resume os dados de vendas ao nível do produto, país e ano. Este resultado é esparso, mas os usuários podem querer ver todas as combinações de país, ano para cada produto. Para conseguir isso, nós tomamos cada partição de v1 com base nos valores do produto e outer juntá-lo primeiro na dimensão do país. Isso nos dará todos os valores de país para cada produto. Em seguida, tomamos esse resultado e particioná-lo nos valores do produto e do país e, em seguida, associá-lo externamente à dimensão temporal. Isso nos dará todos os valores de tempo para cada combinação de produto e país. Preenchendo lacunas 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: armazena apenas 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 tem 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 partição externa particionada. Além disso, para as linhas recém-inseridas de períodos de tempo ausentes, os usuários podem querer ver os valores para a quantidade de unidades de coluna a serem transferidos do período de tempo existente mais recente. O último pode ser realizado usando a função de janela analítica LASTVALUE valor. Aqui está a consulta ea saída desejada: A consulta interna calcula uma junção externa particionada no tempo dentro de cada produto. A consulta interna densifica os dados na dimensão de tempo (significando que a dimensão de tempo agora terá uma linha para cada dia da semana). No entanto, a quantidade da coluna de medida terá nulos para as linhas recém-adicionadas (veja a saída na quantidade da coluna nos resultados a seguir.) A consulta externa usa a função analítica LASTVALUE. Aplicando esta função particiona os dados por produto e ordena os dados no (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 coluna repeatedquantity em A seguinte saída: Computação de valores de dados para preencher lacunas Os exemplos na seção anterior ilustram como usar partição externa particionada para preencher lacunas em uma ou mais dimensões. No entanto, os conjuntos de resultados produzidos por junção externa particionada têm valores nulos para colunas que não estão incluídas no A lista PARTITION BY Tipicamente, estas são colunas de medida. Os usuários podem fazer uso de funções SQL analíticas para substituir esses valores nulos por um valor não nulo. Por exemplo, as seguintes q Uery calcula os totais mensais para produtos de 64MB de cartão de memória e discos DVD-R (IDs 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 ausentes, ele usa a função SQL analítica AVG para calcular as vendas e as unidades como sendo a média dos meses em que o produto foi vendido. Se estiver trabalhando no SQLPlus, os dois comandos a seguir encaixam os cabeçalhos das colunas para maior legibilidade dos resultados: Cálculos de séries temporais na densificação de dados densificados não é apenas para fins de relatório. Ele também permite certos tipos de cálculos, especialmente, cálculos de séries temporais. Os cálculos de séries temporais são mais fáceis quando os dados são densos ao longo da dimensão temporal. Os dados densos têm um número consistente de linhas para cada período de tempo que, por sua vez, tornam simples o uso de funções de janela analíticas com deslocamentos físicos. 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.