サンプルデータ: TPC-DS

TPC Benchmark™ DS（TPC-DS） 仕様で説明されているように、

「TPC-DS は、小売製品サプライヤーの意思決定支援機能をモデル化しています。サポートするスキーマには、顧客、注文、製品データなどの重要なビジネス情報が含まれています。

意思決定支援システムが遭遇する膨大な種類のクエリタイプとユーザーの動作に対処するために、TPC-DS は一般化されたクエリモデルを利用します。このモデルにより、ベンチマークは、オンライン分析処理（OLAP）クエリのインタラクティブで反復的な性質、データマイニングと知識発見の長時間実行される複雑なクエリ、およびよく知られているレポートクエリのより計画された動作の重要な側面をキャプチャできます。」

このトピックの内容：

データベースとスキーマ

Snowflakeは、 SNOWFLAKE_SAMPLE_DATA 共有データベース内で、それぞれ TPCDS_SF10TCL および TPCDS_SF100TCL という名前のスキーマで、TPC-DS の10 TB と100 TB の両バージョンを提供します。

データベースエンティティ、関係、および特性

TPC-DS は、次のスキーマにある7個のファクトテーブルと17個のディメンションで構成されます。

  • TPCDS_SF100TCL: 100 TB （スケール係数 100,000）バージョンは、1億人の顧客と500,000個以上のアイテムを表し、販売データは3チャネル（店舗、カタログ、ウェブ）の5年の期間をカバーします。最大のテーブルである STORE_SALES には3000億行が含まれ、ファクトテーブルには合計で5600億行が含まれています。

  • TPCDS_SF10TCL: 10 TB （スケール係数10,000）バージョンは、6,500万人の顧客と400,000個以上のアイテムを表し、販売データは3チャネル（ストア、カタログ、ウェブ）の5年の期間をカバーします。最大のテーブルである STORE_SALES には290億行が含まれ、ファクトテーブルには合計で560億行が含まれています。

ファクトとディメンション間の関係は、代理キーの結合によって表されます。詳細な関係は多すぎてここに表示できませんが、TPC-DS 仕様でご覧ください。

クエリ定義

TPC-DS には、複雑さとスキャンされるデータの範囲が大きく異なる99クエリのセットが含まれています。各 TPC-DS クエリはビジネス上の質問をし、質問に答えるための対応クエリを含みます。探索に使えるように、すべての99 TPC-DS クエリのサンプルを生成しました。または、 TPC-DS ベンチマークキットのツールを使用して、パラメーター値によって異なるこれらのクエリのさまざまなバージョンを生成できます。

10 TB バージョンでは、Snowflake 2X-Large ウェアハウスを使用すると、99 TPC-DS クエリの完全なセットが45時間以内で完了するようになっています。100 TB バージョンを使用する場合は、4X-Largeウェアハウスを使用すると、クエリが1時間以内で完了するようになっています。

以下では、クエリの内の1つのみを説明します。TPC-DS および関連するすべてのクエリの詳細については、公式の TPC-DS 仕様をご参照ください。

Q57: カタログセールスコールセンターの外れ値

このクエリは、1年分の CATALOG_SALES テーブルデータを調べて、特定のコールセンターの1か月間における売上が平均から10%以上変化するカテゴリとブランドを明らかにします。

ビジネスの質問

各コールセンターのアイテムブランドとカテゴリ、および指定された年の月間売上高を検索します。ここで、年間の平均月間売上の10%を超える偏差の月間売上高は偏差とコールセンターでソートされています。前月と翌月からの売上偏差を報告します。

機能クエリ定義

クエリには、次の合計がリストされます。

  • 合計価格

  • 割引合計価格

  • 割引合計価格と税金

  • 平均数量

  • 平均合計価格

  • 平均割引

これらの集計は、RETURNFLAG および LINESTATUS でグループ化され、RETURNFLAG および LINESTATUS の昇順でリストされます。各グループのラインアイテム数の件数が含まれます。

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
言語: 日本語