Oracle Media Móvel Consulta

Esta é uma questão Evergreen Joe Celko. Ignoro qual plataforma DBMS é usada. Mas, em qualquer caso, Joe conseguiu responder há mais de 10 anos com o SQL padrão. Joe Celko SQL Puzzles and Answers citação: Essa última tentativa de atualização sugere que poderíamos usar o predicado para construir uma consulta que nos daria uma média móvel: a coluna extra ou a abordagem de consulta melhor. A consulta é tecnicamente melhor porque a abordagem UPDATE Desmoralize o banco de dados. No entanto, se os dados históricos que estão sendo registrados não mudem e o cálculo da média móvel é caro, você pode considerar usar a abordagem da coluna. Consulta SQL Puzzle: por todos os meios uniforme. Você simplesmente joga no balde de peso apropriado dependendo da distância do ponto de tempo atual. Por exemplo, quottake weight1 para datapoints dentro de 24 horas a partir do ponto de dados atual0.5 para pontos de dados dentro de 48hrsquot. Esse caso é importante quantos pontos de dados consecutivos (como 6:12 am e 11:48 pm) estão distantes um do outro Um caso de uso que eu posso pensar seria uma tentativa de alisar o histograma sempre que os pontos de dados não são suficientemente densos. 22:22 Não tenho certeza se o resultado esperado (saída) mostra uma média simples de movimentação simples (rolando) durante 3 dias. Como, por exemplo, o primeiro triplo dos números, por definição, dá: mas você espera 4.360 e é confuso. No entanto, sugiro a seguinte solução, que usa AVG de função de janela. Essa abordagem é muito mais eficiente (clara e menos intensiva em recursos) do que a SELF-JOIN introduzida em outras respostas (e estou surpreso que ninguém tenha dado uma solução melhor). Você vê que o AVG está envolvido com o caso quando rownum gt p. days, em seguida, para forçar NULL s nas primeiras linhas, onde 3 Day Moving Average não tem sentido. Respondeu 23 de fevereiro às 13:12. Podemos aplicar o método de junção externa suja esquerda de Joe Celkos (como citado acima por Diego Scaravaggi) para responder a pergunta conforme foi solicitado. Gera o resultado solicitado: respondeu Jan 9 16 às 0:33 Sua resposta 2017 Stack Exchange, IncHá uma boa pergunta sobre a OTN hoje sobre se há uma função Oracle padrão para calcular a média móvel exponencial. A resposta é que não há essa função, mas com a cláusula modelo, você pode calcular isso muito facilmente. E é um ótimo exemplo do que quero dizer com um número variável de cálculos com base em valores calculados, escrito na minha terceira parte do tutorial da cláusula modelo. Antes de hoje, nem sabia o que era uma média móvel exponencial. Você pode ler mais sobre isso aqui na Wikipedia ou aqui com um bom exemplo. Do primeiro link: uma média móvel exponencial (EMA), aplica fatores de ponderação que diminuem exponencialmente. A ponderação para cada ponto de dados mais antigo diminui exponencialmente, dando muito mais importância às observações recentes, enquanto ainda não descarta as observações antigas inteiramente. A partir do segundo link: A fórmula para o cálculo de uma média móvel exponencial (EMA) é: X EMA atual (ou seja, EMA a ser calculado) C Valor de dados original atual K Constante de suavização P EMA anterior (O primeiro EMA no intervalo a ser calculado é Arbitrária e pode ser o valor de dados original correspondente ou, muitas vezes, um valor de média móvel simples. K Smoothing Constant 2 (1 n) E esta fórmula é seguida por um exemplo que eu estendi um pouco, usando esta tabela: Os registros do produto A Combine o exemplo no link. Eu compus os números do produto B. Aqui está a consulta da cláusula do modelo que implementa a fórmula. Observe como a fórmula se traduz diretamente na única e única regra da cláusula do modelo. A constante de suavização K está configurada Para .5, com base em uma janela de valores (n) igual a 3. Desafio: tente isso sem a cláusula do modelo e veja se você pode encontrar algo mais abrangente. 5 comentários: 11.2 recursos em uso com isso como (selecione o produto 39A39 . Data 392009-01-0139 mês, 10 quantidade de união dupla, todos selecionados 39A39, data 392009-02-0139, 15 de dupla união, todos selecionam 39A39, data 392009-03-0139, 17 de união dupla, todos selecionam 39A39, data 392009-04-0139, 20 de união dupla Todos selecionam 39A39, data 392009-05-0139, 22 de dupla união, todos selecionam 39A39, data 392009-06-0139, 20 de dupla união, selecione tudo 39A39, data 392009-07-0139, 25 de dupla união, selecione tudo 39A39, data 392009-08-0139, 27 de dupla união, todos selecionam 39A39, data 392009-09-0139, 30 de união dupla, todos selecionados 39A39, data 392009-10-0139, 35 de união dupla, todos selecionados 39A39, data 392009-11-0139 , 37 de dupla união, todos selecionam 39A39, data 392009-12-0139, 40 de dupla união, todos selecionam 39B39, data 392009-01-0139, 0 de dupla união, todos selecionam 39B39, data 392009-02-0139, 50 de dupla união Todos selecionam 39B39, data 392009-03-0139, 10 da união dupla, selecione 39B39, data 392009-04-0139, 40 da união dupla, selecione 39B39, data 392009-05-0139, 15 da dupla união, selecione tudo 39B39, data 392009-06-0139, 35 de União dupla, todos selecionam 39B39, data 392009-07-0139, 30 de dupla união, todos selecionam 39B39, data 392009-08-0139, 30, de dupla união, todos selecionam 39B39, data 392009-09-0139, 20, de dupla união, todos os selecionados 39B39 , Data 392009-10-0139, 20 da união dupla, selecione 39B39, data 392009-11-0139, 20 da união dupla, selecione 39B39, data 392009-12-0139, 20 a partir de dual), rns como (selecione isso. . Rownumber () over (partição por produto ordem por mês) rn -. 2 (1count () over (partição por produto)) k. 0,5 k do dat), res (produto, mês, quantidade, rn, x) como (selecione r. product, r. month, r. amount, r. rn, r. mount x de rns r onde rn 1 union seleciona todos Ns. prodt, ns. month, ns. amount, ns. rn, ns. k (ns. amount - es. x) es. xx de rns ns, res es onde ns. rn es. rn 1 e ns. product es. produto) selecione o produto, o mês, o montante, o rn, a rodada (x, 3) EMA a partir do pedido de res por produto, mês após a computação do formulário fechado, criei o seguinte código que, se mais como uma ofuscação do que qualquer coisa abrangente. A idéia é criar múltiplos em execução usando uma concatenação de string e a funcionalidade xml-eval. As formas fechadas dos casos especiais apenas precisam de somas em execução. Há um caso geral e dois casos especiais que são muito mais fáceis: com t1 como (selecionar produto, mês, quantidade, quantidade ci, rownumber () over (partição por ordem do produto por mês) rn, --2 (1 rownumber () over (Partição por ordem de produto por mês)) ki 0,5 ki de vendas), t2 como (selecione produto, mês, quantidade, (caso quando rn 1 e 1 mais ki end ci) ai, caso quando rn 1 e mais 1 (1 - Ki) fim bi de t1), t3 como (SELECIONE o produto, MONTH, amount, ai, xmlquery (REPLACE (wmconcat (bi) over (PARTITION BY product ORDER BY MONTH filas ENTRE INCLUÍDO precedente AND ACTUAL), 39,39, 3939 ) VOLTANDO o conteúdo).getnumberval () mi FROM t2), t4 as (selecione o produto, o mês, o montante, o mi) xi do t3) SELECIONE o produto, MONTH, amount, round (mi SUM (xi) over (PARTITION POR produto ORDER BY MONTH filas ENTRE INCLINADO ANTERIOR E LIGAÇÃO ATUAL), 3) ema DE t4 Caso especial K 0,5: com t1 como (selecionar produto, mês, quantidade, rownumber () sobre (partição por ordem de produto por mês) rn, quantidade Poder (2, nvl (nullif (r (Partição por produto ordem por mês) - 1, 0), 1)) ci de vendas) selecione produto, mês, quantidade, rodada (soma (ci) sobre (partição por ordem do produto por mês linhas entre ilimitado anterior E linha atual) potência (2, rn), 3) ema de t1 Caso especial K 2 (1 i): com t1 como (selecionar produto, mês, quantidade, rownumber () sobre (partição por produto ordem por mês) rn, Quantidade de rownumber () por cima (partição por ordem de produto por mês) ci de vendas) selecione produto, mês, quantidade, rodada (soma (ci) sobre (partição por ordem de produto por mês linhas entre linha não limitada precedente e atual) 2 (rn ( Rn 1)), 3) ema de t1 I39ll publicar a prova do formulário fechado se alguém estiver interessado nela. Este é um ótimo exemplo de quotfun com SQLquot :-) Uma combinação de XMLQuery, wmconcat indocumentado e funções analíticas com a cláusula de janela. Eu gosto disso. Embora não seja tão abrangente como a variante da cláusula modelo e os Rafu39s recursivos com um, como você disse a si mesmo. E com certeza, gostaria de ver a prova da forma fechada. Abordei outra questão: como otimizar a constante de suavização SELECT k - constante de suavização. Mse - erro quadrado médio FROM (SELECIONE DE VENDAS MODELO DIMENSÃO POR (produto. ROWNUMBER () OVER (PARTITION BY product ORDER BY month ASC) rn) MEDIDAS (montante - valor das vendas mês - mês. 0 AS C. 0 AS P. 0 AS X. 0 AS SE - erro ao quadrado - - linha de trabalho e atributos - a) linha de trabalho é o produto 39X39, rn 1 - b) os atributos de trabalho são os seguintes:. 0 AS SSE - sum SE para todos os meses de produtos. 0 AS MSE - significa SSE para todos os produtos. 0 AS k - para todos os meses de produtos. 0 AS PreMSE - MSE anterior de k39s para todos os meses de produtos. 0 AS diff - entre MSE atual e anterior. 0,1 AS delta - incremento inicial. 0 AS priorpt - ponto de partida inicial -) REGRAS ITERATE (99) ATÉ (abs (diff39A39,1) lt 0.00010) (Cany, rn amountcv (), cv (). K39A39,1 priorpt39A39,1 delta39A39,1. Xany Rn ORDEM POR produto, rn ASC COALESCE (K39A39,1 Ccv (), cv () (1-K39A39,1) Xcv (), cv () - 1, Ccv (), cv ()). Pproduct, rn Xcv (), Cv () - 1. SEproduct, rn POWER (Ccv (), cv () - Xcv (), cv () - 1, 2). SSE39A39,1 SUM (SE) qualquer, qualquer. MSE39A39,1 SUM (SE) qualquer, qualquer 24. Diff39A39,1 CASE iterationnumber QUANDO 0 então NULL ELSE preMSE39A39,1 - MSE39A39,1 END. PreMSE39A39,1 MSE39A39,1. Delta39A39,1 CASO QUANDO diff39A39,1 lt 0 então - abs (delta39A39, 12) ELSE abs (delta39A39,1) END. Priorpt39A39,1 K39A39,1)) onde o produto 39A39 e rn 1 K MSE ---------- ---------- .599999237 174.01609421 SQL para Análise e Relatórios Tratamento de NULL como Entrada para Funções de Janela Funções de janela As semânticas NULL combinam as semânticas NULL para funções agregadas de SQL. Outra semântica pode ser obtida por funções definidas pelo usuário, ou usando a expressão DECODE ou CASE dentro da função de janela. Funções de viragem com deslocamento lógico Um deslocamento lógico pode ser especificado com constantes tais como RANGE 10 PRECEDING. Ou uma expressão que avalia a uma constante, ou por uma especificação de intervalo como INTERVALO DE GANHAS N DAY MÊS ANO PRECEDING ou uma expressão que avalia em um intervalo. Com deslocamento lógico, só pode haver uma expressão na lista de expressões ORDER BY na função, com o tipo compatível com NUMERIC se o offset for numérico ou DATA se for especificado um intervalo. Exemplo 21-7 Função agregada cumulativa O seguinte é um exemplo de quantidade agregada acumulada por ID de cliente por trimestre em 1999: neste exemplo, a função analítica SUM define, para cada linha, uma janela que começa no início da partição (UNBOUNDED PRECEDING ) E termina, por padrão, na linha atual. SOM SUMOS aninhados são necessários neste exemplo, uma vez que estamos executando uma SUM sobre um valor que é em si uma SUM. As agregações aninhadas são usadas com muita frequência em funções de agregação analítica. Exemplo 21-8 Movendo a função de agregação Este exemplo de uma janela baseada no tempo mostra, para um cliente, a média móvel das vendas para o mês atual e os dois meses anteriores: Observe que as duas primeiras linhas para o cálculo da média móvel de três meses na Os dados de saída são baseados em um tamanho de intervalo menor do especificado, porque o cálculo da janela não pode ultrapassar os dados recuperados pela consulta. Você precisa considerar os tamanhos de janela diferentes encontrados nas bordas dos conjuntos de resultados. Em outras palavras, talvez seja necessário modificar a consulta para incluir exatamente o que deseja. Função agregada centrada A função de cálculo das funções de agregação de janelas centradas em torno da linha atual é direta. Este exemplo calcula para todos os clientes uma média móvel centrada de vendas por uma semana no final de dezembro de 1999. Encontra uma média do total de vendas para um dia anterior à linha atual e um dia após a linha atual, incluindo a linha atual também. Exemplo 21-9 Agregado Centrado As linhas inicial e final para cada cálculo de média móvel centralizada nos dados de saída são baseadas em apenas dois dias, uma vez que o cálculo da janela não pode ultrapassar os dados recuperados pela consulta. Os usuários precisam considerar os diferentes tamanhos de janela encontrados nas bordas dos conjuntos de resultados: a consulta pode precisar ser ajustada. Ativando funções de agregação na presença de duplicações O exemplo a seguir ilustra como as funções de agregação de janela calculam valores quando existem duplicatas, ou seja, quando várias linhas são retornadas para um único valor de pedido. A consulta recupera a quantidade vendida para vários clientes durante um intervalo de tempo especificado. (Embora usemos uma visualização em linha para definir o nosso conjunto de dados base, não tem significado especial e pode ser ignorado.) A consulta define uma janela em movimento que é executada a partir da data da linha atual para 10 dias antes. Observe que a palavra-chave RANGE É usado para definir a cláusula de janela deste exemplo. Isso significa que a janela pode potencialmente conter várias linhas para cada valor no intervalo. Neste caso, existem três pares de linhas com valores de data duplicados. Exemplo 21-10 Funções agregadas de vinil com deslocamentos logísticos Na saída deste exemplo, todas as datas, exceto 6 de maio e 12 de maio, retornam duas linhas com datas duplicadas. Examine os números comentados à direita da saída para ver como os valores são calculados. Observe que cada grupo entre parênteses representa os valores retornados por um único dia. Observe que este exemplo se aplica somente quando você usa a palavra-chave RANGE em vez da palavra-chave ROWS. Também é importante lembrar que com a GAMA. Você só pode usar 1 expressão ORDER BY nas funções analíticas ORDER BY. Com a palavra-chave ROWS, você pode usar várias ordens por expressões nas funções analíticas ORDER BY. Variando o tamanho da janela para cada linha Há situações em que é útil variar o tamanho de uma janela para cada linha, com base em uma condição especificada. Por exemplo, você pode querer tornar a janela maior para certas datas e menor para outras. Suponha que você deseja calcular a média móvel do preço das ações em três dias úteis. Se você tiver um número igual de linhas para cada dia para todos os dias úteis e não há dias não úteis, você pode usar uma função de janela física. No entanto, se as condições observadas não forem atendidas, você ainda pode calcular uma média móvel usando uma expressão nos parâmetros do tamanho da janela. As expressões em uma especificação de tamanho de janela podem ser feitas em várias fontes diferentes. A expressão pode ser uma referência a uma coluna em uma tabela, como uma tabela de horários. Também pode ser uma função que retorna o limite apropriado para a janela com base em valores na linha atual. A seguinte declaração para um banco de dados de preço de estoque hipotético usa uma função definida pelo usuário em sua cláusula RANGE para definir o tamanho da janela: Nesta declaração, ttimekey é um campo de data. Aqui, fn poderia ser uma função PLSQL com a seguinte especificação: 4 se ttimekey for segunda-feira, terça-feira Se algum dos dias anteriores são feriados, ele ajusta adequadamente o recado. Observe que, quando a janela é especificada usando um número em uma função de janela com ORDER BY em uma coluna de data, então é convertido para significar o número de dias. Você também poderia usar a função de conversão literal de intervalo, como NUMTODSINTERVAL (fn (ttimekey), DAY) em vez de apenas fn (ttimekey) para significar a mesma coisa. Você também pode escrever uma função PLSQL que retorna um valor de tipo de dados INTERVAL. Vinculando funções agregadas com deslocamentos físicos Para as janelas expressas em linhas, as expressões de pedidos devem ser únicas para produzir resultados determinísticos. Por exemplo, a consulta a seguir não é determinista porque o timeid não é exclusivo neste conjunto de resultados. Exemplo 21-11 Funções de agregação de vinhedos com deslocamentos físicos Uma maneira de lidar com esse problema seria adicionar a coluna prodid ao conjunto de resultados e ordenar tanto o timeid quanto o prodid. Funções FIRSTVALUE e LASTVALUE As funções FIRSTVALUE e LASTVALUE permitem que você selecione a primeira e a última linha de uma janela. Essas linhas são especialmente valiosas, porque elas geralmente são usadas como linhas de base nos cálculos. Por exemplo, com uma partição que contém os dados de vendas encomendados por dia, você pode perguntar quanto foi cada dia de vendas em comparação com o primeiro dia de vendas (FIRSTVALUE) do período ou você pode querer saber, para um conjunto de linhas no aumento da ordem do cliente Qual foi o tamanho da porcentagem de cada venda na região em comparação com a maior venda (LASTVALUE) na região Se a opção IGNORE NULLS for usada com FIRSTVALUE. Ele retornará o primeiro valor não nulo no conjunto, ou NULL se todos os valores forem NULL. Se IGNORE NULLS for usado com LASTVALUE. Ele retornará o último valor não nulo no conjunto, ou NULL se todos os valores forem NULL. A opção IGNORE NULLS é particularmente útil para preencher adequadamente uma tabela de inventário. Relatando as Funções de Agregação Depois que uma consulta foi processada, valores agregados como o número de linhas resultantes ou um valor médio em uma coluna podem ser facilmente computados dentro de uma partição e disponibilizados para outras funções de relatório. As funções de agregação de relatórios retornam o mesmo valor agregado para cada linha em uma partição. Seu comportamento em relação a NULL é o mesmo que as funções agregadas de SQL. A sintaxe é: Além disso, as seguintes condições se aplicam: Um asterisco () só é permitido em COUNT () DISTINCT é suportado somente se as funções agregadas correspondentes permitirem que a expressão do valor1 e a expressão do valor2 possam ser qualquer expressão válida envolvendo referências ou agregados de colunas. A cláusula PARTITION BY define os grupos nos quais as funções de janelas seriam computadas. Se a cláusula PARTITION BY estiver ausente, a função será calculada em todo o conjunto de resultados da consulta. As funções de relatório podem aparecer apenas na cláusula SELECT ou na cláusula ORDER BY. O principal benefício das funções de relatório é a capacidade de fazer várias passagens de dados em um único bloco de consulta e acelerar o desempenho da consulta. Consultas como contar o número de vendedores com vendas mais de 10 das vendas da cidade não requerem junções entre blocos de consulta separados. Por exemplo, considere a questão Para cada categoria de produto, encontre a região em que teve vendas máximas. A consulta SQL equivalente usando a função agregada de relatório MAX seria: A consulta interna com a função de agregação de relatórios MAX (SUM (salesoldold)) retorna: Os resultados da consulta completa são: Exemplo 21-12 Exemplo de agregação de relatórios Agregados de relatórios combinados com consultas aninhadas ativadas Você responde consultas complexas de forma eficiente. Por exemplo, e se você quiser conhecer os produtos mais vendidos em suas subcategorias de produtos mais significativas. O seguinte é uma consulta que encontra os 5 produtos mais vendidos para cada subcategoria de produtos que contribui com mais de 20 das vendas dentro da categoria de produto: RATIOTOREPORT Função A função RATIOTOREPORT calcula a relação entre um valor e a soma de um conjunto de valores. Se a expressão do valor da expressão for avaliada em NULL. RATIOTOREPORT também avalia NULL. Mas é tratado como zero para calcular a soma de valores para o denominador. Sua sintaxe é: neste, aplica-se o seguinte: expr pode ser qualquer expressão válida envolvendo referências ou agregados de colunas. A cláusula PARTITION BY define os grupos nos quais a função RATIOTOREPORT deve ser computada. Se a cláusula PARTITION BY estiver ausente, a função será calculada em todo o conjunto de resultados da consulta. Para calcular RATIOTOREPORT de vendas para cada canal, você pode usar a seguinte sintaxe: Funções LAGLEAD As funções LAG e LEAD são úteis para comparar valores quando as posições relativas das linhas podem ser conhecidas de forma confiável. Eles funcionam especificando a contagem de linhas que separam a linha de destino da linha atual. Como as funções fornecem acesso a mais de uma linha de uma tabela ao mesmo tempo sem uma auto-união, elas podem aumentar a velocidade de processamento. A função LAG fornece acesso a uma linha em um determinado deslocamento antes da posição atual, e a função LEAD fornece acesso a uma linha em um determinado deslocamento após a posição atual. Sintaxe LAGLEAD Essas funções possuem a seguinte sintaxe: o deslocamento é um parâmetro opcional e o padrão é 1. o padrão é um parâmetro opcional e é o valor retornado se o deslocamento for fora dos limites da tabela ou partição. Consulte Data Densification for Reporting para obter informações que mostrem como usar as funções LAG LEAD para fazer consultas de comparação período a período em dados esparsos. Funções FIRSTLAST As funções de agregação FIRSTLAST permitem que você classifique um conjunto de dados e trabalhe com suas linhas de melhor classificação ou inferior. Depois de encontrar as linhas classificadas superior ou inferior, uma função agregada é aplicada a qualquer coluna desejada. Ou seja, FIRST LAST permite que você classifique na coluna A, mas retorna o resultado de um agregado aplicado nas linhas de primeiro e segundo lugar da coluna B. Isso é valioso porque evita a necessidade de uma auto-associação ou subconsulta, assim Melhorando o desempenho. A sintaxe dessas funções começa com uma função agregada regular (MIN. MAX. SUM. AVG. COUNT. VARIANCE. STDDEV) que produz um único valor de retorno por grupo. Para especificar o ranking usado, as funções FIRST LAST adicionar uma nova cláusula começando com a palavra KEEP. Sintaxe FIRSTLAST Essas funções possuem a seguinte sintaxe: Observe que a cláusula ORDER BY pode levar várias expressões. FIRSTLAST Como agregados regulares Você pode usar a família de agregados FIRST ÚLTIMO como funções agregadas regulares. Exemplo 21-15 FIRSTLAST Exemplo 1 A consulta a seguir nos permite comparar preço mínimo e preço de lista de nossos produtos. Para cada subcategoria de produtos na categoria Vestuário para homens, ele retorna o seguinte: Preço de lista do produto com o preço mínimo mais baixo Preço mínimo mais baixo Preço de lista do produto com o preço mínimo mais alto Preço mínimo mais alto FIRSTLAST Como agregados de relatórios Você também pode usar o PRIMEIRA ÚLTIMA família de agregados como funções agregadas de relatórios. Um exemplo é calcular quais meses tiveram o maior e menor aumento na contagem de cabeças ao longo do ano. A sintaxe para essas funções é semelhante à sintaxe para qualquer outro agregado de relatórios. Considere o exemplo no Exemplo 21-15 para FIRSTLAST. E se quisermos encontrar os preços de lista de produtos individuais e compará-los com os preços de lista dos produtos em sua subcategoria que apresentaram os preços mínimos mais altos e mínimos. A consulta a seguir nos permite encontrar essas informações para a subcategoria Documentação usando o FIRSTLAST como relatório Agregados. Exemplo 21-16 FIRSTLAST Exemplo 2 Usando as funções FIRST e LAST como agregados de relatórios, facilita a inclusão dos resultados em cálculos Salário como uma porcentagem do salário mais alto. Funções do percentil inverso Com a função CUMEDIST, você pode encontrar a distribuição cumulativa (percentil) de um conjunto de valores. No entanto, a operação inversa (achando o valor que calcula para um determinado percentil) não é nem fácil de fazer nem eficientemente calculada. Para superar essa dificuldade, as funções PERCENTILECONT e PERCENTILEDISC foram introduzidas. Estes podem ser usados ​​tanto como funções de relatórios de janelas quanto em funções agregadas normais. Essas funções precisam de uma especificação de classificação e um parâmetro que leva um valor percentil entre 0 e 1. A especificação de classificação é tratada usando uma cláusula ORDER BY com uma expressão. Quando usado como uma função agregada normal, ele retorna um único valor para cada conjunto ordenado. PERCENTILECONT. Que é uma função contínua calculada por interpolação, e PERCENTILEDISC. Que é uma função de etapa que assume valores discretos. Como outros agregados, PERCENTILECONT e PERCENTILEDISC operam em um grupo de linhas em uma consulta agrupada, mas com as seguintes diferenças: requerem um parâmetro entre 0 e 1 (inclusive). Um parâmetro especificado fora desse intervalo resultará em erro. Esse parâmetro deve ser especificado como uma expressão que se avalia em uma constante. Eles exigem uma especificação de classificação. Esta especificação de classificação é uma cláusula ORDER BY com uma única expressão. Expressões múltiplas não são permitidas. Bases de exemplo do percentil inverso da sintaxe agregada usamos a seguinte consulta para retornar as 17 linhas de dados usadas nos exemplos desta seção: PERCENTILEDISC (x) é calculado escaneando os valores CUMEDIST em cada grupo até encontrar o primeiro maior que Ou igual a x. Onde x é o valor percentil especificado. Para a consulta de exemplo em que PERCENTILEDISC (0,5), o resultado é 5.000, como se mostra a seguir: O resultado de PERCENTILECONT é calculado por interpolação linear entre as linhas depois de ordená-las. Para calcular PERCENTILECONT (x). Primeiro calculamos o número de linha RN (1x (n-1)), onde n é o número de linhas no grupo e x é o valor percentil especificado. O resultado final da função agregada é calculado por interpolação linear entre os valores de linhas em números de linha CRN CEIL (RN) e FRN FLOOR (RN). O resultado final será: PERCENTILECONT (X) se (CRN FRN RN), então (valor da expressão da linha na RN) else (CRN-RN) (valor da expressão para a linha na FRN) (RN - FRN) (valor de Expressão para linha no CRN). Considere a consulta de exemplo anterior, onde calculamos PERCENTILECONT (0.5). Aqui n é 17. O número de linha RN (1 0,5 (n-1)) 9 para ambos os grupos. Colocando isto na fórmula, (FRNCRN9), devolvemos o valor da linha 9 como resultado. Outro exemplo é, se você quiser calcular PERCENTILECONT (0.66). O número de linha calculado RN (1 0.66 (n -1)) (1 0.6616) 11.67. PERCENTILECONT (0,66) (12-11,67) (valor da linha 11) (11,67-11) (valor da linha 12). Esses resultados são: as funções agregadas do percentil inverso podem aparecer na cláusula HAVING de uma consulta como outras funções agregadas existentes. Como relatórios de agregados Você também pode usar as funções agregadas PERCENTILECONT. PERCENTILEDISC como função agregada de relatórios. Quando usado como funções de agregação de relatórios, a sintaxe é semelhante à de outros agregados de relatórios. Esta consulta calcula a mesma coisa (limite de crédito médio para os clientes neste conjunto de resultados, mas relata o resultado para cada linha do conjunto de resultados, conforme mostrado na saída a seguir: Restrições do percentil inverso para PERCENTILEDISC. A expressão na cláusula ORDER BY pode Seja de qualquer tipo de dados que você possa classificar (numérico, string, data e assim por diante). No entanto, a expressão na cláusula ORDER BY deve ser um tipo numérico ou de data e hora (incluindo intervalos) porque a interpolação linear é usada para avaliar o PERCENTILECONT. Se a expressão for do tipo DATE. O resultado interpolado é arredondado para a unidade mais pequena para o tipo. Para um tipo DATE, o valor interpolado será arredondado para o segundo mais próximo, para os tipos de intervalo para o segundo mais próximo (INTERVALO DIA A SEGUNDO) Ou para o mês (ANO DE INTERVALO PARA MÊS).Além de outros agregados, as funções do percentil inverso ignoram os NULL na avaliação do resultado. Por exemplo, quando você deseja encontrar o valor médio em um conjunto, a base de dados Oracle ignora a NUL Ls e encontra a mediana entre os valores não nulos. Você pode usar a opção NULLS FIRST NULLS LAST na cláusula ORDER BY, mas eles serão ignorados à medida que NULLs forem ignorados. Funções de classificação e distribuição hipotéticas Estas funções fornecem funcionalidade útil para a análise do que é o caso. Como exemplo, qual seria o grau de uma linha, se a linha fosse hipoteticamente inserida em um conjunto de outras linhas. Esta família de agregados leva um ou mais argumentos de uma linha hipotética e um grupo de linhas ordenado, retornando o RANK. DENSERANK. PERCENTRANK ou CUMEDIST da linha como se fosse hipoteticamente inserido no grupo. Rank hipotético e sintaxe de distribuição Aqui, a expressão constante refere-se a uma expressão que avalia a uma constante, e pode haver mais de uma dessas expressões que são passadas como argumentos para a função. A cláusula ORDER BY pode conter uma ou mais expressões que definem a ordem de classificação em que a classificação será baseada. ASC. DESC. NULLS PRIMEIRAMENTE. As opções NULLS LAST estarão disponíveis para cada expressão no ORDER BY. Exemplo 21-17 Exemplo Hipotético de Classificação e Distribuição 1 Usando os dados de preços de tabela da tabela de produtos usados ​​nesta seção, você pode calcular o RANK. PERCENTRANK e CUMEDIST para um suéter hipotético com um preço de 50 para a forma como ele se encaixa dentro de cada uma das subcategorias de camisola. A consulta e os resultados são: Ao contrário dos agregados do percentil inverso, a cláusula ORDER BY na especificação de classificação para funções de classificação e distribuição hipotéticas pode levar várias expressões. O número de argumentos e as expressões na cláusula ORDER BY devem ser iguais e os argumentos devem ser expressões constantes do mesmo tipo ou compatível com a expressão ORDER BY correspondente. O seguinte é um exemplo usando dois argumentos em várias funções de classificação hipotéticas. Exemplo 21-18 Exemplo Hipotético de Classificação e Distribuição 2 Estas funções podem aparecer na cláusula HAVING de uma consulta exatamente como outras funções agregadas. Eles não podem ser usados ​​como relatórios de funções agregadas ou funções agregadas de janelas. Funções de regressão linear As funções de regressão suportam a montagem de uma linha de regressão de mínimos quadrados comuns a um conjunto de pares de números. Você pode usá-los como funções agregadas ou funções de janelas ou relatórios. As funções são as seguintes: o Oracle aplica a função ao conjunto de pares (e1. E2) depois de eliminar todos os pares para os quais e1 ou e2 são nulos. E1 é interpretado como um valor da variável dependente (um valor y) e e2 é interpretado como um valor da variável independente (um valor x). Ambas as expressões devem ser números. As funções de regressão são todas computadas simultaneamente durante uma única passagem através dos dados. Eles são freqüentemente combinados com o COVARPOP. COVARSAMP. E funções CORR. REGRCOUNT Função REGRCOUNT retorna o número de pares de números não nulos usados ​​para ajustar a linha de regressão. Se for aplicado a um conjunto vazio (ou se não houver pares (e1, e2) onde nenhum dos e1 ou e2 é nulo), a função retorna 0. REGRAVGY e REGRAVGX Funções REGRAVGY e REGRAVGX calculam as médias da variável dependente e as independentes Variável da linha de regressão, respectivamente. REGRAVGY calcula a média do seu primeiro argumento (e1) depois de eliminar (e1. E2) os pares onde e1 ou e2 são nulos. Da mesma forma, REGRAVGX calcula a média do segundo argumento (e2) após a eliminação nula. Ambas as funções retornam NULL se aplicadas a um conjunto vazio. Funções REGRSLOPE e REGRINTERCEPT A função REGRSLOPE calcula a inclinação da linha de regressão ajustada para pares não nulos (e1. E2). A função REGRINTERCEPT calcula o y-intercepto da linha de regressão. REGRINTERCEPT retorna NULL sempre que a inclinação ou as médias de regressão são NULL. Função REGRR2 A função REGRR2 calcula o coeficiente de determinação (geralmente chamado de R-quadrado ou de ajuste) para a linha de regressão. REGRR2 retorna valores entre 0 e 1 quando a linha de regressão é definida (inclinação da linha não é nula) e retorna NULL de outra forma. Quanto mais próximo for o valor para 1, melhor a linha de regressão se encaixa nos dados. REGRSXX, REGRSYY e REGRSXY Funções REGRSXX. As funções REGRSYY e REGRSXY são usadas no cálculo de várias estatísticas de diagnóstico para análise de regressão. Depois de eliminar os pares (e1. E2) em que qualquer de e1 ou e2 é nulo, essas funções fazem os seguintes cálculos: Exemplos de estatísticas de regressão linear Algumas estatísticas de diagnóstico comuns que acompanham a análise de regressão linear são apresentadas na Tabela 21-2, Estatísticas de diagnóstico comuns e suas Expressões . Observe que isso libera novas funções que permitem calcular todas essas. Tabela 21-2 Estatísticas de diagnóstico comuns e suas expressões Exemplo de cálculo de regressão linear Neste exemplo, calculamos uma linha de regressão de mínimos quadrados comuns que expressa a quantidade vendida de um produto como uma função linear do preço da lista de produtos. Os cálculos são agrupados pelo canal de vendas. Os valores INCLINAÇÃO. INTCPT. RSQR são declive, interceptação e coeficiente de determinação da linha de regressão, respectivamente. O valor (número inteiro) COUNT é o número de produtos em cada canal para quem a quantidade vendida e os dados do preço da lista estão disponíveis. Itens frequentes Em vez de contar com a frequência com que ocorre um determinado evento (por exemplo, com que frequência alguém comprou leite na mercearia), itens frequentes fornecem um mecanismo para contar com a frequência com que vários eventos ocorrem juntos (por exemplo, com que frequência alguém comprou o leite E cereais juntos na mercearia). A entrada para a operação de itens frequentes é um conjunto de dados que representa coleções de itens (conjuntos de itens). Alguns exemplos de itens podem ser todos os produtos que um determinado cliente comprou em uma única viagem ao supermercado (comumente chamado de cesta de mercado), as páginas web que um usuário acessou em uma única sessão ou os serviços financeiros que um Dado o cliente utilizar. A noção de um conjunto de itens freqüentes é encontrar esses conjuntos de itens que ocorrem com mais freqüência. Se você aplicar o operador de itens frequentes a dados de pontos de venda de um supermercado, você pode, por exemplo, descobrir que o leite e as bananas são os itens de compra mais comum. Os itens de itens freqüentes foram assim utilizados em ambientes de inteligência de negócios por muitos anos, sendo o mais comum para análise de cesta de mercado no setor varejista. Os itens de itens freqüentes são integrados com o banco de dados, operando no topo das tabelas relacionais e acessados ​​através do SQL. Esta integração fornece alguns benefícios principais: as aplicações que anteriormente dependiam de operações freqüentes de armazenamento de itens agora se beneficiam de um desempenho significativamente melhorado e de uma implementação mais simples. Os aplicativos baseados em SQL que não usavam anteriormente conjuntos de itens freqüentes agora podem ser facilmente estendidos para aproveitar esta funcionalidade. A análise freqüente dos itens é realizada com o pacote PLSQL DBMSFREQUENTITEMSETS. Consulte a Referência de Pacotes e Tipos PLSQL para obter mais informações. Outras funções estatísticas Oracle apresenta 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. Consulte a Referência de Pacotes e Tipos PLSQL para obter informações detalhadas sobre o pacote DBMSSTATFUNCS e Oracle Database SQL Reference para sintaxe e semântica. Estatísticas descritivas Você pode calcular as seguintes estatísticas descritivas: Mediana de um modo de conjunto de dados de um conjunto de dados Você pode calcular as seguintes estatísticas paramétricas: Coeficiente de Spearmans rho Kendalls tau-b Coeficiente Além das funções, esta versão possui um novo pacote PLSQL, DBMSSTATFUNCS. Ele contém a função estatística descritiva RESUMO juntamente com funções para suportar a distribuição adequada. A função RESUMO resume uma coluna numérica de uma tabela com uma variedade de estatísticas descritivas. As cinco funções de montagem de distribuição suportam distribuições normais, uniformes, Weibull, Poisson e exponenciais. Função WIDTHBUCKET 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 21-19. 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 de abertura fechada da linha do número real, por exemplo, o número de balde 2 é atribuído a pontuações entre 5000.0000 e 9999.9999. Às vezes denotado 5000, 10000) para indicar que 5.000 está incluído no intervalo e 10.000 são excluídos. Para acomodar valores fora do intervalo de 0, 20.000), os 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 21-3 para obter uma ilustração gráfica de como os baldes são atribuídos. Você pode especificar os limites na ordem inversa, por exemplo, WIDTHBUCKET (custcreditlimit. 20000. 0. 4). Quando os limites são invertidos, os baldes serão intervalos abertos e fechados. Neste exemplo, o número de balde 1 é (15000,20000, o número de balde 2 é (10000,15000 e o número de balde 4, é (0, 5000. O balde de estouro será numerado 0 (20000. infinito) e a balde subfluxa Será numerado 5 (- infinito. 0. É um erro se o parâmetro de contagem de balde for 0 ou negativo. A consulta em seguida mostra os números de balde para os limites de crédito na tabela de clientes para ambos os casos em que os limites são especificados em regular Ou ordem inversa. Usamos um intervalo de 0 a 20.000. Funções de agregação definidas 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 pode ser usado como funções analíticas ou agregados em visões materializadas. Consulte o Guia do Desenvolvedor de Cartuchos 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 procedimento completo Língua ural. 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 de objetos podem ser processados. Como um exemplo simples de uma função agregada definida pelo usuário, considere a estatística de inclinação. Este cálculo mede se um conjunto de dados tiver uma distribuição desequilibrada sobre o seu significado. Ele irá dizer se uma das colunas da distribuição é significativamente maior do que a outra. Se você criou um agregado definido pelo usuário chamado udskew e aplicou-o aos dados de limite de crédito no exemplo anterior, a instrução SQL e os resultados podem parecer assim: antes de criar funções agregadas definidas pelo usuário, você deve considerar se suas necessidades podem ser atendidas Em SQL normal. Muitos cálculos complexos são possíveis diretamente no SQL, particularmente usando a expressão CASE. Permanecer com SQL regular permitirá um desenvolvimento mais simples, e muitas operações de consulta já estão bem paralelizadas em SQL. Mesmo o exemplo anterior, a estatística de inclinação, pode ser criada usando padrão, embora longo, SQL. 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 statements is: The syntax for searched statements is: You can specify only 255 arguments and each WHEN. THEN pair counts as two arguments. For a workaround to this limit, see Oracle Database SQL Reference . 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 would have to write this query as follows, 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. Creating Histograms With User-Defined Buckets 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 21-21 Histogram Example 1 Example 21-22 Histogram Example 2 Data Densification for Reporting Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it. Moreover, time series calculations can be performed most easily when data is dense along the time dimension. This is because dense data will fill a consistent number of rows for each period, which in turn makes it simple to use the analytic windowing functions with physical offsets. Data densification is the process of converting spare data into dense form. To overcome the problem of sparsity, you can use a partitioned outer join to fill the gaps in a time series or any other dimension. Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join. Note that you can use this type of join to fill the gaps in any dimension, not just the time dimension. Most of the examples here focus on the time dimension because it is the dimension most frequently used as a basis for comparisons. Partition Join Syntax The syntax for partitioned outer join extends the ANSI SQL JOIN clause with the phrase PARTITION BY followed by an expression list. The expressions in the list specify the group to which the outer join is applied. The following are the two forms of syntax normally used for partitioned outer join: Note that FULL OUTER JOIN is not supported with a partitioned outer join. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH sub-query factoring clause v1. summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following q uery computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands will wrap the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, lets first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command will wrap the column headings for greater readability of results: In the FROM clause of the in-line view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The in-line view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001, and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 20, SQL for Aggregation in Data Warehouses. The materialized view is defined using the following statement: Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an in-line view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note: To make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many OLAP tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 22, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view.