# 使用DWS分析零售业百货公司经营状况
#### 零售业百货公司样例简介
本实践将演示以下场景：从OBS加载各个零售商场每日经营的业务数据到数据仓库对应的表中，然后对商铺营业额、客流信息、月度销售排行、月度客流转化率、月度租售比、销售坪效等KPI信息进行汇总和查询。本示例旨在展示在零售业场景中DWS 数据仓库的多维度查询分析的能力。
![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png)
DWS 已预先将样例数据上传到OBS桶的"retail-data"文件夹中，并给所有华为云用户赋予了该OBS桶的只读访问权限。
#### 操作流程
本实践预计时长60分钟，基本流程如下：
1. [准备工作]
2. [步骤一：导入零售业百货公司样例数据]
3. [步骤二：经营状况分析]
 
 #### 支持区域
当前已上传OBS数据的区域如[表1]所示。
 表1区域和OBS桶名 
| 区域       | OBS桶名                   |
|:---|:---|
| 华北-北京一   | dws-demo-cn-north-1     |
| 华北-北京二   | 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-cn-south-4     |
| 中国-香港    | dws-demo-ap-southeast-1 |
| 亚太-新加坡   | dws-demo-ap-southeast-3 |
| 亚太-曼谷    | dws-demo-ap-southeast-2 |
| 拉美-圣地亚哥  | dws-demo-la-south-2     |
| 非洲-约翰内斯堡 | dws-demo-af-south-1     |
| 拉美-墨西哥城一 | dws-demo-na-mexico-1    |
| 拉美-墨西哥城二 | dws-demo-la-north-2     |
| 莫斯科二     | dws-demo-ru-northwest-2 |
| 拉美-圣保罗一  | dws-demo-sa-brazil-1    |
   
 #### 准备工作
- 已注册账号，账号不能处于欠费或冻结状态。
- 参见[新增访问密钥](https://support.huaweicloud.com/usermanual-ca/ca_01_0003.html#section1)获取此账号的"AK/SK"。
- 参见[创建集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0019.html)完成集群创建。
 
 #### 步骤一：导入零售业百货公司样例数据
使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入零售业百货公司样例数据并执行查询。
1. 如果是业务调测场景，推荐通过**SQL编辑器** 进行连接，参见[使用SQL编辑器连接集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0803.html#section1)完成连接。
   
   也可以通过其他方式连接（例如命令行gsql工具），参见[连接DWS集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0131.html)。
   
   
2. 执行以下语句，创建retail数据库。 
   ```
   CREATE DATABASE retail encoding 'utf8' template template0;
   ```
   
   
3. 切换到新创建的数据库retail，创建数据库表。 
   样例数据包含10张数据库表，其关联关系如[图1]所示。
   图1百货公司样例数据表   
   ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000001350301941.png "点击放大")
   复制并执行以下语句，创建零售业百货公司信息数据库表。
   ```
   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
   )
   WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
   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;
   CREATE TABLE FIRM(
           ID INT, 
           CODE VARCHAR(4), 
           NAME VARCHAR(40), 
           CITYID INT, 
           CITYNAME VARCHAR(10),
           CITYCODE VARCHAR(20)
   )
   WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
   DROP TABLE IF EXISTS DATE;
   CREATE TABLE DATE(
           ID INT, 
           DATEKEY DATE, 
           YEAR INT, 
           MONTH INT, 
           DAY INT, 
           WEEK INT, 
           WEEKDAY INT
   )
   WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
   DROP TABLE IF EXISTS PAYTYPE;
   CREATE TABLE PAYTYPE(
           ID INT, 
           CODE VARCHAR(10), 
           TYPE VARCHAR(10), 
           SIGNDATE DATE
   )
   WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
   DROP TABLE IF EXISTS SALES;
   CREATE TABLE SALES(
            ID INT, 
            POSID INT, 
            STOREID INT, 
            DATEKEY INT, 
            PAYTYPE INT, 
            TOTALAMOUNT NUMERIC(18,2),
            DISCOUNTAMOUNT NUMERIC(18,2), 
            ITEMCOUNT INT, 
            PAIDAMOUNT NUMERIC(18,2)
   ) 
   WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);
   DROP TABLE IF EXISTS FLOW;
   CREATE TABLE FLOW (
            ID INT, 
            STOREID INT, 
            DATEKEY INT, 
            INFLOWVALUE INT
   ) 
   WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);
   ```
   
   
4. 创建外表。外表用于识别和关联OBS上的源数据。 
   ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png)
   - *\<obs_bucket_name\>* 表示OBS桶名，当前系统已预置了OBS桶和样例数据，用户无需创建，请替换为DWS所在的实际区域对应的桶名，参见[支持区域]，本实践以"华北-北京四"地区为例，请替换为dws-demo-cn-north-4。不支持跨区域访问OBS桶数据，例如集群在"华北-北京四"，不能将\<obs_bucket_name\>替换成其他区域所对应的桶名。
   
   - \<Access_Key_Id\>和\<Secret_Access_Key\>替换为实际值，在[准备工作]获取。
   
   - 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。
   
   - 创建外表如果提示"ERROR: schema "*xxx*" does not exist Position"，则说明schema不存在，请先参照上一步创建schema。
    
   ```
   CREATE SCHEMA retail_obs_data;
   SET current_schema='retail_obs_data';
   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'
   );
   DROP FOREIGN table if exists CATEGORY_OBS;
   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;
   ```
   
   
 
 #### 步骤二：经营状况分析
以下以零售百货公司标准查询为例，演示在DWS 中进行的基本数据查询。
在进行数据查询之前，请先执行"Analyze"命令生成与数据库表相关的统计信息。统计信息存储在系统表PG_STATISTIC中，执行计划生成器会使用这些统计数据，以生成最有效的查询执行计划。
查询示例如下：
- **查询各商铺的月度营业** **额**
  复制并执行以下语句查询各商铺的月度营业额。
  ```
  SET current_schema='retail_data';
  SELECT DATE_TRUNC('month',datekey) 
  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;
  ```
  

- **查询各门店营收及租售比状况**
  复制并执行以下语句进行营收及租售比状况查询。
  ```
  SET current_schema='retail_data';
  SELECT firname AS firname,
  storename AS storename,
  SUM(paidamount)
  AS sum__paidamount,
  AVG(RENTAMOUNT)/SUM(PAIDAMOUNT)
  AS rentamount_sales_rate
  FROM v_sales_flow_details
  GROUP BY firname, storename
  ORDER BY SUM(paidamount) DESC;
  ```
  
- **各城市营业汇总分析**
  复制并执行以下语句进行汇总分析查询。
  ```
  SET current_schema='retail_data';
  SELECT citycode AS citycode,
  SUM(paidamount)
  AS sum__paidamount
  FROM v_sales_flow_details
  GROUP BY citycode
  ORDER BY SUM(paidamount) DESC;
  ```
  
- **各门店租售比和客流转化率对比分析**
  ```
  SET current_schema='retail_data';
  SELECT brandname AS brandname,
  firname AS firname,
  SUM(PAIDAMOUNT)/AVG(RENTAREA) AS sales_rentarea_rate,
  SUM(ITEMCOUNT)/SUM(INFLOWVALUE) AS poscount_flow_rate,
  AVG(RENTAMOUNT)/SUM(PAIDAMOUNT) AS rentamount_sales_rate
  FROM v_sales_flow_details
  GROUP BY brandname,  firname
  ORDER BY sales_rentarea_rate DESC;
  ```
  
- **品牌业态分析**
  ```
  SET current_schema='retail_data';
  SELECT categoryname AS categoryname,
  brandname AS brandname,
  SUM(paidamount) AS sum__paidamount
  FROM v_sales_flow_details
  GROUP BY categoryname,
  brandname
  ORDER BY sum__paidamount DESC;
  ```
  
- **查询各品牌每日营业状况**
  ```
  SET current_schema='retail_data';
  SELECT brandname AS brandname,
  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
  LIMIT 50000;
  ```