Amostra de dados: TPC-DS

Como descrito na especificação TPC Benchmark™ DS (TPC-DS):

“O TPC-DS modela as funções de suporte à decisão de um fornecedor de produtos de varejo. O esquema de suporte contém informações comerciais vitais, tais como dados de clientes, pedidos e produtos.

A fim de abordar a enorme variedade de tipos de consulta e comportamentos de usuários encontrados por um sistema de suporte à decisão, o TPC-DS utiliza um modelo de consulta generalizada. Este modelo permite que o benchmark capture aspectos importantes da natureza interativa e iterativa do processamento analítico on-line (OLAP), as consultas complexas de longo prazo de mineração de dados e descoberta de conhecimento, e o comportamento mais planejado de consultas de relatórios bem conhecidas.”

Neste tópico:

Banco de dados e esquemas

O Snowflake fornece ambas as versões do TPC-DS, de 10 TB e 100 TB, em esquemas chamados TPCDS_SF10TCL e TPCDS_SF100TCL, respectivamente, dentro do banco de dados compartilhado SNOWFLAKE_SAMPLE_DATA.

Entidades, relacionamentos e características do banco de dados

O TPC-DS consiste em 7 tabelas de fatos e 17 dimensões nos seguintes esquemas:

  • TPCDS_SF100TCL: A versão de 100 TB (fator de escala de 100.000) representa 100 milhões de clientes e mais de 500.000 itens armazenados, com dados de vendas abrangendo 3 canais – lojas, catálogos e a Web – cobrindo um período de 5 anos. A maior tabela, STORE_SALES, contém quase 300 bilhões de linhas, e as tabelas contêm mais de 560 bilhões de linhas no total.

  • TPCDS_SF10TCL: A versão de 10 TB (fator de escala de 10.000) representa 65 milhões de clientes e mais de 400.000 itens armazenados, com dados de vendas abrangendo 3 canais – lojas, catálogos e a Web – cobrindo um período de 5 anos. A maior tabela, STORE_SALES, contém quase 29 bilhões de linhas, e as tabelas contêm mais de 56 bilhões de linhas no total.

As relações entre fatos e dimensões são representadas através de junções em chaves alternativas. As relações detalhadas são muito numerosas para serem exibidas aqui, mas podem ser encontradas na especificação TPC-DS.

Definições de consulta

O TPC-DS contém um conjunto de 99 consultas com grande variação de complexidade e diversidade de dados verificados. Cada consulta TPC-DS faz uma pergunta comercial e inclui a consulta correspondente para responder à pergunta. Geramos amostras de todas as 99 consultas TPC-DS para que você possa explorar. Alternativamente, você pode usar as ferramentas no Kit de benchmark do TPC-DS para gerar muitas versões diferentes destas consultas que variam de acordo com os valores dos parâmetros.

Para a versão de 10 TB, o conjunto completo de 99 consultas TPC-DS deve ser concluído em menos de 45 minutos usando um warehouse Snowflake 2X-Large. Se você usar a versão de 100 TB, as consultas devem ser concluídas em menos de 1 hora usando um warehouse 4X-Large.

Abaixo, descrevemos apenas uma das consultas. Mais informações sobre TPC-DS e todas as consultas envolvidas podem ser encontradas na especificação oficial do TPC-DS.

Q57: Catalog Sales Call Center Outliers

Esta consulta analisa os dados da tabela CATALOG_SALES de um ano e revela as categorias e marcas em que as vendas em um mês variam mais de 10% em relação à média para um determinado call center.

Questão comercial

Encontre as marcas e categorias de itens para cada call center e seus números de vendas mensais para um determinado ano onde o número de vendas mensais desviou mais de 10% da média de vendas mensais do ano, classificadas por desvio e call center. Relate o desvio de vendas em relação aos mês anterior e seguinte.

Definição da consulta funcional

A consulta relaciona os seguintes totais:

  • Preço estendido

  • Preço estendido com desconto

  • Preço estendido com desconto mais imposto

  • Quantidade média

  • Preço estendido médio

  • Desconto médio

Estes agregados são agrupados por RETURNFLAG e LINESTATUS e são listados em ordem crescente de RETURNFLAG e LINESTATUS. Está incluída uma contagem do número de itens de linha em cada grupo:

use schema snowflake_sample_data.tpcds_sf10Tcl;

-- QID=TPC-DS_query57

with v1 as(
  select i_category, i_brand, cc_name, d_year, d_moy,
        sum(cs_sales_price) sum_sales,
        avg(sum(cs_sales_price)) over
          (partition by i_category, i_brand,
                     cc_name, d_year)
          avg_monthly_sales,
        rank() over
          (partition by i_category, i_brand,
                     cc_name
           order by d_year, d_moy) rn
  from item, catalog_sales, date_dim, call_center
  where cs_item_sk = i_item_sk and
       cs_sold_date_sk = d_date_sk and
       cc_call_center_sk= cs_call_center_sk and
       (
         d_year = 1999 or
         ( d_year = 1999-1 and d_moy =12) or
         ( d_year = 1999+1 and d_moy =1)
       )
  group by i_category, i_brand,
          cc_name , d_year, d_moy),
v2 as(
  select v1.i_category ,v1.d_year, v1.d_moy ,v1.avg_monthly_sales
        ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
  from v1, v1 v1_lag, v1 v1_lead
  where v1.i_category = v1_lag.i_category and
       v1.i_category = v1_lead.i_category and
       v1.i_brand = v1_lag.i_brand and
       v1.i_brand = v1_lead.i_brand and
       v1.cc_name = v1_lag.cc_name and
       v1.cc_name = v1_lead.cc_name and
       v1.rn = v1_lag.rn + 1 and
       v1.rn = v1_lead.rn - 1)
select  *
from v2
where  d_year = 1999 and
        avg_monthly_sales > 0 and
        case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
order by sum_sales - avg_monthly_sales, 3
limit 100;
Copy
Linguagem: Português