Mais

Seleção de referência na consulta SQL (cláusula where) com ArcPy?

Seleção de referência na consulta SQL (cláusula where) com ArcPy?


Eu continuo levantando erros com minha where_clause na 9ª linha deste script. FID é um campo de ID de objeto e quero que o cursor selecione qualquer linha para a qual está apontando no momento. Não sei como fazer isso com a sintaxe adequada - alguma ideia? Estou executando isso no ArcGIS Desktop 10.3.

Este é o erro:

Erro de tempo de execução Traceback (última chamada mais recente): Arquivo "", linha 27, em Arquivo "c: arquivos de programas (x86) arcgis desktop10.3 arcpy arcpy management.py", linha 7211, em SelectLayerByAttribute raise e ExecuteError: ERROR 000358: Expressão inválida falha ao executar (SelectLayerByAttribute).

#Faça uma camada fora do FC arcpy.MakeFeatureLayer_management original (fc, 'Sales_lyr') #Use um cursor de atualização quando houver um valor que você precise alterar na tabela de atributos com arcpy.da.UpdateCursor ('Sales_lyr', [ "BOROUGH", "FID", "COUNT"]) como cursor: #Para cada linha do cursor, faça o seguinte para a linha no cursor: #Selecione a linha atual no UpdateCursor arcpy.SelectLayerByAttribute_management ('Sales_lyr', "NEW_SELECTION "," FID IN ("+ str (linha [1]) +") ")

O problema aqui é que você converteu o valor FID (tipo de campo objectID) em string que deve ser um valor numérico dentro da expressão, tente o seguinte:

com arcpy.da.UpdateCursor ('Sales_lyr', ["BOROUGH", "FID", "COUNT"]) como cursor: para linha no cursor: expressão = '"FID" = {}'. formato (linha [1] ) arcpy.SelectLayerByAttribute_management ('Sales_lyr', "NEW_SELECTION", expressão)

A razão para este erro é que as instruções SQL SELECT são logicamente * processado na seguinte ordem:

A PARTIR DE : seleção de uma tabela ou várias JOINed e todas as combinações de linhas que correspondem às condições ON.

ONDE : as condições são avaliadas e as linhas que não correspondem são removidas.

GRUPO POR : as linhas são agrupadas (e cada grupo reduz para uma linha)

TENDO : as condições são avaliadas e as linhas que não correspondem são removidas.

SELECIONE : lista de colunas é avaliada.

DISTINTO : linhas duplicadas são removidas (se for uma instrução SELECT DISTINCT)

UNIÃO , EXCETO , INTERSECT : a ação desse operando é executada nas linhas de instruções sub-SELECT. Por exemplo, se for uma UNION, todas as linhas serão reunidas (e as duplicatas eliminadas, a menos que seja uma UNION ALL) depois que todas as instruções sub-SELECT forem avaliadas. Da mesma forma para os casos EXCEPT ou INTERSECT.

ORDENAR POR : as linhas são ordenadas.

Portanto, você não pode usar na cláusula WHERE, algo que ainda não foi preenchido ou calculado. Veja também esta pergunta: oracle-sql-clause-Evaluation-order

* logicamente processado: Observe que os motores de banco de dados também podem escolher outra ordem de avaliação para uma consulta (e é isso que eles costumam fazer!). A única restrição é que o os resultados devem ser os mesmos que se a ordem acima fosse usada.


Sim, algo como o abaixo.

Ou, para usar o exemplo de cláusulas WHERE que você especificou, que tal o seguinte?

Depende, mas provavelmente sim (com alguns possíveis problemas de desempenho devido a diferentes planos de consulta sendo armazenados em cache). Você pode fornecer um pouco mais de detalhes sobre quais são exatamente os tipos de valores inseridos no parâmetro? Todos os 3 valores serão datas, o próprio parâmetro é um valor de string que você precisa converter para o tipo apropriado e sua consulta está em um procedimento armazenado?


Tabelas de ligação

Embora você possa ter raros casos em que você projeta um relatório com base em apenas uma tabela de banco de dados, você geralmente precisa usar pelo menos duas, e muitas vezes mais, tabelas em seu relatório, porque a maioria dos bancos de dados relacionais modernos são normalizado. A normalização do banco de dados se refere à divisão de informações repetitivas do banco de dados em tabelas separadas no banco de dados por razões de eficiência e manutenção. Considere a seguinte tabela de funcionários:

Embora isso seja um ambiente de relatório simples, porque você não precisa escolher mais de uma tabela para imprimir uma lista de funcionários ou contracheque, torna-se mais difícil de manter. Observe que os nomes dos departamentos se repetem várias vezes nesta pequena tabela. (Pense no mesmo cenário para uma empresa de 50.000 funcionários!) Isso não apenas ocupa uma grande quantidade de espaço de armazenamento, mas se o nome de um departamento muda, muito trabalho precisa ser feito para fazer a mudança nesta tabela. Por exemplo, se o departamento de Tecnologia da Informação mudar seu nome para Sistemas de Informação, uma função de busca e substituição deve ser realizada em toda a tabela Employee, substituindo cada ocorrência do nome antigo pelo novo nome.

Compare este layout de tabela única com o seguinte ambiente de banco de dados:

Aqui, você pode ver que o banco de dados foi normalizado colocando as informações do departamento em sua própria tabela de pesquisa. Nesse ambiente, muito menos armazenamento é usado pela tabela Funcionário, porque apenas um número de departamento é armazenado para cada funcionário, não o nome do departamento inteiro. E se o nome do departamento de Tecnologia da Informação mudar, apenas um registro na tabela Departamento deve ser alterado em todo o banco de dados.

Guia Database Expert Links

O uso de várias tabelas complica o ambiente de relatório, porque você precisa de mais do que apenas a tabela Funcionário para imprimir uma lista de funcionários ou contracheque. No exemplo anterior, você não só precisa incluir as duas tabelas em seu relatório, mas também deve vinculá-las com um campo comum. Linking tabelas (frequentemente também conhecidas como juntando tabelas) consiste na escolha de um ou mais campos comuns que permitirão que a segunda tabela siga a tabela principal, pois a tabela principal é lida registro a registro. Você vincula tabelas no Crystal Reports com a guia Database Expert Links, ilustrada na Figura 16-5.


Figura 16-5: A guia Database Expert Links

A guia Database Expert Links aparece se você escolher inicialmente duas ou mais tabelas ao criar um relatório pela primeira vez ou sempre que escolher tabelas adicionais no Database Expert posteriormente no processo de design do relatório. Se quiser trabalhar com links de banco de dados em outras ocasiões, simplesmente exiba o Database Expert clicando no botão da barra de ferramentas Database Expert ou escolhendo Database Expert nos menus suspensos (conforme discutido anteriormente neste capítulo). Em seguida, clique na guia Links.

Você está livre para mover as tabelas individuais na guia Links se desejar vê-las em uma organização diferente. Você também pode redimensionar cada janela de mesa para torná-la mais alta, mais curta, mais estreita ou mais larga. Se você deseja que o Crystal Reports reorganize automaticamente as tabelas de acordo com a forma como estão vinculadas, clique no botão Organizar automaticamente.

A guia Database Expert Links normalmente escolhe links entre as tabelas quando você a exibe pela primeira vez. Se você vir linhas com setas conectando campos nas tabelas, a guia Database Expert Links já vinculou automaticamente as tabelas para você (a vinculação automática será discutida posteriormente neste capítulo). Pode ser necessário excluir esses links existentes, se eles estiverem incorretos, ou adicionar novos links você mesmo.

Para excluir um link, clique na linha que conecta as duas tabelas. A linha do link, junto com os campos que ele conecta, serão destacados. Clique no botão Excluir link ou pressione a tecla DEL. Se você deseja alterar as opções de link, como o tipo de junção (discutido posteriormente no capítulo em Tipos de junção, Tipos de link e Aplicação de junção), o índice usado pelo link ou o comportamento do link de várias tabelas, clique no botão Opções de link ou clique duas vezes no link selecionado.

Você também pode fazer escolhas em um menu pop-up clicando com o botão direito do mouse em uma linha de link selecionada.

Para desenhar um novo link, clique em um campo da tabela a partir da qual deseja vincular, arraste o mouse até a outra tabela que deseja vincular e, a seguir, solte no campo ao qual deseja vincular. Uma linha de link será desenhada entre as duas tabelas e campos.

Se o Crystal Reports não detectar problemas potenciais com o link que você desenhou, a linha do link simplesmente aparecerá e você não verá nenhuma mensagem. Se, no entanto, o Crystal Reports detectar um problema potencial com o link, como tipos de campo incompatíveis, você receberá uma mensagem de aviso e o link pode não ser criado.

Significado das setas do índice

A maioria dos sistemas de banco de dados permite que os campos do banco de dados sejam indexados pelo designer do banco de dados. Esses campos indexados são indicados por setas coloridas. Um índice é uma configuração especial que o designer do banco de dados cria para acelerar o acesso a uma tabela. A pesquisa de registros específicos dessa tabela será muito mais rápida quando a pesquisa for baseada em um campo indexado. As diferentes cores das setas de índice indicam que diferentes índices foram criados pelo designer do banco de dados para a tabela. Para exibir uma chave para as cores da seta do índice, clique no botão Legenda do índice. A janela aparecerá mostrando as diferentes cores e seus índices.

Ao vincular tabelas de banco de dados, você pode vincular em qualquer campo que você escolher & rdquothere não é um requisito para vincular a ou a partir de um campo indexado. Por motivos de desempenho, no entanto, você ainda pode tentar vincular para campos indexados de qualquer maneira. Você pode ver velocidades de recuperação de registro melhoradas.

Você descobrirá rapidamente que precisa estar muito familiarizado com o banco de dados que está relatando para vincular tabelas e campos com precisão. Você tem que saber o layout das tabelas e os dados que os campos comuns contêm para vinculá-los com êxito. Essa tarefa é ainda mais complicada por designers de banco de dados que insistem em proteger seus empregos criando tabelas e nomes de campos confusos e enigmáticos.

Provavelmente, a abordagem mais rápida é consultar alguém que projetou o banco de dados ou está familiarizado com seu layout e conteúdo. Exceto isso, você pode discernir as tabelas e campos adequados para vincular, se eles forem nomeados logicamente. Se nada mais, você pode navegar por campos individuais na guia Database Expert Links clicando com o botão direito no nome de um campo e escolhendo Browse Field no menu pop-up. Procurando por tipos de dados semelhantes e dados de amostra que parecem corresponder em ambas as tabelas, você pode encontrar bons candidatos para links.

Sempre certifique-se de testar seu relatório e verificar se os dados corretos estão sendo retornados depois de vincular as tabelas. Isso é muito fácil para criar um link incorreto que não exibe nenhuma mensagem de erro, mas não retorna os dados correspondentes corretamente ao relatório.

Pedido de Link

Quando você adiciona tabelas ao relatório e, em seguida, exibe a guia Links, o Crystal Reports faz uma suposição sobre a ordem na qual você deseja vincular as tabelas (essa suposição é feita apenas se o Crystal Reports automaticamente vincula as tabelas, o que geralmente acontece). Se você estiver ligando apenas duas tabelas, isso não será um problema, pois há apenas um link entre duas tabelas.

No entanto, se você vincular três ou mais tabelas (tabelas A, B e C neste exemplo), o Crystal Reports vinculará as tabelas em uma certa ordem: talvez primeiro vinculando a tabela A à tabela B em um campo e, em seguida, vinculando a tabela A para a tabela C em outro campo. Se o banco de dados que você usou no relatório for baseado na linguagem SQL (discutido com mais detalhes posteriormente neste capítulo), você poderá ver a consulta SQL que o Crystal Reports envia ao banco de dados e ver a ordem em que esses links Vai acontecer.

Na maioria dos casos, a ordem do link não será significativa e você não precisará alterá-la. No entanto, em certas situações com certos tipos de estruturas de tabela e sistemas de banco de dados ou drivers de conexão, você pode notar uma diferença nos dados resultantes se escolher um A para C, depois A para B, em vez de A para B e, em seguida, A para o pedido C. Se isso for significativo, você pode alterar o pedido clicando no botão Links do pedido. Clicar neste botão exibirá a caixa de diálogo Links do pedido.

Selecione o link que deseja mover para cima ou para baixo na ordem e clique na seta para cima ou para baixo na parte superior da caixa de diálogo. Clique na caixa de seleção Link Ordering is Enforced para garantir que o Crystal Reports reordene as instruções de link na consulta SQL que está sendo enviada ao banco de dados.

Usando vários tipos de banco de dados no mesmo relatório

O Crystal Reports não se limita a usar apenas um tipo de banco de dados por relatório. Você pode, por exemplo, desejar obter a tabela de transação principal para o seu relatório de um banco de dados cliente / servidor usando um driver de banco de dados de acesso direto, uma tabela de pesquisa menor de um banco de dados Microsoft Access em uma unidade LAN compartilhada e outra tabela de pesquisa de um Planilha do Microsoft Excel em sua unidade C via ODBC ou conexão Access / Excel (DAO).

Para fazer isso, basta escolher todas as diferentes tabelas de diferentes categorias do Database Expert ao criar o relatório pela primeira vez. Ou para adicionar tabelas adicionais (mesmo de diferentes tipos de banco de dados) depois de já ter começado a projetar um relatório, simplesmente exiba novamente o Especialista em Banco de Dados usando o botão da barra de ferramentas ou as opções do menu suspenso discutidas anteriormente. Depois de escolher a tabela ou tabelas adicionais, clique na guia Links e vincule-os apropriadamente.

Em geral, esse tipo de relatório misto é perfeitamente aceitável. No entanto, como as conexões com os diferentes tipos de banco de dados não podem ser realizadas com uma única consulta SQL (isso ocorre porque uma consulta SQL, por sua natureza, não pode cruzar os limites do banco de dados), o Crystal Reports vinculará as próprias tabelas internamente. Em casos limitados, isso pode exigir que você vincule apenas em campos de string. Na maioria das vezes, no entanto, o uso de banco de dados misto resultará em uma mensagem de aviso de que seu relatório contém mais de um tipo de banco de dados e que você não pode usar Expressões SQL ou agrupamento baseado em servidor (ambos os tópicos serão discutidos posteriormente neste capítulo).

Ao adicionar inicialmente tabelas a um relatório, o Crystal Reports tentará vincular as tabelas automaticamente. O resultado geralmente serão linhas de link que aparecem na guia Links quando você a exibe pela primeira vez, mesmo antes de desenhar qualquer link manual em você mesmo. Esse é um recurso com boas intenções, mas geralmente é mais problemático do que vale a pena. Essa vinculação vincula campos automaticamente em duas tabelas adjacentes se os campos atenderem a estes critérios:

Os nomes dos campos são exatamente os mesmos.

Os tipos de dados são idênticos.

No caso de campos de string, os comprimentos dos campos são iguais.

Em um cenário ideal (como o banco de dados de amostra XTREME fornecido com o Crystal Reports), a vinculação automática funciona perfeitamente. No mundo real, entretanto, as coisas geralmente são bem diferentes.

Considere, por exemplo, um relatório que inclui uma tabela de fornecedores e uma tabela de clientes. Ambas as tabelas contêm campos denominados Endereço, Cidade, Estado e Zip_Code. É perfeitamente concebível que esses campos tenham tipos de dados e comprimentos de campo idênticos. A vinculação automática vinculará obedientemente as duas tabelas em todos os quatro campos. Porém, esses não são os campos adequados para vincular essas duas tabelas.

Outro tipo de link automático que o Crystal Reports tentará é o link por chave. Nesse cenário, o Crystal Reports tenta determinar um relacionamento de chave primária / chave estrangeira entre as tabelas lendo a estrutura interna do banco de dados fornecida pelo driver do banco de dados. Normalmente, você deve escolher essa forma de vinculação automática clicando no botão de opção Por chave e, em seguida, clicando no botão Link automático na guia Link. Se o conjunto específico de configurações de chave primária / chave estrangeira não puder ser encontrado pelo Crystal Reports, você receberá uma mensagem indicando que o link de chave não pode ser executado e perguntando se deseja fazer o link por meio do cenário de nome de campo.

Você provavelmente descobrirá que a vinculação automática, por chave ou por nome de campo, geralmente resulta no desenho de linhas de link incorretas. Você então é forçado a deletar os links incorretos e desenhar os corretos. Embora o Crystal Reports 8.5 e anteriores permitissem que você desabilitasse essa opção de vinculação automática com uma opção de caixa de diálogo, você deve usar o Registro do Windows em versões posteriores. Modifique a chave do Registro HKEY_CURRENT_USER / Software / Crystal Decisions / 10.0 / Crystal Reports / Database Options / DoAutoSmartLinking para fazer essa alteração no Crystal Reports 10. Certifique-se de entender completamente como modificar as configurações do Registro do Windows antes de tentar esta alteração.

Tipos de junção, tipos de link e aplicação de junção

Ao vincular duas tabelas, você deve considerar cuidadosamente quais registros serão retornados de ambas as tabelas. Considere uma ligeira modificação nas estruturas de tabela normalizadas ilustradas anteriormente neste capítulo.


Alguns exemplos fazem referência a uma tabela chamada Players:

Alguns exemplos fazem referência a uma tabela chamada NPCs:

Alguns exemplos fazem referência a uma tabela chamada Guilds:

Você pode usar esta cláusula WITH para emular nomes de tabelas temporários para Jogadores e NPCs em subconsultas que suportam a cláusula WITH .:

Exceto quando indicado de outra forma, o conteúdo desta página está licenciado pela Licença Creative Commons Atribuição 4.0, e os exemplos de código são licenciados pela Licença Apache 2.0. Para obter detalhes, consulte as políticas de sites para desenvolvedores do Google. Java é uma marca registrada da Oracle e / ou de suas afiliadas.


RDBMS significa Relational Database Management System.

RDBMS é a base para SQL e para todos os sistemas de banco de dados modernos, como MS SQL Server, IBM DB2, Oracle, MySQL e Microsoft Access.

Os dados no RDBMS são armazenados em objetos de banco de dados chamados tabelas. Uma tabela é uma coleção de entradas de dados relacionadas e consiste em colunas e linhas.

Observe a tabela & quotCustomers & quot:

Exemplo

Cada tabela é dividida em entidades menores chamadas campos. Os campos da tabela Clientes consistem em CustomerID, CustomerName, ContactName, Address, City, PostalCode e Country. Um campo é uma coluna em uma tabela projetada para manter informações específicas sobre cada registro na tabela.

Um registro, também chamado de linha, é cada entrada individual que existe em uma tabela. Por exemplo, existem 91 registros na tabela Clientes acima. Um registro é uma entidade horizontal em uma tabela.

Uma coluna é uma entidade vertical em uma tabela que contém todas as informações associadas a um campo específico em uma tabela.


Seleção de referência na consulta SQL (cláusula where) com ArcPy? - Sistemas de Informação Geográfica

A agregação é uma parte fundamental do armazenamento de dados. Para melhorar o desempenho de agregação em seu warehouse, o banco de dados Oracle oferece a seguinte funcionalidade:

Extensões CUBE e ROLLUP para a cláusula GROUP BY

As extensões CUBE, ROLLUP e GROUPING SETS para SQL tornam a consulta e os relatórios mais fáceis e rápidos. CUBE, ROLLUP e conjuntos de agrupamento produzem um único conjunto de resultados que é equivalente a UNION ALL de linhas agrupadas de forma diferente. ROLLUP calcula agregações como SUM, COUNT, MAX, MIN e AVG em níveis crescentes de agregação, desde o mais detalhado até um total geral. CUBE é uma extensão semelhante a ROLLUP, permitindo que uma única instrução calcule todas as combinações possíveis de agregações. As extensões CUBE, ROLLUP e GROUPING SETS permitem especificar apenas os agrupamentos necessários na cláusula GROUP BY. Isso permite uma análise eficiente em várias dimensões sem executar uma operação CUBE. Computando um CUBO cria uma carga de processamento pesada, portanto, substituir cubos por conjuntos de agrupamento pode aumentar significativamente o desempenho.

Para melhorar o desempenho, CUBE, ROLLUP e GROUPING SETS podem ser paralelizados: vários processos podem executar todas essas instruções simultaneamente. Esses recursos tornam os cálculos agregados mais eficientes, melhorando assim o desempenho e a escalabilidade do banco de dados.

As três funções GROUPING ajudam a identificar o grupo ao qual cada linha pertence e permitem a classificação de linhas de subtotais e resultados de filtragem.

Esta seção contém os seguintes tópicos:

20.1.1 Sobre a análise em múltiplas dimensões

Um dos conceitos-chave nos sistemas de suporte à decisão é a análise multidimensional: examinar a empresa a partir de todas as combinações necessárias de dimensões. O termo dimensão é usado para significar qualquer categoria usada na especificação de perguntas. Entre as dimensões mais comumente especificadas estão o tempo, a geografia, o produto, o departamento e o canal de distribuição, mas as dimensões potenciais são tão infinitas quanto as variedades da atividade empresarial. Os eventos ou entidades associados a um determinado conjunto de valores de dimensão são geralmente chamados de fatos. Os fatos podem ser vendas em unidades ou moeda local, lucros, contagens de clientes, volumes de produção ou qualquer outra coisa que valha a pena rastrear.

Aqui estão alguns exemplos de solicitações multidimensionais:

Mostre as vendas totais em todos os produtos em níveis crescentes de agregação para uma dimensão geográfica, de estado para país para região, para 1999 e 2000.

Crie uma análise tabular cruzada de nossas operações mostrando as despesas por território na América do Sul para 1999 e 2000. Inclua todos os subtotais possíveis.

Liste os 10 principais representantes de vendas na Ásia de acordo com a receita de vendas de 2.000 produtos automotivos e classifique suas comissões.

Todas essas solicitações envolvem múltiplas dimensões. Muitas questões multidimensionais requerem dados agregados e comparações de conjuntos de dados, geralmente ao longo do tempo, geografia ou orçamentos.

Para visualizar dados que possuem muitas dimensões, os analistas comumente usam a analogia de um cubo de dados, ou seja, um espaço onde os fatos são armazenados na interseção de n dimensões. A Figura 20-1 mostra um cubo de dados e como ele pode ser usado de forma diferente por vários grupos. O cubo armazena dados de vendas organizados pelas dimensões de produto, mercado, vendas e tempo. Observe que esta é apenas uma metáfora: os dados reais são armazenados fisicamente em tabelas normais. Os dados do cubo consistem em dados detalhados e agregados.

Figura 20-1 Cubos lógicos e visualizações por usuários diferentes

Você pode recuperar fatias de dados do cubo. Eles correspondem a relatórios tabulares cruzados, como o mostrado na Tabela 20-1. Os gerentes regionais podem estudar os dados comparando fatias do cubo aplicáveis ​​a diferentes mercados. Em contraste, os gerentes de produto podem comparar fatias que se aplicam a produtos diferentes. Um usuário ad hoc pode trabalhar com uma ampla variedade de restrições, trabalhando em um cubo de subconjunto.

Responder a perguntas multidimensionais geralmente envolve acessar e consultar grandes quantidades de dados, às vezes em milhões de linhas. Como a enxurrada de dados detalhados gerados por grandes organizações não pode ser interpretada no nível mais baixo, visões agregadas das informações são essenciais. Agregações, como somas e contagens, em muitas dimensões são vitais para análises multidimensionais. Portanto, as tarefas analíticas requerem agregação de dados conveniente e eficiente.

20.1.2 Sobre o desempenho de agregação otimizado

Não apenas os problemas multidimensionais, mas todos os tipos de processamento podem se beneficiar dos recursos de agregação aprimorados. Sistemas de processamento de transações, financeiros e de manufatura - todos esses geram um grande número de relatórios de produção que precisam de recursos de sistema substanciais. A eficiência aprimorada ao criar esses relatórios reduzirá a carga do sistema. Na verdade, qualquer processo de computador que agrega dados de detalhes a níveis mais elevados se beneficiará do desempenho de agregação otimizado.

Essas extensões fornecem recursos de agregação e trazem muitos benefícios, incluindo:

Programação simplificada que requer menos código SQL para muitas tarefas.

Processamento de consultas mais rápido e eficiente.

Cargas de processamento de cliente e tráfego de rede reduzidos porque o trabalho de agregação é transferido para servidores.

Oportunidades para agregações de cache porque consultas semelhantes podem alavancar o trabalho existente.

20.1.3 Armazenamento de dados: um cenário agregado

Para ilustrar o uso da extensão GROUP BY, este capítulo usa os dados sh do esquema de amostra. Todos os exemplos referem-se a dados deste cenário. A empresa hipotética tem vendas em todo o mundo e rastreia as vendas por informações em dólares e quantidades. Como há muitas linhas de dados, as consultas mostradas aqui normalmente têm restrições rígidas em suas cláusulas WHERE para limitar os resultados a um pequeno número de linhas.

A Tabela 20-1 é um exemplo de relatório tabular cruzado que mostra o total de vendas por country_id e channel_desc para os EUA e França por meio da Internet e vendas diretas em setembro de 2000.

Tabela 20-1 Relatório Tabular Cruzado Simples com Subtotais

Considere que mesmo um relatório simples como este, com apenas nove valores em sua grade, gera quatro subtotais e um total geral. Metade dos valores necessários para este relatório não seriam calculados com uma consulta que solicitou SUM (amount_sold) e fez um GROUP BY (channel_desc, country_id). Para obter os agregados de nível superior, seriam necessárias consultas adicionais. Os comandos de banco de dados que oferecem cálculo aprimorado de subtotais trazem grandes benefícios para consultas, relatórios e operações analíticas.

Interpretando NULLs em exemplos de agregação

NULLs retornados pelas extensões GROUP BY nem sempre são o valor de significado nulo tradicional desconhecido. Em vez disso, um NULL pode indicar que sua linha é um subtotal. Para evitar a introdução de outro não valor no sistema de banco de dados, esses valores subtotais não recebem uma tag especial.

GROUPING Funções para obter detalhes sobre como os nulos que representam os subtotais são diferenciados dos nulos armazenados nos dados

20.2 Extensão ROLLUP para GROUP BY

ROLLUP permite que uma instrução SELECT calcule vários níveis de subtotais em um grupo de dimensões especificado. Ele também calcula um total geral. ROLLUP é uma extensão simples da cláusula GROUP BY, portanto, sua sintaxe é extremamente fácil de usar. A extensão ROLLUP é altamente eficiente, adicionando sobrecarga mínima a uma consulta.

A ação de ROLLUP é direta: ele cria subtotais que acumulam do nível mais detalhado para um total geral, seguindo uma lista de agrupamento especificada na cláusula ROLLUP. ROLLUP leva como argumento uma lista ordenada de colunas de agrupamento. Primeiro, ele calcula os valores agregados padrão especificados na cláusula GROUP BY. Em seguida, ele cria subtotais de nível progressivamente mais alto, movendo-se da direita para a esquerda através da lista de colunas de agrupamento. Finalmente, isso cria um total geral.

ROLLUP cria subtotais em n + 1 níveis, onde n é o número de colunas de agrupamento. Por exemplo, se uma consulta especifica ROLLUP em colunas de agrupamento de tempo, região e departamento (n = 3), o conjunto de resultados incluirá linhas em quatro níveis de agregação.

Você pode querer compactar seus dados ao usar ROLLUP. Isso é particularmente útil quando há poucas atualizações para partições mais antigas.

Esta seção contém os seguintes tópicos:

20.2.1 Quando usar ROLLUP

Use a extensão ROLLUP em tarefas que envolvem subtotais.

É muito útil para calcular o subtotal ao longo de uma dimensão hierárquica, como tempo ou geografia. Por exemplo, uma consulta pode especificar um ROLLUP (y, m, dia) ou ROLLUP (país, estado, cidade).

Para administradores de data warehouse que usam tabelas de resumo, o ROLLUP pode simplificar e acelerar a manutenção de tabelas de resumo.

20.2.2 Sintaxe ROLLUP

ROLLUP aparece na cláusula GROUP BY em uma instrução SELECT. Sua forma é:

Este exemplo usa os dados nos dados do esquema de amostra sh, os mesmos dados usados ​​na Figura 20-1. O ROLLUP tem três dimensões.

Observe que os resultados nem sempre somam devido aos arredondamentos.

Esta consulta retorna os seguintes conjuntos de linhas:

Linhas de agregação regulares que seriam produzidas por GROUP BY sem usar ROLLUP.

Subtotais de primeiro nível agregando em country_id para cada combinação de channel_desc e calendar_month.

Subtotais de segundo nível agregando em calendar_month_desc e country_id para cada valor channel_desc.

Visualize e execute um exemplo relacionado no Oracle Live SQL no Oracle LiveSQL: ROLLUP com GROUP BY

20.2.3 Acúmulo Parcial

Você também pode acumular para que apenas alguns dos subtotais sejam incluídos. Este rollup parcial usa a seguinte sintaxe:

Nesse caso, a cláusula GROUP BY cria subtotais nos níveis de agregação (2 + 1 = 3). Ou seja, no nível (expr1, expr2, expr3), (expr1, expr2) e (expr1).

Exemplo 20-2 ROLLUP parcial

Esta consulta retorna os seguintes conjuntos de linhas:

Linhas de agregação regulares que seriam produzidas por GROUP BY sem usar ROLLUP.

Subtotais de primeiro nível agregando em country_id para cada combinação de channel_desc e calendar_month_desc.

Subtotais de segundo nível agregando em calendar_month_desc e country_id para cada valor channel_desc.

Não produz uma linha total geral.

20.3 Extensão CUBE para GROUP BY

CUBE pega um conjunto especificado de colunas de agrupamento e cria subtotais para todas as suas combinações possíveis. Em termos de análise multidimensional, CUBE gera todos os subtotais que podem ser calculados para um cubo de dados com as dimensões especificadas. Se você especificou CUBO (hora, região, departamento), o conjunto de resultados incluirá todos os valores que seriam incluídos em uma instrução ROLLUP equivalente mais combinações adicionais. Por exemplo, na Figura 20-1, os totais departamentais nas regiões (279.000 e 319.000) não seriam calculados por uma cláusula ROLLUP (tempo, região, departamento), mas seriam calculados por um CUBO (tempo, região, departamento) cláusula. Se n colunas forem especificadas para um CUBO, haverá de 2 a n combinações de subtotais retornadas. A sintaxe do CUBO fornece um exemplo de cubo tridimensional.

Esta seção contém os seguintes tópicos:

20.3.1 Quando usar o CUBE

Considere o uso do CUBE em qualquer situação que requeira relatórios tabulares cruzados. Os dados necessários para relatórios tabulares cruzados podem ser gerados com um único SELECT usando CUBO. Como ROLLUP, CUBE pode ser útil na geração de tabelas de resumo. Observe que o preenchimento de tabelas de resumo é ainda mais rápido se a consulta CUBE for executada em paralelo.

CUBE é normalmente mais adequado em consultas que usam colunas de várias dimensões em vez de colunas que representam diferentes níveis de uma única dimensão. Por exemplo, uma tabulação cruzada comumente solicitada pode precisar de subtotais para todas as combinações de mês, estado e produto. Essas são três dimensões independentes, e a análise de todas as combinações subtotais possíveis é comum. Em contraste, uma tabulação cruzada mostrando todas as combinações possíveis de ano, mês e dia teria vários valores de interesse limitado, porque há uma hierarquia natural na dimensão do tempo. Subtotais como lucro por dia do mês somados ao longo do ano seriam desnecessários na maioria das análises. Relativamente poucos usuários precisam perguntar "Quais foram as vendas totais no dia 16 de cada mês ao longo do ano?" Consulte "Manipulação de hierarquia em ROLLUP e CUBE" para obter um exemplo de manipulação de cálculos de rollup com eficiência.

20.3.2 CUBE Sintaxe

CUBE aparece na cláusula GROUP BY em uma instrução SELECT. Sua forma é:

Exemplo 20-3 Palavra-chave CUBE em uma consulta

Esta consulta ilustra a agregação CUBE em três dimensões.

20.3.3 CUBO Parcial

O CUBO parcial se assemelha ao ROLLUP parcial porque você pode limitá-lo a certas dimensões e precedê-lo com colunas fora do operador CUBO. Nesse caso, os subtotais de todas as combinações possíveis são limitados às dimensões dentro da lista de cubos (entre parênteses) e são combinados com os itens anteriores na lista GROUP BY.

A sintaxe para CUBO parcial é a seguinte:

Este exemplo de sintaxe calcula 2 * 2 ou 4 subtotais. Isso é:

Exemplo 20-4 CUBO Parcial em uma Consulta

Usando o banco de dados de vendas, você pode emitir a seguinte declaração:

20.3.4 Calculando subtotais sem CUBO

Assim como para ROLLUP, várias instruções SELECT combinadas com instruções UNION ALL podem fornecer as mesmas informações coletadas por meio de CUBE. No entanto, isso pode exigir muitas instruções SELECT. Para um cubo n-dimensional, são necessárias 2 a n instruções SELECT. No exemplo de três dimensões, isso significaria emitir instruções SELECT vinculadas a UNION ALL. Muitas instruções SELECT geram processamento ineficiente e SQL muito demorado.

Consider the impact of adding just one more dimension when calculating all possible combinations: the number of SELECT statements would double to 16. The more columns used in a CUBE clause, the greater the savings compared to the UNION ALL approach.

20.4 GROUPING Functions

Two challenges arise with the use of ROLLUP and CUBE . First, how can you programmatically determine which result set rows are subtotals, and how do you find the exact level of aggregation for a given subtotal? You often need to use subtotals in calculations such as percent-of-totals, so you need an easy way to determine which rows are the subtotals. Second, what happens if query results contain both stored NULL values and "NULL" values created by a ROLLUP or CUBE ? How can you differentiate between the two? This section discusses some of these situations.

This section contains the following topics:

20.4.1 GROUPING Function

GROUPING handles these problems. Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL , returns a 0.

GROUPING appears in the selection list portion of a SELECT statement. Its form is:

Example 20-5 GROUPING to Mask Columns

This example uses GROUPING to create a set of mask columns for the result set shown in Example 20-2. The mask columns are easy to analyze programmatically.

A program can easily identify the detail rows by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".

You can improve the readability of result sets by using the GROUPING and DECODE functions as shown in Example 20-6.

Example 20-6 GROUPING For Readability

To understand the previous statement, note its first column specification, which handles the channel_desc column. Consider the first line of the previous statement:

In this, the channel_desc value is determined with a DECODE function that contains a GROUPING function. The GROUPING function returns a 1 if a row value is an aggregate created by ROLLUP or CUBE , otherwise it returns a 0. The DECODE function then operates on the GROUPING function's results. It returns the text "All Channels" if it receives a 1 and the channel_desc value from the database if it receives a 0. Values from the database will be either a real value such as "Internet" or a stored NULL . The second column specification, displaying country_id , works the same way.

20.4.2 When to Use GROUPING

The GROUPING function is not only useful for identifying NULLs , it also enables sorting subtotal rows and filtering results. In Example 20-7, you retrieve a subset of the subtotals created by a CUBE and none of the base-level aggregations. The HAVING clause constrains columns that use GROUPING functions.

Example 20-7 GROUPING Combined with HAVING

Compare the result set of Example 20-7 with that in Example 20-2 to see how Example 20-7 is a precisely specified group: it contains only the yearly totals, regional totals aggregated over time and department, and the grand total.

20.4.3 GROUPING_ID Function

To find the GROUP BY level of a particular row, a query must return GROUPING function information for each of the GROUP BY columns. If you do this using the GROUPING function, every GROUP BY column requires another column using the GROUPING function. For instance, a four-column GROUP BY clause must be analyzed with four GROUPING functions. This is inconvenient to write in SQL and increases the number of columns required in the query. When you want to store the query result sets in tables, as with materialized views, the extra columns waste storage space.

To address these problems, you can use the GROUPING_ID function. GROUPING_ID returns a single number that enables you to determine the exact GROUP BY level. For each row, GROUPING_ID takes the set of 1's and 0's that would be generated if you used the appropriate GROUPING functions and concatenates them, forming a bit vector. The bit vector is treated as a binary number, and the number's base-10 value is returned by the GROUPING_ID function. For instance, if you group with the expression CUBE(a , b) the possible values are as shown in Table 20-2.

Table 20-2 GROUPING_ID Example for CUBE(a, b)

GROUPING_ID clearly distinguishes groupings created by grouping set specification, and it is very useful during refresh and rewrite of materialized views.

20.4.4 GROUP_ID Function

While the extensions to GROUP BY offer power and flexibility, they also allow complex result sets that can include duplicate groupings. The GROUP_ID function lets you distinguish among duplicate groupings. If there are multiple sets of rows calculated for a given level, GROUP_ID assigns the value of 0 to all the rows in the first set. All other sets of duplicate rows for a particular grouping are assigned higher values, starting with 1. For example, consider the following query, which generates a duplicate grouping:

Example 20-8 GROUP_ID in a Query

This query generates the following groupings: ( country_id , cust_state_province ), ( country_id ), ( country_id ), and (). Note that the grouping ( country_id ) is repeated twice. The syntax for GROUPING SETS is explained in "GROUPING SETS Expression" .

This function helps you filter out duplicate groupings from the result. For example, you can filter out duplicate ( region ) groupings from the previous example by adding a HAVING clause condition GROUP_ID()=0 to the query.

20.5 GROUPING SETS Expression

You can s electively specify the set of groups that you want to create using a GROUPING SETS expression within a GROUP BY clause. This allows precise specification across multiple dimensions without computing the whole CUBE . "GROUPING SETS Syntax" contains the GROUPING SETS syntax.

Note that this statement uses composite columns, described in "About Composite Columns and Grouping" . This statement calculates aggregates over three groupings:

(channel_desc, calendar_month_desc, country_iso_code)

Compare the previous statement with the following alternative, which uses the CUBE operation and the GROUPING_ID function to return the desired rows:

This statement computes all the 8 (2 *2 *2) groupings, though only the previous 3 groups are of interest to you.

Another alternative is the following statement, which is lengthy due to several unions. This statement requires three scans of the base table, making it inefficient. CUBE and ROLLUP can be thought of as grouping sets with very specific semantics. For example, consider the following statement:

This statement is equivalent to:

And this statement is equivalent to:

20.5.1 GROUPING SETS Syntax

GROUPING SETS syntax lets you define multiple groupings in the same query. GROUP BY computes all the groupings specified and combines them with UNION ALL . For example, consider the following statement:

This statement is equivalent to:

Table 20-3 shows grouping sets specification and equivalent GROUP BY specification. Note that some examples use composite columns.

Table 20-3 GROUPING SETS Statements and Equivalent GROUP BY

GROUP BY GROUPING SETS(a, b, c)

GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY c

GROUP BY GROUPING SETS(a, b, (b, c))

GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY b, c

GROUP BY GROUPING SETS((a, b, c))

GROUP BY GROUPING SETS(a, (b), ())

GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY ()

GROUP BY GROUPING SETS(a, ROLLUP(b, c))

GROUP BY a UNION ALL GROUP BY ROLLUP(b, c)

In the absence of an optimizer that looks across query blocks to generate the execution plan, a query based on UNION would need multiple scans of the base table, sales. This could be very inefficient as fact tables will normally be huge. Using GROUPING SETS statements, all the groupings of interest are available in the same query block.

20.6 About Composite Columns and Grouping

A composite column is a collection of columns that are treated as a unit during the computation of groupings. You specify the columns in parentheses as in the following statement:

In this statement, the data is not rolled up across year and quarter, but is instead equivalent to the following groupings of a UNION ALL :

( year , quarter , month , day ),

Here, ( quarter , month ) form a composite column and are treated as a unit. In general, composite columns are useful in ROLLUP , CUBE , GROUPING SETS , and concatenated groupings. For example, in CUBE or ROLLUP , composite columns would mean skipping aggregation across certain levels. That is, the following statement:

Here, (b , c) are treated as a unit and rollup will not be applied across (b , c) . It is as if you have an alias, for example z, for (b , c) and the GROUP BY expression reduces to GROUP BY ROLLUP(a , z) . Compare this with the normal rollup as in the following:

This would be the following:

Similarly, the following statement is equivalent to the four GROUP BY s:

In GROUPING SETS , a composite column is used to denote a particular level of GROUP BY . See Table 20-3 for more examples of composite columns.

Example 20-9 Composite Columns

You do not have full control over what aggregation levels you want with CUBE and ROLLUP . For example, consider the following statement:

This statement results in Oracle computing the following groupings:

(channel_desc, calendar_month_desc, country_iso_code)

If you are just interested in the first, third, and fourth of these groupings, you cannot limit the calculation to those groupings without using composite columns. With composite columns, this is possible by treating month and country as a single unit while rolling up. Columns enclosed in parentheses are treated as a unit while computing CUBE and ROLLUP . Thus, you would say:

20.7 Concatenated Groupings and Data Aggregation

Concatenated groupings offer a concise way to generate useful combinations of groupings. Groupings specified with concatenated groupings yield the cross-product of groupings from each grouping set. The cross-product operation enables even a small number of concatenated groupings to generate a large number of final groups. The concatenated groupings are specified simply by listing multiple grouping sets, cubes, and rollups, and separating them with commas. Here is an example of concatenated grouping sets:

This SQL defines the following groupings:

Concatenation of grouping sets is very helpful for these reasons:

Ease of query development

You need not enumerate all groupings manually.

SQL generated by analytical applications often involves concatenation of grouping sets, with each grouping set defining groupings needed for a dimension.

Example 20-10 Concatenated Groupings

You can also specify more than one grouping in the GROUP BY clause. For example, if you want aggregated sales values for each product rolled up across all levels in the time dimension ( year , month and day ), and across all levels in the geography dimension ( region ), you can issue the following statement:

This results in the following groupings:

( channel_desc , calendar_year , calendar_quarter_desc )

( channel_desc , country_iso_code , cust_state_province )

This is the cross-product of the following:

The expression, channel_desc

ROLLUP ( calendar_year , calendar_quarter_desc ), which is equivalent to (( calendar_year , calendar_quarter_desc ), ( calendar_year ), ())

ROLLUP(country_iso_code, cust_state_province) , which is equivalent to (( country_iso_code , cust_state_province ), ( country_iso_code ), ())

Note that the output contains two occurrences of ( channel_desc ) group. To filter out the extra ( channel_desc ) group, the query could use a GROUP_ID function.

Another concatenated join example is Example 20-11, showing the cross product of two grouping sets.

Example 20-11 Concatenated Groupings (Cross-Product of Two Grouping Sets)

This statement results in the computation of groupings:

( country_iso_code , year ), ( country_iso_code , calendar_quarter_desc ), ( cust_state_province , year ) and ( cust_state_province , calendar_quarter_desc )

20.7.1 Concatenated Groupings and Hierarchical Data Cubes

One of the most importan t uses for concatenated groupings is to generate the aggregates needed for a hierarchical cube of data. A hierarchical cube is a data set where the data is aggregated along the rollup hierarchy of each of its dimensions and these aggregations are combined across dimensions. It includes the typical set of aggregations needed for business intelligence queries. By using concatenated groupings, you can generate all the aggregations needed by a hierarchical cube with just n ROLLUP s (where n is the number of dimensions), and avoid generating unwanted aggregations.

Consider just three of the dimensions in the sh sample schema data set, each of which has a multilevel hierarchy:

time: year , quarter , month , day ( week is in a separate hierarchy)

product: category , subcategory , prod_name

geography: region , subregion , country , state , city

This data is represented using a column for each level of the hierarchies, creating a total of twelve columns for dimensions, plus the columns holding sales figures.

For your business intelligence needs, you would like to calculate and store certain aggregates of the various combinations of dimensions. In Example 20-12, you create the aggregates for all levels, except for "day", which would create too many rows. In particular, you want to use ROLLUP within each dimension to generate useful aggregates. Once you have the ROLLUP -based aggregates within each dimension, you want to combine them with the other dimensions. This will generate a hierarchical cube. Note that this is not at all the same as a CUBE using all twelve of the dimension columns: that would create 2 to the 12th power (4,096) aggregation groups, of which you need only a small fraction. Concatenated grouping sets make it easy to generate exactly the aggregations you need. Example 20-12 shows where a GROUP BY clause is needed.

Example 20-12 Concatenated Groupings and Hierarchical Cubes

The rollups in the GROUP BY specification generate the following groups, four for each dimension.

Table 20-4 Hierarchical CUBE Example

category, subcategory, name

region, subregion, country, state, city

region, subregion, country, state

The concatenated grouping sets specified in the previous SQL will take the ROLLUP aggregations listed in the table and perform a cross-product on them. The cross-product will create the 96 (4x4x6) aggregate groups needed for a hierarchical cube of the data. There are major advantages in using three ROLLUP expressions to replace what would otherwise require 96 grouping set expressions: the concise SQL is far less error-prone to develop and far easier to maintain, and it enables much better query optimization. You can picture how a cube with more dimensions and more levels would make the use of concatenated groupings even more advantageous.

See "Working with Hierarchical Cubes in SQL" for more information regarding hierarchical cubes.

20.8 Considerations when Using Aggregation in Data Warehouses

This section discusses the following topics.

20.8.1 Hierarchy Handling in ROLLUP and CUBE

The ROLLUP and CUBE extensions work independently of any hierarchy metadata in your system. Their calculations are based entirely on the columns specified in the SELECT statement in which they appear. This approach enables CUBE and ROLLUP to be used whether or not hierarchy metadata is available. The simplest way to handle levels in hierarchical dimensions is by using the ROLLUP extension and indicating levels explicitly through separate columns. The following code shows a simple example of this with months rolled up to quarters and quarters rolled up to years.

Example 20-13 ROLLUP and CUBE Hierarchy Handling

20.8.2 Column Capacity in ROLLUP and CUBE

CUBE , ROLLUP , and GROUPING SETS do not restrict the GROUP BY clause column capacity. The GROUP BY clause, with or without the extensions, can work with up to 255 columns. However, the combinatorial explosion of CUBE makes it unwise to specify a large number of columns with the CUBE extension. Consider that a 20-column list for CUBE would create 2 to the 20 combinations in the result set. A very large CUBE list could strain system resources, so any such query must be tested carefully for performance and the load it places on the system.

20.8.3 HAVING Clause Used with GROUP BY Extensions

The HAVING clause of SELECT statements is unaffected by the use of GROUP BY . Note that the conditions specified in the HAVING clause apply to both the subtotal and non-subtotal rows of the result set. In some cases a query may need to exclude the subtotal rows or the non-subtotal rows from the HAVING clause. This can be achieved by using a GROUPING or GROUPING_ID function together with the HAVING clause. See Example 20-7 and its associated SQL statement for an example.

20.8.4 ORDER BY Clause Used with GROUP BY Extensions

In many cases, a query must order the rows in a certain way, and this is done with the ORDER BY clause. The ORDER BY clause of a SELECT statement is unaffected by the use of GROUP BY , because the ORDER BY clause is applied after the GROUP BY calculations are complete.

Note that the ORDER BY specification makes no distinction between aggregate and non-aggregate rows of the result set. For instance, you might wish to list sales figures in declining order, but still have the subtotals at the end of each group. Simply ordering sales figures in descending sequence will not be sufficient, because that will place the subtotals (the largest values) at the start of each group. Therefore, it is essential that the columns in the ORDER BY clause include columns that differentiate aggregate from non-aggregate columns. This requirement means that queries using ORDER BY along with aggregation extensions to GROUP BY will generally need to use one or more of the GROUPING functions.

20.8.5 Using Other Aggregate Functions with ROLLUP and CUBE

The examples in this chapter show ROLLUP and CUBE used with the SUM function. While this is the most common type of aggregation, these extensions can also be used with all other functions available to the GROUP BY clause, for example, COUNT , AVG , MIN , MAX , STDDEV , and VARIANCE . COUNT , which is often needed in cross-tabular analyses, is likely to be the second most commonly used function.

20.8.6 Using In-Memory Aggregation

Analytic queries typically attempt to find patterns and trends by performing complex aggregations on data. In-memory aggregation uses KEY VECTOR and VECTOR GROUP BY operations to optimize query blocks involving aggregation and joins from a single large table to multiple small tables, such as in a typical star query. These operations use efficient in-memory arrays for joins and aggregation, and are especially effective when the underlying tables are stored in the In-Memory Column Store (IM column store).

The VECTOR GROUP BY transformation is an optimization transformation that enables efficient in-memory array-based aggregation. It accumulates aggregate values into in-memory arrays during table scans. This results in enhanced performance for joins and joins and aggregates.

The VECTOR GROUP BY transformation is a two-part process, similar to that of star transformation, that involves the following steps:

  1. The dimension tables are scanned and any WHERE clause predicates are applied. A new data structure called a key vector is created based on the results of these scans.

The key vector is similar to a bloom filter as it allows the join predicates to be applied as additional filter predicates during the scan of the fact table, but it also enables Oracle Database to conduct the GROUP BY or aggregation during the scan of the fact table instead of having to do it afterwards.

The combination of these two phases dramatically improves the efficiency of a multiple table join with complex aggregations. Both phases are visible in the execution plan of your query.

Example 20-14 Example: Aggregation Using VECTOR GROUP BY Transformation

Consider the following query that joins the products , customers , and times dimensions with the sales fact table:

When the IM column store is configured, the Optimizer rewrites this query to use vector joins and VECTOR GROUP BY aggregation. Figure 20-2 describes how aggregation is performed using VECTOR GROUP BY . The predicates on the dimension tables PRODUCTS , CUSTOMERS , and TIMES are converted to filters on the fact table SALES . The GROUP BY is performed simultaneously with the scan of the SALES table by using in-memory arrays.

Figure 20-2 VECTOR GROUP BY Using Oracle In-Memory Column Store

20.9 Computation Using the WITH Clause

The WITH clause (formally known as subquery_factoring_clause ) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. WITH is a part of the SQL-99 standard. This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. Using the WITH clause, Oracle retrieves the results of a query block and stores them in the user's temporary tablespace. Depending on how your system is configured, the results may be stored in the shared temporary tablespace or local temporary tablespace. Note that Oracle Database does not support recursive use of the WITH clause. Note that Oracle Database supports recursive use of the WITH clause that may be used for such queries as are used with a bill of materials or expansion of parent-child hierarchies to parent-descendant hierarchies. See Oracle Database SQL Language Reference for more information.

In previous releases, the term temporary tablespace referred to what is now called a shared temporary tablespace .

The following query is an example of where you can improve performance and write SQL more simply by using the WITH clause. The query calculates the sum of sales for each channel and holds it under the name channel_summary . Then it checks each channel's sales total to see if any channel's sales are greater than one third of the total sales. By using the WITH clause, the channel_summary data is calculated just once, avoiding an extra scan through the large sales table.

Note that this example could also be performed efficiently using the reporting aggregate functions described in SQL for Analysis and Reporting.

20.10 Working with Hierarchical Cubes in SQL

This section illustrates examples of working with hierarchical cubes. It contains the following topics:

20.10.1 Specifying Hierarchical Cubes in SQL

Oracle Database can specify hierarchical cubes in a simple and efficient SQL query. These hierarchical cubes represent the logical cubes referred to in many analytical SQL products. To specify data in the form of hierarchical cubes, you can use one of the extensions to the GROUP BY clause, concatenated grouping sets, to generate the aggregates needed for a hierarchical cube of data. By using concatenated rollup (rolling up along the hierarchy of each dimension and then concatenate them across multiple dimensions), you can generate all the aggregations needed by a hierarchical cube.

Example 20-16 Concatenated ROLLUP

The following shows the GROUP BY clause needed to create a hierarchical cube for a 2-dimensional example similar to Example 20-12. The following simple syntax performs a concatenated rollup:

This concatenated rollup takes the ROLLUP aggregations similar to those listed in Table 20-4 in the prior section and performs a cross-product on them. The cross-product will create the 16 (4x4) aggregate groups needed for a hierarchical cube of the data.

20.10.2 Querying Hierarchical Cubes in SQL

Analytic applications treat data as cubes, but they want only certain slices and regions of the cube. Concatenated rollup (hierarchical cube) enables relational data to be treated as cubes. To handle complex analytic queries, the fundamental technique is to enclose a hierarchical cube query in an outer query that specifies the exact slice needed from the cube. Oracle Database optimizes the processing of hierarchical cubes nested inside slicing queries. By applying many powerful algorithms, these queries can be processed at unprecedented speed and scale. This enables SQL analytical tools and applications to use a consistent style of queries to handle the most complex questions.

Example 20-17 Hierarchical Cube Query

Consider the following analytic query. It consists of a hierarchical cube query nested in a slicing query.

The inner hierarchical cube specified defines a simple cube, with two dimensions and four levels in each dimension. It would generate 16 groups (4 Time levels * 4 Product levels). The GROUPING_ID function in the query identifies the specific group each row belongs to, based on the aggregation level of the grouping-columns in its argument.

The outer query applies the constraints needed for our specific query, limiting Division to a value of 25 and Month to a value of 200201 (representing January 2002 in this case). In conceptual terms, it slices a small chunk of data from the cube. The outer query's constraint on the GID column, indicated in the query by gid-for-division-month would be the value of a key indicating that the data is grouped as a combination of division and month . The GID constraint selects only those rows that are aggregated at the level of a GROUP BY month, division clause.

Oracle Database removes unneeded aggregation groups from query processing based on the outer query conditions. The outer conditions of the previous query limit the result set to a single group aggregating division and month . Any other groups involving year , month , brand , and item are unnecessary here. The group pruning optimization recognizes this and transforms the query into:

The bold items highlight the changed SQL. The inner query now has a simple GROUP BY clause of month , division . The columns year , quarter , brand , and item have been converted to null to match the simplified GROUP BY clause. Because the query now requests just one group, fifteen out of sixteen groups are removed from the processing, greatly reducing the work. For a cube with more dimensions and more levels, the savings possible through group pruning can be far greater. Note that the group pruning transformation works with all the GROUP BY extensions: ROLLUP , CUBE , and GROUPING SETS .

While the optimizer has simplified the previous query to a simple GROUP BY , faster response times can be achieved if the group is precomputed and stored in a materialized view. Because online analytical queries can ask for any slice of the cube many groups may need to be precomputed and stored in a materialized view. This is discussed in the next section.

This section contains the following topics:

20.10.2.1 SQL for Creating Materialized Views to Store Hierarchical Cubes

Analytical SQL requires fast response times for multiple users, and this in turn demands that significant parts of a cube be precomputed and held in materialized views.

Data warehouse designers can choose exactly how much data to materialize. A data warehouse can have the full hierarchical cube materialized. While this will take the most storage space, it ensures quick response for any query within the cube. Alternatively, a data warehouse could have just partial materialization, saving storage space, but allowing only a subset of possible queries to be answered at highest speed. If the queries cover the full range of aggregate groupings possible in its data set, it may be best to materialize the whole hierarchical cube.

This means that each dimension's aggregation hierarchy is precomputed in combination with each of the other dimensions. Naturally, precomputing a full hierarchical cube requires more disk space and higher creation and refresh times than a small set of aggregate groups. The trade-off in processing time and disk space versus query performance must be considered before deciding to create it. An additional possibility you could consider is to use data compression to lessen your disk space requirements.

Oracle Database SQL Language Reference for table compression syntax and restrictions

Oracle Database Administrator's Guide for further details about table compression

20.10.2.2 Examples of Hierarchical Cube Materialized Views

This section shows complete and partial hierarchical cube materialized views. Many of the examples are meant to illustrate capabilities, and do not actually run.

In a data warehouse where rolling window scenario is very common, it is recommended that you store the hierarchical cube in multiple materialized views - one for each level of time you are interested in. Hence, a complete hierarchical cube will be stored in four materialized views: sales_hierarchical_mon_cube_mv , sales_hierarchical_qtr_cube_mv , sales_hierarchical_yr_cube_mv , and sales_hierarchical_all_cube_mv .

The following statements create a complete hierarchical cube stored in a set of three composite partitioned and one list partitioned materialized view.

Example 20-18 Complete Hierarchical Cube Materialized View

This allows use of PCT refresh on the materialized views sales_hierarchical_mon_cube_mv , sales_hierarchical_qtr_cube_mv , and sales_hierarchical_yr_cube_mv on partition maintenance operations to sales table. PCT refresh can also be used when there have been significant changes to the base table and log based fast refresh is estimated to be slower than PCT refresh. You can just specify the method as force ( method => ' ? ') in to refresh sub-programs in the DBMS_MVIEW package and Oracle Database will pick the best method of refresh. See "About Partition Change Tracking (PCT) Refresh for Materialized Views" for more information regarding PCT refresh.

Because sales_hierarchical_qtr_cube_mv does not contain any column from times table, PCT refresh is not enabled on it. But, you can still call refresh sub-programs in the DBMS_MVIEW package with method as force ( method => ' ? ') and Oracle Database will pick the best method of refresh.

If you are interested in a partial cube (that is, a subset of groupings from the complete cube), then Oracle recommends storing the cube as a "federated cube". A federated cube stores each grouping of interest in a separate materialized view.

These materialized views can be created as BUILD DEFERRED and then, you can execute DBMS_MVIEW.REFRESH_DEPENDENT(number_of_failures, 'SALES', 'C' . ) so that the complete refresh of each of the materialized views defined on the detail table sales is scheduled in the most efficient order. See "Scheduling Refresh of Materialized Views" for more information.

Because each of these materialized views is partitioned on the time level (month, quarter, or year) present in the SELECT list, PCT is enabled on sales table for each one of them, thus providing an opportunity to apply PCT refresh method in addition to FAST and COMPLETE refresh methods.


The MultiDimensional eXpressions (MDX) language provides a specialized syntax for querying and manipulating the multidimensional data stored in OLAP cubes. [1] While it is possible to translate some of these into traditional SQL, it would frequently require the synthesis of clumsy SQL expressions even for very simple MDX expressions. MDX has been embraced by a wide majority of OLAP vendors and has become the standard for OLAP systems.

MDX was first introduced as part of the OLE DB for OLAP specification in 1997 from Microsoft. It was invented by the group of SQL Server engineers including Mosha Pasumansky. The specification was quickly followed by commercial release of Microsoft OLAP Services 7.0 in 1998 and later by Microsoft Analysis Services. The latest version of the OLE DB for OLAP specification was issued by Microsoft in 1999.

While it was not an open standard, but rather a Microsoft-owned specification, it was adopted by a wide range of OLAP vendors.

The XML for Analysis specification referred back to the OLE DB for OLAP specification for details on the MDX Query Language. In Analysis Services 2005, Microsoft added some MDX Query Language extensions like subselects. Products like Microsoft Excel 2007 started to use these new MDX Query Language extensions. Some refer to this newer variant of MDX as MDX 2005.

MdXML Edit

In 2001 the XMLA Council released the XML for Analysis (XMLA) standard, which included mdXML as a query language. In the XMLA 1.1 specification, mdXML is essentially MDX wrapped in the XML <Statement> tag.

There are six primary data types in MDX

  • Escalar. Scalar is either a number or a string. It can be specified as a literal, e.g. number 5 or string "OLAP" or it can be returned by an MDX function, e.g. Aggregate (number), UniqueName (string), .Value (number or string) etc.
  • Dimension/Hierarchy. Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions - they are assumed to be mutually independent. A dimension will contain some members (see below) organized in some hierarchy or hierarchies containing levels. It can be specified by its unique name, e.g. [Time] or it can be returned by an MDX function, e.g. .Dimension . Hierarchy is a dimensionhierarchy of a cube. It can be specified by its unique name, e.g. [Time].[Fiscal] or it can be returned by an MDX function, e.g. .Hierarchy . Hierarchies are contained within dimensions. (OLEDB for OLAP MDX specification does not distinguish between dimension and hierarchy data types. Some implementations, such as Microsoft Analysis Services, treat them differently.)
  • Nível. Level is a level in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month] or it can be returned by an MDX function, e.g. .Level .
  • Membro. Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month].[August 2006] , by qualified name, e.g. [Time].[Fiscal].[2006].[Q3].[August 2006] or returned by an MDX function, e.g. .PrevMember , .Parent , .FirstChild etc. Note that all members are specific to a hierarchy. If the self-same product is a member of two different hierarchies ( [Product].[ByManufacturer] and [Product].[ByCategory] ), there will be two different members visible that may need to be coordinated in sets and tuples (see below).
  • Tuple. Tuple is an ordered collection of one or more members from different dimensions. Tuples can be specified by enumerating the members, e.g. ([Time].[Fiscal].[Month].[August], [Customer].[By Geography].[All Customers].[USA], [Measures].[Sales]) or returned by an MDX function, e.g. .Item .
  • Definir. Set is an ordered collection of tuples with the same dimensionality, or hierarchality in the case of Microsoft's implementation. It can be specified enumerating the tuples, e.g. <([Measures].[Sales], [Time].[Fiscal].[2006]), ([Measures].[Sales], [Time].[Fiscal].[2007])>or returned by MDX function or operator, e.g. Crossjoin , Filter , Order , Descendants etc.
  • Other data types. Member properties are equivalent to atributos in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query. The scalar data value of a member property for some member can be accessed in an expression through MDX, either by naming the property (for example, [Product].CurrentMember.[Sales Price] ) or by using a special access function (for example, [Product].CurrentMember.Properties("Sales Price") ). In limited contexts, MDX allows other data types as well - for example Array can be used inside the SetToArray function to specify an array that is not processed by MDX but passed to a user-defined function in an ActiveX library. Objects of other data types are represented as scalar strings indicating the object names, such as measure group name in Microsoft's MeasureGroupMeasures function or KPI name in for example Microsoft's KPIValue or KPIGoal functions.

The following example, adapted from the SQL Server 2000 Books Online, shows a basic MDX query that uses the SELECT statement. This query returns a result set that contains the 2002 and 2003 store sales amounts for stores in the state of California.

In this example, the query defines the following result set information

  • The SELECT clause sets the query axes as the Store Sales member of the Measures dimension, and the 2002 and 2003 members of the Date dimension.
  • The FROM clause indicates that the data source is the Sales cube.
  • The WHERE clause defines the "slicer axis" as the California member of the Store dimension.

Note: You can specify up to 128 query axes in an MDX query.

If you create two axes, one must be the column axis and one must be the row axis, although it doesn't matter in which order they appear within the query. If you create a query that has only one axis, it must be the column axis. The square brackets around the particular object identifier are optional as long as the object identifier is not one of the reserved words and does not otherwise contain any characters other than letters, numbers or underscores.


Additional Conditions for Distributed Partitioned Views

For distributed partitioned views (when one or more member tables are remote), the following additional conditions apply:

A distributed transaction will be started to guarantee atomicity across all nodes affected by the update.

Set the XACT_ABORT SET option to ON for INSERT, UPDATE, or DELETE statements to work.

Any columns in remote tables of type smallmoney that are referenced in a partitioned view are mapped as dinheiro. Therefore, the corresponding columns (in the same ordinal position in the select list) in the local tables must also be of type dinheiro.

Under database compatibility level 110 and higher, any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime. Corresponding columns (in the same ordinal position in the select list) in the local tables must be smalldatetime. This is a change in behavior from earlier versions of SQL Server in which any columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as data hora and corresponding columns in local tables must be of type data hora. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

Any linked server in the partitioned view cannot be a loopback linked server. This is a linked server that points to the same instance of SQL Server.

The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE actions that involve updatable partitioned views and remote tables.

When the member tables and partitioned view definition are in place, the SQL Server query optimizer builds intelligent plans that use queries efficiently to access data from member tables. With the CHECK constraint definitions, the query processor maps the distribution of key values across the member tables. When a user issues a query, the query processor compares the map to the values specified in the WHERE clause, and builds an execution plan with a minimal amount of data transfer between member servers. Therefore, although some member tables may be located in remote servers, the instance of SQL Server resolves distributed queries so that the amount of distributed data that has to be transferred is minimal.


Examples¶

This shows a simple query using FIRST_VALUE() . This query contains two ORDER BY sub-clauses, one to control the order of rows in each partition, and one to control the order of the output of the full query.

The next query contrasts the outputs of FIRST_VALUE , NTH_VALUE , and LAST_VALUE . Observe que:

The query creates a sliding window frame that is 3 rows wide, which contains:

The row that precedes the current row.

The row that follows the current row.

The 2 in the call NTH_VALUE(i, 2) specifies the second row in the window frame (which, in this case, is also the current row).

When the current row is the very first row in the window frame, there is no preceding row to reference, so FIRST_VALUE() returns a NULL for that row.


Assista o vídeo: ArcGIS - Connecting access database