Data Warehouse Service GaussDB (DWS) -1

vim /home/user/Desktop/product_info0.csv 100,XHDK-A,2017-09-01,A,2017 Shirt Women,red,M,328,2017-09-04,715,good!
205,KDKE-B,2017-09-01,A,2017 T-shirt Women,pink,L,584,2017-09-05,40,very good!
300,JODL-X,2017-09-01,A,2017 T-shirt men,red,XL,15,2017-09-03,502,Bad.
310,QQPX-R,2017-09-02,B,2017 jacket women,red,L,411,2017-09-05,436,It's nice.
150,ABEF-C,2017-09-03,B,2017 Jeans Women,blue,M,123,2017-09-06,120,good. 

200,BCQP-E,2017-09-04,B,2017 casual pants men,black,L,997,2017-09-10,301,good quality.
250,EABE-D,2017-09-10,A,2017 dress women,black,S,841,2017-09-15,299,This dress fits well.
108,CDXK-F,2017-09-11,A,2017 dress women,red,M,85,2017-09-14,22,It's really amazing to buy.
450,MMCE-H,2017-09-11,A,2017 jacket women,white,M,114,2017-09-14,22,very good.
260,OCDA-G,2017-09-12,B,2017 woolen coat women,red,L,2004,2017-09-15,826,Very comfortable. 980,"ZKDS-J",2017-09-13,"B","2017 Women's Cotton Clothing","red","M",112,,,
98,"FKQB-I",2017-09-15,"B","2017 new shoes men","red","M",4345,2017-09-18,5473
50,"DMQY-K",2017-09-21,"A","2017 pants men","red","37",28,2017-09-25,58,"good","good","good"
80,"GKLW-l",2017-09-22,"A","2017 Jeans Men","red","39",58,2017-09-25,72,"Very comfortable."
30,"HWEC-L",2017-09-23,"A","2017 shoes women","red","M",403,2017-09-26,607,"good!"
40,"IQPD-M",2017-09-24,"B","2017 new pants Women","red","M",35,2017-09-27,52,"very good."
50,"LPEC-N",2017-09-25,"B","2017 dress Women","red","M",29,2017-09-28,47,"not good at all."
60,"NQAB-O",2017-09-26,"B","2017 jacket women","red","S",69,2017-09-29,70,"It's beautiful."
70,"HWNB-P",2017-09-27,"B","2017 jacket women","red","L",30,2017-09-30,55,"I like it so much"
80,"JKHU-Q",2017-09-29,"C","2017 T-shirt","red","M",90,2017-10-02,82,"very good." cd /home/user/Desktop/ ll

cat /home/user/Downloads/credentials.csv|grep -w user

CREATE FOREIGN TABLE product_info_ext
(
product_price integer not null,
product_id char(30) not null,
product_time date,
product_level char(10),
product_name varchar(200),
product_type1 varchar(20),
product_type2 char(10),
product_monthly_sales_cnt integer,
product_comment_time date,
product_comment_num integer,
product_comment_content varchar(200) ) SERVER gsmpp_server OPTIONS(
LOCATION'obs://OBS_bucket_name/input_data/',
FORMAT 'CSV' ,
DELIMITER ',',
ENCODING 'utf8',
HEADER 'false',
ACCESS_KEY'AK',
SECRET_ACCESS_KEY'SK',
FILL_MISSING_FIELDS 'true',
IGNORE_EXTRA_DATA 'true'
)
READ ONLY LOG INTO product_info_err PER NODE REJECT LIMIT 'unlimited';

CREATE TABLE product_info
(
product_price integer not null,
product_id char(30) not null,
product_time date ,
product_level char(10) ,
product_name varchar(200) ,
product_type1 varchar(20) ,
product_type2 char(10) ,
product_monthly_sales_cnt integer ,
product_comment_time date ,
product_comment_num integer ,
product_comment_content varchar(200) ) WITH (
orientation = column,
compression=middle
) DISTRIBUTE BY hash (product_id);

INSERT INTO product_info SELECT * FROM product_info_ext;

VACUUM FULL product_info;
ANALYZE product_info;

SELECT * FROM product_info;

INSERT INTO product_info VALUES (200,'ARCP-E','2016-05-04','B','2016 casual pants men','black','L',997,'2016-09-10',301,'good quality.'); INSERT INTO product_info VALUES (500,'JYXL-E','2017-06-04','B','2017 casual pants men','black','M',990,'2016-11-10',302,'good quality.'); UPDATE product_info SET product_price = 185 WHERE product_id = 'KDKE-B'; DELETE product_info WHERE product_id ='KDKE-B'; SELECT COUNT(*) FROM product_info;

cat /home/user/Downloads/credentials.csv

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; CREATE TABLE POS( ID INT, POSCODE VARCHAR(20), STATUS INT, MODIFICATIONDATE DATE
)
WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION; CREATE TABLE BRAND ( ID INT, BRANDCODE VARCHAR(10), BRANDNAME VARCHAR(100), SECTORID INT
)
WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION; CREATE TABLE SECTOR( ID INT, SECTORCODE VARCHAR(10), SECTORNAME VARCHAR(20), CATEGORYID INT
)
WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION; CREATE TABLE CATEGORY( ID INT, CODE VARCHAR(10), NAME VARCHAR(20)
)
WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION; 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; 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; CREATE TABLE PAYTYPE( ID INT, CODE VARCHAR(10), TYPE VARCHAR(10), SIGNDATE DATE
)
WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION; 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); CREATE TABLE FLOW ( ID INT, STOREID INT, DATEKEY INT, INFLOWVALUE INT
) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID); SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'retail_data'; 

create schema retail_obs_data;

CREATE FOREIGN TABLE SALES_OBS
( like retail_data.SALES
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/sales', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE FLOW_OBS
( like retail_data.flow
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/flow', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE BRAND_OBS
( like retail_data.brand
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/brand', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', 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://dws-demo-ap-southeast-3/retail-data/category', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE DATE_OBS
( like retail_data.date
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/date', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE FIRM_OBS
( like retail_data.firm
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/firm', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE PAYTYPE_OBS
( like retail_data.paytype
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/paytype', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE POS_OBS
( like retail_data.pos
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/pos', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE SECTOR_OBS
( like retail_data.sector
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/sector', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); CREATE FOREIGN TABLE STORE_OBS
( like retail_data.store
)
SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-ap-southeast-3/retail-data/store', format 'csv', delimiter ',', access_key '<AK>', secret_access_key '<SK>', chunksize '64', IGNORE_EXTRA_DATA 'on', header 'on'
); 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; DROP FOREIGN TABLE {Foreign_table_name};

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;

CREATE TABLE task1 AS 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;

CREATE TABLE task2 AS 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;

CREATE TABLE task3 AS SELECT citycode AS citycode,
SUM(paidamount)
AS sum__paidamount
FROM v_sales_flow_details
GROUP BY citycode
ORDER BY SUM(paidamount) DESC;

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;

CREATE TABLE task4 AS 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;

CREATE TABLE task5 AS 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;