Moving Average Sql Oracle

Funções SQL As funções SQL são incorporadas no Oracle Database e estão disponíveis para serem usadas 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 nula. As únicas funções SQL que não seguem necessariamente esse 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 configurado para LINGUISTIC para seu 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 como LINGUISTIC. Então as funções são interpretadas sem considerar a configuração NLSSORT. NLSSORT pode ser definido explicitamente. Se não for definido explicitamente, ele é derivado de NLSLANGUAGE. Consulte o Guia de Suporte de Globalização de Banco de Dados Oracle para obter mais informações sobre essas configurações. Nos diagramas de sintaxe das funções SQL, os argumentos são indicados pelos seus tipos de dados. Quando a função de parâmetro aparece 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 as funções SQL às colunas LOB, o banco de dados Oracle cria LOBs temporários durante o processamento SQL e PLSQL. Você deve garantir que a cota temporária do tablespace seja suficiente para armazenar estes LOBs temporários para sua aplicação. Funções definidas pelo usuário para 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 Oracle para obter informações sobre funções usadas com o Oracle Text Oracle Data Mining Application Developers Guide para obter informações sobre funções de conjunto de itens freqüentes usadas com Oracle Data Mining A sintaxe mostrando as categorias Das seguintes funções: as seções que se seguem exibem 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 embutidas são descritas em ordem alfabética. Funções de uma única linha As funções de uma linha retornam uma única linha de resultados para cada linha de uma tabela ou visualização consultada. Essas funções podem aparecer em listas selecionadas, cláusulas WHERE, 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 COS. COSH. 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 Retornando valores de caracteres As funções de caracteres que retornam valores de caracteres retornam os valores dos seguintes tipos de dados, a menos que seja documentado de outra forma: se o argumento de entrada for CHAR ou VARCHAR2. Então o valor retornado é VARCHAR2. Se o argumento de entrada for 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 o trunca e retorna o resultado sem uma mensagem de erro. Para as funções que retornam os valores do CLOB, se o comprimento dos valores de retorno exceder o limite, o Oracle levanta um erro e não retorna dados. As funções de personagem que retornam valores de caracteres são: Funções de caracteres NLS As funções de caracteres NLS retornam informações sobre o conjunto de caracteres. As funções de caracteres NLS são: Funções de caracteres Retornando valores de número As funções de caracteres que retornam valores de número podem levar como argumento qualquer tipo de tipo de caractere. As funções de personagem que retornam os valores do número são: Funções de data de hora As funções de data de funcionamento funcionam na data (DATA), timestamp (TIMESTAMP. TIMESTAMP WITH TIME ZONE) e TIMESTAMP WITH LOCAL TIME ZONE) e intervalo (INTERVALO DIA PARA SEGUNDO. INTERVALO ANO PARA MÊS) Valores. Algumas das funções de data e hora foram projetadas para o tipo de dados Oracle DATE (ADDMONTHS. CURRENTDATE. LASTDAY. NEWTIME. E NEXTDAY). Se você fornecer um valor de carimbo de data / hora como seu argumento, o Oracle Database converte internamente o tipo de entrada para um valor DATE e retorna um valor DATA. 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 timestamp ou valores de intervalo. As funções de data e hora restantes foram projetadas para aceitar qualquer um dos três tipos de dados (data, timestamp e intervalo) e retornar o valor de um desses tipos. As funções de data e hora são: Funções de comparação geral As funções de comparação geral determinam o maior e menor valor de um conjunto de valores. As funções de comparação geral 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 das funções segue o tipo de dados da convenção TO 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 objetos grandes As funções de objetos grandes operam em LOBs. As funções de objetos grandes são: Funções de coleção As funções de coleção funcionam em tabelas aninhadas e varrays. As funções de coleta de 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 criados usando o pacote DBMSDATAMINING ou a API Oracle Data Mining Java. As funções de mineração de dados SQL são: Funções XML As funções XML operam 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 a saída de formatação, consulte o 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 a NULL As funções relacionadas ao NULL facilitam o processamento nulo. As funções relacionadas ao NULL são: Funções de ambiente e identificador As funções de ambiente e identificador fornecem informações sobre a instância e a sessão. Essas funções são: Funções de agregação As funções de agregação retornam uma única linha de resultados com base em grupos de linhas e não em linhas individuais. As funções agregadas podem aparecer nas listas selecionadas e nas cláusulas ORDER BY e HAVING. Eles são comumente usados ​​com a cláusula GROUP BY em uma instrução SELECT, onde o Oracle Database divide as linhas de uma tabela ou exibição consultada em grupos. Em uma consulta contendo 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 um desses. O Oracle aplica as funções agregadas a cada grupo de linhas e retorna uma única linha de resultados para cada grupo. Se você omitir a cláusula GROUP BY, o Oracle aplica funções agregadas na lista de seleção para todas as linhas da tabela ou visualização consultada. Você usa funções agregadas na cláusula HAVING para eliminar grupos da saída com base nos resultados das funções agregadas, e não nos valores das linhas individuais da tabela ou visualização consultada. Usando a Cláusula GROUP BY: Exemplos e a 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 tomam um único argumento aceitam essas cláusulas: DISTINCT faz com que uma função agregada considere Apenas valores distintos da expressão do argumento. Tudo causa uma função agregada para considerar todos os valores, incluindo todas as duplicatas. Por exemplo, a média DISTINCT de 1, 1, 1 e 3 é 2. A média ALL é 1.5. Se você não especifica nem, o padrão é ALL. Todas as funções agregadas, 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 nulo, mas retorna um número ou zero. Para todas as funções agregadas restantes, se o conjunto de dados não contém linhas ou contém apenas linhas com nulos como argumentos para a função agregada, a função retorna nula. 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 classificam como FIRST ou LAST em relação a uma determinada especificação de classificação. Consulte FIRST para obter mais informações. Você pode aniquilar funções agregadas. Por exemplo, o exemplo a seguir calcula a média dos salários máximos de todos os departamentos no esquema de exemplo hr: Este cálculo avalia o agregado interno (MAX (salário)) para cada grupo definido pela cláusula GROUP BY (departmentid) e 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 agregadas na medida em que retornam várias linhas para cada grupo. O grupo de linhas é chamado de janela e é definido pelo analítico. 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 um intervalo lógico, como o tempo. As funções analíticas são o último conjunto de operações realizadas em uma consulta, exceto para a cláusula ORDER BY final. Todas juntas e todas ONDE. GRUPO BY. E as cláusulas HAVING são concluídas antes das funções analíticas serem processadas. Portanto, as funções analíticas podem aparecer apenas na lista de seleção ou na opção ORDER BY. As funções analíticas são comumente usadas para calcular agregados cumulativos, moventes, centrados e de relatórios.22 SQL para Análise e Relatórios A Oracle aprimorou os recursos de processamento analítico do SQL, introduzindo uma nova família de funções SQL analíticas. Essas 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, porcentagem de classificação e N-tiles. Os cálculos da janela móvel permitem que você encontre agregações em movimento e cumulativas, como somas e médias. A análise da Laglead permite referências directas entre filas para que você possa calcular as mudanças de período a período. A análise Firstlast permite que você encontre o primeiro ou último valor em um grupo ordenado. Outros aprimoramentos no SQL incluem a expressão CASE e a junção externa particionada. As expressões CASE fornecem if-then logic úteis em muitas situações. A junção externa particionada é uma extensão da sintaxe de associação externa da ANSI que permite aos usuários densificar seletivamente certas dimensões enquanto mantém outras escassas. Isso permite que as ferramentas de relatório densifiquem seletivamente dimensões, por exemplo, aquelas que aparecem em seus relatórios de tabelas cruzadas, enquanto mantêm outras escassas. Para melhorar o desempenho, as funções analíticas podem ser paralelizadas: vários processos podem executar simultaneamente todas essas afirmações. Essas capacidades tornam os cálculos mais fáceis e eficientes, aumentando assim o desempenho, a escalabilidade e a simplicidade do banco de dados. As funções analíticas são classificadas como 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 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 22-1. Figura 22-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 22-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 22-2. Figura 22-2 Exemplo de Janela deslizante Classificar, Vincular e Reportagem Funções 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 expressa a quantidade vendida de um produto como uma função linear do preço de 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. 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ística descritiva 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 . 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. Agregados Definidos 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 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 visualizaçõ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 podem ser programadas usando um idioma 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 do objeto 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. Operações pivotantes O d ata retornado por consultas de inteligência de negócios é muitas vezes mais utilizável se apresentado em um formato crosstabular. O pivô da indicação SELECT permite que você escreva consultas de crosstabulation que rotem linhas em colunas, agregando dados no processo de rotação. Pivoting é uma técnica chave nos data warehouses. Nela, você transforma várias linhas de entrada em linhas menos e geralmente mais amplas no data warehouse. Ao girar, um operador de agregação é aplicado para cada item na lista de valores da coluna pivô. A coluna de pivô não pode conter uma expressão arbitrária. Se você precisa girar em uma expressão, então você deve alias a expressão em uma visualizaçã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: Pivote A seguinte declaração ilustra um pivô típico na coluna do canal: Observe que a saída criou quatro novas colunas alias , VENDAS DIRETAS. INTERNETES. CATALOGSALES. E TELESALES. Um para cada um dos valores de pivô. O resultado é uma soma. Se nenhum alias for fornecido, o título da coluna será os valores do IN-list. Pivote em múltiplas colunas Você pode girar em mais de uma coluna. A seguinte declaração ilustra um pivô típico de várias colunas: Observe que este exemplo especifica uma lista IN de várias colunas com cabeçalhos de coluna projetados para coincidir com os membros do Listado IN. Pivote: vários agregados 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 agregada, além de um sublinhado. Distinguir Nulos Gerados por PIVOT de Nulos em Dados de Origem Você pode distinguir entre valores nulos gerados pelo uso de PIVOT e aqueles que existem nos dados de origem. O exemplo a seguir ilustra nulos que o PIVOT gera. A seguinte consulta retorna linhas com 5 colunas, coluna prodid. E pivô colunas resultantes Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Para cada valor exclusivo de prodid. 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. Suponha que temos uma tabela de vendas2 da seguinte estrutura: do resultado, sabemos que para prodid 100, existem 2 linhas de vendas para o quarto trimestre. E 1 linha de vendas para o quarto trimestre 2 para prodid 200, há 1 linha de vendas para o quarto trimestre. E nenhuma linha de vendas para o trimestre 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 prodid 200 no quarto Q2. Operações de não-rotação Uma variável não invoca uma operação PIVOT. Em vez disso, ele gira dados de colunas em linhas. Se você estiver trabalhando com dados articulados, uma operação UNPIVOT não pode reverter as agregações que foram feitas pela PIVOT ou por qualquer outro meio. Para ilustrar o não gráfico, primeiro crie uma tabela giratória que inclua quatro colunas, para trimestres do ano: o conteúdo das tabelas é semelhante ao seguinte: A seguinte operação UNPIVOT gira as quartas colunas em linhas. Para cada produto, haverá quatro linhas, uma para cada trimestre. Observe o uso de INCLUDE NULLS neste exemplo. Você também pode usar EXCLUDE NULLS. Qual é a configuração padrão. Além disso, você também pode deslocalizar usando duas colunas, como se segue: Wildcard e Subquery Pivoting com Operações XML Se você quiser usar um argumento curinga ou subconsulta em suas colunas giratórias, você pode fazê-lo com a sintaxe XML PIVOT. Com PIVOT XML, a saída da operação é formatada de forma adequada XML. O exemplo a seguir ilustra o uso da palavra-chave do curinga, QUALQUER. Ele produz XML que inclui todos os valores do canal na exibição de vendas: Observe que a palavra-chave ANY está disponível nas 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 o pivotclause 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 produz XML que inclui todos os valores dos canais 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. Data Densification for Reporting Data normalmente é armazenada 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 união. Note que você pode usar esse tipo de junçã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, temos apenas 18 linhas porque as semanas 25 e 26 estão faltando em 2000 e as semanas 26 e 28 em 2001. Preenchendo lacunas em dados Podemos tirar dados escassos da consulta anterior e fazer uma junção externa particionada com um conjunto denso de Dados de tempo. Na consulta a seguir, alias nossa consulta original como v e selecionamos dados da tabela de horários, que alias como t. Aqui recuperamos 22 linhas porque não há lacunas na série. As quatro linhas adicionadas possuem 0 como seu valor de vendas definido como 0 usando a função NVL. Observe que nesta consulta, uma condição WHERE foi colocada por semanas entre 20 e 30 na visualização em linha para a dimensão temporal. Isso foi introduzido para manter o conjunto de resultados pequeno. Lacunas de preenchimento em duas dimensões Os dados de N-dimensional geralmente são exibidos como uma aba transversal bidimensional densa de (n - 2) dimensões da página. Isso requer que todos os valores de dimensão para as duas dimensões que aparecem na aba cruzada sejam preenchidos. O seguinte é outro exemplo em que a capacidade de junção externa particionada pode ser usada para preencher as lacunas em duas dimensões: nessa consulta, a cláusula de factoring WITH subquery A v1 resume os dados de vendas no produto, no país e no nível do ano. Este resultado é esparso, mas os usuários podem querer ver todas as combinações país, ano para cada produto. Para conseguir isso, tomamos cada partição de v1 com base nos valores dos produtos e junte-a externamente na dimensão do país primeiro. Isso nos dará todos os valores do país para cada produto. Em seguida, tomamos esse resultado e particionamos nos valores do produto e do país e, em seguida, juntamos-o na dimensão do tempo. Isso nos dará todos os valores de tempo para cada combinação de produtos e países. Lacunas de preenchimento em uma tabela de inventário Uma tabela de inventário normalmente rastreia a quantidade de unidades disponíveis para vários produtos. Esta tabela é esparsa: apenas armazena uma linha para um produto quando há um evento. Para uma tabela de vendas, o evento é uma venda, e para a tabela de inventário, o evento é uma alteração na quantidade disponível para um produto. Por exemplo, considere a seguinte tabela de inventário: A tabela de inventário agora possui as seguintes linhas: Para fins de relatório, os usuários podem querer ver esses dados de inventário de forma diferente. Por exemplo, eles podem querer ver todos os valores de tempo para cada produto. Isso pode ser feito usando a junção externa particionada. Além disso, para as linhas recém-inseridas de períodos de tempo faltantes, os usuários podem querer ver os valores da quantidade de unidades de coluna a serem transferidas do período de tempo existente mais recente. O último pode ser realizado usando o valor LASTVALUE da função de janela analítica. Aqui está a consulta e a saída desejada: a consulta interna computa uma junção externa particionada no tempo dentro de cada produto. A consulta interna densifica os dados na dimensão do tempo (significando que a dimensão do tempo agora terá uma linha para cada dia da semana). No entanto, a quantidade de coluna de medida terá nulos para as linhas recém-adicionadas (veja a saída na quantidade de coluna nos seguintes resultados. A consulta externa usa a função analítica LASTVALUE. Aplicando esta função, particiona os dados por produto e ordena os dados no Coluna de dimensão de tempo (timeid). Para cada linha, a função encontra o último valor não nulo na janela devido à opção IGNORE NULLS, que você pode usar com LASTVALUE e FIRSTVALUE. Vemos a saída desejada na quantidade repetida da coluna em A seguinte saída: Computação de valores de dados para preencher lacunas Os exemplos na seção anterior ilustram como usar a junção externa particionada para preencher lacunas em uma ou mais dimensões. No entanto, os conjuntos de resultados produzidos por junção externa particionada possuem valores nulos para colunas que não estão incluídas A lista PARTITION BY. Normalmente, estas são colunas de medidas. Os usuários podem usar funções SQL analíticas para substituir esses valores nulos por um valor não nulo. Por exemplo, o seguinte q Utile calcula totais mensais para produtos de 64MB de cartão de memória e discos DVD-R (ID de produto 122 e 136) para o ano 2000. Ele usa partição externa particionada para densificar dados para todos os meses. Para os meses que faltam, ele usa a função SQL analítica AVG para calcular as vendas e as unidades como a média dos meses em que o produto foi vendido. Se estiver trabalhando no SQLPlus, os dois comandos a seguir envolver 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. 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 seguinte comando envolve os cabeçalhos das colunas para 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 21, 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, observe que limitamos a consulta apenas a dois produtos para manter o tempo de processamento curto. Passo 4 Crie a consulta de comparação Nós estabelecemos o cenário para a nossa consulta de comparação. Podemos obter cálculos de comparação de período a período em todos os níveis de tempo. Ele requer a aplicação de funções analíticas a um cubo hierárquico com dados densos ao longo da dimensão temporal. Alguns dos cálculos que podemos alcançar para cada nível de tempo são: Soma das vendas para o período anterior em todos os níveis de tempo. Variação nas vendas ao longo do período anterior. Soma das vendas no mesmo período de um ano atrás em todos os níveis de tempo. Variação nas vendas em relação ao mesmo período do ano passado. O seguinte exemplo executa todos os quatro desses cálculos. Ele usa uma junção externa particionada das vistas cubeprodtime e edgetime para criar uma visão em linha de dados densos chamados densecubeprodtime. A consulta então usa a função LAG da mesma maneira que o exemplo anterior de nível único. A cláusula WHERE externa especifica o tempo em três níveis: os dias de agosto de 2001, todo o mês e todo o terceiro trimestre de 2001. Observe que as últimas duas linhas dos resultados contêm as agregações de nível de mês e quarto. Observe que fazer Os resultados são mais fáceis de ler se você estiver usando o SQLPlus, os cabeçalhos das colunas devem ser ajustados com os seguintes comandos. Os comandos dobrarão os cabeçalhos das colunas para reduzir o comprimento da linha: Aqui está a consulta que compara as vendas atuais com vendas anteriores e ano anterior: a primeira função LAG (salespriorperiod) divide os dados no gidp. gato. Subcat. Prod. Gidt e ordena as linhas em todas as colunas de dimensão do tempo. Obtém o valor de vendas do período anterior passando um deslocamento de 1. A segunda função LAG (salessameperiodprioryprioryear) particiona os dados em colunas adicionais qtrnum. Monnum. E daynum e ordena-lo em ano para que, com um deslocamento de 1, ele pode calcular as vendas do ano passado no mesmo período. A cláusula SELECT mais externa calcula as variações. Criando um membro personalizado em uma dimensão: Exemplo Em muitas tarefas SQL analíticas, é útil definir membros personalizados em uma dimensão. Por exemplo, você pode definir um período de tempo especializado para análises. Você pode usar uma junção externa particionada para adicionar temporariamente um membro a uma dimensão. Observe que a nova cláusula SQL MODEL é adequada para criar cenários mais complexos envolvendo novos membros em dimensões. Consulte o Capítulo 23, SQL for Modeling para obter mais informações sobre este tópico. Como um exemplo de uma tarefa, e se quisermos definir um novo membro para a nossa dimensão de tempo, queremos criar um 13º membro do nível do mês em nossa dimensão de tempo. Este 13º mês é definido como o somatório das vendas para cada produto no primeiro mês de cada trimestre do ano de 2001. A solução tem duas etapas. Note que vamos construir esta solução usando as visualizações e tabelas criadas no exemplo anterior. São necessárias duas etapas. Primeiro, crie uma vista com o novo membro adicionado à dimensão apropriada. A vista usa uma operação UNION ALL para adicionar o novo membro. Para consultar usando o membro personalizado, use uma expressão CASE e uma junção externa particionada. O nosso novo membro para a dimensão do tempo é criado com a seguinte visão: nesta declaração, o timecão de exibição é definido executando um UNION ALL da exibição edgetime (definido no exemplo anterior) e o 13º mês definido pelo usuário. O valor gidt de 8 foi escolhido para diferenciar o membro personalizado dos membros padrão. O UNION ALL especifica os atributos para um membro do 13º mês fazendo um SELECT da tabela DUAL. Observe que o ID do agrupamento, coluna gidt. É definido como 8 e o número do trimestre é definido como 5. Então, o segundo passo é usar uma visualização em linha da consulta para executar uma junção externa particionada de cubeprodtime com timec. Esta etapa cria dados de vendas para o 13º mês em cada nível de agregação de produtos. Na consulta principal, a função analítica SUM é usada com uma expressão CASE para calcular o 13º mês, que é definido como o somatório das vendas de primeiros meses de cada trimestre. A função SUM usa um CASO para limitar os dados aos meses 1, 4, 7 e 10 dentro de cada ano. Devido ao pequeno conjunto de dados, com apenas 2 produtos, os valores cumulativos dos resultados são necessariamente repetições de agregações de nível inferior. Para um conjunto mais realista de valores cumulativos, você pode incluir mais produtos das subcategorias Game Console e Y Box Games na visualização materializada subjacente. Diversas capacidades de análise e relatórios Esta seção ilustra os seguintes recursos analíticos adicionais: WIDTHBUCKET Função Para uma determinada expressão, a função WIDTHBUCKET retorna o número do 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 22-24. 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). 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.