以 100GB SSB 性能測試為例，通過 ByteHouse 云數倉開啟你的數據分析之路

作者：ByteHouse 團隊 2023-04-22 09:40:36

ByteHouse 作為云原生的數據平臺，從架構層面入手，通過存儲和計算分離的云原生架構完美適配云上基礎設施。ByteHouse 作為云原生的數據平臺，從架構層面入手，通過存儲和計算分離的云原生架構完美適配云上基礎設施。

I. 傳統數倉的演進：云數倉

近年來，隨著數據“爆炸式”的增長，越來越多的數據被產生、收集和存儲。而挖掘海量數據中的真實價值，從其中提取商機并洞見未來，則成了現代企業和組織不可忽視的命題。

隨著數據量級和復雜度的增大，數據分析處理的技術架構也在不斷演進。在面對海量數據分析時，傳統 OLAP 技術架構中的痛點變得越來越明顯，如擴容縮容耗時長，導致資源利用率偏低，成本居高不下；以及運維配置復雜，需要專業的技術人員介入等。

為了解決這類問題，云數倉的概念應運而生。和傳統數倉架構不同的是，云原生數倉借助于云平臺的基礎資源，實現了資源的動態擴縮容，并最大化利用資源，從而達到 Pay as you go 按實際用量付費的模式。

ByteHouse 作為云原生的數據平臺，從架構層面入手，通過存儲和計算分離的云原生架構完美適配云上基礎設施。在字節跳動內部，ByteHouse 已經支持 80% 的分析應用場景，包括用戶增長業務、廣告、A/B 測試等。除了極致的分析性能之外，ByteHouse 開箱即用，按實際使用付費的特性也極大地降低了企業和個人的上手門檻，能夠在短短數分鐘內體驗到數據分析的魅力。Talk is cheap, 接下來就讓我們通過一個實戰案例來體驗下 ByteHouse 云數倉的強大功能。

II. 快速上手 ByteHouse —— 輕量級云數倉

本章節通過使用 ByteHouse 云數倉進行 SSB 基準測試，在帶領讀者了解產品性能的同時，也一并熟悉產品中各個模塊的功能，開啟你的數據分析之路，通過分析海量數據，加速數據洞察。ByteHouse 的架構總覽如下。

SSB 基準測試

SSB（Star Schema Benchmark）是由麻省州立大學波士頓校區的研究員定義的基于現實商業應用的數據模型。SSB 是在 TPC-H 標準的基礎上改進而成，主要將 TPC-H 中的雪花模型改成了更為通用的的星型模型，將基準查詢從復雜的 Ad-hoc 查詢改成了結構更加固定的 OLAP 查詢，從而主要用于模擬測試 OLAP 引擎和輕量數倉場景下的查詢性能。由于 SSB 基準測試較為中立，并貼近現實的商業場景，因此在學界及工業界有廣泛的應用。

SSB 基準測試中對應的表結構如下所示，可以看到 SSB 主要采用星型模型，其中包含了 1 個事實表 lineorder 和 4 個維度表 customer, part, dwdate 以及 supplier，每張維度表通過 Primary Key 和事實表進行關聯。測試通過執行 13 條 SQL 進行查詢，包含了多表關聯，group by，復雜條件等多種組合。更多詳細信息請參考 SSB 文獻 (??https://www.cs.umb.edu/~poneil/StarSchemaB.pdf??)。

步驟一：官網注冊并開通 ByteHouse

訪問ByteHouse 云數倉火山引擎官網，注冊火山引擎賬戶，完成實名認證后，即可登錄到產品控制臺。開通產品進行測試，目前 ByteHouse 支持包年包月和按量付費兩種模式的實例，便于您根據業務需求進行選擇。

ByteHouse 云數倉火山引擎官網

??https://www.volcengine.com/product/bytehouse-cloud??

產品控制臺

??https://console.volcengine.com/bytehouse??

步驟二：創建計算組

登錄到控制臺后，可以看到數據庫表管理、數據加載、SQL 工作表、計算組、查詢歷史和角色管理等幾大模塊。分別具有如下作用：

數據庫表管理：用于創建和管理數據庫、數據表以及視圖等數據對象數據加載：用于從不同的離線和實時數據源如對象存儲、Kafka 等地寫入數據 SQL 工作表：在界面上編輯、管理并運行 SQL 查詢計算組：創建和管理虛擬的計算資源，用于執行數據查詢等操作查詢歷史：用于查看 SQL 的歷史執行記錄、狀態和查詢詳情等

為了方便進行后續的建庫建表和查詢等操作，首先在 ByteHouse 控制臺創建型號為 L 的計算組，如下圖所示

計算組是 Bytehouse 中的計算資源集群，可按需進行橫向擴展。計算組提供所需的資源如 CPU、內存及臨時存儲等，用于執行數據查詢 DQL、DML 等操作。ByteHouse 計算組能夠實現彈性擴縮容，讀寫分離、存算分離等，并且能對資源進行細粒度的權限控制。

步驟三：創建數據庫表

在控制臺頁面中創建名為 ssb_``100 的數據庫

創建完畢后，進入到 SQL 工作表模塊，通過如下建表語句建立四個數據表（事實表），并保存對應的 SQL 語句。

CREATE TABLE ssb_100.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),
C_PLACEHOLDER   Nullable(String)
)
ENGINE = CnchMergeTree ORDER BY (C_CUSTKEY);
CREATE TABLE ssb_100.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),
LO_PLACEHOLDER          Nullable(String)
)
ENGINE = CnchMergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);
CREATE TABLE ssb_100.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),
P_PLACEHOLDER   Nullable(String)
)
ENGINE = CnchMergeTree ORDER BY P_PARTKEY;
CREATE TABLE ssb_100.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,
S_PLACEHOLDER   Nullable(String)
)
ENGINE = CnchMergeTree ORDER BY S_SUPPKEY;
CREATE TABLE ssb_100.dwdate
(
D_DATEKEY            UInt32,
D_DATE               String,
D_DAYOFWEEK          String,    -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
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,
S_PLACEHOLDER        Nullable(String)
)
ENGINE=CnchMergeTree() ORDER BY (D_DATEKEY);

SQL 執行完畢后，在控制臺左側對應的數據對象頁面會展示出創建完成的五個工作表，分別為 customer,dwdate,lineorder以及part 和 supplier

步驟四：從對象存儲中導入 SSB 數據

通過預先生成 SSB_100 GB 的數據集并存儲在對象存儲（如 AWS S3 或者火山引擎 TOS），我們可以方便且快速的將數據導入到 ByteHouse 中進行分析。本次實踐中通過配置火山引擎 TOS 的數據源對數據進行導入。

首先在數據加載模塊，新建對象存儲數據源，并配置對應的秘鑰連接火山引擎對象存儲

連接新的數據源后，選擇 bytehouse-shared-dataset 的儲存桶和ssb_100/lineorder.csv 相應的路徑

選擇之前建的數據庫ssb_100和對應標表lineorder，然后按創建。重復步驟為其他四個工作表數據加載。

數據源中存儲的數據條數如下所示。用于導入完成后，對數據表的行數進行統計，進行準確性校驗。

創建導入任務完成后，點擊“開始”啟動導入任務，任務啟動后會在幾秒鐘內分配資源并初始化導入任務，并在導入過程中展示預估的時間和導入進度。在導入任務的執行詳情中，可以查看導入狀態、導入詳細日志、配置信息等。

步驟五：數據處理及分析

1、原始查詢測試

通過執行 SSB 的 13 條查詢語句，對于多表關聯和排序等場景進行性能測試。查詢語句如下所示：

-- pre-warm
select * from ssb_100.customer order by C_CUSTKEY desc limit 100;
select * from ssb_100.dwdate order by D_DATEKEY desc limit 100;
select * from ssb_100.lineorder order by LO_ORDERKEY desc limit 100;
select * from ssb_100.part order by P_PARTKEY desc limit 100;
select * from ssb_100.supplier order by S_SUPPKEY desc limit 100;
select * from ssb_100.lineorder_flat order by LO_ORDERKEY desc limit 100;

-- Q1.1
select sum(LO_EXTENDEDPRICE*LO_DISCOUNT) as revenue
from ssb_100.lineorder
where toYear(LO_ORDERDATE) = 1993
and LO_DISCOUNT between 1 and 3
and LO_QUANTITY < 25;

-- Q1.2
select sum(LO_EXTENDEDPRICE*LO_DISCOUNT) as revenue
from ssb_100.lineorder
where toYYYYMM(LO_ORDERDATE) = 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 ssb_100.lineorder
where toISOWeek(LO_ORDERDATE) = 6
and toYear(LO_ORDERDATE)= 1994
and LO_DISCOUNT between 5 and 7
and LO_QUANTITY between 26 and 35;

-- Q2.1
select sum(LO_REVENUE), toYear(LO_ORDERDATE) AS d_year, P_BRAND
from ssb_100.lineorder, ssb_100.part, ssb_100.supplier
where LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
and P_CATEGORY = 'MFGR#53' and S_REGION = 'AMERICA'
GROUP BY d_year, P_BRAND;

-- Q2.2
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
and 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), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY 
and 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, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
GROUP BY C_NATION, S_NATION, year
ORDER BY year ASC, revenue DESC;

-- Q3.2 
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC;

-- Q3.3
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC;

-- Q3.4
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC;

-- Q4.1
SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY year, C_NATION
ORDER BY year ASC, C_NATION ASC;

-- Q4.2
SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
and C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY year, S_NATION, P_CATEGORY
ORDER BY year ASC, S_NATION ASC, P_CATEGORY ASC;
    
-- Q4.3
SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
and S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
GROUP BY year, S_CITY, P_BRAND
ORDER BY year ASC, S_CITY ASC, P_BRAND ASC;

2.打平表測試

為了方便對 SSB 數據集進行測試，我們可以通過改寫 SSB，將星型模型打平轉換為大寬表進行分析

注：為了確保打平表的執行，需要配置參數 SET max_memory_usage = 20000000000; 此外需要在 ByteHouse 控制臺中配置查詢超時為 3600s (我的賬戶 > 查詢配置 > 查詢超時)，避免執行超時導致的失敗。

SET max_memory_usage = 20000000000;
SET send_timeout = 3600;
SET receive_timeout = 3600;
CREATE TABLE IF NOT EXISTS ssb_100.lineorder_flat
engine = CnchMergeTree
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 ssb_100.lineorder as L
inner join ssb_100.customer as C on C.C_CUSTKEY = L.LO_CUSTKEY
inner join ssb_100.supplier as S on S.S_SUPPKEY = L.LO_SUPPKEY
inner join ssb_100.part as P on P.P_PARTKEY = L.LO_PARTKEY;

建表完成后，通過執行查詢語句進行 SSB 性能測試，如下所示：

-- F1.1
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb_100.lineorder_flat
WHERE toYear(LO_ORDERDATE) = 1993
AND LO_DISCOUNT BETWEEN 1 AND 3
AND LO_QUANTITY < 25;
-- F1.2
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb_100.lineorder_flat
WHERE toYYYYMM(LO_ORDERDATE) = 199401
AND LO_DISCOUNT BETWEEN 4 AND 6
AND LO_QUANTITY BETWEEN 26 AND 35;
-- F1.3
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM ssb_100.lineorder_flat
WHERE toISOWeek(LO_ORDERDATE) = 6
AND toYear(LO_ORDERDATE) = 1994
AND LO_DISCOUNT BETWEEN 5 AND 7
AND LO_QUANTITY BETWEEN 26 AND 35;
-- F2.1
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder_flat
WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY year, P_BRAND
ORDER BY year, P_BRAND;
-- F2.2
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.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;
-- F2.3
SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
FROM ssb_100.lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY year, P_BRAND
ORDER BY year, P_BRAND;
-- F3.1
SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
GROUP BY C_NATION, S_NATION, year
ORDER BY year ASC, revenue DESC;
-- F3.2
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC;
-- F3.3
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC;
-- F3.4
SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
FROM ssb_100.lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
GROUP BY C_CITY, S_CITY, year
ORDER BY year ASC, revenue DESC;
-- F4.1
SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY year, C_NATION
ORDER BY year ASC, C_NATION ASC;
-- F4.2
SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY year, S_NATION, P_CATEGORY
ORDER BY year ASC, S_NATION ASC, P_CATEGORY ASC;
-- F4.3
SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM ssb_100.lineorder_flat
WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
GROUP BY year, S_CITY, P_BRAND
ORDER BY year ASC, S_CITY ASC, P_BRAND ASC;