Estos contenidos se han traducido de forma automática para su comodidad, pero Huawei Cloud no garantiza la exactitud de estos. Para consultar los contenidos originales, acceda a la versión en inglés.
Centro de ayuda/ GaussDB(DWS)/ Pasos iniciales/ Paso 4: Importar datos de muestra y realizar consultas/ Análisis del estado de operaciones de una tienda departamental minorista
Actualización más reciente 2023-11-30 GMT+08:00
Ver PDF
Compartir

Análisis del estado de operaciones de una tienda departamental minorista

Fondo

En esta práctica, los datos empresariales diarios de cada tienda minorista se cargan desde OBS a la tabla correspondiente en el clúster de almacén de datos para resumir y consultar KPI. Estos datos incluyen la rotación de la tienda, el flujo de clientes, la clasificación de ventas mensuales, la tasa de conversión mensual del flujo de clientes, la relación precio-alquiler mensual y las ventas por unidad de área. Este ejemplo muestra la consulta y el análisis multidimensional de GaussDB(DWS) en el escenario minorista.

Los datos de muestra se han cargado a la carpeta de traffic-data en un bucket de OBS, y a todas las cuentas de Huawei Cloud se les ha concedido el permiso de solo lectura para acceder al depósito OBS.

Esta práctica dura unos 60 minutos. El proceso básico es el siguiente:

  1. Preparativos
  2. Paso 1: Importación de datos de muestra desde la tienda departamental minorista
  3. Paso 2: Realización del análisis del estado de las operaciones

Preparativos

Paso 1: Importación de datos de muestra desde la tienda departamental minorista

Después de conectarse al clúster mediante la herramienta cliente SQL, realice las siguientes operaciones en la herramienta cliente SQL para importar los datos de ejemplo de los grandes almacenes minoristas y realizar consultas.

  1. Ejecute la siguiente instrucción para crear la base de datos minorista: 

    1
    		 
    create database retail encoding 'utf8' template template0;

  2. Realice los siguientes pasos para cambiar a la nueva base de datos:

    1. En la ventana Object Browser del cliente de Data Studio, haga clic con el botón derecho en la conexión de base de datos y elija Refresh en el menú contextual. A continuación, se muestra la nueva base de datos.
    2. Haga clic con el botón derecho en el nombre de la nueva base de datos retail y elija Connect to DB a la base de datos en el menú contextual.
    3. Haga clic con el botón derecho en el nombre de la nueva base de datos retail y elija Open Terminal en el menú contextual. Se muestra la ventana de comandos SQL para conectarse a la base de datos especificada. Realice los siguientes pasos en la ventana.

  3. Crear una tabla de base de datos.

    Los datos de muestra consisten en 10 tablas de base de datos cuyas asociaciones se muestran en Figura 1.

    Figura 1 Ejemplos de tablas de datos de grandes almacenes minoristas
    Copie y ejecute las siguientes instrucciones para cambiar a crear una tabla de base de datos de información de tiendas por departamentos. 
      1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
    		 
    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. Cree una tabla externa, que se utiliza para identificar y asociar los datos de origen en OBS.

    • <obs_bucket_name> indica el nombre del bucket OBS. Solo algunas regiones son compatibles. Los clústeres de GaussDB(DWS) no admiten el acceso entre regiones a los datos del bucket de OBS.
    • En esta práctica, se utiliza como ejemplo CN-Hong Kong Escriba dws-demo-ap-southeast-1 y reemplace <Access_Key_Id> y <Secret_Access_Key> por el valor obtenido en Preparación.
    • Si se muestra el mensaje "ERROR: schema "xxx" does not exist Position" al crear una tabla externa, el esquema no existe. Realice el paso anterior para crear un esquema.
      1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
    		 
    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. Copy and execute the following statements to import the foreign table data to the cluster: 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    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;

    Se necesita algún tiempo para importar datos.

  6. Copie y ejecute la siguiente instrucción para crear la vista v_sales_flow_details: 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    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;

Paso 2: Realización del análisis del estado de las operaciones

A continuación se utiliza la consulta estándar de información de venta minorista de grandes almacenes como ejemplo para demostrar cómo realizar una consulta de datos básica en GaussDB(DWS).

Antes de consultar datos, ejecute el comando Analyze para generar estadísticas relacionadas con la tabla de la base de datos. Los datos estadísticos se almacenan en la tabla del sistema PG_STATISTIC y son útiles cuando se ejecuta el planificador, que le proporciona un plan de ejecución de consultas eficiente.

Los siguientes son ejemplos de consulta:

  • Consultar los ingresos totales por ventas de cada tienda

    Copie y ejecute las siguientes instrucciones para consultar el volumen de negocios total de cada tienda:

    1
2
3
4
5
6
7
8
    		 
    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;
  • Consultar los ingresos por ventas y la relación precio-alquiler de cada tienda

    Copie y ejecute la siguiente instrucción para consultar los ingresos por ventas y la relación precio-alquiler de cada tienda:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    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;
  • Analizar los ingresos por ventas de cada provincia

    Copie y ejecute la siguiente declaración para analizar y consultar los ingresos por ventas de todas las provincias:

    1
2
3
4
5
6
7
    		 
    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;
  • Analizar y comparar la relación precio-alquiler y la tasa de conversión del flujo de clientes de cada tienda 
    1
2
3
4
5
6
7
8
9
    		 
    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;
  • Análisis de marcas en la industria minorista 
    1
2
3
4
5
6
7
8
    		 
    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;
  • Consultar información de ventas diaria de cada marca 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    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;