3.4 第 4 步：导入样例数据并查询
3.4.3 零售业百货公司经营状况分析
3.4.3 零售业百货公司经营状况分析
零售业百货公司样例简介
本实践将演示以下场景：从OBS加载各个零售商场每日经营的业务数据到数据仓库对 应的表中，然后对商铺营业额、客流信息、月度销售排行、月度客流转化率、月度租 售比、销售坪效等KPI信息进行汇总和查询。本示例旨在展示在零售业场景中
GaussDB(DWS) 数据仓库的多维度查询分析的能力。
说明
● GaussDB(DWS) 已预先将样例数据上传到OBS桶的“retail-data”文件夹中，并给所有华为 云用户赋予了该OBS桶的只读访问权限。
● GaussDB(DWS) 集群暂不支持跨区域访问OBS桶数据，当前已上传OBS桶数据的区域如支持 区域所示，非以下区域的集群暂不支持该实验。
本实践预计时长60分钟，基本流程如下：
1. 准备工作
2. 步骤一：导入零售业百货公司样例数据
3. 步骤二：经营状况分析
支持区域
表3-8 区域和 OBS 桶名
区域 OBS桶名
华北-北京一 dws-demo
华北-北京二 dws-demo-cn-north-2 华北-北京四 dws-demo-cn-north-4 华北-乌兰察布一 dws-demo-cn-north-9 华东-上海一 dws-demo-cn-east-3 华东-上海二 dws-demo-cn-east-2 华南-广州 dws-demo-cn-south-1 中国-香港 dws-demo-ap-southeast-1 亚太-新加坡 dws-demo-ap-southeast-3 亚太-曼谷 dws-demo-ap-southeast-2 拉美-圣地亚哥 dws-demo-la-south-2
准备工作
● 已注册华为云帐号，帐号不能处于欠费或冻结状态。
● 参见创建访问密钥(AK和SK)获取此帐号的“AK/SK”。
● 已创建集群，并已使用Data Studio连接集群，参见步骤一：创建集群和步骤二：
使用Data Studio连接集群。
步骤一：导入零售业百货公司样例数据
使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入零 售业百货公司样例数据并执行查询。
步骤1 执行以下语句，创建retail数据库。
create database retail encoding 'utf8' template template0;
步骤2 执行以下步骤切换为连接新建的数据库。
1. 在Data Studio客户端的“对象浏览器”窗口，右键单击数据库连接名称，在弹出 菜单中单击“刷新”，刷新后就可以看到新建的数据库。
2. 右键单击“retail”数据库名称，在弹出菜单中单击“打开连接”。
3. 右键单击“retail”数据库名称，在弹出菜单中单击“打开新的终端”，即可打开 连接到指定数据库的SQL命令窗口，后面的步骤，请全部在该命令窗口中执行。
步骤3 创建数据库表。
样例数据包含10张数据库表，其关联关系如图3-12所示。
图3-12 百货公司样例数据表
复制并执行以下语句，创建零售业百货公司信息数据库表。
create schema retail_data;
set current_schema='retail_data';
DROP TABLE IF EXISTS STORE;
CREATE TABLE STORE ( ID INT,
STORECODE VARCHAR(10), STORENAME VARCHAR(100), FIRMID INT,
FLOOR INT, BRANDID INT,
RENTAMOUNT NUMERIC(18,2), RENTAREA NUMERIC(18,2)
) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS POS;
CREATE TABLE POS(
ID INT,
POSCODE VARCHAR(20), STATUS INT,
MODIFICATIONDATE DATE
)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS BRAND;
CREATE TABLE BRAND ( ID INT,
BRANDCODE VARCHAR(10), BRANDNAME VARCHAR(100), SECTORID INT
)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS SECTOR;
CREATE TABLE SECTOR(
ID INT,
SECTORCODE VARCHAR(10), SECTORNAME VARCHAR(20), CATEGORYID INT
)
DROP TABLE IF EXISTS CATEGORY;
CREATE TABLE CATEGORY(
ID INT,
CODE VARCHAR(10), NAME VARCHAR(20)
)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS FIRM;
)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS DATE;
)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS PAYTYPE;
)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
DROP TABLE IF EXISTS SALES;
) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);
DROP TABLE IF EXISTS FLOW;
) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);
步骤4 创建外表。外表用于识别和关联OBS上的源数据。
须知
drop FOREIGN table if exists SALES_OBS;
CREATE FOREIGN TABLE SALES_OBS ( like retail_data.SALES
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/sales', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists FLOW_OBS;
CREATE FOREIGN TABLE FLOW_OBS ( like retail_data.flow
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/flow', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists BRAND_OBS;
CREATE FOREIGN TABLE BRAND_OBS ( like retail_data.brand
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/brand', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
CREATE FOREIGN TABLE CATEGORY_OBS ( like retail_data.category
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/category', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists DATE_OBS;
CREATE FOREIGN TABLE DATE_OBS ( like retail_data.date
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/date', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists FIRM_OBS;
CREATE FOREIGN TABLE FIRM_OBS ( like retail_data.firm
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/firm', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists PAYTYPE_OBS;
CREATE FOREIGN TABLE PAYTYPE_OBS ( like retail_data.paytype
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/paytype', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on',
header 'on' );
drop FOREIGN table if exists POS_OBS;
CREATE FOREIGN TABLE POS_OBS ( like retail_data.pos
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/pos', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists SECTOR_OBS;
CREATE FOREIGN TABLE SECTOR_OBS ( like retail_data.sector
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/sector', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
drop FOREIGN table if exists STORE_OBS;
CREATE FOREIGN TABLE STORE_OBS ( like retail_data.store
)SERVER gsmpp_server OPTIONS (
encoding 'utf8',
location 'obs://<obs_bucket_name>/retail-data/store', format 'csv',
delimiter ',',
access_key '<Access_Key_Id>',
secret_access_key '<Secret_Access_Key>', chunksize '64',
IGNORE_EXTRA_DATA 'on', header 'on'
);
步骤5 复制并执行以下语句，导入外表数据到集群。
insert into retail_data.store select * from retail_obs_data.STORE_OBS;
insert into retail_data.sector select * from retail_obs_data.SECTOR_OBS;
insert into retail_data.paytype select * from retail_obs_data.PAYTYPE_OBS;
insert into retail_data.firm select * from retail_obs_data.FIRM_OBS;
insert into retail_data.flow select * from retail_obs_data.FLOW_OBS;
insert into retail_data.category select * from retail_obs_data.CATEGORY_OBS;
insert into retail_data.date select * from retail_obs_data.DATE_OBS;
insert into retail_data.pos select * from retail_obs_data.POS_OBS;
insert into retail_data.brand select * from retail_obs_data.BRAND_OBS;
insert into retail_data.sales select * from retail_obs_data.SALES_OBS;
导入数据需要一些时间，请耐心等待。
步骤6 复制并执行以下语句，创建视图v_sales_flow_details。
set current_schema='retail_data';
CREATE VIEW v_sales_flow_details AS SELECT
FIRM.ID FIRMID, FIRM.NAME FIRNAME, FIRM. CITYCODE, CATEGORY.ID CATEGORYID, CATEGORY.NAME CATEGORYNAME, SECTOR.ID SECTORID, SECTOR.SECTORNAME,
BRAND.ID BRANDID, BRAND.BRANDNAME,
STORE.ID STOREID, STORE.STORENAME, STORE.RENTAMOUNT, STORE.RENTAREA,
DATE.DATEKEY, SALES.TOTALAMOUNT, DISCOUNTAMOUNT, ITEMCOUNT, PAIDAMOUNT, INFLOWVALUE FROM SALES
INNER JOIN STORE ON SALES.STOREID = STORE.ID INNER JOIN FIRM ON STORE.FIRMID = FIRM.ID INNER JOIN BRAND ON STORE.BRANDID = BRAND.ID INNER JOIN SECTOR ON BRAND.SECTORID = SECTOR.ID
INNER JOIN CATEGORY ON SECTOR.CATEGORYID = CATEGORY.ID INNER JOIN DATE ON SALES.DATEKEY = DATE.ID
INNER JOIN FLOW ON FLOW.DATEKEY = DATE.ID AND FLOW.STOREID = STORE.ID;
----结束 AT TIME ZONE 'UTC' AS __timestamp, SUM(paidamount)
AS sum__paidamount FROM v_sales_flow_details
GROUP BY DATE_TRUNC('month',datekey) AT TIME ZONE 'UTC' ORDER BY SUM(paidamount) DESC;
SUM(paidamount)
DATE_TRUNC('day', datekey) AT TIME ZONE 'UTC' AS __timestamp, SUM(paidamount) AS sum__paidamount
FROM v_sales_flow_details
WHERE datekey >= '2016-01-01 00:00:00' AND datekey <= '2016-01-30 00:00:00' GROUP BY brandname,
DATE_TRUNC('day', datekey) AT TIME ZONE 'UTC' ORDER BY sum__paidamount ASC