Beispieldaten: TPC-DS

Wie in der TPC Benchmark ™ DS (TPC-DS)-Spezifikation beschrieben:

„TPC-DS modelliert die Entscheidungsunterstützungsfunktionen eines Produktlieferanten im Einzelhandel. Das unterstützende Schema enthält wichtige Geschäftsinformationen wie Kunden-, Auftrags- und Produktdaten.

Um die enorme Bandbreite an Abfragetypen und Benutzerverhalten eines Entscheidungsunterstützungssystems zu berücksichtigen, verwendet TPC-DS ein generalisiertes Abfragemodell. Dieses Modell ermöglicht es der Benchmark, wichtige Aspekte der interaktiven, iterativen Natur von OLAP-Abfragen (On-Line Analytical Processing), die zeitaufwändige Ausführung komplexer Abfragen in Data Mining und Knowledge Discovery sowie das planmäßigere Verhalten von bekannten Berichtsabfragen abzudecken.“

Unter diesem Thema:

Datenbank und Schemas

Snowflake stellt sowohl 10-TB- als auch 100-TB-Versionen von TPC-DS in Schemas mit den Namen TPCDS_SF10TCL bzw. TPCDS_SF100TCL innerhalb der freigegebenen SNOWFLAKE_SAMPLE_DATA-Datenbank zur Verfügung.

Entitäten, Beziehungen und Eigenschaften der Datenbank

TPC-DS besteht aus 7 Faktentabellen und 17 Dimensionen in den folgenden Schemas:

  • TPCDS_SF100TCL: Die 100-TB-Version (Skalierungsfaktor 100.000) repräsentiert 100 Millionen Kunden und über 500.000 gespeicherte Artikel, wobei die Verkaufsdaten 3 Kanäle – Geschäfte, Kataloge und Internet – über einen Zeitraum von 5 Jahren umfassen. Die größte Tabelle, STORE_SALES, enthält fast 300 Milliarden Zeilen, und die Faktentabellen enthalten insgesamt über 560 Milliarden Zeilen.

  • TPCDS_SF10TCL: Die 10-TB-Version (Skalierungsfaktor 10.000) repräsentiert 65 Millionen Kunden und über 400.000 gespeicherte Artikel, wobei die Verkaufsdaten 3 Kanäle – Geschäfte, Kataloge und Internet – über einen Zeitraum von 5 Jahren umfassen. Die größte Tabelle, STORE_SALES, enthält fast 29 Milliarden Zeilen, und die Faktentabellen enthalten insgesamt über 56 Milliarden Zeilen.

Die Beziehungen zwischen Fakten und Dimensionen werden durch Joins auf Ersatzschlüsseln dargestellt. Die detaillierten Beziehungen sind zu zahlreich, um sie hier anzuzeigen, sie sind aber in der Spezifikation zu TPC-DS zu finden.

Abfragedefinitionen

TPC-DS enthält ein Set von 99 Abfragen mit großen Unterschieden in Komplexität und Umfang der gescannten Daten. Jede TPC-DS-Abfrage steht für eine Geschäftsfrage und enthält die entsprechende Abfrage zur Beantwortung der Frage. Wir haben Beispiele für alle 99 TPC-DS-Abfragen für Sie generiert. Alternativ können Sie die Tools des TPC-DS-Benchmark-Kits verwenden, um viele verschiedene Versionen dieser Abfragen zu generieren, die sich durch die Parameterwerte unterscheiden.

Für die 10-TB-Version sollte das vollständige Set von 99 TPC-DS-Abfragen mit einem Snowflake 2X-Large-Warehouse in unter 45 Minuten abgeschlossen sein. Wenn Sie die 100-TB-Version verwenden, sollten die Abfragen mit einem 4X-Large-Warehouse in unter 1 Stunde abgeschlossen sein.

Im Folgenden beschreiben wir nur eine der Abfragen. Weitere Informationen zu TPC-DS und allen damit verbundenen Abfragen finden Sie in der offiziellen TPC-DS-Spezifikation.

Q57: Callcenter-Ausreißer im Katalog-Vertrieb

Bei dieser Abfrage wird der Wert von CATALOG_SALES-Tabellendaten über ein Jahr betrachtet, und es werden die Kategorien und Marken aufgezeigt, bei denen die Umsätze eines Monats um mehr als 10 % vom Durchschnitt eines bestimmten Callcenters abweichen.

Geschäftsfrage

Suchen Sie die Artikelmarken und -kategorien für jedes Callcenter und deren monatliche Verkaufszahlen für ein bestimmtes Jahr, wobei die monatlichen Verkaufszahlen um mehr als 10 % vom durchschnittlichen Monatsumsatz des Jahres abweichen, sortiert nach Abweichung und Callcenter. Melden Sie die Umsatzabweichung gegenüber den Vormonaten und den Folgemonaten.

Definition der funktionalen Abfrage

Die Abfrage listet die folgenden Summen auf:

  • Erweiterter Preis

  • Ermäßigter erweiterter Preis

  • Ermäßigter erweiterter Preis zuzüglich Mehrwertsteuer

  • Durchschnittliche Menge

  • Durchschnittlicher erweiterter Preis

  • Durchschnittlicher Rabatt

Diese Summen sind nach RETURNFLAG und LINESTATUS gruppiert und werden in aufsteigender Reihenfolge von RETURNFLAG und LINESTATUS aufgelistet. Ein Zähler für die Anzahl der Einzelelemente in jeder Gruppe ist enthalten:

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
Sprache: Deutsch