Données d’échantillon : TPC-DS

Comme décrit dans la spécification TPC Benchmark™ DS (TPC-DS) :

« TPC-DS modélise les fonctions d’aide à la décision d’un fournisseur de produits au détail. Le schéma de prise en charge contient des informations commerciales essentielles, telles que les données client, de commande et de produit.

Afin de tenir compte de l’énorme éventail de types de requêtes et de comportements d’utilisateur rencontrés par un système d’aide à la décision, TPC-DS utilise un modèle de requête généralisé. Ce modèle permet au benchmark de capturer des aspects importants de la nature interactive et itérative des requêtes de traitement analytique de ligne (OLAP), des requêtes complexes de longue durée de l’exploration de données et de la découverte de connaissances, et du comportement planifié des requêtes de rapport bien connues ».

Dans ce chapitre :

Base de données et schémas

Snowflake fournit les versions 10 TB et 100 TB de TPC-DS dans les schémas nommés TPCDS_SF10TCL et TPCDS_SF100TCL, respectivement, dans la base de données partagée SNOWFLAKE_SAMPLE_DATA.

Entités, relations et caractéristiques de base de données

TPC-DS se compose de 7 tables de faits et de 17 dimensions dans les schémas suivants :

  • TPCDS_SF100TCL : la version 100 TB (facteur d’échelle 100 000) représente 100 millions de clients et plus de 500 000 éléments stockés, avec des données de ventes couvrant trois canaux (magasins, catalogues et Internet) sur une période de cinq ans. La plus grande table, STORE_SALES, contient près de 300 milliards de lignes, et les tables de faits contiennent plus de 560 milliards de lignes au total.

  • TPCDS_SF10TCL : la version 10 TB (facteur d’échelle 10 000) représente 65 millions de clients et plus de 400 000 éléments stockés, avec des données de ventes couvrant trois canaux (magasins, catalogues, et Internet) sur une période de cinq ans. La plus grande table, STORE_SALES, contient près de 29 milliards de lignes, et les tables de faits contiennent plus de 56 milliards de lignes au total.

Les relations entre les faits et les dimensions sont représentées par des liaisons sur des clés de substitution. Les relations détaillées sont trop nombreuses pour être affichées ici, mais peuvent être trouvées dans les spécifications TPC-DS.

Définitions de requête

TPC-DS contient un ensemble de 99 requêtes dont la complexité et l’étendue des données numérisées varient considérablement. Chaque requête TPC-DS pose une question métier et inclut la requête correspondante pour y répondre. Nous avons généré des échantillons de toutes les 99 requêtes TPC-DS que vous pouvez explorer. Vous pouvez également utiliser les outils du kit de benchmark TPC-DS pour générer de nombreuses versions différentes de ces requêtes qui varient en fonction des valeurs de paramètre.

Pour la version 10 TB, l’ensemble complet de 99 requêtes TPC-DS devrait être complété en moins de 45 minutes avec un entrepôt Snowflake 2X-Large. Si vous utilisez la version 100 TB, les requêtes devraient être traitées en moins d’une heure avec un entrepôt 4X-Large.

Ci-dessous, nous ne décrivons qu’une seule de ces requêtes. Vous pouvez trouver plus d’informations sur TPC-DS et toutes les requêtes impliquées dans les spécifications officielles TPC-DS.

Q57 : valeurs aberrantes du centre d’appels sur les ventes de catalogue

Cette requête examine les données de la table CATALOG_SALES d’une année et révèle les catégories et les marques dont les ventes mensuelles varient de plus de 10 % par rapport à la moyenne pour un centre d’appels donné.

Question métier

Trouvez les marques et catégories d’article pour chaque centre d’appels et leurs chiffres de ventes mensuels pour une année donnée où le chiffre d’affaires mensuel s’écarte de plus de 10% du chiffre d’affaires mensuel moyen de l’année, classées par écart et centre d’appels. Faites un rapport sur l’écart de ventes par rapport au mois précédent et au mois suivant.

Définition de la requête fonctionnelle

La requête répertorie les totaux suivants :

  • Prix étendu

  • Prix étendu réduit

  • Prix étendu réduit plus taxe

  • Quantité moyenne

  • Prix étendu moyen

  • Remise moyenne

Ces agrégats sont regroupés par RETURNFLAG et LINESTATUS, et sont répertoriés par ordre croissant de RETURNFLAG et LINESTATUS. Un décompte du nombre d’éléments de ligne dans chaque groupe est inclus :

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
Langage: Français