Mover Média Sql Consulta

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 da Laglead permite referências directas entre filas para que você possa calcular as mudanças de período a período. A análise Firstlast permite que você encontre o primeiro ou último valor em um grupo ordenado. Outros aprimoramentos no SQL incluem a expressão CASE e a junção externa particionada. As expressões CASE fornecem if-then logic úteis em muitas situações. A junção externa particionada é uma extensão da sintaxe de associação externa da ANSI que permite aos usuários densificar seletivamente certas dimensões enquanto mantém outras escassas. Isso permite que as ferramentas de relatório densifiquem seletivamente dimensões, por exemplo, aquelas que aparecem em seus relatórios de tabelas cruzadas, enquanto mantêm outras escassas. Para melhorar o desempenho, as funções analíticas podem ser paralelizadas: vários processos podem executar simultaneamente todas essas afirmações. Essas capacidades tornam os cálculos mais fáceis e eficientes, aumentando assim o desempenho, a escalabilidade e a simplicidade do banco de dados. As funções analíticas são classificadas como descrito na Tabela 22-1. Tabela 22-1 Funções analíticas e seus usos Para executar essas operações, as funções analíticas adicionam vários novos elementos ao processamento SQL. Esses elementos se baseiam no SQL existente para permitir expressões de cálculo flexíveis e poderosas. Com poucas exceções, as funções analíticas possuem esses novos elementos. O fluxo de processamento está representado na Figura 22-1. Figura 22-1 Ordem de processamento Os conceitos essenciais utilizados nas funções analíticas são: o processamento de consultas usando funções analíticas ocorre em três etapas. Primeiro, todas as junções, ONDE. As cláusulas GROUP BY e HAVING são realizadas. Em segundo lugar, o conjunto de resultados é disponibilizado para as funções analíticas e todos os seus cálculos ocorrem. Em terceiro lugar, se a consulta tiver uma cláusula ORDER BY no final, a ORDER BY é processada para permitir uma ordem de saída precisa. A ordem de processamento é mostrada na Figura 22-1. Partições de conjunto de resultados As funções analíticas permitem aos usuários dividir os conjuntos de resultados da consulta em grupos de linhas chamados de partições. Observe que o termo partições usadas com funções analíticas não está relacionado com o recurso de partição de tabela. Ao longo deste capítulo, o termo "partições" refere-se apenas ao significado relacionado às funções analíticas. As partições são criadas após os grupos definidos com as cláusulas GROUP BY, por isso estão disponíveis para todos os resultados agregados, como somas e médias. As divisões de partição podem ser baseadas em colunas ou expressões desejadas. Um conjunto de resultados de consulta pode ser dividido em apenas uma partição que contém todas as linhas, algumas partições grandes ou muitas partições pequenas que possuem apenas algumas linhas cada. Para cada linha de uma partição, você pode definir uma janela deslizante de dados. Esta janela determina o intervalo de linhas usadas para executar os cálculos para a linha atual. Os tamanhos das janelas podem ser baseados em um número físico de linhas ou um intervalo lógico, como o tempo. A janela tem uma linha inicial e uma linha final. Dependendo da sua definição, a janela pode se mover em uma ou em ambas as extremidades. Por exemplo, uma janela definida para uma função de soma cumulativa teria sua linha inicial fixada na primeira linha de sua partição, e sua linha final deslizaria desde o ponto de partida até a última linha da partição. Em contraste, uma janela definida para uma média móvel teria seus pontos de partida e de extremidade deslizados para que eles mantenham uma faixa física ou lógica constante. Uma janela pode ser definida como grande como todas as linhas em uma partição ou apenas uma janela deslizante de uma linha dentro de uma partição. Quando uma janela está perto de uma borda, a função retorna resultados apenas para as linhas disponíveis, ao invés de avisá-lo de que os resultados não são o que você deseja. Ao usar as funções da janela, a linha atual está incluída durante os cálculos, portanto, você deve especificar (n -1) quando estiver lidando com n itens. Cada cálculo executado com uma função analítica é baseado em uma linha atual dentro de uma partição. A linha atual serve como o ponto de referência que determina o início e o final da janela. Por exemplo, um cálculo de média móvel centrada poderia ser definido com uma janela que contenha a linha atual, as seis linhas precedentes e as seis linhas seguintes. Isso criaria uma janela deslizante de 13 linhas, como mostrado na Figura 22-2. Figura 22-2 Exemplo de Janela deslizante Classificar, Vincular e Reportagem Funções Esta seção ilustra as funções analíticas básicas para classificação, janelas e relatórios. Exemplo de cálculo de regressão linear Neste exemplo, calculamos uma linha de regressão de mínimos quadrados comuns que expressa a quantidade vendida de um produto como uma função linear do preço de lista de produtos. Os cálculos são agrupados pelo canal de vendas. Os valores SLOPE. INTCPT. RSQR são inclinação, interceptação e coeficiente de determinação da linha de regressão, respectivamente. O valor (número inteiro) COUNT é o número de produtos em cada canal para quem a quantidade vendida e os dados do preço da lista estão disponíveis. Agregados estatísticos O Oracle fornece um conjunto de funções estatísticas SQL e um pacote de estatísticas, DBMSSTATFUNCS. Esta seção lista algumas das novas funções, juntamente com a sintaxe básica. Estatística descritiva Você pode calcular as seguintes estatísticas descritivas: Mediana de um modo de conjunto de dados de um conjunto de dados Você pode calcular as seguintes estatísticas paramétricas: Spearmans rho Coeficiente Kendalls tau-b Coeficiente Além das funções, esta versão possui um pacote PLSQL, DBMSSTATFUNCS . Ele contém a função estatística descritiva RESUMO juntamente com funções para suportar o ajuste de distribuição. A função RESUMO resume uma coluna numérica de uma tabela com uma variedade de estatísticas descritivas. As cinco funções de montagem de distribuição suportam distribuição normal, uniforme, Weibull, Poisson e exponencial. Agregados Definidos pelo Usuário A Oracle oferece uma facilidade para criar suas próprias funções, chamadas funções agregadas definidas pelo usuário. Essas funções são escritas em linguagens de programação como PLSQL, Java e C e podem ser usadas como funções analíticas ou agregados em visualizações materializadas. Consulte o Guia do desenvolvedor de cartuchos de dados do banco de dados Oracle para obter mais informações sobre sintaxe e restrições. As vantagens dessas funções são: funções altamente complexas podem ser programadas usando um idioma totalmente processual. Maior escalabilidade do que outras técnicas quando as funções definidas pelo usuário são programadas para processamento paralelo. Os tipos de dados do objeto podem ser processados. Como um exemplo simples de uma função agregada definida pelo usuário, considere a estatística de desvio. Este cálculo mede se um conjunto de dados tiver uma distribuição desequilibrada sobre o seu significado. Ele irá dizer se uma cauda da distribuição é significativamente maior que a outra. Se você criou um agregado definido pelo usuário chamado udskew e aplicou-o aos dados de limite de crédito no exemplo anterior, a instrução SQL e os resultados podem parecer assim: antes de criar funções agregadas definidas pelo usuário, você deve considerar se suas necessidades podem ser atendidas Em SQL normal. Muitos cálculos complexos são possíveis diretamente no SQL, particularmente usando a expressão CASE. Permanecer com SQL regular permitirá um desenvolvimento mais simples e muitas operações de consulta já estão bem paralelizadas no SQL. Mesmo o exemplo anterior, a estatística de inclinação, pode ser criada usando padrão, embora longo, SQL. Operações pivotantes O d ata retornado por consultas de inteligência de negócios é muitas vezes mais utilizável se apresentado em um formato crosstabular. O pivô da indicação SELECT permite que você escreva consultas de crosstabulation que rotem linhas em colunas, agregando dados no processo de rotação. Pivoting é uma técnica chave nos data warehouses. Nela, você transforma várias linhas de entrada em linhas menos e geralmente mais amplas no data warehouse. Ao girar, um operador de agregação é aplicado para cada item na lista de valores da coluna pivô. A coluna de pivô não pode conter uma expressão arbitrária. Se você precisa girar em uma expressão, então você deve alias a expressão em uma visualização antes da operação PIVOT. A sintaxe básica é a seguinte: Para ilustrar o uso do pivô, crie a seguinte visão como base para exemplos posteriores: Exemplo: Pivote A seguinte declaração ilustra um pivô típico na coluna do canal: Observe que a saída criou quatro novas colunas alias , VENDAS DIRETAS. INTERNETES. CATALOGSALES. E TELESALES. Um para cada um dos valores de pivô. O resultado é uma soma. Se nenhum alias for fornecido, o título da coluna será os valores do IN-list. Pivote em múltiplas colunas Você pode girar em mais de uma coluna. A seguinte declaração ilustra um pivô típico de várias colunas: Observe que este exemplo especifica uma lista IN de várias colunas com cabeçalhos de coluna projetados para coincidir com os membros do Listado IN. Pivote: vários agregados Você pode girar com vários agregados, como mostrado no exemplo a seguir: Observe que a consulta cria cabeçalhos de coluna, concatenando os valores de pivô (ou alias) com o alias da função agregada, além de um sublinhado. Distinguir Nulos Gerados por PIVOT de Nulos em Dados de Origem Você pode distinguir entre valores nulos gerados pelo uso de PIVOT e aqueles que existem nos dados de origem. O exemplo a seguir ilustra nulos que o PIVOT gera. A seguinte consulta retorna linhas com 5 colunas, coluna prodid. E pivô colunas resultantes Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Para cada valor exclusivo de prodid. Q1COUNTTOTAL retorna o número total de linhas cujo valor qtr é Q1. Isto é, e Q2COUNTTOTAL retorna o número total de linhas cujo valor qtr é Q2. Suponha que temos uma tabela de vendas2 da seguinte estrutura: do resultado, sabemos que para prodid 100, existem 2 linhas de vendas para o quarto trimestre. E 1 linha de vendas para o quarto trimestre 2 para prodid 200, há 1 linha de vendas para o quarto trimestre. E nenhuma linha de vendas para o trimestre Q2. Assim, em Q2COUNTTOTAL. Você pode identificar que NULLlt1gt vem de uma linha na tabela original cuja medida é de valor nulo, enquanto NULLlt2gt é devido a nenhuma linha estar presente na tabela original para prodid 200 no quarto Q2. Operações de não-rotação Uma variável não invoca uma operação PIVOT. Em vez disso, ele gira dados de colunas em linhas. Se você estiver trabalhando com dados articulados, uma operação UNPIVOT não pode reverter as agregações que foram feitas pela PIVOT ou por qualquer outro meio. Para ilustrar o não gráfico, primeiro crie uma tabela giratória que inclua quatro colunas, para trimestres do ano: o conteúdo das tabelas é semelhante ao seguinte: A seguinte operação UNPIVOT gira as quartas colunas em linhas. Para cada produto, haverá quatro linhas, uma para cada trimestre. Observe o uso de INCLUDE NULLS neste exemplo. Você também pode usar EXCLUDE NULLS. Qual é a configuração padrão. Além disso, você também pode deslocalizar usando duas colunas, como se segue: Wildcard e Subquery Pivoting com Operações XML Se você quiser usar um argumento curinga ou subconsulta em suas colunas giratórias, você pode fazê-lo com a sintaxe XML PIVOT. Com PIVOT XML, a saída da operação é formatada de forma adequada XML. O exemplo a seguir ilustra o uso da palavra-chave do curinga, QUALQUER. Ele produz XML que inclui todos os valores do canal na exibição de vendas: Observe que a palavra-chave ANY está disponível nas operações PIVOT apenas como parte de uma operação XML. Esta saída inclui dados para casos em que o canal existe no conjunto de dados. Observe também que as funções de agregação devem especificar uma cláusula GROUP BY para retornar vários valores, mas o pivotclause não contém uma cláusula GROUP BY explícita. Em vez disso, o pivotclause executa um GROUP BY implícito. O exemplo a seguir ilustra o uso de uma subconsulta. Ele produz XML que inclui todos os valores dos canais e os dados de vendas correspondentes a cada canal: a saída densifica os dados para incluir todos os canais possíveis para cada produto. Data Densification for Reporting Data normalmente é armazenada em forma esparsa. Ou seja, se nenhum valor existe para uma determinada combinação de valores de dimensão, nenhuma linha existe na tabela de fatos. No entanto, você pode querer visualizar os dados de forma densa, com linhas para todos os valores de dimensão de combinação exibidos mesmo quando não existem dados de fato para eles. Por exemplo, se um produto não vendeu durante um determinado período de tempo, você ainda pode querer ver o produto para esse período de tempo com zero valor de vendas ao lado dele. Além disso, os cálculos das séries temporais podem ser realizados com maior facilidade quando os dados são densos ao longo da dimensão do tempo. Isso ocorre porque os dados densos preencherão um número consistente de linhas para cada período, o que, por sua vez, torna simples usar as funções analíticas de janelas com deslocamentos físicos. A densificação de dados é o processo de conversão de dados dispersos em uma forma densa. Para superar o problema da sparsity, você pode usar uma junção externa particionada para preencher as lacunas em uma série temporal ou qualquer outra dimensão. Essa união alarga a sintaxe de união externa convencional aplicando a junção externa a cada partição lógica definida em uma consulta. Oracle logicamente particiona as linhas em sua consulta com base na expressão que você especifica na cláusula PARTITION BY. O resultado de uma junção externa particionada é uma UNION das junções externas de cada uma das partições na tabela logicamente particionada com a tabela do outro lado da união. Note que você pode usar esse tipo de junção para preencher as lacunas em qualquer dimensão, não apenas a dimensão do tempo. A maioria dos exemplos aqui se concentra na dimensão do tempo porque é a dimensão mais utilizada como base para comparações. Sintaxe de associação de partição A sintaxe para junção externa particionada estende a cláusula ANSI SQL JOIN com a frase PARTITION BY seguida de uma lista de expressões. As expressões na lista especificam o grupo ao qual a associação externa é aplicada. As seguintes são as duas formas de sintaxe normalmente usadas para junção externa particionada: Observe que FULL OUTER JOIN não é suportado com uma associação externa particionada. Amostra de Dados Esparsos Uma situação típica com uma dimensão esparsa é mostrada no exemplo a seguir, que calcula as vendas semanais e as vendas acumuladas no ano para o produto Bounce nas semanas 20 a 30 em 2000 e 2001: neste exemplo, nós Seria de esperar 22 linhas de dados (11 semanas a cada 2 anos) se os dados fossem densos. No entanto, temos apenas 18 linhas porque as semanas 25 e 26 estão faltando em 2000 e as semanas 26 e 28 em 2001. Preenchendo lacunas em dados Podemos tirar dados escassos da consulta anterior e fazer uma junção externa particionada com um conjunto denso de Dados de tempo. Na consulta a seguir, alias nossa consulta original como v e selecionamos dados da tabela de horários, que alias como t. Aqui recuperamos 22 linhas porque não há lacunas na série. As quatro linhas adicionadas possuem 0 como seu valor de vendas definido como 0 usando a função NVL. Observe que nesta consulta, uma condição WHERE foi colocada por semanas entre 20 e 30 na visualização em linha para a dimensão temporal. Isso foi introduzido para manter o conjunto de resultados pequeno. Lacunas de preenchimento em duas dimensões Os dados de N-dimensional geralmente são exibidos como uma aba transversal bidimensional densa de (n - 2) dimensões da página. Isso requer que todos os valores de dimensão para as duas dimensões que aparecem na aba cruzada sejam preenchidos. O seguinte é outro exemplo em que a capacidade de junção externa particionada pode ser usada para preencher as lacunas em duas dimensões: nessa consulta, a cláusula de factoring WITH subquery A v1 resume os dados de vendas no produto, no país e no nível do ano. Este resultado é esparso, mas os usuários podem querer ver todas as combinações país, ano para cada produto. Para conseguir isso, tomamos cada partição de v1 com base nos valores dos produtos e junte-a externamente na dimensão do país primeiro. Isso nos dará todos os valores do país para cada produto. Em seguida, tomamos esse resultado e particionamos nos valores do produto e do país e, em seguida, juntamos-o na dimensão do tempo. Isso nos dará todos os valores de tempo para cada combinação de produtos e países. Lacunas de preenchimento em uma tabela de inventário Uma tabela de inventário normalmente rastreia a quantidade de unidades disponíveis para vários produtos. Esta tabela é esparsa: apenas armazena uma linha para um produto quando há um evento. Para uma tabela de vendas, o evento é uma venda, e para a tabela de inventário, o evento é uma alteração na quantidade disponível para um produto. Por exemplo, considere a seguinte tabela de inventário: A tabela de inventário agora possui as seguintes linhas: Para fins de relatório, os usuários podem querer ver esses dados de inventário de forma diferente. Por exemplo, eles podem querer ver todos os valores de tempo para cada produto. Isso pode ser feito usando a junção externa particionada. Além disso, para as linhas recém-inseridas de períodos de tempo faltantes, os usuários podem querer ver os valores da quantidade de unidades de coluna a serem transferidas do período de tempo existente mais recente. O último pode ser realizado usando o valor LASTVALUE da função de janela analítica. Aqui está a consulta e a saída desejada: a consulta interna computa uma junção externa particionada no tempo dentro de cada produto. A consulta interna densifica os dados na dimensão do tempo (significando que a dimensão do tempo agora terá uma linha para cada dia da semana). No entanto, a quantidade de coluna de medida terá nulos para as linhas recém-adicionadas (veja a saída na quantidade de coluna nos seguintes resultados. A consulta externa usa a função analítica LASTVALUE. Aplicando esta função, particiona os dados por produto e ordena os dados no Coluna de dimensão de tempo (timeid). Para cada linha, a função encontra o último valor não nulo na janela devido à opção IGNORE NULLS, que você pode usar com LASTVALUE e FIRSTVALUE. Vemos a saída desejada na quantidade repetida da coluna em A seguinte saída: Computação de valores de dados para preencher lacunas Os exemplos na seção anterior ilustram como usar a junção externa particionada para preencher lacunas em uma ou mais dimensões. No entanto, os conjuntos de resultados produzidos por junção externa particionada possuem valores nulos para colunas que não estão incluídas A lista PARTITION BY. Normalmente, estas são colunas de medidas. Os usuários podem usar funções SQL analíticas para substituir esses valores nulos por um valor não nulo. Por exemplo, o seguinte q Utile calcula totais mensais para produtos de 64MB de cartão de memória e discos DVD-R (ID de produto 122 e 136) para o ano 2000. Ele usa partição externa particionada para densificar dados para todos os meses. Para os meses que faltam, ele usa a função SQL analítica AVG para calcular as vendas e as unidades como a média dos meses em que o produto foi vendido. Se estiver trabalhando no SQLPlus, os dois comandos a seguir envolver os cabeçalhos das colunas para maior legibilidade dos resultados: Cálculos de séries temporais na Densificação de dados densificados não é apenas para fins de relatório. Também permite certos tipos de cálculos, especialmente cálculos de séries temporais. Os cálculos da série temporal são mais fáceis quando os dados são densos ao longo da dimensão do tempo. Os dados densos têm um número consistente de linhas para cada período de tempo, o que, por sua vez, torna simples usar funções de janela analítica com deslocamentos físicos. Para ilustrar, primeiro façamos o exemplo de preenchimento de lacunas em dados. E vamos adicionar uma função analítica a essa consulta. Na versão aprimorada a seguir, calculamos vendas semanais ao longo do ano, ao lado das vendas semanais. Os valores NULL que a junção externa particionada são inseridos na criação de séries temporais densas são tratados de maneira usual: a função SUM os trata como 0s. Comparação período a período para um nível de tempo: Exemplo Como usamos esse recurso para comparar valores em períodos de tempo Especificamente, como calculamos uma comparação de vendas ano a ano no nível da semana. A seguinte consulta retorna na mesma linha , Para cada produto, as vendas acumuladas a cada semana de 2001 com a de 2000. Observe que, neste exemplo, começamos com uma cláusula WITH. Isso melhora a legibilidade da consulta e nos permite focar a união externa particionada. Se estiver trabalhando no SQLPlus, o seguinte comando envolve os cabeçalhos das colunas para maior legibilidade dos resultados: na cláusula FROM da visualização em linha densesales. Usamos uma junção externa particionada de vista agregada v e exibição de tempo t para preencher lacunas nos dados de vendas ao longo da dimensão do tempo. A saída da união externa particionada é então processada pela função analítica SUM. OVER para calcular as vendas semanais do ano até à data (a coluna weeklyytdsales). Assim, a visão densesales calcula os dados de vendas do ano até à data para cada semana, incluindo aqueles que faltam na visão agregada s. A exibição em linha yearoveryearsales então calcula ano a ano as vendas semanais semestralmente usando a função LAG. A função LAG denominada weeklyytdsalesprioryear especifica uma cláusula PARTITION BY que combina linhas para a mesma semana dos anos 2000 e 2001 em uma única partição. Em seguida, passamos um deslocamento de 1 para a função LAG para obter as vendas do ano semanal para o ano anterior. O bloco de consulta mais externo seleciona dados de yearoveryearsales com a condição yr 2001. e, portanto, a consulta retorna, para cada produto, é semanal Vendas no acumulado do ano nas semanas especificadas dos anos de 2001 e 2000. Comparação Período-Período para vários níveis de tempo: Exemplo Embora o exemplo anterior nos mostre uma maneira de criar comparações para um único nível de tempo, seria ainda mais Útil para lidar com vários níveis de tempo em uma única consulta. Por exemplo, podemos comparar as vendas em relação ao período anterior nos níveis de ano, trimestre, mês e dia. Como podemos criar uma consulta que realiza uma comparação de ano a ano sobre as vendas do acumulado para todos os níveis da hierarquia do tempo. Vamos realizar várias etapas para realizar esta tarefa. O objetivo é uma única consulta com comparações ao nível do dia, semana, mês, trimestre e ano. Os passos são os seguintes: criaremos uma visão chamada cubeprodtime. Que possui um cubo hierárquico de vendas agregado em tempos e produtos. Em seguida, criaremos uma visão da dimensão do tempo para usar como uma ponta do cubo. A borda do tempo, que contém um conjunto completo de datas, será particionada externa juntada aos dados esparsos na vista cubeprodtime. Finalmente, para obter o máximo desempenho, criaremos uma visão materializada, mvprodtime. Construído usando a mesma definição como cubeprodtime. Para obter mais informações sobre cubos hierárquicos, consulte o Capítulo 21, SQL para agregação em data warehouses. A visualização materializada é definida na Etapa 1 na seção a seguir. Passo 1 Crie a visão do cubo hierárquico A visualização materializada mostrada no seguinte pode existir no seu sistema se não, crie-o agora. Se você deve gerá-lo, note que limitamos a consulta apenas a dois produtos para manter o tempo de processamento curto: porque essa visão é limitada a dois produtos, ele retorna um pouco mais de 2200 linhas. Observe que a coluna HierarchicalTime contém representações de seqüência de tempo de todos os níveis da hierarquia de tempo. A expressão CASE usada para a coluna HierarchicalTime anexa um marcador (0, 1.) a cada string de data para indicar o nível de tempo do valor. A 0 representa o nível do ano, 1 é quarto, 2 meses e 3 é o dia. Observe que a cláusula GROUP BY é um ROLLUP concatenado que especifica a hierarquia de rollup para o tempo e as dimensões do produto. A cláusula GROUP BY é o que determina o conteúdo hierárquico do cubo. Passo 2 Crie o tempo de exibição de exibição, que é um conjunto completo de valores de data. Edgetime é a fonte para preencher lacunas de tempo no cubo hierárquico usando uma junção externa particionada. A coluna HierarchicalTime em edgetime será usada em uma junção particionada com a coluna HierarchicalTime na vista cubeprodtime. A seguinte declaração define edgetime: Etapa 3 Crie a visualização materializada mvprodtime para suportar um desempenho mais rápido. A definição de exibição materializada é uma duplicata da vista cubeprodtime definida anteriormente. Como é uma consulta duplicada, as referências ao cubeprodtime serão reescritas para usar a visão materializada do mvprodtime. O material que se segue pode já existir no seu sistema se não, crie-o agora. Se você deve gerá-lo, observe que limitamos a consulta apenas a dois produtos para manter o tempo de processamento curto. Passo 4 Crie a consulta de comparação Nós estabelecemos o cenário para a nossa consulta de comparação. Podemos obter cálculos de comparação de período a período em todos os níveis de tempo. Ele requer a aplicação de funções analíticas a um cubo hierárquico com dados densos ao longo da dimensão temporal. Alguns dos cálculos que podemos alcançar para cada nível de tempo são: Soma das vendas para o período anterior em todos os níveis de tempo. Variação nas vendas ao longo do período anterior. Soma das vendas no mesmo período de um ano atrás em todos os níveis de tempo. Variação nas vendas em relação ao mesmo período do ano passado. O seguinte exemplo executa todos os quatro desses cálculos. Ele usa uma junção externa particionada das vistas cubeprodtime e edgetime para criar uma visão em linha de dados densos chamados densecubeprodtime. A consulta então usa a função LAG da mesma maneira que o exemplo anterior de nível único. A cláusula WHERE externa especifica o tempo em três níveis: os dias de agosto de 2001, todo o mês e todo o terceiro trimestre de 2001. Observe que as últimas duas linhas dos resultados contêm as agregações de nível de mês e quarto. Observe que fazer Os resultados são mais fáceis de ler se você estiver usando o SQLPlus, os cabeçalhos das colunas devem ser ajustados com os seguintes comandos. Os comandos dobrarão os cabeçalhos das colunas para reduzir o comprimento da linha: Aqui está a consulta que compara as vendas atuais com vendas anteriores e ano anterior: a primeira função LAG (salespriorperiod) divide os dados no gidp. gato. Subcat. Prod. Gidt e ordena as linhas em todas as colunas de dimensão do tempo. Obtém o valor de vendas do período anterior passando um deslocamento de 1. A segunda função LAG (salessameperiodprioryprioryear) particiona os dados em colunas adicionais qtrnum. Monnum. E daynum e ordena-lo em ano para que, com um deslocamento de 1, ele pode calcular as vendas do ano passado no mesmo período. A cláusula SELECT mais externa calcula as variações. Criando um membro personalizado em uma dimensão: Exemplo Em muitas tarefas SQL analíticas, é útil definir membros personalizados em uma dimensão. Por exemplo, você pode definir um período de tempo especializado para análises. Você pode usar uma junção externa particionada para adicionar temporariamente um membro a uma dimensão. Observe que a nova cláusula SQL MODEL é adequada para criar cenários mais complexos envolvendo novos membros em dimensões. Consulte o Capítulo 23, SQL for Modeling para obter mais informações sobre este tópico. Como um exemplo de uma tarefa, e se quisermos definir um novo membro para a nossa dimensão de tempo, queremos criar um 13º membro do nível do mês em nossa dimensão de tempo. Este 13º mês é definido como o somatório das vendas para cada produto no primeiro mês de cada trimestre do ano de 2001. A solução tem duas etapas. Note que vamos construir esta solução usando as visualizações e tabelas criadas no exemplo anterior. São necessárias duas etapas. Primeiro, crie uma vista com o novo membro adicionado à dimensão apropriada. A vista usa uma operação UNION ALL para adicionar o novo membro. Para consultar usando o membro personalizado, use uma expressão CASE e uma junção externa particionada. O nosso novo membro para a dimensão do tempo é criado com a seguinte visão: nesta declaração, o timecão de exibição é definido executando um UNION ALL da exibição edgetime (definido no exemplo anterior) e o 13º mês definido pelo usuário. O valor gidt de 8 foi escolhido para diferenciar o membro personalizado dos membros padrão. O UNION ALL especifica os atributos para um membro do 13º mês fazendo um SELECT da tabela DUAL. Observe que o ID do agrupamento, coluna gidt. É definido como 8 e o número do trimestre é definido como 5. Então, o segundo passo é usar uma visualização em linha da consulta para executar uma junção externa particionada de cubeprodtime com timec. Esta etapa cria dados de vendas para o 13º mês em cada nível de agregação de produtos. Na consulta principal, a função analítica SUM é usada com uma expressão CASE para calcular o 13º mês, que é definido como o somatório das vendas de primeiros meses de cada trimestre. A função SUM usa um CASO para limitar os dados aos meses 1, 4, 7 e 10 dentro de cada ano. Devido ao pequeno conjunto de dados, com apenas 2 produtos, os valores cumulativos dos resultados são necessariamente repetições de agregações de nível inferior. Para um conjunto mais realista de valores cumulativos, você pode incluir mais produtos das subcategorias Game Console e Y Box Games na visualização materializada subjacente. Diversas capacidades de análise e relatórios Esta seção ilustra os seguintes recursos analíticos adicionais: WIDTHBUCKET Função Para uma determinada expressão, a função WIDTHBUCKET retorna o número do balde que o resultado dessa expressão será atribuído após a avaliação. Você pode gerar histogramas de equiwidth com esta função. Equiwidth histogramas dividem conjuntos de dados em baldes cujo tamanho de intervalo (valor mais alto para menor valor) é igual. O número de linhas detidas por cada balde variará. Uma função relacionada, NTILE. Cria baldes de equiheight. Equiwidth histogramas só podem ser gerados para tipos numéricos, data ou data-hora. Portanto, os três primeiros parâmetros devem ser todas as expressões numéricas ou todas as expressões de data. Não são permitidos outros tipos de expressões. Se o primeiro parâmetro for NULL. O resultado é NULL. Se o segundo ou o terceiro parâmetro for NULL. Uma mensagem de erro é retornada, pois um valor NULL não pode indicar qualquer ponto final (ou qualquer ponto) para um intervalo em uma data ou uma dimensão de valor numérico. O último parâmetro (número de baldes) deve ser uma expressão numérica que avalie para um valor inteiro positivo 0, NULL. Ou um valor negativo resultará em um erro. Os baldes estão numerados de 0 a (n 1). O balde 0 contém a contagem de valores inferiores ao mínimo. O balde (n 1) contém a contagem de valores maiores ou iguais ao valor máximo especificado. WIDTHBUCKET Sintaxe O WIDTHBUCKET leva quatro expressões como parâmetros. O primeiro parâmetro é a expressão que o histograma equiwidth é para. Os segundo e terceiro parâmetros são expressões que indicam os pontos finais do intervalo aceitável para o primeiro parâmetro. O quarto parâmetro indica o número de baldes. Considere os seguintes dados dos clientes da tabela. Que mostra os limites de crédito de 17 clientes. Estes dados são reunidos na consulta mostrada no Exemplo 22-24. Na tabela de clientes. A coluna custcreditlimit contém valores entre 1500 e 15000, e podemos atribuir os valores a quatro baldes de equiwidth, numerados de 1 a 4, usando WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Idealmente, cada balde é um intervalo fechado aberto da linha do número real, por exemplo, o número 2 do balde é atribuído a pontuações entre 5000.0000 e 9999.9999. Às vezes denotado 5000, 10000) para indicar que 5.000 estão incluídos no intervalo e 10.000 são excluídos. Para acomodar valores fora do intervalo 0, 20.000), valores inferiores a 0 são atribuídos a um balde de fluxo inferior designado que é numerado 0 e valores atribuídos a um balde de transbordo designado que é numerado 5 (baldes numéricos 1 em geral). Consulte a Figura 22-3 para obter uma ilustração gráfica de como os compartimentos são atribuídos. 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. Most people are familiar with the phrase, quotthis will kill two birds with one stonequot. If you39re not, the phase refers to an approach that addresses two objectives in one action. (Unfortunately, the expression itself is rather unpleasant, as most of us don39t want to throw stones at innocent animals) Today I39m going to cover some basics on two great features in SQL Server: the Columnstore index (available only in SQL Server Enterprise) and the SQL Query Store . Microsoft actually implemented the Columnstore index in SQL 2012 Enterprise, though they39ve enhanced it in the last two releases of SQL Server. Microsoft introduced the Query Store in SQL Server 2016. So, what are these features and why are they important Well, I have a demo that will introduce both features and show how they can help us. Before I go any further, I also cover this (and other SQL 2016 features) in my CODE Magazine article on new features SQL 2016. As a basic introduction, the Columnstore index can help speed up queries that scanaggregate over large amounts of data, and the Query Store tracks query executions, execution plans, and runtime statistics that you39d normally need to collect manually. Trust me when I say, these are great features. For this demo, I39ll be using the Microsoft Contoso Retail Data Warehouse demo database. Loosely speaking, Contoso DW is like quota really big AdventureWorksquot, with tables containing millions of rows. (The largest AdventureWorks table contains roughly 100,000 rows at most). You can download the Contoso DW database here: microsoften-usdownloaddetails. aspxid18279. Contoso DW works very well when you want to test performance on queries against larger tables. Contoso DW contains a standard data warehouse Fact table called FactOnLineSales, with 12.6 million rows. That39s certainly not the largest data warehouse table in the world, but it39s not child39s play either. Suppose I want to summarize product sales amount for 2009, and rank the products. I might query the fact table and join to the Product Dimension table and use a RANK function, like so: Here39s a partial result set of the top 10 rows, by Total Sales. On my laptop (i7, 16 GB of RAM), the query takes anywhere from 3-4 seconds to run. That might not seem like the end of the world, but some users might expect near-instant results (the way you might see near-instant results when using Excel against an OLAP cube). The only index I currently have on this table is a clustered index on a sales key. If I look at the execution plan, SQL Server makes a suggestion to add a covering index to the table: Now, just because SQL Server suggests an index, doesn39t mean you should blindly create indexes on every quotmissing indexquot message. However, in this instance, SQL Server detects that we are filtering based on year, and using the Product Key and Sales Amount. So, SQL Server suggests a covering index, with the DateKey as the index key field. The reason we call this a quotcoveringquot index is because SQL Server will quotbring along the non-key fieldsquot we used in the query, quotfor the ridequot. That way, SQL Server doesn39t need to use the table or the clustered index at all the database engine can simply use the covering index for the query. Covering indexes are popular in certain data warehousing and reporting database scenarios, though they do come at a cost of the database engine maintaining them. Note: Covering indexes have been around for a long time, so I haven39t yet covered the Columnstore index and the Query Store. So, I will add the covering index: If I re-execute the same query I ran a moment ago (the one that aggregated the sales amount for each product), the query sometimes seems to run about a second faster, and I get a different execution plan, one that uses an Index Seek instead of an Index Scan (using the date key on the covering index to retrieve sales for 2009). So, prior to the Columnstore Index, this could be one way to optimize this query in much older versions of SQL Server. It runs a little faster than the first one, and I get an execution plan with an Index Seek instead of an Index Scan. However, there are some issues: The two execution operators quotIndex Seekquot and quotHash Match (Aggregate)quot both essentially operate quotrow by rowquot. Imagine this in a table with hundreds of millions of rows. Related, think about the contents of a fact table: in this case, a single date key value andor a single product key value might be repeated across hundreds of thousands of rows (remember, the fact table also has keys for geography, promotion, salesman, etc.) So, when the quotIndex Seekquot and quotHash Matchquot work row by row, they are doing so over values that might be repeated across many other rows. This is normally where I39d segue to the SQL Server Columnstore index, which offers a scenario to improve the performance of this query in amazing ways. But before I do that, let39s go back in time. Let39s go back to the year 2010, when Microsoft introduced an add-in for Excel known as PowerPivot. Many people probably remember seeing demos of PowerPivot for Excel, where a user could read millions of rows from an outside data source into Excel. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed quotVertiPaqquot. This in-memory technology in PowerPivot would basically take duplicate business keyforeign key values and compress them down to a single vector. The in-memory technology would also scanaggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box. Why am I taking this little stroll down memory lane Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains. So, I39m going to create a columnstore index on the table, and I39ll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I39ll create a duplicate copy of FactOnlineSales (I39ll call it FactOnlineSalesDetailNCCS), and I39ll create a columnstore index on the duplicated table that way I won39t interfere with the original table and the covering index in any way. Next, I39ll create a columnstore index on the new table: Note several things: I39ve specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no quotkeyquot. We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore. To use the analogy of PowerPivot for Excel when we create a columnstore index, we39re telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot So, I39ll re-run the query, this time using the duplicated FactOnlineSalesDetailNCCS table that contains the columnstore index. This query runs instantly in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial quotbat of an eyelashquot. We could look at the execution plan (and in a few moments, we will), but now it39s time to cover the Query Store feature. Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we39d like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we39d like to see the same statistics that we39d be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query. Well, that39s what the Query Store allows us to do we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later. So, I39m going to enable the Query Store on the Contoso database with the following command (and I39ll also clear out any caching): Then I39ll run the two queries (and quotpretendquot that I ran them hours ago): Now let39s pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them Fortunately, that39s quite easy. If I expand the Contoso DW database, I39ll see a Query Store folder. The Query Store has tremendous functionality and I39ll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index. So I39ll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don39t lie the columnstore index outperformed the original tablecovering index by a factor of almost 7 to 1. I can change the metric to look at memory consumption instead. In this case, note that query 2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents quotin-memoryquot technology SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index. OK, so we have some graphs to view execution statistics can we see the execution plan (and execution operators) associated with each execution Yes, we can If you click on the vertical bar for the query that used the columnstore index, you39ll see the execution plan below. The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100 of the cost of the query. You might be saying, quotWait a minute, the first query used a covering index and performed an index seek so how can a columnstore index scan be fasterquot That39s a legitimate question, and fortunately there39s an answer. Even when the first query performed an index seek, it still executed quotrow by rowquot. If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW . which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently. Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query) Finally, you might be asking, quotOK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel but my query only wanted data for 2009. So is SQL Server scanning over the entire set of dataquot Again, a good question. The answer is, quotNot reallyquot. Fortunately for us, the new columnstore index buffer pool performs another function called quotsegment eliminationquot. Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009. I39ll stop here. In subsequent blog posts I39ll cover both the columnstore index and Query Store in more detail. Essentially, what we39ve seen here today is that the Columnstore index can significantly speed up queries that scanaggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later. In the end, we39d like to produce a result set that shows the following. Notice three things: The columns essentially pivot all of the possible Return Reasons, after showing the sales amount The result set contains subtotals by the week ending (Sunday) date across all clients (where the Client is NULL) The result set contains a grand total row (where the Client and Date are both NULL) First, before I get into the SQL end we could use the dynamic pivotmatrix capability in SSRS. We would simply need to combine the two result sets by one column and then we could feed the results to the SSRS matrix control, which will spread the return reasons across the columns axis of the report. However, not everyone uses SSRS (though most people should). But even then, sometimes developers need to consume result sets in something other than a reporting tool. So for this example, let39s assume we want to generate the result set for a web grid page and possibly the developer wants to quotstrip outquot the subtotal rows (where I have a ResultSetNum value of 2 and 3) and place them in a summary grid. So bottom line, we need to generate the output above directly from a stored procedure. And as an added twist next week there could be Return Reason X and Y and Z. So we don39t know how many return reasons there could be. We simple want the query to pivot on the possible distinct values for Return Reason. Here is where the T-SQL PIVOT has a restriction we need to provide it the possible values. Since we won39t know that until run-time, we need to generate the query string dynamically using the dynamic SQL pattern. The dynamic SQL pattern involves generating the syntax, piece by piece, storing it in a string, and then executing the string at the end. Dynamic SQL can be tricky, as we have to embed syntax inside a string. But in this case, it our only true option if we want to handle a variable number of return reasons. I39ve always found that the best way to create a dynamic SQL solution is by figuring out what the quotidealquot generated-query would be at the end (in this case, given the Return reasons we know about).and then reverse-engineering it by piecing it together one part at a time. And so, here is the SQL we need if we knew those Return Reasons (A through D) were static and would not change. The query does the following: Combines the data from SalesData with the data from ReturnData, where we quothard-wirequot the word Sales as an Action Type form the Sales Table, and then use the Return Reason from the Return Data into the same ActionType column. That will give us a clean ActionType column on which to pivot. We are combining the two SELECT statements into a common table expression (CTE), which is basically a derived table subquery that we subsequently use in the next statement (to PIVOT) A PIVOT statement against the CTE, that sums the dollars for the Action Type being in one of the possible Action Type values. Note that this isn39t the final result set. We are placing this into a CTE that reads from the first CTE. The reason for this is because we want to do multiple groupings at the end. The final SELECT statement, that reads from the PIVOTCTE, and combines it with a subsequent query against the same PIVOTCTE, but where we also implement two groupings in the GROUPING SETS feature in SQL 2008: GROUPING by the Week End Date (dbo. WeekEndingDate) GROUPING for all rows () So if we knew with certainty that we39d never have more return reason codes, then that would be the solution. However, we need to account for other reason codes. So we need to generate that entire query above as one big string where we construct the possible return reasons as one comma separated list. I39m going to show the entire T-SQL code to generate (and execute) the desired query. And then I39ll break it out into parts and explain each step. So first, here39s the entire code to dynamically generate what I39ve got above. There are basically five steps we need to cover. Passo 1 . we know that somewhere in the mix, we need to generate a string for this in the query: SalesAmount, Reason A, Reason B, Reason C, Reason D0160016001600160 What we can do is built a temporary common table expression that combines the hard wired quotSales Amountquot column with the unique list of possible reason codes. Once we have that in a CTE, we can use the nice little trick of FOR XML PATH(3939) to collapse those rows into a single string, put a comma in front of each row that the query reads, and then use STUFF to replace the first instance of a comma with an empty space. This is a trick that you can find in hundreds of SQL blogs. So this first part builds a string called ActionString that we can use further down. Passo 2 . we also know that we39ll want to SUM the generatedpivoted reason columns, along with the standard sales column. So we39ll need a separate string for that, which I39ll call SUMSTRING. I39ll simply use the original ActionString, and then REPLACE the outer brackets with SUM syntax, plus the original brackets. Step 3: Now the real work begins. Using that original query as a model, we want to generate the original query (starting with the UNION of the two tables), but replacing any references to pivoted columns with the strings we dynamically generated above. Also, while not absolutely required, I39ve also created a variable to simply any carriage returnline feed combinations that we want to embed into the generated query (for readability). So we39ll construct the entire query into a variable called SQLPivotQuery. Passo 4. We continue constructing the query again, concatenating the syntax we can quothard-wirequot with the ActionSelectString (that we generated dynamically to hold all the possible return reason values) Step 5 . Finally, we39ll generate the final part of the Pivot Query, that reads from the 2 nd common table expression (PIVOTCTE, from the model above) and generates the final SELECT to read from the PIVOTCTE and combine it with a 2 nd read against PIVOTCTE to implement the grouping sets. Finally, we can quotexecutequot the string using the SQL system stored proc spexecuteSQL So hopefully you can see that the process to following for this type of effort is Determine what the final query would be, based on your current set of data and values (i. e. built a query model) Write the necessary T-SQL code to generate that query model as a string. Arguably the most important part is determining the unique set of values on which you39ll PIVOT, and then collapsing them into one string using the STUFF function and the FOR XML PATH(3939) trick So whats on my mind today Well, at least 13 items Two summers ago, I wrote a draft BDR that focused (in part) on the role of education and the value of a good liberal arts background not just for the software industry but even for other industries as well. One of the themes of this particular BDR emphasized a pivotal and enlightened viewpoint from renowned software architect Allen Holub regarding liberal arts. Ill (faithfully) paraphrase his message: he highlighted the parallels between programming and studying history, by reminding everyone that history is reading and writing (and Ill add, identifying patterns), and software development is also reading and writing (and again, identifying patterns). And so I wrote an opinion piece that focused on this and other related topics. But until today, I never got around to either publishingposting it. Every so often Id think of revising it, and Id even sit down for a few minutes and make some adjustments to it. But then life in general would get in the way and Id never finish it. So what changed A few weeks ago, fellow CoDe Magazine columnist and industry leader Ted Neward wrote a piece in his regular column, Managed Coder , that caught my attention. The title of the article is On Liberal Arts. and I highly recommend that everyone read it. Ted discusses the value of a liberal arts background, the false dichotomy between a liberal arts background and success in software development, and the need to writecommunicate well. He talks about some of his own past encounters with HR personnel management regarding his educational background. He also emphasizes the need to accept and adapt to changes in our industry, as well as the hallmarks of a successful software professional (being reliable, planning ahead, and learning to get past initial conflict with other team members). So its a great read, as are Teds other CoDe articles and blog entries. It also got me back to thinking about my views on this (and other topics) as well, and finally motivated me to finish my own editorial. So, better late than never, here are my current Bakers Dozen of Reflections: I have a saying: Water freezes at 32 degrees . If youre in a trainingmentoring role, you might think youre doing everything in the world to help someone when in fact, theyre only feeling a temperature of 34 degrees and therefore things arent solidifying for them. Sometimes it takes just a little bit more effort or another ideachemical catalyst or a new perspective which means those with prior education can draw on different sources. Water freezes at 32 degrees . Some people can maintain high levels of concentration even with a room full of noisy people. Im not one of them occasionally I need some privacy to think through a critical issue. Some people describe this as you gotta learn to walk away from it. Stated another way, its a search for the rarefied air. This past week I spent hours in half-lit, quiet room with a whiteboard, until I fully understood a problem. It was only then that I could go talk with other developers about a solution. The message here isnt to preach how you should go about your business of solving problems but rather for everyone to know their strengths and what works, and use them to your advantage as much as possible. Some phrases are like fingernails on a chalkboard for me. Use it as a teaching moment is one. (Why is it like fingernails on a chalkboard Because if youre in a mentoring role, you should usually be in teaching moment mode anyway, however subtly). Heres another I cant really explain it in words, but I understand it. This might sound a bit cold, but if a person truly cant explain something in words, maybe they dont understand. Sure, a person can have a fuzzy sense of how something works I can bluff my way through describing how a digital camera works but the truth is that I dont really understand it all that well. There is a field of study known as epistemology (the study of knowledge). One of the fundamental bases of understanding whether its a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc. Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Ler. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some. NET code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff. net November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. So there you have it. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from. NET, youre in luck. You can pass an ADO. NET data table (with the same tablename property as the name of the Table Type) to the procedure. For. NET developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment.