Star Schema Benchmark

sh bin/build-ssb-dbgen.sh

sh bin/gen-ssb-data.sh -s 1000

# Any of FE host
export FE_HOST='127.0.0.1'
# http_port in fe.conf
export FE_HTTP_PORT=8030
# query_port in fe.conf
export FE_QUERY_PORT=9030
# Doris username
export USER='root'
# Doris password
export PASSWORD=''
# The database where SSB tables located
export DB='ssb'

sh bin/create-ssb-tables.sh -s 1000

 sh bin/load-ssb-data.sh

selectcount(*)from part;
selectcount(*)from customer;
selectcount(*)from supplier;
selectcount(*)from dates;
selectcount(*)from lineorder;
selectcount(*)from lineorder_flat;

--Q1.1
SELECTSUM(LO_EXTENDEDPRICE * LO_DISCOUNT)AS revenue
FROM lineorder_flat
WHERE
 LO_ORDERDATE >=19930101
AND LO_ORDERDATE <=19931231
AND LO_DISCOUNT BETWEEN1AND3
AND LO_QUANTITY <25;

--Q1.2
SELECTSUM(LO_EXTENDEDPRICE * LO_DISCOUNT)AS revenue
FROM lineorder_flat
WHERE
 LO_ORDERDATE >=19940101
AND LO_ORDERDATE <=19940131
AND LO_DISCOUNT BETWEEN4AND6
AND LO_QUANTITY BETWEEN26AND35;

--Q1.3
SELECTSUM(LO_EXTENDEDPRICE * LO_DISCOUNT)AS revenue
FROM lineorder_flat
WHERE
 weekofyear(LO_ORDERDATE)=6
AND LO_ORDERDATE >=19940101
AND LO_ORDERDATE <=19941231
AND LO_DISCOUNT BETWEEN5AND7
AND LO_QUANTITY BETWEEN26AND35;

--Q2.1
SELECT
SUM(LO_REVENUE),(LO_ORDERDATE DIV10000)ASYEAR,
 P_BRAND
FROM lineorder_flat
WHERE P_CATEGORY ='MFGR#12'AND S_REGION ='AMERICA'
GROUPBYYEAR, P_BRAND
ORDERBYYEAR, P_BRAND;

--Q2.2
SELECT
SUM(LO_REVENUE),(LO_ORDERDATE DIV10000)ASYEAR,
 P_BRAND
FROM lineorder_flat
WHERE
 P_BRAND >='MFGR#2221'
AND P_BRAND <='MFGR#2228'
AND S_REGION ='ASIA'
GROUPBYYEAR, P_BRAND
ORDERBYYEAR, P_BRAND;

--Q2.3
SELECT
SUM(LO_REVENUE),(LO_ORDERDATE DIV10000)ASYEAR,
 P_BRAND
FROM lineorder_flat
WHERE
 P_BRAND ='MFGR#2239'
AND S_REGION ='EUROPE'
GROUPBYYEAR, P_BRAND
ORDERBYYEAR, P_BRAND;

--Q3.1
SELECT
 C_NATION,
 S_NATION,(LO_ORDERDATE DIV10000)ASYEAR,
SUM(LO_REVENUE)AS revenue
FROM lineorder_flat
WHERE
 C_REGION ='ASIA'
AND S_REGION ='ASIA'
AND LO_ORDERDATE >=19920101
AND LO_ORDERDATE <=19971231
GROUPBY C_NATION, S_NATION,YEAR
ORDERBYYEARASC, revenue DESC;

--Q3.2
SELECT
 C_CITY,
 S_CITY,(LO_ORDERDATE DIV10000)ASYEAR,
SUM(LO_REVENUE)AS revenue
FROM lineorder_flat
WHERE
 C_NATION ='UNITED STATES'
AND S_NATION ='UNITED STATES'
AND LO_ORDERDATE >=19920101
AND LO_ORDERDATE <=19971231
GROUPBY C_CITY, S_CITY,YEAR
ORDERBYYEARASC, revenue DESC;

--Q3.3
SELECT
 C_CITY,
 S_CITY,(LO_ORDERDATE DIV10000)ASYEAR,
SUM(LO_REVENUE)AS revenue
FROM lineorder_flat
WHERE
 C_CITY IN('UNITED KI1','UNITED KI5')
AND S_CITY IN('UNITED KI1','UNITED KI5')
AND LO_ORDERDATE >=19920101
AND LO_ORDERDATE <=19971231
GROUPBY C_CITY, S_CITY,YEAR
ORDERBYYEARASC, revenue DESC;

--Q3.4
SELECT
 C_CITY,
 S_CITY,(LO_ORDERDATE DIV10000)ASYEAR,
SUM(LO_REVENUE)AS revenue
FROM lineorder_flat
WHERE
 C_CITY IN('UNITED KI1','UNITED KI5')
AND S_CITY IN('UNITED KI1','UNITED KI5')
AND LO_ORDERDATE >=19971201
AND LO_ORDERDATE <=19971231
GROUPBY C_CITY, S_CITY,YEAR
ORDERBYYEARASC, revenue DESC;

--Q4.1
SELECT(LO_ORDERDATE DIV10000)ASYEAR,
 C_NATION,
SUM(LO_REVENUE - LO_SUPPLYCOST)AS profit
FROM lineorder_flat
WHERE
 C_REGION ='AMERICA'
AND S_REGION ='AMERICA'
AND P_MFGR IN('MFGR#1','MFGR#2')
GROUPBYYEAR, C_NATION
ORDERBYYEARASC, C_NATION ASC;

--Q4.2
SELECT(LO_ORDERDATE DIV10000)ASYEAR,
 S_NATION,
 P_CATEGORY,
SUM(LO_REVENUE - LO_SUPPLYCOST)AS profit
FROM lineorder_flat
WHERE
 C_REGION ='AMERICA'
AND S_REGION ='AMERICA'
AND LO_ORDERDATE >=19970101
AND LO_ORDERDATE <=19981231
AND P_MFGR IN('MFGR#1','MFGR#2')
GROUPBYYEAR, S_NATION, P_CATEGORY
ORDERBY
YEARASC,
 S_NATION ASC,
 P_CATEGORY ASC;

--Q4.3
SELECT(LO_ORDERDATE DIV10000)ASYEAR,
 S_CITY,
 P_BRAND,
SUM(LO_REVENUE - LO_SUPPLYCOST)AS profit
FROM lineorder_flat
WHERE
 S_NATION ='UNITED STATES'
AND LO_ORDERDATE >=19970101
AND LO_ORDERDATE <=19981231
AND P_CATEGORY ='MFGR#14'
GROUPBYYEAR, S_CITY, P_BRAND
ORDERBYYEARASC, S_CITY ASC, P_BRAND ASC;

--Q1.1
SELECTSUM(lo_extendedprice * lo_discount)AS REVENUE
FROM lineorder, dates
WHERE
 lo_orderdate = d_datekey
AND d_year =1993
AND lo_discount BETWEEN1AND3
AND lo_quantity <25;

--Q1.2
SELECTSUM(lo_extendedprice * lo_discount)AS REVENUE
FROM lineorder, dates
WHERE
 lo_orderdate = d_datekey
AND d_yearmonth ='Jan1994'
AND lo_discount BETWEEN4AND6
AND lo_quantity BETWEEN26AND35;

--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 BETWEEN5AND7
AND lo_quantity BETWEEN26AND35;

--Q2.1
SELECTSUM(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'
GROUPBY d_year, p_brand
ORDERBY p_brand;

--Q2.2
SELECTSUM(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'
GROUPBY d_year, p_brand
ORDERBY d_year, p_brand;

--Q2.3
SELECTSUM(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'
GROUPBY d_year, p_brand
ORDERBY d_year, p_brand;

--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
GROUPBY c_nation, s_nation, d_year
ORDERBY d_year ASC, REVENUE DESC;

--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
GROUPBY c_city, s_city, d_year
ORDERBY d_year ASC, REVENUE DESC;

--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
GROUPBY c_city, s_city, d_year
ORDERBY d_year ASC, REVENUE DESC;

--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'
GROUPBY c_city, s_city, d_year
ORDERBY d_year ASC, REVENUE DESC;

--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'
)
GROUPBY d_year, c_nation
ORDERBY d_year, c_nation;

--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'
)
GROUPBY d_year, s_nation, p_category
ORDERBY d_year, s_nation, p_category;

--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'
GROUPBY d_year, s_city, p_brand
ORDERBY d_year, s_city, p_brand;