示例数据：TPC-DS

如 TPC Benchmark™ DS (TPC-DS) (http://www.tpc.org/TPC_Documents_Current_Versions/pdf/TPC-DS_v2.5.0.pdf) 规范所述：

“TPC-DS 对零售产品供应商的决策支持功能进行建模。支持架构包含重要的业务信息，例如客户、订单和产品数据。

为了解决决策支持系统遇到的大量查询类型和用户行为，TPC-DS 使用广义查询模型。该模型允许基准测试获取以下对象的重要方面：在线分析处理 (OLAP) 查询的交互性、迭代性；数据挖掘和知识发现中运行时间较长的复杂查询；以及众所周知的报告查询的更有计划的行为。”

本主题内容：

数据库和架构

Snowflake 在 SNOWFLAKE_SAMPLE_DATA 共享数据库中分别以名为 TPCDS_SF10TCL 和 TPCDS_SF100TCL 的架构提供 10 TB 和 100 TB 版本的 TPC-DS。

数据库实体、关系和特征

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）版本代表 6500 万客户和存储的 400,000 多件商品，其中销售数据涉及商店、目录和网络等 3 个渠道，时间涵盖 5 年。最大的表“STORE_SALES”包含近 290 亿行，事实数据表总共包含超过 560 亿行。

事实和维度之间的关系通过代理键上的联接来表示。详细的关系太多，无法在此处显示，但可以在 TPC-DS 规范中找到。

查询定义

TPC-DS 包含一组 99 个查询，这些查询的复杂性和扫描数据范围差异很大。每个 TPC-DS 查询都提出一个业务问题，并包含相应的查询来回答该问题。我们已经生成了所有 99 个 TPC-DS 查询示例供您探索。或者，您可以使用 TPC-DS 基准工具包中的工具生成这些查询的许多不同版本，这些查询因参数值而异。

对于 10 TB 版本，使用 Snowflake 2 倍大的仓库，完整的 99 个 TPC-DS 查询应在 45 分钟内完成。如果您使用的是 100 TB 版本，那么使用 4 倍大的仓库，查询应在 1 小时内完成。

下面，我们仅介绍其中一个查询。有关 TPC-DS 以及所涉及的所有查询的更多信息可以在官方 TPC-DS 规范中找到。

问题 57：目录销售呼叫中心异常值

此查询着眼于一年的 CATALOG_SALES 表格数据，并揭示给定呼叫中心一个月的销售额与平均水平相差 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
语言: 中文