SSB基准测试

SSB（Star Schema Benchmark）是麻省州立大学波士顿校区的研究人员定义的基于现实商业应用的数据模型，业界公认用来模拟决策支持类应用，比较公正和中立。
学术界和工业界普遍采用它来评价决策支持技术方面应用的性能。
全方位评测系统的整体商业计算综合能力，对厂商的要求更高。
在银行信贷分析和信用卡分析、电信运营分析、税收分析、烟草行业决策分析中都有广泛的应用。

SSB基准测试包括：
1个事实表：lineorder
4个维度表：customer，part，dwdate，supplier
13条标准SQL查询测试语句：统计查询、多表关联、sum、复杂条件、group by、order by等组合方式。

  • image

SSB数据生成器

SSB数据自动生成器下载地址：点击这里

实验证明，在Linux上操作比较方便，步骤如下：

  • 将下载的文件上传至Linux主机上的目录

  • 执行如下命令，生成dbgen文件：make

  • image

  • 查看 dbgen 命令帮助： ./dbgen -h

  • image

  • 生成数据文件，命令如下：

  • [
    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px 0px 0px 22px; white-space: pre-wrap; overflow-wrap: break-word; font-size: 12px !important; font-family: "Courier New" !important;">(customer.tbl)
    dbgen -s 1 -T c

    (part.tbl)
    dbgen -s 1 -T p

    (supplier.tbl)
    dbgen -s 1 -T s

    (date.tbl)
    dbgen -s 1 -T d

    (fact table lineorder.tbl)
    dbgen -s 1 -T l

    (for all SSBM tables)
    dbgen -s 1 -T a</pre>

    [
    复制代码

    ](javascript:void(0); "复制代码")

  • 生成文件如下： ll *tbl -l

  • image

  • 连接mysql数据库，创建数据库，数据表

  • image

  • 创建数据库： create database ssb;

  • 创建数据库表：

  • [
    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px 0px 0px 22px; white-space: pre-wrap; overflow-wrap: break-word; font-size: 12px !important; font-family: "Courier New" !important;">CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER,
    C_NAME VARCHAR(25) NOT NULL,
    C_ADDRESS VARCHAR(40) NOT NULL,
    C_CITY VARCHAR(10) NOT NULL,
    C_NATION VARCHAR(15) NOT NULL,
    C_REGION VARCHAR(12) NOT NULL,
    C_PHONE VARCHAR(15) NOT NULL,
    C_MKTSEGMENT VARCHAR(10) NOT NULL); CREATE TABLE DATES ( D_DATEKEY INTEGER,
    D_DATE VARCHAR(18) NOT NULL,
    D_DAYOFWEEK VARCHAR(18) NOT NULL,
    D_MONTH VARCHAR(9) NOT NULL,
    D_YEAR INTEGER NOT NULL,
    D_YEARMONTHNUM INTEGER,
    D_YEARMONTH VARCHAR(7) NOT NULL,
    D_DAYNUMINWEEK INTEGER,
    D_DAYNUMINMONTH INTEGER,
    D_DAYNUMINYEAR INTEGER,
    D_MONTHNUMINYEAR INTEGER,
    D_WEEKNUMINYEAR INTEGER,
    D_SELLINGSEASON VARCHAR(12) NOT NULL,
    D_LASTDAYINWEEKFL INTEGER,
    D_LASTDAYINMONTHFL INTEGER,
    D_HOLIDAYFL INTEGER,
    D_WEEKDAYFL INTEGER); CREATE TABLE PART ( P_PARTKEY INTEGER,
    P_NAME VARCHAR(22) NOT NULL,
    P_MFGR VARCHAR(6) NOT NULL,
    P_CATEGORY VARCHAR(7) NOT NULL,
    P_BRAND VARCHAR(9) NOT NULL,
    P_COLOR VARCHAR(11) NOT NULL,
    P_TYPE VARCHAR(25) NOT NULL,
    P_SIZE INTEGER NOT NULL,
    P_CONTAINER VARCHAR(10) NOT NULL); CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER,
    S_NAME VARCHAR(25) NOT NULL,
    S_ADDRESS VARCHAR(25) NOT NULL,
    S_CITY VARCHAR(10) NOT NULL,
    S_NATION VARCHAR(15) NOT NULL,
    S_REGION VARCHAR(12) NOT NULL,
    S_PHONE VARCHAR(15) NOT NULL); CREATE TABLE LINEORDER ( LO_ORDERKEY BIGINT,
    LO_LINENUMBER BIGINT,
    LO_CUSTKEY INTEGER NOT NULL,
    LO_PARTKEY INTEGER NOT NULL,
    LO_SUPPKEY INTEGER NOT NULL,
    LO_ORDERDATE INTEGER NOT NULL,
    LO_ORDERPRIOTITY VARCHAR(15) NOT NULL,
    LO_SHIPPRIOTITY INTEGER,
    LO_QUANTITY BIGINT,
    LO_EXTENDEDPRICE BIGINT,
    LO_ORDTOTALPRICE BIGINT,
    LO_DISCOUNT BIGINT,
    LO_REVENUE BIGINT,
    LO_SUPPLYCOST BIGINT,
    LO_TAX BIGINT,
    LO_COMMITDATE INTEGER NOT NULL,
    LO_SHIPMODE VARCHAR(10) NOT NULL);</pre>

    [
    复制代码

    ](javascript:void(0); "复制代码")

  • 执行完成后，结果如下：

  • image

  • 执行如下SQL、导入数据

  • [
    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px 0px 0px 22px; white-space: pre-wrap; overflow-wrap: break-word; font-size: 12px !important; font-family: "Courier New" !important;">set autocommit=off; load data infile '/dbgen/lineorder.tbl' into table LINEORDER fields terminated by '|' lines terminated by '|\n'; commit; load data infile '/dbgen/supplier.tbl' into table SUPPLIER fields terminated by '|' lines terminated by '|\n'; commit; load data infile '/dbgen/customer.tbl' into table CUSTOMER fields terminated by '|' lines terminated by '|\n'; commit; load data infile '/dbgen/part.tbl' into table PART fields terminated by '|' lines terminated by '|\n'; commit; load data infile '/dbgen/date.tbl' into table DATES fields terminated by '|' lines terminated by '|\n'; commit;</pre>

    [
    复制代码

    ](javascript:void(0); "复制代码")

  • 查看数据是否正确

  • image

标准SQL查询测试语句

  • [
    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px 0px 0px 22px; white-space: pre-wrap; overflow-wrap: break-word; font-size: 12px !important; font-family: "Courier New" !important;">PROMPT Q1.1
    SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM LINEORDER, DATES WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993
    AND LO_DISCOUNT BETWEEN 1 AND 3
    AND LO_QUANTITY < 25;

    PROMPT Q1.2
    SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM LINEORDER, DATES WHERE LO_ORDERDATE = D_DATEKEY AND D_YEARMONTH = 'Jan1994'
    AND LO_DISCOUNT BETWEEN 4 AND 6
    AND LO_QUANTITY BETWEEN 26 AND 35;

    PROMPT Q1.3
    SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM LINEORDER, DATES WHERE LO_ORDERDATE = D_DATEKEY AND D_WEEKNUMINYEAR = 6
    AND D_YEAR = 1994
    AND LO_DISCOUNT BETWEEN 5 AND 7
    AND LO_QUANTITY BETWEEN 26 AND 35;

    PROMPT Q2.1
    SELECT SUM(LO_REVENUE), D_YEAR, P_BRAND FROM LINEORDER, DATES, PART, SUPPLIER WHERE LO_ORDERDATE = D_DATEKEY AND LO_PARTKEY = P_PARTKEY AND LO_SUPPKEY = S_SUPPKEY AND P_CATEGORY = 'MFGR#12'
    AND S_REGION = 'AMERICA'
    GROUP BY D_YEAR, P_BRAND ORDER BY D_YEAR, P_BRAND;

    PROMPT Q2.2
    SELECT SUM(LO_REVENUE), D_YEAR, P_BRAND FROM LINEORDER, DATES, PART, SUPPLIER WHERE LO_ORDERDATE = D_DATEKEY AND LO_PARTKEY = P_PARTKEY AND LO_SUPPKEY = S_SUPPKEY AND P_BRAND BETWEEN 'MFGR#2221'
    AND 'MFGR#2228'
    AND S_REGION = 'ASIA'
    GROUP BY D_YEAR, P_BRAND ORDER BY D_YEAR, P_BRAND;

    PROMPT Q2.3
    SELECT SUM(LO_REVENUE), D_YEAR, P_BRAND FROM LINEORDER, DATES, PART, SUPPLIER WHERE LO_ORDERDATE = D_DATEKEY AND LO_PARTKEY = P_PARTKEY AND LO_SUPPKEY = S_SUPPKEY AND P_BRAND= 'MFGR#2239'
    AND S_REGION = 'EUROPE'
    GROUP BY D_YEAR, P_BRAND ORDER BY D_YEAR, P_BRAND;

    PROMPT Q3.1
    SELECT C_NATION, S_NATION, D_YEAR, SUM(LO_REVENUE) AS REVENUE FROM CUSTOMER, LINEORDER, SUPPLIER, DATES WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_ORDERDATE = D_DATEKEY AND C_REGION = 'ASIA'
    AND S_REGION = 'ASIA'
    AND D_YEAR >= 1992 AND D_YEAR <= 1997
    GROUP BY C_NATION, S_NATION, D_YEAR ORDER BY D_YEAR ASC, REVENUE DESC;

    PROMPT Q3.2
    SELECT C_CITY, S_CITY, D_YEAR, SUM(LO_REVENUE) AS REVENUE FROM CUSTOMER, LINEORDER, SUPPLIER, DATES WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_ORDERDATE = D_DATEKEY AND C_NATION = 'UNITED STATES'
    AND S_NATION = 'UNITED STATES'
    AND D_YEAR >= 1992 AND D_YEAR <= 1997
    GROUP BY C_CITY, S_CITY, D_YEAR ORDER BY D_YEAR ASC, REVENUE DESC;

    PROMPT Q3.3
    SELECT C_CITY, S_CITY, D_YEAR, SUM(LO_REVENUE) AS REVENUE FROM CUSTOMER, LINEORDER, SUPPLIER, DATES WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_ORDERDATE = D_DATEKEY AND (C_CITY='UNITED KI1'
    OR C_CITY='UNITED KI5') AND (S_CITY='UNITED KI1'
    OR S_CITY='UNITED KI5') AND D_YEAR >= 1992 AND D_YEAR <= 1997
    GROUP BY C_CITY, S_CITY, D_YEAR ORDER BY D_YEAR ASC, REVENUE DESC;

    PROMPT Q3.4
    SELECT C_CITY, S_CITY, D_YEAR, SUM(LO_REVENUE) AS REVENUE FROM CUSTOMER, LINEORDER, SUPPLIER, DATES WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_ORDERDATE = D_DATEKEY AND (C_CITY='UNITED KI1'
    OR C_CITY='UNITED KI5') AND (S_CITY='UNITED KI1'
    OR S_CITY='UNITED KI5') AND D_YEARMONTH = 'Dec1997'
    GROUP BY C_CITY, S_CITY, D_YEAR ORDER BY D_YEAR ASC, REVENUE DESC;

    PROMPT Q4.1
    SELECT D_YEAR, C_NATION, SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT FROM DATES, CUSTOMER, SUPPLIER, PART, LINEORDER WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_PARTKEY = P_PARTKEY AND LO_ORDERDATE = D_DATEKEY AND C_REGION = 'AMERICA'
    AND S_REGION = 'AMERICA'
    AND (P_MFGR = 'MFGR#1'
    OR P_MFGR = 'MFGR#2') GROUP BY D_YEAR, C_NATION ORDER BY D_YEAR, C_NATION;

    PROMPT Q4.2
    SELECT D_YEAR, S_NATION, P_CATEGORY, SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT FROM DATES, CUSTOMER, SUPPLIER, PART, LINEORDER WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_PARTKEY = P_PARTKEY AND LO_ORDERDATE = D_DATEKEY AND C_REGION = 'AMERICA'
    AND S_REGION = 'AMERICA'
    AND (D_YEAR = 1997 OR D_YEAR = 1998) AND (P_MFGR = 'MFGR#1'
    OR P_MFGR = 'MFGR#2') GROUP BY D_YEAR, S_NATION, P_CATEGORY ORDER BY D_YEAR, S_NATION, P_CATEGORY;

    PROMPT Q4.3
    SELECT D_YEAR, S_CITY, P_BRAND, SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT FROM DATES, CUSTOMER, SUPPLIER, PART, LINEORDER WHERE LO_CUSTKEY = C_CUSTKEY AND LO_SUPPKEY = S_SUPPKEY AND LO_PARTKEY = P_PARTKEY AND LO_ORDERDATE = D_DATEKEY AND S_NATION = 'UNITED STATES'
    AND (D_YEAR = 1997 OR D_YEAR = 1998) AND P_CATEGORY = 'MFGR#14'
    GROUP BY D_YEAR, S_CITY, P_BRAND ORDER BY D_YEAR, S_CITY, P_BRAND;</pre>

    [
    复制代码

    ](javascript:void(0); "复制代码")

  • nfobright 342 on linux 64

  • image
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • rljs
    rljs by sennchi Timeline of History Part One The Cognitiv...
    sennchi阅读 7,499评论 0赞 10
  • 2003-2014考研真题
    **2014真题Directions:Read the following text. Choose the be...
    又是夜半惊坐起阅读 10,129评论 0赞 23
  • 人生何求
    有茶有酒，人生何求？不过快意自在，无忧无愁…故人再相见，一笑泯恩仇。昔日为情死，今日不回头，世间情与爱，不过自作多...
    花花的樱花阅读 234评论 0赞 1
  • 2017-11-19 Day 85
    工 休息的日子，早起完成了培训的ppt并上交。意识清醒，毫无睡意。 因为考核没有完成，以及周末的学生，还是去了公司...
    从心印心阅读 174评论 0赞 0
  • AVFoundation入门
    一.AVFoundation含义AVFoundation是苹果OS X 系统和iOS系统中用于处理基于时间的媒体数...
    爱笑的猫mi阅读 856评论 0赞 0