Mover Média Em Sql Query

Introdução Com o lançamento do SQL Server 2016 Service Pack 1, a tecnologia In-Memory ColumnStore também está disponível nas Edições Standard, Web e Even Express e LocalDB. Além do benefício de apenas uma base de código para manter, essa alteração na política também se tornará uma poupança de espaço de armazenamento de disco clara devido às suas altas taxas de duplicação e compressão de dados e, por último, mas também, também é um desempenho de consulta ad hoc grave Booster A principal diferença entre os sabores SQL é a quantidade de energia e memória da CPU atribuída a tarefas como (re) construção do Índice Clustering ColumnStore. Por exemplo: com a edição padrão, um único núcleo (tempo máximo do processador 100 do processo sqlservr) está sendo usado e a consulta de um CCI acontece com um máximo de 2 CPUs (MAXDOP2), contra o alavancagem de todas as CPUs disponíveis na Enterprise Edition. Criando um Custer (Clustering ColumnStore Index) com o SQL Server 2016 Standard Edition: Criando um CCI com todos os 4 núcleos disponíveis com o SQL Server 2016 Enterprise Edition: os tempos de base para carregar 7,2 GB 60 milhões de linhas de um único arquivo TPCH lineItem não mostra muito de Uma diferença entre os sabores quando Bulk inserindo os dados diretamente em uma tabela de heap ou uma tabela com um CCI, a diferença fica clara quando comparamos o tempo necessário para construir um CCI em uma tabela de pilha ou reconstruindo um CCI: para resumir, o absoluto A maneira mais rápida de ter dados disponíveis em uma tabela com um Índice de ColumnStore Clustered é: carregar em heap construir o CCI depois com SQL 2016 Ent. Ed. Carga direta no CCI Para tabelas com um Índice de ColumnStore Clustered já criado, certifique-se de transmitir diretamente para os Grupos de Linha Comprimidos para maximizar a taxa de transferência. Para fazer isso, o tamanho do lote Inserir deve ser igual ou maior do que 100K linhas (102400 para ser preciso). Os lotes menores serão escritos em tabelas compactadas do delta store antes de serem movidas pela tupla em seus segmentos de grupo de linhas compactados finais, o que significa que o SQL Server deve tocar os dados duas vezes: existem várias opções para carregar dados e vamos superar o uso mais utilizado Como o comando Bulk Insert, BCP e SSIS. Permite ver o que é necessário para obter o melhor desempenho e como monitorar 1) Inserção em massa de T-SQL Vamos começar com o comando BULK INSERT: Verificar o progresso da carga de dados Para verificar o número de linhas que já foram carregadas no CCI, mesmo quando A opção Table Lock está sendo usada, consulte um novo dmv chamado sys. dmdbcolumnstorerowgroupphysicalstats: Este DMV também revelará os possíveis estados do Grupo de Recursos com mais detalhes durante o carregamento. Existem quatro estados do grupo de linhas possíveis ao carregar dados. Quando você vê o estado INVISBILE, como na imagem abaixo, significa que os dados estão sendo compactados em um RowGroup. 0: INVISIBLE (RowGroup está em processo de construção a partir de dados na loja delta) 1: OPEN160160160160160160160 (RowGroup está aceitando novos registros) 2: CLOSED160160160 (o RowGroup está preenchido, mas ainda não está compactado pelo processo de movimentação de tupla) 3: COMPRESSED160 ( RowGroup é preenchido e compactado). 4 TOMBSTONE160 (RowGroup está pronto para ser lixo coletado e removido) Ao especificar o tamanho do lote com um valor de 102400 ou superior, você alcançará o máximo desempenho e os dados serão transmitidos e comprimidos diretamente em seu RG final, este comportamento aparecerá como COMPRIMIDO. Você também pode verificar um DMV que foi introduzido com o SQL2014 para verificar o Estado RowGroup, que é o sys. columnstorerowgroups DMV: Test Result Bulk inserindo dados em uma tabela com CCI através do comando Bulk Insert pode ser melhorado, adicionando o Batchsize102400 e TABLOCK opções. Isso traz uma melhoria de 8 no throughput. 2) BCP. exe O utilitário BCP ainda está sendo usado bastante em muitos ambientes de produção, portanto vale a pena verificá-lo rapidamente: por padrão, o BCP fornece 1000 linhas no momento ao SQL Server. O tempo necessário para carregar 7,2 GB de dados via BCP: 530 segundos. Ou160 113K rowssec O estado RowGroup mostra NVISIBLE, o que significa que, com as configurações padrão, a Delta Store está sendo usada. Para certificar-se de que o comando BCP transmite os dados diretamente nos RGs compactados, você deve adicionar a opção batchsize b com um valor de pelo menos 102400. Eu executei vários testes com tamanhos de lotes maiores: até 1048576, mas o 102400 me deu o melhor resultado. BCP DB. dbo. LINEITEMCCI em F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock O estado do RowGroup agora mostra COMPRIMIDO, o que significa que ignoramos a loja Delta e os fluxos de dados nos RGs compactados: Resultado: o BCP Completado em 457 segundos ou linhas de 133K por segundo ou durante o teste percebi que as configurações padrão do SSIS 2016 usam tamanhos de buffer de memória que também podem potencialmente limitar o tamanho do lote para se tornar menos de 100K linhas. No exemplo abaixo, você vê que os dados desembarcaram nas lojas delta: os estados RG são fechados e os campos deltastorehobiti são preenchidos, o que significa que as lojas delta são alavancadas. Este foi o momento de chegar e verificar com os meus colegas que felizmente perceberam isso e uma solução já existe (consulte: Capacidade de dimensionamento automático do Buffer de fluxo de dados beneficia o carregamento de dados no CCI). Para aproveitar ao máximo as capacidades de transmissão de CCI, é necessário aumentar as configurações do MaxRows do BufferSize da memória padrão: mude estes em valores maiores de 10x: 8211 DefaultMaxBufferRows de 10000 para 1024000 e o mais importante: 8211 DefaultBufferSize de 10485760 para 104857600. Observação: a nova configuração AutoAdjustBufferSize deve ser definida como True quando você carrega linhas de dados muito largas. Altere também os valores para o adaptador de destino: 8211 Linhas por lote: 160 de nenhum em 102400 8211 Tamanho máximo de inserção de inserção: de 2147483647 a 102400 A paridade característica introduzida com o SQL Server 2016 SP1 abre uma nova gama de possibilidades para se beneficiar de Esperançosamente As orientações anteriores ajudam você a maximizar o desempenho de Inserção em massa, BCP e SSIS ao carregar dados em um Índice de ColumnStore em Cluster. O que será a maneira mais rápida e rápida de carregar dados de um arquivo plano em uma tabela no SQL Server 2016 Muito mudou desde a minha inicial Publique neste tópico há muitos anos, como a introdução de tabelas otimizadas em memória e os índices de tabelas de Columnstore atualizáveis. Além disso, a lista de veículos de transporte de dados para escolher está crescendo: além do BCP, o comando T-SQL Bulk Insert, SSIS como ferramenta ETL e PowerShell, alguns novos foram adicionados, como PolyBase, R Script Externo ou ADF. Nesta publicação, vou começar com a verificação de quanto mais rápido o novo amplo durável, as tabelas de memória não duráveis ​​são a configuração da linha de base. Para esses testes, estou usando uma VM padrão DS4V2 Azure com 8 núcleos28 GB de RAM e 2 volumes de disco rígido com armazenamento em cache do host RW habilitado. (Ambos os Luns fornecem 275 MBsec RW throughput embora a GUI indique um limite de 60MBsec). Eu gerei um único arquivo plano de 60 milhões de linhas7.2 gigabytes TPCH lineitem como dados para carregar. Como linha de base para uso para comparação, usaremos o tempo necessário para carregar o arquivo em uma tabela Heap: Este comando regular de Inserção em massa completa em 7 minutos com uma média de 143K rowssec. Habilitando o banco de dados de teste para tabelas otimizadas de memória As tabelas introduzidas em memória (em SQL20142016 Enterprise amp Developer Edition) foram projetadas para OLTP muito rápido com muitas transações pequenas e alta concorrência, que é um tipo de carga de trabalho completamente diferente como inserção em massa, mas apenas Por curiosidades, experimente Existem 2 tipos de mesas em memória: tabelas duráveis ​​e não duráveis. Os duráveis ​​irão persistir dados no disco, os não duráveis ​​não serão. Para habilitar esta opção, devemos fazer algumas tarefas domésticas e atribuir um volume de disco rápido para hospedar esses arquivos. Em primeiro lugar, altere o banco de dados para habilitar a opção Contém MEMORYOPTIMIZEDDATA, seguida de adicionar uma localização de arquivo e um grupo de arquivos que conterão as tabelas otimizadas para a memória: a terceira coisa a fazer é adicionar um pool de memória separado à instância do SQL Server para que possa manter tudo Os dados que vamos carregar em tabelas na memória separadas do seu pool de memória padrão: encadernando um banco de dados a um pool de memória. As etapas para definir um pool de memória separado e para vincular um banco de dados estão listadas abaixo: conjuntos de memória extra são gerenciados através do Governador de recursos SQL. O 4º e último passo é vincular o banco de dados de teste ao novo pool de memória com o comando sys. spxtpbinddbresourcepool.160 Para que a ligação se torne efetiva, devemos retirar o banco de dados offline e trazê-lo novamente. Uma vez ligado, podemos alterar dinamicamente a quantidade de memória atribuída ao seu pool através do comando ALTER RESOURCE POOL PoolHK WITH (MAXMEMORYPERCENT 80). Bulk Insert in Durable In-Memory table Agora, estamos todos configurados com a opção In-memory habilitada, podemos criar uma tabela na memória. Toda tabela de otimização de memória deve ter pelo menos um índice (um índice Range ou Hash) que são completamente (re) integrados na memória e nunca são armazenados no disco. Uma tabela durável deve ter uma chave primária declarada, que pode ser suportada pelo índice requerido. Para suportar uma chave primária, adicionei uma coluna ROWID1 de rownumber adicional à tabela: Especificar um tamanho de lote de 1 (até 5) Milhões de linhas para o comando de inserção em massa ajuda a persistir dados no disco enquanto a inserção em massa está em andamento (em vez de salvar Tudo no final), minimizando a pressão da memória no pool de memória PookHK que criamos. A carga de dados na tabela durável em memória é completada em 5 minutos 28 segundos ou 183K Rowssec. Isso é bom, mas não muito mais rápido do que a nossa linha de base. Olhando para o sys. dmoswaitstats mostra que o waitstat número 1 é IMPPROVIOWAIT que ocorre quando o SQL Server espera que um IO de carga em massa termine. Olhando para o contador de desempenho Bulk Copy Rowssec e Disk Write Bytessec mostra os picos de descarga de disco de 275 MBsec uma vez que um lote entrou (os pontos verdes). Esse é o máximo do que o disco pode entregar, mas não explica tudo. Dado o menor ganho, estacionaremos este para futuras investigações. Monitorando o pool de memória Através do sys. dmresourcegovernorresourcepools dmv podemos verificar se a nossa tabela na memória alavanca o pool de memória PoolHK recém-criado: a saída mostra que este é o caso, os 7.2GB (alguns extra para o Rowid) foram descompactados carregados na memória PoolHk pool: se você tentar carregar mais dados do que a memória disponível para o pool, você receberá uma mensagem adequada como essa: a declaração foi encerrada. Msg 701, Nível 17, Estado 103, Linha 5 Não há memória do sistema insuficiente no pool de recursos 8216PookHK para executar esta consulta. Para procurar um nível mais profundo na alocação de espaço de memória em uma base de tabela por memória, você pode executar a seguinte consulta (tirada do documento SQL Server In-Memory OLTP Internals para o documento SQL Server 2016): os dados que acabamos de carregar são armazenados como um Estrutura de varheap com um índice de hash: até agora tão bom Agora vamos passar e verificar a forma como o encenação em uma tabela não durável executa Inserção em massa na tabela In-Memory não durável Para tabelas IMND, não precisamos de uma chave primária, então nós apenas Adicione e índice de Hash não agrupado e defina DURABILITY SCHEMAONLY. A inserção em massa O carregamento de dados na tabela não durável termina dentro de 3 minutos com uma taxa de transferência de 335K rowssec (vs 7 minutos). Esta é 2,3 vezes mais rápida que a inserção em uma tabela de heap. Para o encaminhamento de dados, esta definitivamente é uma vitória rápida SSIS Single Bulk Insert em uma tabela não durável Tradicionalmente, o SSIS é a maneira mais rápida de carregar um arquivo rapidamente no SQL Server porque o SSIS tratará todo o pré-processamento de dados para que o mecanismo do SQL Server possa Gastar sua CPU na persistência dos dados no disco. Isso ainda será o caso ao inserir os dados em uma tabela não durável. Abaixo de um resumo dos testes que executei com o SSIS para esta publicação: a opção SSIS Fastparse e160 as configurações DefaultBufferMaxRows e DefaultBufferSize são os principais impulsionadores de desempenho. Além disso, o provedor OLE DB (SQLOLEDB.1) nativo executa um pouco melhor do que o SQL Native Client (SQLNCLI11.1). Quando você executa SSIS e SQL Server lado a lado, o aumento do tamanho do pacote de rede não é necessário.160160 Resultado líquido: um pacote SSIS básico que lê uma fonte de arquivo simples e grava os dados diretamente na tabela não durável por meio de um destino OLE DB Executa semelhante ao comando Inserção em massa em uma tabela IMND: as linhas de 60 milhões são carregadas em 2minutos 59seconds ou 335K rowssec, idênticos ao comando Bulk insert. SSIS com Distribuidor de Dados Balanced Mas wait8230160 as mesas em memória são projetadas para trabalhar com o bloqueio do amplificador sem bloqueio, então isso significa que podemos carregar dados também através de múltiplos fluxos. Isso é fácil de alcançar com o SSIS o Balanced Data Distributor irá trazer apenas isso (o BDD Está listado na seção comum da caixa de ferramentas do SSIS) Adicionando o componente BDD e inserindo os dados na mesma tabela não durável com 3 fluxos fornece a melhor taxa de transferência: agora somos até 526000 Rowssec Olhando para esta linha muito plana, com apenas 160 de tempo de CPU usado pelo SQLServer, parece que estamos atingindo algum gargalo: tentei ser criativo, alavancando a função de módulo e adicionando mais 2 fluxos de dados dentro do pacote (cada processamento 13 dos dados) 160, mas isso não está melhorando Muito (1 min52sec), então um ótimo tópico para investigar um futuro post160160 A opção de tabela In-Memory Non-Durable traz alguma melhoria de desempenho séria para o armazenamento temporário de dados 1.5x mais rápido com um inserto em massa T e até 3,6 vezes mais rápido com o SSIS. Esta opção, principalmente projetada para acelerar o OLTP, também pode fazer uma grande diferença para encolher sua janela de lote rapidamente (Para continuar) 22 SQL para Análise e Relatórios A Oracle aprimorou os recursos de processamento analítico 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 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 o último valor em um grupo ordenado. Outras melhorias 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 junçã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 as dimensões, por exemplo, as 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. Esses recursos 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 é 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 estágios. 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 conjuntos de resultados de 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 particionado 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 final deslizando 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 os resultados apenas para as linhas disponíveis, em vez de avisá-lo de que os resultados não são o que deseja. Ao usar as funções da janela, a linha atual é 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 fim 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 Janela deslizante Exemplo Classificação, vedação e funções de relatórios 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 da lista de produtos. Os cálculos são agrupados pelo canal de vendas. Os valores INCLINAÇÃO. INTCPT. RSQR são declive, 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í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 . Ele contém a função estatística descritiva RESUMO juntamente com funções para suportar a distribuição adequada. 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ções normais, uniformes, Weibull, Poisson e exponenciais. 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 inclinação. Este cálculo mede se um conjunto de dados tiver uma distribuição desequilibrada sobre o seu significado. Ele irá dizer se uma das colunas da distribuição é significativamente maior do 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 em SQL. Mesmo o exemplo anterior, a estatística de inclinação, pode ser criada usando padrão, embora longo, SQL. Operações pivotantes O dada 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 em 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 instrução ilustra um pivô típico de várias colunas: Observe que este exemplo especifica uma coluna IN com várias colunas com cabeçalhos de coluna projetados para coincidir com os membros da Lista 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. Ou seja, 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 Q2 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 a não figura, 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 a seguir: Wildcard e Subquery Pivoting com operações XML Se você deseja usar um argumento curinga ou subconsulta em suas colunas giratórias, você pode fazê-lo com a sintaxe XML PIVOT. Com o PIVOT XML, a saída da operação é formatada corretamente XML. O exemplo a seguir ilustra o uso da palavra-chave do curinga, QUALQUER. Ele exibe XML que inclui todos os valores de canal na exibição de vendas: Observe que a palavra-chave ANY está disponível em operações PIVOT somente 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 ver os dados em forma densa, com linhas para todos os valores de cota 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 de janela analítica 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 de sparsity, você pode usar uma junção externa particionada para preencher as lacunas em uma série temporal ou qualquer outra dimensão. Tal associação amplia a sintaxe de associação externa convencional aplicando a junção externa a cada partição lógica definida em uma consulta. O Oracle logicamente particiona as linhas em sua consulta com base na expressão que você especificou 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, e 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 usada como base para comparações. Sintaxe de associação de partição A sintaxe para associaçã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 junção externa particionada. Exemplo de dados dispersos 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 acumulado do ano para o produto Bounce nas semanas 20 a 30 em 2000 e 2001: neste exemplo, nós Esperaria 22 linhas de dados (11 semanas a cada 2 anos) se os dados fossem densos. No entanto, recebemos 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 nós 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 cruzada 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. Esse 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, levamos 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 produto e país. Lacunas de preenchimento em uma tabela de inventário Uma tabela de estoque 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 (o que significa 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 recentemente 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 time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following q uery computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands wraps the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, let us first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command wraps the column headings for greater readability of results: In the FROM clause of the inline view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The inline view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001. and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an inline view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many analytical SQL tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 23, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (a nalysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way.