當前位置：首頁 > 编程资源 > 综合教程 >内容正文

综合教程

SSB基准测试

發布時間：2024/5/24 综合教程 32 生活家

生活随笔收集整理的這篇文章主要介紹了 SSB基准测试小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

SSB（Star Schema Benchmark）是麻省州立大學波士頓校區的研究人員定義的基于現實商業應用的數據模型，業界公認用來模擬決策支持類應用，比較公正和中立。
學術界和工業界普遍采用它來評價決策支持技術方面應用的性能。
全方位評測系統的整體商業計算綜合能力，對廠商的要求更高。
在銀行信貸分析和信用卡分析、電信運營分析、稅收分析、煙草行業決策分析中都有廣泛的應用。

SSB基準測試包括：
1個事實表：lineorder
4個維度表：customer，part，dwdate，supplier
13條標準SQL查詢測試語句：統計查詢、多表關聯、sum、復雜條件、group by、order by等組合方式。

SSB數據生成器

SSB數據自動生成器下載地址：點擊這里

實驗證明，在Linux上操作比較方便，步驟如下：

將下載的文件上傳至Linux主機上的目錄
執行如下命令，生成dbgen文件：make

查看 dbgen 命令幫助： ./dbgen -h

生成數據文件，命令如下：

(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

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

連接mysql數據庫，創建數據庫，數據表

創建數據庫：create database ssb;
創建數據庫表：

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);

執行完成后，結果如下：

執行如下SQL、導入數據

set autocommit=off;
load data infile '/dbgen/lineorder.tbl' into table LINEORDER fields terminated by '|' lines terminated by '|
';
commit;

load data infile '/dbgen/supplier.tbl' into table SUPPLIER fields terminated by '|' lines terminated by '|
';
commit;

load data infile '/dbgen/customer.tbl' into table CUSTOMER fields terminated by '|' lines terminated by '|
';
commit;

load data infile '/dbgen/part.tbl' into table PART fields terminated by '|' lines terminated by '|
';
commit;

load data infile '/dbgen/date.tbl' into table DATES fields terminated by '|' lines terminated by '|
';
commit;

查看數據是否正確

標準SQL查詢測試語句

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;

nfobright 342 on linux 64

總結

以上是生活随笔為你收集整理的SSB基准测试的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：使用DataGrid控件
下一篇： imageX及其安装windows