DorisDBvsClickHouse查询测试

作者：fanersai_668 | 来源：互联网 | 2023-09-18 15:47

测试方法为了方便用户快速的了解DorisDB的性能指标，这里我们提供了一个标准的Starschemabenchmark的测试方法和工具仅供参考。Starschemab

测试方法

为了方便用户快速的了解DorisDB的性能指标&＃xff0c;这里我们提供了一个标准的Star schema benchmark的测试方法和工具仅供参考。

Star schema benchmark&＃xff08;以下简称SSB&＃xff09;是学术界和工业界广泛使用的一个星型模型测试集&＃xff08;来源论文&＃xff09;&＃xff0c;通过这个测试集合也可以容易的和其他OLAP产品进行性能对比。

测试数据

表名	行数	解释
lineorder	6亿	SSB商品订单表
customer	300万	SSB客户表
part	140万	SSB 零部件表
supplier	20万	SSB 供应商表
dates	2556	日期表
lineorder_flat	6亿	SSB打平后的宽表

测试数据环境

主机资源&＃xff1a;

主机	内存	CPU
CentOS	128G	16core

DorisDB使用资源:

FE 单台&＃xff0c;BE 5台部署

内存	CPU	数量
80G	16core	6

ClickHouse使用资源:

内存	CPU	数量
80G	16core	1

说明&＃xff1a;由于资源原因&＃xff0c;DorisDB、ClickHouse集群和上同时部署了CDH集群和其他组件&＃xff0c;所以该测试并不能100%代表DorisDB、ClickHouse的性能。

测试结果

单表测试结果如下

img

多表测试结果如下

img

测试过程

DorisDB

下载ssb-poc工具包并编译

wget http://dorisdb-public.oss-cn-zhangjiakou.aliyuncs.com/ssb-poc-0.9.zip unzip ssb-poc-0.9.zip cd ssb-poc make && make install #所有相关工具安装到output目录

生成数据

cd output # 运行生成数据的脚本 bin/gen-ssb.sh 100 data_dir

建表

CREATE TABLE IF NOT EXISTS &＃96;lineorder&＃96; (&＃96;lo_orderkey&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_linenumber&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_custkey&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_partkey&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_suppkey&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_orderdate&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_orderpriority&＃96; varchar(16) NOT NULL COMMENT "",&＃96;lo_shippriority&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_quantity&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_extendedprice&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_ordtotalprice&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_discount&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_revenue&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_supplycost&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_tax&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_commitdate&＃96; int(11) NOT NULL COMMENT "",&＃96;lo_shipmode&＃96; varchar(11) NOT NULL COMMENT "" ) ENGINE&＃61;OLAP DUPLICATE KEY(&＃96;lo_orderkey&＃96;) COMMENT "OLAP" DISTRIBUTED BY HASH(&＃96;lo_orderkey&＃96;) BUCKETS 96 PROPERTIES ( "replication_num" &＃61; "1", "colocate_with" &＃61; "group1", "in_memory" &＃61; "false", "storage_format" &＃61; "DEFAULT" );CREATE TABLE IF NOT EXISTS &＃96;customer&＃96; (&＃96;c_custkey&＃96; int(11) NOT NULL COMMENT "",&＃96;c_name&＃96; varchar(26) NOT NULL COMMENT "",&＃96;c_address&＃96; varchar(41) NOT NULL COMMENT "",&＃96;c_city&＃96; varchar(11) NOT NULL COMMENT "",&＃96;c_nation&＃96; varchar(16) NOT NULL COMMENT "",&＃96;c_region&＃96; varchar(13) NOT NULL COMMENT "",&＃96;c_phone&＃96; varchar(16) NOT NULL COMMENT "",&＃96;c_mktsegment&＃96; varchar(11) NOT NULL COMMENT "" ) ENGINE&＃61;OLAP DUPLICATE KEY(&＃96;c_custkey&＃96;) COMMENT "OLAP" DISTRIBUTED BY HASH(&＃96;c_custkey&＃96;) BUCKETS 12 PROPERTIES ( "replication_num" &＃61; "1", "colocate_with" &＃61; "groupa2", "in_memory" &＃61; "false", "storage_format" &＃61; "DEFAULT" );CREATE TABLE IF NOT EXISTS &＃96;dates&＃96; (&＃96;d_datekey&＃96; int(11) NOT NULL COMMENT "",&＃96;d_date&＃96; varchar(20) NOT NULL COMMENT "",&＃96;d_dayofweek&＃96; varchar(10) NOT NULL COMMENT "",&＃96;d_month&＃96; varchar(11) NOT NULL COMMENT "",&＃96;d_year&＃96; int(11) NOT NULL COMMENT "",&＃96;d_yearmonthnum&＃96; int(11) NOT NULL COMMENT "",&＃96;d_yearmonth&＃96; varchar(9) NOT NULL COMMENT "",&＃96;d_daynuminweek&＃96; int(11) NOT NULL COMMENT "",&＃96;d_daynuminmonth&＃96; int(11) NOT NULL COMMENT "",&＃96;d_daynuminyear&＃96; int(11) NOT NULL COMMENT "",&＃96;d_monthnuminyear&＃96; int(11) NOT NULL COMMENT "",&＃96;d_weeknuminyear&＃96; int(11) NOT NULL COMMENT "",&＃96;d_sellingseason&＃96; varchar(14) NOT NULL COMMENT "",&＃96;d_lastdayinweekfl&＃96; int(11) NOT NULL COMMENT "",&＃96;d_lastdayinmonthfl&＃96; int(11) NOT NULL COMMENT "",&＃96;d_holidayfl&＃96; int(11) NOT NULL COMMENT "",&＃96;d_weekdayfl&＃96; int(11) NOT NULL COMMENT "" ) ENGINE&＃61;OLAP DUPLICATE KEY(&＃96;d_datekey&＃96;) COMMENT "OLAP" DISTRIBUTED BY HASH(&＃96;d_datekey&＃96;) BUCKETS 1 PROPERTIES ( "replication_num" &＃61; "1", "in_memory" &＃61; "false", "colocate_with" &＃61; "groupa3", "storage_format" &＃61; "DEFAULT" );CREATE TABLE IF NOT EXISTS &＃96;supplier&＃96; (&＃96;s_suppkey&＃96; int(11) NOT NULL COMMENT "",&＃96;s_name&＃96; varchar(26) NOT NULL COMMENT "",&＃96;s_address&＃96; varchar(26) NOT NULL COMMENT "",&＃96;s_city&＃96; varchar(11) NOT NULL COMMENT "",&＃96;s_nation&＃96; varchar(16) NOT NULL COMMENT "",&＃96;s_region&＃96; varchar(13) NOT NULL COMMENT "",&＃96;s_phone&＃96; varchar(16) NOT NULL COMMENT "" ) ENGINE&＃61;OLAP DUPLICATE KEY(&＃96;s_suppkey&＃96;) COMMENT "OLAP" DISTRIBUTED BY HASH(&＃96;s_suppkey&＃96;) BUCKETS 12 PROPERTIES ( "replication_num" &＃61; "1", "colocate_with" &＃61; "groupa4", "in_memory" &＃61; "false", "storage_format" &＃61; "DEFAULT" );CREATE TABLE IF NOT EXISTS &＃96;part&＃96; (&＃96;p_partkey&＃96; int(11) NOT NULL COMMENT "",&＃96;p_name&＃96; varchar(23) NOT NULL COMMENT "",&＃96;p_mfgr&＃96; varchar(7) NOT NULL COMMENT "",&＃96;p_category&＃96; varchar(8) NOT NULL COMMENT "",&＃96;p_brand&＃96; varchar(10) NOT NULL COMMENT "",&＃96;p_color&＃96; varchar(12) NOT NULL COMMENT "",&＃96;p_type&＃96; varchar(26) NOT NULL COMMENT "",&＃96;p_size&＃96; int(11) NOT NULL COMMENT "",&＃96;p_container&＃96; varchar(11) NOT NULL COMMENT "" ) ENGINE&＃61;OLAP DUPLICATE KEY(&＃96;p_partkey&＃96;) COMMENT "OLAP" DISTRIBUTED BY HASH(&＃96;p_partkey&＃96;) BUCKETS 12 PROPERTIES ( "replication_num" &＃61; "1", "colocate_with" &＃61; "groupa5", "in_memory" &＃61; "false", "storage_format" &＃61; "DEFAULT" );CREATE TABLE IF NOT EXISTS &＃96;lineorder_flat&＃96; (&＃96;LO_ORDERKEY&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_ORDERDATE&＃96; date NOT NULL COMMENT "",&＃96;LO_LINENUMBER&＃96; tinyint(4) NOT NULL COMMENT "",&＃96;LO_CUSTKEY&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_PARTKEY&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_SUPPKEY&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_ORDERPRIORITY&＃96; varchar(100) NOT NULL COMMENT "",&＃96;LO_SHIPPRIORITY&＃96; tinyint(4) NOT NULL COMMENT "",&＃96;LO_QUANTITY&＃96; tinyint(4) NOT NULL COMMENT "",&＃96;LO_EXTENDEDPRICE&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_ORDTOTALPRICE&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_DISCOUNT&＃96; tinyint(4) NOT NULL COMMENT "",&＃96;LO_REVENUE&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_SUPPLYCOST&＃96; int(11) NOT NULL COMMENT "",&＃96;LO_TAX&＃96; tinyint(4) NOT NULL COMMENT "",&＃96;LO_COMMITDATE&＃96; date NOT NULL COMMENT "",&＃96;LO_SHIPMODE&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_NAME&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_ADDRESS&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_CITY&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_NATION&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_REGION&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_PHONE&＃96; varchar(100) NOT NULL COMMENT "",&＃96;C_MKTSEGMENT&＃96; varchar(100) NOT NULL COMMENT "",&＃96;S_NAME&＃96; varchar(100) NOT NULL COMMENT "",&＃96;S_ADDRESS&＃96; varchar(100) NOT NULL COMMENT "",&＃96;S_CITY&＃96; varchar(100) NOT NULL COMMENT "",&＃96;S_NATION&＃96; varchar(100) NOT NULL COMMENT "",&＃96;S_REGION&＃96; varchar(100) NOT NULL COMMENT "",&＃96;S_PHONE&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_NAME&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_MFGR&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_CATEGORY&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_BRAND&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_COLOR&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_TYPE&＃96; varchar(100) NOT NULL COMMENT "",&＃96;P_SIZE&＃96; tinyint(4) NOT NULL COMMENT "",&＃96;P_CONTAINER&＃96; varchar(100) NOT NULL COMMENT "" ) ENGINE&＃61;OLAP DUPLICATE KEY(&＃96;LO_ORDERKEY&＃96;) COMMENT "OLAP" DISTRIBUTED BY HASH(&＃96;LO_ORDERKEY&＃96;) BUCKETS 192 PROPERTIES ( "replication_num" &＃61; "1", "colocate_with" &＃61; "groupxx1", "in_memory" &＃61; "false", "storage_format" &＃61; "DEFAULT" );

导入数据

通过stream load

#执行导入数据的脚本&＃96; bin /stream_load.sh data_dir

生成ssb单表数据

bin/flat_insert.sh

查询

单表查询

测试ssb单表查询(SQL 参见 share/ssb_test/sql/ssb-flat/)

bin/benchmark.sh -p -d ssb-flat

查询结果&＃xff1a;

image2021-4-16_16-50-49.png

多表查询

测试ssb多表查询 (SQL 参见 share/ssb_test/sql/ssb/)

bin/benchmark.sh -p -d ssb

查询结果

image2021-4-16_17-3-4.png

Clickhouse

下载安装 dbgen

git clone https://github.com/vadimtk/ssb-dbgen.git cd ssb-dbgen make

生成数据

./dbgen -s 1000 -T c ./dbgen -s 1000 -T l ./dbgen -s 1000 -T p ./dbgen -s 1000 -T s ./dbgen -s 1000 -T d

在ClickHouse中创建表

CREATE TABLE test.customer(C_CUSTKEY UInt32,C_NAME String,C_ADDRESS String,C_CITY LowCardinality (String),C_NATION LowCardinality (String),C_REGION LowCardinality (String),C_PHONE String,C_MKTSEGMENT LowCardinality (String))ENGINE &＃61; MergeTree ORDER BY (C_CUSTKEY); CREATE TABLE test.lineorder (LO_ORDERKEY UInt32,LO_LINENUMBER UInt8,LO_CUSTKEY UInt32,LO_PARTKEY UInt32,LO_SUPPKEY UInt32,LO_ORDERDATE Date,LO_ORDERPRIORITY LowCardinality (String),LO_SHIPPRIORITY UInt8,LO_QUANTITY UInt8,LO_EXTENDEDPRICE UInt32,LO_ORDTOTALPRICE UInt32,LO_DISCOUNT UInt8,LO_REVENUE UInt32,LO_SUPPLYCOST UInt32,LO_TAX UInt8,LO_COMMITDATE Date,LO_SHIPMODE LowCardinality (String) ) ENGINE &＃61; MergeTree PARTITION BY toYear (LO_ORDERDATE) ORDER BY(LO_ORDERDATE, LO_ORDERKEY); CREATE TABLE test.part (P_PARTKEY UInt32,P_NAME String,P_MFGR LowCardinality (String),P_CATEGORY LowCardinality (String),P_BRAND LowCardinality (String),P_COLOR LowCardinality (String),P_TYPE LowCardinality (String),P_SIZE UInt8,P_CONTAINER LowCardinality (String) ) ENGINE &＃61; MergeTree ORDER BY P_PARTKEY; CREATE TABLE test.supplier (S_SUPPKEY UInt32,S_NAME String,S_ADDRESS String,S_CITY LowCardinality (String),S_NATION LowCardinality (String),S_REGION LowCardinality (String),S_PHONE String) ENGINE &＃61; MergeTree ORDER BY S_SUPPKEY; CREATE TABLE test.date(D_DATEKEY UInt32,D_DATE String,D_DAYOFWEEK String,D_MONTH String,D_YEAR UInt32,D_YEARMONTHNUM UInt32,D_YEARMONTH String,D_DAYNUMINWEEK UInt32,D_DAYNUMINMONTH UInt32,D_DAYNUMINYEAR UInt32,D_MONTHNUMINYEAR UInt32,D_WEEKNUMINYEAR UInt32,D_SELLINGSEASON String,D_LASTDAYINWEEKFL UInt32,D_LASTDAYINMONTHFL UInt32,D_HOLIDAYFL UInt32,D_WEEKDAYFL UInt32) ENGINE &＃61; MergeTree ORDER BY D_DATEKEY;

导入数据

clickhouse-client -h prd-worker05 --port&＃61;9002 --query "INSERT INTO test.customer FORMAT CSV"

将"星型模式"转换为非规范化的"平面模式"

set max_memory_usage_for_all_queries&＃61;80000000000; set max_memory_usage&＃61;80000000000; set max_bytes_before_external_group_by&＃61;40000000000; CREATE TABLE lineorder_flat ENGINE &＃61; MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS SELECT l.LO_ORDERKEY AS LO_ORDERKEY,l.LO_LINENUMBER AS LO_LINENUMBER,l.LO_CUSTKEY AS LO_CUSTKEY,l.LO_PARTKEY AS LO_PARTKEY,l.LO_SUPPKEY AS LO_SUPPKEY,l.LO_ORDERDATE AS LO_ORDERDATE,l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,l.LO_QUANTITY AS LO_QUANTITY,l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,l.LO_DISCOUNT AS LO_DISCOUNT,l.LO_REVENUE AS LO_REVENUE,l.LO_SUPPLYCOST AS LO_SUPPLYCOST,l.LO_TAX AS LO_TAX,l.LO_COMMITDATE AS LO_COMMITDATE,l.LO_SHIPMODE AS LO_SHIPMODE,c.C_NAME AS C_NAME,c.C_ADDRESS AS C_ADDRESS,c.C_CITY AS C_CITY,c.C_NATION AS C_NATION,c.C_REGION AS C_REGION,c.C_PHONE AS C_PHONE,c.C_MKTSEGMENT AS C_MKTSEGMENT,s.S_NAME AS S_NAME,s.S_ADDRESS AS S_ADDRESS,s.S_CITY AS S_CITY,s.S_NATION AS S_NATION,s.S_REGION AS S_REGION,s.S_PHONE AS S_PHONE,p.P_NAME AS P_NAME,p.P_MFGR AS P_MFGR,p.P_CATEGORY AS P_CATEGORY,p.P_BRAND AS P_BRAND,p.P_COLOR AS P_COLOR,p.P_TYPE AS P_TYPE,p.P_SIZE AS P_SIZE,p.P_CONTAINER AS P_CONTAINER FROM lineorder AS l INNER JOIN customer AS c ON c.C_CUSTKEY &＃61; l.LO_CUSTKEY INNER JOIN supplier AS s ON s.S_SUPPKEY &＃61; l.LO_SUPPKEY INNER JOIN part AS p ON p.P_PARTKEY &＃61; l.LO_PARTKEY;

查询:

Q1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYear(LO_ORDERDATE) &＃61; 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;

Q1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYYYYMM(LO_ORDERDATE) &＃61; 199401 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 toISOWeek(LO_ORDERDATE) &＃61; 6 AND toYear(LO_ORDERDATE) &＃61; 1994AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1

SELECTsum(LO_REVENUE),toYear(LO_ORDERDATE) AS year,P_BRAND FROM lineorder_flat WHERE P_CATEGORY &＃61; &＃39;MFGR#12&＃39; AND S_REGION &＃61; &＃39;AMERICA&＃39; GROUP BYyear,P_BRAND ORDER BYyear,P_BRAND;

Q2.2

SELECTsum(LO_REVENUE),toYear(LO_ORDERDATE) AS year,P_BRAND FROM lineorder_flat WHERE P_BRAND >&＃61; &＃39;MFGR#2221&＃39; AND P_BRAND <&＃61; &＃39;MFGR#2228&＃39; AND S_REGION &＃61; &＃39;ASIA&＃39; GROUP BYyear,P_BRAND ORDER BYyear,P_BRAND;

Q2.3

SELECTsum(LO_REVENUE),toYear(LO_ORDERDATE) AS year,P_BRAND FROM lineorder_flat WHERE P_BRAND &＃61; &＃39;MFGR#2239&＃39; AND S_REGION &＃61; &＃39;EUROPE&＃39; GROUP BYyear,P_BRAND ORDER BYyear,P_BRAND;

Q3.1

SELECTC_NATION,S_NATION,toYear(LO_ORDERDATE) AS year,sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_REGION &＃61; &＃39;ASIA&＃39; AND S_REGION &＃61; &＃39;ASIA&＃39; AND year >&＃61; 1992 AND year <&＃61; 1997 GROUP BYC_NATION,S_NATION,year ORDER BYyear ASC,revenue DESC;

Q3.2

SELECTC_CITY,S_CITY,toYear(LO_ORDERDATE) AS year,sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_NATION &＃61; &＃39;UNITED STATES&＃39; AND S_NATION &＃61; &＃39;UNITED STATES&＃39; AND year >&＃61; 1992 AND year <&＃61; 1997 GROUP BYC_CITY,S_CITY,year ORDER BYyear ASC,revenue DESC;

Q3.3

SELECTC_CITY,S_CITY,toYear(LO_ORDERDATE) AS year,sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY &＃61; &＃39;UNITED KI1&＃39; OR C_CITY &＃61; &＃39;UNITED KI5&＃39;) AND (S_CITY &＃61; &＃39;UNITED KI1&＃39; OR S_CITY &＃61; &＃39;UNITED KI5&＃39;) AND year >&＃61; 1992 AND year <&＃61; 1997 GROUP BYC_CITY,S_CITY,year ORDER BYyear ASC,revenue DESC;

Q3.4

SELECTC_CITY,S_CITY,toYear(LO_ORDERDATE) AS year,sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY &＃61; &＃39;UNITED KI1&＃39; OR C_CITY &＃61; &＃39;UNITED KI5&＃39;) AND (S_CITY &＃61; &＃39;UNITED KI1&＃39; OR S_CITY &＃61; &＃39;UNITED KI5&＃39;) AND toYYYYMM(LO_ORDERDATE) &＃61; 199712 GROUP BYC_CITY,S_CITY,year ORDER BYyear ASC,revenue DESC;

Q4.1

SELECTtoYear(LO_ORDERDATE) AS year,C_NATION,sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE C_REGION &＃61; &＃39;AMERICA&＃39; AND S_REGION &＃61; &＃39;AMERICA&＃39; AND (P_MFGR &＃61; &＃39;MFGR#1&＃39; OR P_MFGR &＃61; &＃39;MFGR#2&＃39;) GROUP BYyear,C_NATION ORDER BYyear ASC,C_NATION ASC;

Q4.2

SELECTtoYear(LO_ORDERDATE) AS year,S_NATION,P_CATEGORY,sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE C_REGION &＃61; &＃39;AMERICA&＃39; AND S_REGION &＃61; &＃39;AMERICA&＃39; AND (year &＃61; 1997 OR year &＃61; 1998) AND (P_MFGR &＃61; &＃39;MFGR#1&＃39; OR P_MFGR &＃61; &＃39;MFGR#2&＃39;) GROUP BYyear,S_NATION,P_CATEGORY ORDER BYyear ASC,S_NATION ASC,P_CATEGORY ASC;

Q4.3

SELECTtoYear(LO_ORDERDATE) AS year,S_CITY,P_BRAND,sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE S_NATION &＃61; &＃39;UNITED STATES&＃39; AND (year &＃61; 1997 OR year &＃61; 1998) AND P_CATEGORY &＃61; &＃39;MFGR#14&＃39; GROUP BYyear,S_CITY,P_BRAND ORDER BYyear ASC,S_CITY ASC,P_BRAND ASC;

查询结果

单表查询结果

img

多表查询结果

如果你有更多问题欢迎加入我们的群聊

点个在看你最好看

推荐阅读

buffer
兆芯X86 CPU架构的演进与现状（国产CPU系列）

本文详细介绍了兆芯X86 CPU架构的发展历程，从公司成立背景到关键技术授权，再到具体芯片架构的演进，全面解析了兆芯在国产CPU领域的贡献与挑战。 ... [详细]

蜡笔小新 2024-11-14 15:04:34
timezone
C#中数值结果的格式化展示方法与技巧

在C#编程中，数值结果的格式化展示是提高代码可读性和用户体验的重要手段。本文探讨了多种格式化方法和技巧，如使用格式说明符、自定义格式字符串等，以实现对数值结果的精确控制。通过实例演示，展示了如何灵活运用这些技术来满足不同的展示需求。 ... [详细]

蜡笔小新 2024-11-11 09:27:57
get
Leetcode学习成长记：天池leetcode基础训练营Task01数组

前言这是本人第一次参加由Datawhale举办的组队学习活动，这个活动每月一次，之前也一直关注，但未亲身参与过，这次看到活动 ... [详细]

蜡笔小新 2024-11-14 18:01:31
stream
MySQL初级篇——字符串、日期时间、流程控制函数的相关应用

文章目录：1.字符串函数2.日期时间函数2.1获取日期时间2.2日期与时间戳的转换2.3获取年月日、时分秒、星期数、天数等函数2.4时间和秒钟的转换2. ... [详细]

蜡笔小新 2024-11-14 10:57:02
io
三角测量计算三维坐标的代码_双目三维重建——层次化重建思考

双目三维重建——层次化重建思考FesianXu2020.7.22atANTFINANCIALintern前言本文是笔者阅读[1]第10章内容的笔记，本文从宏观的角度阐 ... [详细]

蜡笔小新 2024-11-13 19:31:37
stream
SoundPool

如果应用程序经常播放密集、急促而又短暂的音效（如游戏音效）那么使用MediaPlayer显得有些不太适合了。因为MediaPlayer存在如下缺点：1)延时时间较长，且资源占用率高 ... [详细]

蜡笔小新 2024-11-13 16:47:19
process
在范围[0..n-1]中产生m个不同的随机数 - Generating m distinct random numbers in the range [0..n-1]

Ihavetwomethodsofgeneratingmdistinctrandomnumbersintherange[0..n-1]我有两种方法在范围[0.n-1]中生 ... [详细]

蜡笔小新 2024-11-13 09:49:14
main
深入解析C语言中结构体的内存对齐机制及其优化方法

为了提高CPU访问效率，C语言中的结构体成员在内存中遵循特定的对齐规则。本文详细解析了这些对齐机制，并探讨了如何通过合理的布局和编译器选项来优化结构体的内存使用，从而提升程序性能。 ... [详细]

蜡笔小新 2024-11-11 11:53:59
io
机器学习的持续探索与进展

在机器学习领域，深入探讨了概率论与数理统计的基础知识，特别是这些理论在数据挖掘中的应用。文章重点分析了偏差（Bias）与方差（Variance）之间的平衡问题，强调了方差反映了不同训练模型之间的差异，例如在K折交叉验证中，不同模型之间的性能差异显著。此外，还讨论了如何通过优化模型选择和参数调整来有效控制这一平衡，以提高模型的泛化能力。 ... [详细]

蜡笔小新 2024-11-11 10:27:39
io
CentOS 6.7系统维护：查看补丁及常用巡检命令汇总

在 CentOS 6.7 系统维护中，常用的巡检命令包括：`uname -a` 用于查看内核、操作系统和 CPU 信息；`head -n 1 /etc/issue` 用于查看操作系统的版本；`cat /proc/cpuinfo` 用于获取详细的 CPU 信息；`hostname` 用于显示当前主机名；`ls` 命令则用于列出目录内容。这些命令可以帮助系统管理员快速了解系统的运行状态和配置信息，确保系统的稳定性和安全性。 ... [详细]

蜡笔小新 2024-11-10 11:31:40
get
线程能否先以安全方式获取对象，再进行非安全发布？

线程能否先以安全方式获取对象，再进行非安全发布？ ... [详细]

蜡笔小新 2024-11-09 09:21:53
io
在Linux系统上编译安装MySQL 5.5源码详细指南

本文详细介绍了在Linux系统上编译安装MySQL 5.5源码的步骤。首先，通过Yum安装必要的依赖软件包，如GCC、GCC-C++等，确保编译环境的完备。接着，下载并解压MySQL 5.5的源码包，配置编译选项，进行编译和安装。最后，完成安装后，进行基本的配置和启动测试，确保MySQL服务正常运行。 ... [详细]

蜡笔小新 2024-11-08 19:06:26
get
精选Linux经典著作在数字图书馆展出

数字图书馆近期展出了一批精选的Linux经典著作，这些书籍虽然部分较为陈旧，但依然具有重要的参考价值。如需转载相关内容，请务必注明来源：小文论坛（http://www.xiaowenbbs.com）。 ... [详细]

蜡笔小新 2024-11-08 10:55:29
scala
Scala学习指南：从零开始掌握基础

本指南从零开始介绍Scala编程语言的基础知识，重点讲解了Scala解释器REPL（读取-求值-打印-循环）的使用方法。REPL是Scala开发中的重要工具，能够帮助初学者快速理解和实践Scala的基本语法和特性。通过详细的示例和练习，读者将能够熟练掌握Scala的基础概念和编程技巧。 ... [详细]

蜡笔小新 2024-11-07 18:07:59
get
依然最钟爱《People Have the Power》，强烈推荐大家聆听这首经典之作

尽管今日情绪低落，我在音乐库中反复筛选，最终还是选择了《People Have the Power》来激励自己。这首歌不仅旋律动听，歌词也充满力量，能够带给人正能量。强烈建议大家找来聆听，体验其独特的魅力。《People Have the Power》虽然不是出自专辑《Horses》，但同样是一首不可多得的经典之作。 ... [详细]

蜡笔小新 2024-11-10 13:57:50

fanersai_668

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章