샘플 데이터: 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) 버전은 5년 동안 매장, 카탈로그 및 웹의 3개 채널에 전체에서의 판매 데이터와 함께 1억 명의 고객과 500,000개 이상의 항목이 저장되어 있음을 나타냅니다. 가장 큰 테이블인 STORE_SALES에는 약 3,000억 개의 행이 있고 정보 테이블에는 총 5,600억 개 이상의 행이 있습니다.

  • TPCDS_SF10TCL: 10 TB(배율 인자 10,000) 버전은 5년 동안 매장, 카탈로그 및 웹의 3개 채널에 전체에서의 판매 데이터와 함께 6,500만 명의 고객과 400,000개 이상의 항목이 저장되어 있음을 나타냅니다. 가장 큰 테이블인 STORE_SALES에는 약 2,900억 개의 행이 있고 정보 테이블에는 총 560억 개 이상의 행이 있습니다.

정보와 차원 사이의 관계는 대체 키에서의 조인을 통해 표시됩니다. 자세한 관계는 매우 방대하여 여기에 표시할 수 없지만, TPC-DS 사양에서 확인할 수 있습니다.

쿼리 정의

TPC-DS에는 스캔된 데이터의 복잡성과 범위가 매우 다양한 99개의 쿼리 세트가 포함되어 있습니다. 각 TPC-DS 쿼리는 비즈니스 관련 질문을 하며 질문에 답하기 위한 해당 쿼리가 포함됩니다. Snowflake는 사용자가 탐색할 수 있는 총 99개의 TPC-DS 쿼리 샘플을 생성해 두었습니다. 또는, TPC-DS Benchmark Kit의 도구를 사용하여 매개 변수 값에 따라 달라지는 이러한 쿼리의 다양한 버전을 생성할 수도 있습니다.

10 TB 버전의 경우 전체 99개 TPC-DS 쿼리 세트는 Snowflake의 2X-Large 웨어하우스를 사용하여 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