SSB基准测试-MySQL

已于 2022-10-28 15:55:33 修改
阅读量1.2k 收藏 5
点赞数 3
分类专栏： ssb基准测试 文章标签： ssb基准测试
于 2022-10-28 14:53:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/docsz/article/details/127570028
版权

1、SSB

1.1、概述

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

1.2、SSB基准测试包括

1个事实表：lineorder；
4个维度表：customer，part，dwdate，supplier；
13条标准SQL查询测试语句：统计查询、多表关联、sum、复杂条件、group by、order by等组合方式。
在这里插入图片描述

1.3、SSB数据生成器

生成数据文件
git clone https://github.com/electrum/ssb-dbgen.git
cd ssb-dbgen
sed -i 's/^MACHINE.*=.*/MACHINE=LINUX/' makefile		# 将平台改为LINUX
make													# 生成可执行程序dbgen

# 利用dbgen生成示例数据，-T指定表，-s指定数据放大系数
./dbgen -s 50 -T c									# CUSTOMER表
	1500000 customer.tbl
./dbgen -s 20 -T p									# PART表
	1000000 part.tbl
./dbgen -s 50 -T s									# SUPPLIER表
	100000 supplier.tbl
./dbgen -s 1 -T d									# DATE_DIM表
	2556 date.tbl
./dbgen -s 2 -T l									# LINEORDER表
	11997996 lineorder.tbl

1.4、MySQL配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data/mysql/data
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/data/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#忽略大小写配置
lower_case_table_names=1
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
#  ---------------- Session Buffer Cache ----------------
max_allowed_packet = 64M

2、MySQL操作

2.1、创建数据库

create database ssb;

2.2、 创建数据表

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,
o_linenumber     bigint,
o_custkey        integer not null,
o_partkey        integer not null,
o_suppkey        integer not null,
o_orderdate      integer not null,
o_orderpriotity  varchar(15) not null,
o_shippriotity   integer,
o_quantity       bigint,
o_extendedprice  bigint,
o_ordtotalprice  bigint,
o_discount       bigint,
lo_revenue        bigint,
lo_supplycost     bigint,
lo_tax            bigint,
lo_commitdate     integer not null,
lo_shipmode       varchar(10) not null);

create table if not exists `lineorder_flat` (
  `lo_orderkey` int(11) not null comment "",
  `lo_orderdate` date not null comment "",
  `lo_linenumber` tinyint(4) not null comment "",
  `lo_custkey` int(11) not null comment "",
  `lo_partkey` int(11) not null comment "",
  `lo_suppkey` int(11) not null comment "",
  `lo_orderpriotity` varchar(100) not null comment "",
  `lo_shippriotity` tinyint(4) not null comment "",
  `lo_quantity` tinyint(4) not null comment "",
  `lo_extendedprice` int(11) not null comment "",
  `lo_ordtotalprice` int(11) not null comment "",
  `lo_discount` tinyint(4) not null comment "",
  `lo_revenue` int(11) not null comment "",
  `lo_supplycost` int(11) not null comment "",
  `lo_tax` tinyint(4) not null comment "",
  `lo_commitdate` date not null comment "",
  `lo_shipmode` varchar(100) not null comment "",
  `c_name` varchar(100) not null comment "",
  `c_address` varchar(100) not null comment "",
  `c_city` varchar(100) not null comment "",
  `c_nation` varchar(100) not null comment "",
  `c_region` varchar(100) not null comment "",
  `c_phone` varchar(100) not null comment "",
  `c_mktsegment` varchar(100) not null comment "",
  `s_name` varchar(100) not null comment "",
  `s_address` varchar(100) not null comment "",
  `s_city` varchar(100) not null comment "",
  `s_nation` varchar(100) not null comment "",
  `s_region` varchar(100) not null comment "",
  `s_phone` varchar(100) not null comment "",
  `p_name` varchar(100) not null comment "",
  `p_mfgr` varchar(100) not null comment "",
  `p_category` varchar(100) not null comment "",
  `p_brand` varchar(100) not null comment "",
  `p_color` varchar(100) not null comment "",
  `p_type` varchar(100) not null comment "",
  `p_size` tinyint(4) not null comment "",
  `p_container` varchar(100) not null comment ""
);

2.3、 导入数据

load data infile '/var/lib/mysql-files/lineorder.tbl' into table lineorder fields terminated by '|' lines terminated by '|\n';
commit;

load data infile '/var/lib/mysql-files/supplier.tbl' into table supplier fields terminated by '|' lines terminated by '|\n';
commit;

load data infile '/var/lib/mysql-files/customer.tbl' into table customer fields terminated by '|' lines terminated by '|\n';
commit;

load data infile '/var/lib/mysql-files/part.tbl' into table part fields terminated by '|' lines terminated by '|\n';
commit;

load data infile '/var/lib/mysql-files/date.tbl' into table dates fields terminated by '|' lines terminated by '|\n';
commit;

2.4、lineorder_flat insert

insert into lineorder_flat select `lo_orderkey` , `lo_orderdate` , `lo_linenumber` , `lo_custkey` , `lo_partkey` , `lo_suppkey` , `lo_orderpriotity` , `lo_shippriotity` , `lo_quantity` , `lo_extendedprice` , `lo_ordtotalprice` , `lo_discount` , `lo_revenue` , `lo_supplycost` , `lo_tax` , `lo_commitdate` , `lo_shipmode` , `c_name` , `c_address` , `c_city` , `c_nation` , `c_region` , `c_phone` , `c_mktsegment` , `s_name` , `s_address` , `s_city` , `s_nation` , `s_region` , `s_phone` , `p_name` , `p_mfgr` , `p_category` , `p_brand` , `p_color` , `p_type` , `p_size` , `p_container` from lineorder l inner join customer c on (c.c_custkey = l.lo_custkey)  inner join supplier s on (s.s_suppkey = l.lo_suppkey)  inner join part p on  (p.p_partkey = l.lo_partkey) where year(lo_orderdate) in (1992, 1993);

insert into lineorder_flat select `lo_orderkey` , `lo_orderdate` , `lo_linenumber` , `lo_custkey` , `lo_partkey` , `lo_suppkey` , `lo_orderpriotity` , `lo_shippriotity` , `lo_quantity` , `lo_extendedprice` , `lo_ordtotalprice` , `lo_discount` , `lo_revenue` , `lo_supplycost` , `lo_tax` , `lo_commitdate` , `lo_shipmode` , `c_name` , `c_address` , `c_city` , `c_nation` , `c_region` , `c_phone` , `c_mktsegment` , `s_name` , `s_address` , `s_city` , `s_nation` , `s_region` , `s_phone` , `p_name` , `p_mfgr` , `p_category` , `p_brand` , `p_color` , `p_type` , `p_size` , `p_container` from lineorder l inner join customer c on (c.c_custkey = l.lo_custkey)  inner join supplier s on (s.s_suppkey = l.lo_suppkey)  inner join part p on  (p.p_partkey = l.lo_partkey) where year(lo_orderdate) in (1994, 1995);

insert into lineorder_flat select `lo_orderkey` , `lo_orderdate` , `lo_linenumber` , `lo_custkey` , `lo_partkey` , `lo_suppkey` , `lo_orderpriotity` , `lo_shippriotity` , `lo_quantity` , `lo_extendedprice` , `lo_ordtotalprice` , `lo_discount` , `lo_revenue` , `lo_supplycost` , `lo_tax` , `lo_commitdate` , `lo_shipmode` , `c_name` , `c_address` , `c_city` , `c_nation` , `c_region` , `c_phone` , `c_mktsegment` , `s_name` , `s_address` , `s_city` , `s_nation` , `s_region` , `s_phone` , `p_name` , `p_mfgr` , `p_category` , `p_brand` , `p_color` , `p_type` , `p_size` , `p_container` from lineorder l inner join customer c on (c.c_custkey = l.lo_custkey)  inner join supplier s on (s.s_suppkey = l.lo_suppkey)  inner join part p on  (p.p_partkey = l.lo_partkey) where year(lo_orderdate) in (1996, 1997);

insert into lineorder_flat select `lo_orderkey` , `lo_orderdate` , `lo_linenumber` , `lo_custkey` , `lo_partkey` , `lo_suppkey` , `lo_orderpriotity` , `lo_shippriotity` , `lo_quantity` , `lo_extendedprice` , `lo_ordtotalprice` , `lo_discount` , `lo_revenue` , `lo_supplycost` , `lo_tax` , `lo_commitdate` , `lo_shipmode` , `c_name` , `c_address` , `c_city` , `c_nation` , `c_region` , `c_phone` , `c_mktsegment` , `s_name` , `s_address` , `s_city` , `s_nation` , `s_region` , `s_phone` , `p_name` , `p_mfgr` , `p_category` , `p_brand` , `p_color` , `p_type` , `p_size` , `p_container` from lineorder l inner join customer c on (c.c_custkey = l.lo_custkey)  inner join supplier s on (s.s_suppkey = l.lo_suppkey)  inner join part p on  (p.p_partkey = l.lo_partkey) where year(lo_orderdate) in (1998);

2.5、 创建索引

alter table dates add primary key (d_datekey);
alter table dates add index d_year(d_year);

alter table part add primary key (p_partkey);
alter table part add index p_brand(p_brand);

alter table supplier add primary key (s_suppkey);
alter table supplier add index s_city(s_city);
alter table supplier add index s_nation(s_nation);
alter table supplier add index s_region(s_region);

alter table customer add primary key (c_custkey);
alter table customer add index c_nation(c_nation);
alter table customer add index c_region(c_region);

alter table lineorder add index lo_orderkey(lo_orderkey);
alter table lineorder add index lo_linenumber(lo_linenumber);
alter table lineorder add index lo_custkey(lo_custkey);
alter table lineorder add index lo_partkey(lo_partkey);
alter table lineorder add index lo_suppkey(lo_suppkey);
alter table lineorder add index lo_orderdate(lo_orderdate);
alter table lineorder add index lo_revenue(lo_revenue);
alter table lineorder add index lo_supplycost(lo_supplycost);

alter table lineorder_flat add index lo_orderkey(lo_orderkey);
alter table lineorder_flat add index lo_orderdate(lo_orderdate);
alter table lineorder_flat add index lo_custkey(lo_custkey);
alter table lineorder_flat add index lo_partkey(lo_partkey);
alter table lineorder_flat add index lo_suppkey(lo_suppkey);
alter table lineorder_flat add index lo_quantity(lo_quantity);
alter table lineorder_flat add index p_brand(p_brand);
alter table lineorder_flat add index c_nation(c_nation);
alter table lineorder_flat add index c_city(c_city);
alter table lineorder_flat add index s_city(s_city);
alter table lineorder_flat add index c_region(c_region);
alter table lineorder_flat add index s_region(s_region);
alter table lineorder_flat add index  p_mfgr(p_mfgr);

3、 执行标准SQL-多表查询

--Q1.1
select sum(lo_revenue) as revenue
from lineorder join dates on lo_orderdate = d_datekey
where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;

--Q1.2
select sum(lo_revenue) as revenue
from lineorder
join dates on lo_orderdate = d_datekey
where d_yearmonthnum = 199401
and lo_discount between 4 and 6
and lo_quantity between 26 and 35;


--Q1.3
select sum(lo_revenue) as revenue
from lineorder
join dates on lo_orderdate = d_datekey
where d_weeknuminyear = 6 and d_year = 1994
and lo_discount between 5 and 7
and lo_quantity between 26 and 35;


--Q2.1
select sum(lo_revenue) as lo_revenue, d_year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_category = 'MFGR#12' and s_region = 'AMERICA'
group by d_year, p_brand
order by d_year, p_brand;


--Q2.2
select sum(lo_revenue) as lo_revenue, d_year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'
group by d_year, p_brand
order by d_year, p_brand;


--Q2.3
select sum(lo_revenue) as lo_revenue, d_year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_brand = 'MFGR#2239' and s_region = 'EUROPE'
group by d_year, p_brand
order by d_year, p_brand;


--Q3.1
select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where 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, lo_revenue desc;


--Q3.2
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where 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, lo_revenue desc;


--Q3.3
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where (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, lo_revenue desc;


--Q3.4
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where (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, lo_revenue desc;


--Q4.1
select d_year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where 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;


--Q4.2
select d_year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where 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;


--Q4.3
select d_year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where c_region = 'AMERICA'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;

3.1、多表查询结果

在这里插入图片描述

4、执行标准SQL-单表查询

--Q1.1
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE LO_ORDERDATE >= 19930101 and LO_ORDERDATE <= 19931231 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;


SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19940131 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;


--Q1.3
SELECT sum(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 BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;


--Q2.1
SELECT
    sum(LO_REVENUE),
    (LO_ORDERDATE DIV 10000) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;


--Q2.2
SELECT
    sum(LO_REVENUE),
    (LO_ORDERDATE DIV 10000) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;


--Q2.3
SELECT
    sum(LO_REVENUE),
    (LO_ORDERDATE DIV 10000) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;


--Q3.1
SELECT
    C_NATION,
    S_NATION,
    (LO_ORDERDATE DIV 10000) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE   <= 19971231
GROUP BY
    C_NATION,
    S_NATION,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q3.2
SELECT
    C_CITY,
    S_CITY,
    (LO_ORDERDATE DIV 10000) AS year,
    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
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q3.3
SELECT
    C_CITY,
    S_CITY,
    (LO_ORDERDATE DIV 10000) AS year,
    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
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q3.4
SELECT
    C_CITY,
    S_CITY,
    (LO_ORDERDATE DIV 10000) AS year,
    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
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q4.1
SELECT
   (LO_ORDERDATE DIV 10000) AS year,
    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')
GROUP BY
    year,
    C_NATION
ORDER BY
    year ASC,
    C_NATION ASC;


--Q4.2
SELECT
   (LO_ORDERDATE DIV 10000) AS year,
    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')
GROUP BY
    year,
    S_NATION,
    P_CATEGORY
ORDER BY
    year ASC,
    S_NATION ASC,
    P_CATEGORY ASC;


--Q4.3
SELECT
    (LO_ORDERDATE DIV 10000) AS year,
    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'
GROUP BY
    year,
    S_CITY,
    P_BRAND
ORDER BY
    year ASC,
    S_CITY ASC,
    P_BRAND ASC;

4.1、单表查询结果

在这里插入图片描述

docsz
关注
  • 3
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
比 ClickHouse 再快 24% ！ YMatrix 5.0 SSB 基准测试报告
weixin_57459958的博客
12-05 311
YMatrix 5.0 SSB 基准性能测试。
ssb-bot-feed：Scuttlebutt机器人，可读取RSS提要并自动发布更新
02-01
ssb-bot-feed Scuttlebutt机器人可以自动读取RSS提要并发布更新 用法 下载并运行 npm install -g ssb-bot-feed 句法： ssb-bot-feed [feed1] [feed2] [feedN] 例： ssb-bot-feed ...
clickhouse SSB 性能测试
司马懿的西山居
11-29 1824
SSB（Star Schema Benchmark）的介绍论文地址： https://www.cs.umb.edu/~poneil/StarSchemaB.PDF 官网链接 https://clickhouse.com/docs/en/getting-started/example-datasets/star-schema/ 如果安装系统时，时最小化mini安装，经常会提示很多命令不存在 提示 git 不存在，使用 yum install git 安装即可 提示 make: command not fo
SSB系统的性能分析
05-12
调制的目的是把要传输的模拟信号或数字信号变换成适合信道传输的高频信号。按照调制器输入信号的形式，调制可分为模拟调制和数字调制。单边带调制是模拟调制中幅度调制的一种。本文介绍了单边带调制与解调的原理及其实现、已调信号的频谱特性、单边带解调器的调制度增益，并对单边带调制与双边带调制的性能进行了比较。现在移动通信系统都在使用数字调制技术，通信系统正在由模拟方式向数字方式过渡，可见模拟调制最终将逐步被数字调制所取代。
Doris【部署 02】Linux环境Doris数据库进行SSB基准测试
最新发布
Java与大数据相关实践内容分享！
05-16 1080
Linux环境Doris数据库进行SSB基准测试
第3.0章：StarRocks SSB性能测试（拓展篇）
流木的博客
01-07 4358
SSB（Star Schema Benchmark）是麻省州立大学波士顿校区的研究人员定义的基于现实商业应用的数据模型，是业界公认的可用来模拟决策支持类应用的测试集合，其测试结果比较公正和中立。学术界和工业界普遍采用它来评价决策支持技术类应用的性能，以此全方位评测系统的整体商业计算综合能力。 通过SSB测试集合，我们可以方便的对比各种OLAP产品的基础性能指标，StarRocks的SSB测试报告可以在官网获取，地址为： StarRocks - 新一代极速全场景MPP数据库https://www.star
SSB基准测试-Apache Doris
docsz的博客
10-28 678
apache doris性能测试
SSB基准测试-ClickHouse
docsz的博客
11-03 445
SSB基准测试-ClickHouse
SSB基准测试-模型介绍
com728871的博客
07-04 1205
SSB（Star Schema Benchmark）是麻省州立大学波士顿校区的研究人员定义的基于现实商业应用的数据模型，业界公认用来模拟决策支持类应用，比较公正和中立。学术界和工业界普遍采用它来评价决策支持技术方面应用的性能。全...
Clickhouse（20.4.2.9） SSB性能测试
聂雄超的博客
02-24 1583
SSB（Star Schema Benchmark）是麻省州立大学波士顿校区的研究人员定义的基于现实商业应用的数据模型，业界公认用来模拟决策支持类应用，比较公正和中立。
TPC-C 、TPC-H、TPC-DS和SSB测试基准（Benchmark）介绍
Laurence的技术博客
12-06 2767
TPC是一个非盈利组织，致力于定义事务处理和数据库benchmark，其提供了面向不同应用场景的多种Benchmark: https://www.tpc.org/information/benchmarks5.asp 下图是对几个主要的benchmark的汇总： 接下来，我们着重介绍一上最广泛讨论的四种Benchmark.面向OLTP测试，也就是测试关系型数据库的数据集。面向OLTP测试，也就是测试分析型数据库/数据仓库的数据集，业务场景是“商品零售业”。TPC-H是一款面向商品零售业的决策支持系统测试
OLAP(七)：StarRocks
.
08-26 962
StarRocks 是一款极速统一的Lakehouse产品，具备水平在线扩缩容，金融级高可用，兼容 MySQL 5.7 协议和 MySQL 生态，提供全面向量化引擎与多种数据源联邦查询等重要特性。StarRocks 致力于在全场景 OLAP 业务上为用户提供统一的解决方案，适用于对性能，实时性，并发能力和灵活性有较高要求的各类应用场景。首先我们先来看一下 StarRocks 是一款什么样的产品，他的产品定位是什么样的，他处于大数据生态什么位置上。
ssb-invites-db:使用ssb-invites-schema进行盾牌邀请的flumeview-reduce
05-08
ssb-invites-db 一种针对scuttle- ssb插件使用scuttle- flumeview-reduce。 允许在root记录（例如的顶部使用多态邀请/回复系统。 // Plug into a Scuttlebot server Server.use(require('ssb-invites-db')) // ...
SSB-Keys-Mnemonic-dotnet:NodeJS ssb-keys-mnemonic https的C＃（.NET Standard）实现
04-12
SSB按键助记符在SSB密钥和助记码之间进行转换的模块。 C＃（.NET Standard）实现用法using SSB . Keys . Mnemonic ;字符串KeysToWords（按键） var mnemonic = new MnemonicConverter ();var keys = new Keys{Curve ...
ssb-browser-demo
03-30
ssb浏览器演示 尝试 建造 npm run build 启动本地服务 npm start 测试 这与这里无关b / c被浏览器化的代码不在节点进程中运行，因此无法执行ipc 需要具有2个浏览器进程（2个标识）。 然后，为另一个发布“跟随”...
ssb-secure-partial-replication
03-14
SSB安全部分复制 现状：设计阶段 本文档将概述转换现有SSB身份以使其准备部分复制所需的步骤。 首先，如所述，将从现有的SSB提要中生成根元。 主供稿与元供稿链接在一起，这意味着应用程序可以开始使用从元供稿链接...
以 100GB SSB 性能测试为例，通过 ByteHouse 云数仓开启你的数据分析之路
字节跳动技术团队官方博客
04-21 3710
动手点关注干货不迷路I. 传统数仓的演进：云数仓近年来，随着数据“爆炸式”的增长，越来越多的数据被产生、收集和存储。而挖掘海量数据中的真实价值，从其中提取商机并洞见未来，则成了现代企业和组织不可忽视的命题。随着数据量级和复杂度的增大，数据分析处理的技术架构也在不断演进。在面对海量数据分析时，传统 OLAP 技术架构中的痛点变得越来越明显，如扩容缩容耗时长，导致资源利用率偏低，成本居高不下；以及运维...
SSB数据集导入postgresql
weixin_45867695的博客
04-03 351
windows上，使用MInGW64编译ssb-dbgen，生成ssb数据集文件，导入postgresql数据库
如何用iq调制实现dsb-ask和ssb-ask
11-22
DSB-ASK（Double Sideband Amplitude Shift Keying 双边带幅度调制）是一种调制方式，它将数字信号转化为两边带的调制信号传输。而SSB-ASK（Single Sideband Amplitude Shift Keying 单边带幅度调制）则是DSB-ASK的一种变种，只传输其中一边带的信号。 要实现DSB-ASK调制，可以按照以下步骤进行： 1. 将数字信号进行二进制编码，将每个数字转化为对应的基带信号。 2. 使用一个载波信号，可以选择正弦波或者余弦波作为载波。 3. 将基带信号与载波信号相乘，得到DSB-ASK调制的信号波形。 4. 可以通过增加滤波器来去除多余的频谱。 而要实现SSB-ASK调制，可以按照以下步骤进行： 1. 同样将数字信号进行二进制编码，将每个数字转化为对应的基带信号。 2. 使用一个载波信号，可以选择正弦波或者余弦波作为载波。 3. 将基带信号与载波信号相乘，得到DSB-ASK调制的信号波形。 4. 对DSB-ASK调制的信号进行带通滤波，将另外一个带通滤波器封锁。 5. 进行滤波后，得到SSB-ASK调制的信号波形。 在实际应用中，也可以利用数学软件进行模拟和实现，例如使用Matlab等工具进行DSB-ASK和SSB-ASK调制的仿真实验。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值