{"id":72732,"date":"2024-08-09T00:44:42","date_gmt":"2024-08-08T21:14:42","guid":{"rendered":"https:\/\/nabfollower.com\/blog\/data-warehouse-service-gaussdb-dws-16fa\/"},"modified":"2024-08-09T00:44:42","modified_gmt":"2024-08-08T21:14:42","slug":"data-warehouse-service-gaussdb-dws-16fa","status":"publish","type":"post","link":"https:\/\/nabfollower.com\/blog\/data-warehouse-service-gaussdb-dws-16fa\/","title":{"rendered":"\u0633\u0631\u0648\u06cc\u0633 \u0627\u0646\u0628\u0627\u0631 \u062f\u0627\u062f\u0647 GaussDB (DWS) -1"},"content":{"rendered":"

Summarize this content to 400 words in Persian Lang
\n *\u0633\u0631\u0648\u06cc\u0633 \u0627\u0646\u0628\u0627\u0631 \u062f\u0627\u062f\u0647*<\/p>\n

*\u0627\u06cc\u062c\u0627\u062f \u06cc\u06a9 \u0633\u0637\u0644 OBS*<\/p>\n

vim \/home\/user\/Desktop\/product_info0.csv<\/p>\n

100,XHDK-A,2017-09-01,A,2017 Shirt Women,red,M,328,2017-09-04,715,good!
\n205,KDKE-B,2017-09-01,A,2017 T-shirt Women,pink,L,584,2017-09-05,40,very good!
\n300,JODL-X,2017-09-01,A,2017 T-shirt men,red,XL,15,2017-09-03,502,Bad.
\n310,QQPX-R,2017-09-02,B,2017 jacket women,red,L,411,2017-09-05,436,It’s nice.
\n150,ABEF-C,2017-09-03,B,2017 Jeans Women,blue,M,123,2017-09-06,120,good.<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

200,BCQP-E,2017-09-04,B,2017 casual pants men,black,L,997,2017-09-10,301,good quality.
\n250,EABE-D,2017-09-10,A,2017 dress women,black,S,841,2017-09-15,299,This dress fits well.
\n108,CDXK-F,2017-09-11,A,2017 dress women,red,M,85,2017-09-14,22,It’s really amazing to buy.
\n450,MMCE-H,2017-09-11,A,2017 jacket women,white,M,114,2017-09-14,22,very good.
\n260,OCDA-G,2017-09-12,B,2017 woolen coat women,red,L,2004,2017-09-15,826,Very comfortable.<\/p>\n

980,”ZKDS-J”,2017-09-13,”B”,”2017 Women’s Cotton Clothing”,”red”,”M”,112,,,
\n98,”FKQB-I”,2017-09-15,”B”,”2017 new shoes men”,”red”,”M”,4345,2017-09-18,5473
\n50,”DMQY-K”,2017-09-21,”A”,”2017 pants men”,”red”,”37″,28,2017-09-25,58,”good”,”good”,”good”
\n80,”GKLW-l”,2017-09-22,”A”,”2017 Jeans Men”,”red”,”39″,58,2017-09-25,72,”Very comfortable.”
\n30,”HWEC-L”,2017-09-23,”A”,”2017 shoes women”,”red”,”M”,403,2017-09-26,607,”good!”
\n40,”IQPD-M”,2017-09-24,”B”,”2017 new pants Women”,”red”,”M”,35,2017-09-27,52,”very good.”
\n50,”LPEC-N”,2017-09-25,”B”,”2017 dress Women”,”red”,”M”,29,2017-09-28,47,”not good at all.”
\n60,”NQAB-O”,2017-09-26,”B”,”2017 jacket women”,”red”,”S”,69,2017-09-29,70,”It’s beautiful.”
\n70,”HWNB-P”,2017-09-27,”B”,”2017 jacket women”,”red”,”L”,30,2017-09-30,55,”I like it so much”
\n80,”JKHU-Q”,2017-09-29,”C”,”2017 T-shirt”,”red”,”M”,90,2017-10-02,82,”very good.”<\/p>\n

cd \/home\/user\/Desktop\/<\/p>\n

ll<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

*\u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 DAS \u0628\u0631\u0627\u06cc \u0627\u062a\u0635\u0627\u0644 \u0628\u0647 \u062e\u0648\u0634\u0647 GaussDB(DWS).*<\/p>\n

cat \/home\/user\/Downloads\/credentials.csv|grep -w user<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE FOREIGN TABLE product_info_ext
\n(
\nproduct_price integer not null,
\nproduct_id char(30) not null,
\nproduct_time date,
\nproduct_level char(10),
\nproduct_name varchar(200),
\nproduct_type1 varchar(20),
\nproduct_type2 char(10),
\nproduct_monthly_sales_cnt integer,
\nproduct_comment_time date,
\nproduct_comment_num integer,
\nproduct_comment_content varchar(200)
\n)
\nSERVER gsmpp_server
\nOPTIONS(
\nLOCATION’obs:\/\/OBS_bucket_name\/input_data\/’,
\nFORMAT ‘CSV’ ,
\nDELIMITER ‘,’,
\nENCODING ‘utf8’,
\nHEADER ‘false’,
\nACCESS_KEY’AK’,
\nSECRET_ACCESS_KEY’SK’,
\nFILL_MISSING_FIELDS ‘true’,
\nIGNORE_EXTRA_DATA ‘true’
\n)
\nREAD ONLY
\nLOG INTO product_info_err
\nPER NODE REJECT LIMIT ‘unlimited’;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE product_info
\n(
\nproduct_price integer not null,
\nproduct_id char(30) not null,
\nproduct_time date ,
\nproduct_level char(10) ,
\nproduct_name varchar(200) ,
\nproduct_type1 varchar(20) ,
\nproduct_type2 char(10) ,
\nproduct_monthly_sales_cnt integer ,
\nproduct_comment_time date ,
\nproduct_comment_num integer ,
\nproduct_comment_content varchar(200)
\n)
\nWITH (
\norientation = column,
\ncompression=middle
\n)
\nDISTRIBUTE BY hash (product_id);<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

INSERT INTO product_info SELECT * FROM product_info_ext;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

VACUUM FULL product_info;
\nANALYZE product_info;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

SELECT * FROM product_info;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

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.’);<\/p>\n

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.’);<\/p>\n

UPDATE product_info SET product_price = 185 WHERE product_id = ‘KDKE-B’;<\/p>\n

DELETE product_info WHERE product_id =’KDKE-B’;<\/p>\n

SELECT COUNT(*) FROM product_info;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

cat \/home\/user\/Downloads\/credentials.csv<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE STORE (
\n ID INT,
\n STORECODE VARCHAR(10),
\n STORENAME VARCHAR(100),
\n FIRMID INT,
\n FLOOR INT,
\n BRANDID INT,
\n RENTAMOUNT NUMERIC(18,2),
\n RENTAREA NUMERIC(18,2)
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE POS(
\n ID INT,
\n POSCODE VARCHAR(20),
\n STATUS INT,
\n MODIFICATIONDATE DATE
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE BRAND (
\n ID INT,
\n BRANDCODE VARCHAR(10),
\n BRANDNAME VARCHAR(100),
\n SECTORID INT
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE SECTOR(
\n ID INT,
\n SECTORCODE VARCHAR(10),
\n SECTORNAME VARCHAR(20),
\n CATEGORYID INT
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE CATEGORY(
\n ID INT,
\n CODE VARCHAR(10),
\n NAME VARCHAR(20)
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE FIRM(
\n ID INT,
\n CODE VARCHAR(4),
\n NAME VARCHAR(40),
\n CITYID INT,
\n CITYNAME VARCHAR(10),
\n CITYCODE VARCHAR(20)
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE DATE(
\n ID INT,
\n DATEKEY DATE,
\n YEAR INT,
\n MONTH INT,
\n DAY INT,
\n WEEK INT,
\n WEEKDAY INT
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE PAYTYPE(
\n ID INT,
\n CODE VARCHAR(10),
\n TYPE VARCHAR(10),
\n SIGNDATE DATE
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;<\/p>\n

CREATE TABLE SALES(
\n ID INT,
\n POSID INT,
\n STOREID INT,
\n DATEKEY INT,
\n PAYTYPE INT,
\n TOTALAMOUNT NUMERIC(18,2),
\n DISCOUNTAMOUNT NUMERIC(18,2),
\n ITEMCOUNT INT,
\n PAIDAMOUNT NUMERIC(18,2)
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);<\/p>\n

CREATE TABLE FLOW (
\n ID INT,
\n STOREID INT,
\n DATEKEY INT,
\n INFLOWVALUE INT
\n)
\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);<\/p>\n

SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = ‘retail_data’;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

create schema retail_obs_data;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE FOREIGN TABLE SALES_OBS
\n(
\n like retail_data.SALES
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/sales’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE FLOW_OBS
\n(
\n like retail_data.flow
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/flow’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE BRAND_OBS
\n(
\n like retail_data.brand
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/brand’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE CATEGORY_OBS
\n(
\n like retail_data.category
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/category’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE DATE_OBS
\n(
\n like retail_data.date
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/date’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE FIRM_OBS
\n(
\n like retail_data.firm
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/firm’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE PAYTYPE_OBS
\n(
\n like retail_data.paytype
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/paytype’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE POS_OBS
\n(
\n like retail_data.pos
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/pos’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE SECTOR_OBS
\n(
\n like retail_data.sector
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/sector’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

CREATE FOREIGN TABLE STORE_OBS
\n(
\n like retail_data.store
\n)
\nSERVER gsmpp_server
\nOPTIONS (
\n encoding ‘utf8’,
\n location ‘obs:\/\/dws-demo-ap-southeast-3\/retail-data\/store’,
\n format ‘csv’,
\n delimiter ‘,’,
\n access_key ”,
\n secret_access_key ”,
\n chunksize ’64’,
\n IGNORE_EXTRA_DATA ‘on’,
\n header ‘on’
\n);<\/p>\n

INSERT INTO retail_data.store SELECT * FROM retail_obs_data.STORE_OBS;
\ninsert into retail_data.sector select * from retail_obs_data.SECTOR_OBS;
\ninsert into retail_data.paytype select * from retail_obs_data.PAYTYPE_OBS;
\ninsert into retail_data.firm select * from retail_obs_data.FIRM_OBS;
\ninsert into retail_data.flow select * from retail_obs_data.FLOW_OBS;
\ninsert into retail_data.category select * from retail_obs_data.CATEGORY_OBS;
\ninsert into retail_data.date select * from retail_obs_data.DATE_OBS;
\ninsert into retail_data.pos select * from retail_obs_data.POS_OBS;
\ninsert into retail_data.brand select * from retail_obs_data.BRAND_OBS;
\ninsert into retail_data.sales select * from retail_obs_data.SALES_OBS;<\/p>\n

DROP FOREIGN TABLE {Foreign_table_name};<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE VIEW v_sales_flow_details AS
\nSELECT
\nFIRM.ID FIRMID, FIRM.NAME FIRNAME, FIRM. CITYCODE,
\nCATEGORY.ID CATEGORYID, CATEGORY.NAME CATEGORYNAME,
\nSECTOR.ID SECTORID, SECTOR.SECTORNAME,
\nBRAND.ID BRANDID, BRAND.BRANDNAME,
\nSTORE.ID STOREID, STORE.STORENAME, STORE.RENTAMOUNT, STORE.RENTAREA,
\nDATE.DATEKEY, SALES.TOTALAMOUNT, DISCOUNTAMOUNT, ITEMCOUNT, PAIDAMOUNT, INFLOWVALUE
\nFROM SALES
\nINNER JOIN STORE ON SALES.STOREID = STORE.ID
\nINNER JOIN FIRM ON STORE.FIRMID = FIRM.ID
\nINNER JOIN BRAND ON STORE.BRANDID = BRAND.ID
\nINNER JOIN SECTOR ON BRAND.SECTORID = SECTOR.ID
\nINNER JOIN CATEGORY ON SECTOR.CATEGORYID = CATEGORY.ID
\nINNER JOIN DATE ON SALES.DATEKEY = DATE.ID
\nINNER JOIN FLOW ON FLOW.DATEKEY = DATE.ID AND FLOW.STOREID = STORE.ID;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE task1 AS
\nSELECT DATE_TRUNC(‘month’,datekey)
\nAT TIME ZONE ‘UTC’ AS __timestamp,
\nSUM(paidamount)
\nAS sum__paidamount
\nFROM v_sales_flow_details
\nGROUP BY DATE_TRUNC(‘month’,datekey) AT TIME ZONE ‘UTC’
\nORDER BY SUM(paidamount) DESC;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE task2 AS
\nSELECT firname AS firname,
\nstorename AS storename,
\nSUM(paidamount)
\nAS sum__paidamount,
\nAVG(RENTAMOUNT)\/SUM(PAIDAMOUNT)
\nAS rentamount_sales_rate
\nFROM v_sales_flow_details
\nGROUP BY firname, storename
\nORDER BY SUM(paidamount) DESC;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE task3 AS
\nSELECT citycode AS citycode,
\nSUM(paidamount)
\nAS sum__paidamount
\nFROM v_sales_flow_details
\nGROUP BY citycode
\nORDER BY SUM(paidamount) DESC;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

SELECT brandname AS brandname,
\nfirname AS firname,
\nSUM(PAIDAMOUNT)\/AVG(RENTAREA) AS sales_rentarea_rate,
\nSUM(ITEMCOUNT)\/SUM(INFLOWVALUE) AS poscount_flow_rate,
\nAVG(RENTAMOUNT)\/SUM(PAIDAMOUNT) AS rentamount_sales_rate
\nFROM v_sales_flow_details
\nGROUP BY brandname, firname
\nORDER BY sales_rentarea_rate DESC;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE task4 AS
\nSELECT categoryname AS categoryname,
\nbrandname AS brandname,
\nSUM(paidamount) AS sum__paidamount
\nFROM v_sales_flow_details
\nGROUP BY categoryname,
\nbrandname
\nORDER BY sum__paidamount DESC;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

CREATE TABLE task5 AS
\nSELECT brandname AS brandname,
\nDATE_TRUNC(‘day’, datekey) AT TIME ZONE ‘UTC’ AS __timestamp,
\nSUM(paidamount) AS sum__paidamount
\nFROM v_sales_flow_details
\nWHERE datekey >= ‘2016-01-01 00:00:00’
\nAND datekey <\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\n

*\u0633\u0631\u0648\u06cc\u0633 \u0627\u0646\u0628\u0627\u0631 \u062f\u0627\u062f\u0647
*<\/em>
<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

*\u0627\u06cc\u062c\u0627\u062f \u06cc\u06a9 \u0633\u0637\u0644 OBS
*<\/em><\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n

\n
vim \/home\/user\/Desktop\/product_info0.csv\n\n100,XHDK-A,2017-09-01,A,2017 Shirt Women,red,M,328,2017-09-04,715,good!\n205,KDKE-B,2017-09-01,A,2017 T-shirt Women,pink,L,584,2017-09-05,40,very good!\n300,JODL-X,2017-09-01,A,2017 T-shirt men,red,XL,15,2017-09-03,502,Bad.\n310,QQPX-R,2017-09-02,B,2017 jacket women,red,L,411,2017-09-05,436,It's nice.\n150,ABEF-C,2017-09-03,B,2017 Jeans Women,blue,M,123,2017-09-06,120,good.\n\n\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
200,BCQP-E,2017-09-04,B,2017 casual pants men,black,L,997,2017-09-10,301,good quality.\n250,EABE-D,2017-09-10,A,2017 dress women,black,S,841,2017-09-15,299,This dress fits well.\n108,CDXK-F,2017-09-11,A,2017 dress women,red,M,85,2017-09-14,22,It's really amazing to buy.\n450,MMCE-H,2017-09-11,A,2017 jacket women,white,M,114,2017-09-14,22,very good.\n260,OCDA-G,2017-09-12,B,2017 woolen coat women,red,L,2004,2017-09-15,826,Very comfortable.\n\n980,\"ZKDS-J\",2017-09-13,\"B\",\"2017 Women's Cotton Clothing\",\"red\",\"M\",112,,,\n98,\"FKQB-I\",2017-09-15,\"B\",\"2017 new shoes men\",\"red\",\"M\",4345,2017-09-18,5473\n50,\"DMQY-K\",2017-09-21,\"A\",\"2017 pants men\",\"red\",\"37\",28,2017-09-25,58,\"good\",\"good\",\"good\"\n80,\"GKLW-l\",2017-09-22,\"A\",\"2017 Jeans Men\",\"red\",\"39\",58,2017-09-25,72,\"Very comfortable.\"\n30,\"HWEC-L\",2017-09-23,\"A\",\"2017 shoes women\",\"red\",\"M\",403,2017-09-26,607,\"good!\"\n40,\"IQPD-M\",2017-09-24,\"B\",\"2017 new pants Women\",\"red\",\"M\",35,2017-09-27,52,\"very good.\"\n50,\"LPEC-N\",2017-09-25,\"B\",\"2017 dress Women\",\"red\",\"M\",29,2017-09-28,47,\"not good at all.\"\n60,\"NQAB-O\",2017-09-26,\"B\",\"2017 jacket women\",\"red\",\"S\",69,2017-09-29,70,\"It's beautiful.\"\n70,\"HWNB-P\",2017-09-27,\"B\",\"2017 jacket women\",\"red\",\"L\",30,2017-09-30,55,\"I like it so much\"\n80,\"JKHU-Q\",2017-09-29,\"C\",\"2017 T-shirt\",\"red\",\"M\",90,2017-10-02,82,\"very good.\"\n\n\ncd \/home\/user\/Desktop\/\n\nll\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
*\u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 DAS \u0628\u0631\u0627\u06cc \u0627\u062a\u0635\u0627\u0644 \u0628\u0647 \u062e\u0648\u0634\u0647 GaussDB(DWS).
*<\/em><\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
cat \/home\/user\/Downloads\/credentials.csv|grep -w user\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE FOREIGN TABLE product_info_ext\n(\nproduct_price integer not null,\nproduct_id char(30) not null,\nproduct_time date,\nproduct_level char(10),\nproduct_name varchar(200),\nproduct_type1 varchar(20),\nproduct_type2 char(10),\nproduct_monthly_sales_cnt integer,\nproduct_comment_time date,\nproduct_comment_num integer,\nproduct_comment_content varchar(200) \n) \nSERVER gsmpp_server \nOPTIONS(\nLOCATION'obs:\/\/OBS_bucket_name\/input_data\/',\nFORMAT 'CSV' ,\nDELIMITER ',',\nENCODING 'utf8',\nHEADER 'false',\nACCESS_KEY'AK',\nSECRET_ACCESS_KEY'SK',\nFILL_MISSING_FIELDS 'true',\nIGNORE_EXTRA_DATA 'true'\n)\nREAD ONLY \nLOG INTO product_info_err \nPER NODE REJECT LIMIT 'unlimited';\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n
CREATE TABLE product_info\n(\nproduct_price integer not null,\nproduct_id char(30) not null,\nproduct_time date ,\nproduct_level char(10) ,\nproduct_name varchar(200) ,\nproduct_type1 varchar(20) ,\nproduct_type2 char(10) ,\nproduct_monthly_sales_cnt integer ,\nproduct_comment_time date ,\nproduct_comment_num integer ,\nproduct_comment_content varchar(200) \n) \nWITH (\norientation = column,\ncompression=middle\n) \nDISTRIBUTE BY hash (product_id);\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n
INSERT INTO product_info SELECT * FROM product_info_ext;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n
VACUUM FULL product_info;\nANALYZE product_info;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n
SELECT * FROM product_info;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
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.');\n\nINSERT INTO product_info VALUES (500,'JYXL-E','2017-06-04','B','2017 casual pants men','black','M',990,'2016-11-10',302,'good quality.');\n\nUPDATE product_info SET product_price = 185 WHERE product_id = 'KDKE-B';\n\nDELETE product_info WHERE product_id ='KDKE-B';\n\nSELECT COUNT(*) FROM product_info;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
cat \/home\/user\/Downloads\/credentials.csv\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE TABLE STORE (\n        ID INT, \n        STORECODE VARCHAR(10), \n        STORENAME VARCHAR(100), \n        FIRMID INT, \n        FLOOR INT, \n        BRANDID INT, \n        RENTAMOUNT NUMERIC(18,2), \n        RENTAREA NUMERIC(18,2)\n) \nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\n\nCREATE TABLE POS(\n        ID INT, \n        POSCODE VARCHAR(20), \n        STATUS INT, \n        MODIFICATIONDATE DATE\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE BRAND (\n        ID INT, \n        BRANDCODE VARCHAR(10), \n        BRANDNAME VARCHAR(100), \n        SECTORID INT\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE SECTOR(\n        ID INT, \n        SECTORCODE VARCHAR(10), \n        SECTORNAME VARCHAR(20), \n        CATEGORYID INT\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE CATEGORY(\n        ID INT, \n        CODE VARCHAR(10), \n        NAME VARCHAR(20)\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE FIRM(\n        ID INT, \n        CODE VARCHAR(4), \n        NAME VARCHAR(40), \n        CITYID INT, \n        CITYNAME VARCHAR(10),\n        CITYCODE VARCHAR(20)\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE DATE(\n        ID INT, \n        DATEKEY DATE, \n        YEAR INT, \n        MONTH INT, \n        DAY INT, \n        WEEK INT, \n        WEEKDAY INT\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE PAYTYPE(\n        ID INT, \n        CODE VARCHAR(10), \n        TYPE VARCHAR(10), \n        SIGNDATE DATE\n)\nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;\n\nCREATE TABLE SALES(\n         ID INT, \n         POSID INT, \n         STOREID INT, \n         DATEKEY INT, \n         PAYTYPE INT, \n         TOTALAMOUNT NUMERIC(18,2),\n         DISCOUNTAMOUNT NUMERIC(18,2), \n         ITEMCOUNT INT, \n         PAIDAMOUNT NUMERIC(18,2)\n) \nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);\n\nCREATE TABLE FLOW (\n         ID INT, \n         STOREID INT, \n         DATEKEY INT, \n         INFLOWVALUE INT\n) \nWITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);\n\nSELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'retail_data';\n\n\n\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
create schema retail_obs_data;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE FOREIGN TABLE SALES_OBS\n(\n        like retail_data.SALES\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/sales',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE FLOW_OBS\n(\n        like retail_data.flow\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/flow',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE BRAND_OBS\n(\n        like retail_data.brand\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/brand',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE CATEGORY_OBS\n(\n       like retail_data.category\n)\nSERVER gsmpp_server \nOPTIONS (\n       encoding 'utf8',\n       location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/category',\n       format 'csv',\n       delimiter ',',\n       access_key '',\n       secret_access_key '',\n       chunksize '64',\n       IGNORE_EXTRA_DATA 'on',\n       header 'on'\n);\n\nCREATE FOREIGN TABLE DATE_OBS\n(\n        like retail_data.date\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/date',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE FIRM_OBS\n(\n        like retail_data.firm\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/firm',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE PAYTYPE_OBS\n(\n        like retail_data.paytype\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/paytype',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE POS_OBS\n(\n        like retail_data.pos\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/pos',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE SECTOR_OBS\n(\n        like retail_data.sector\n)\nSERVER gsmpp_server \nOPTIONS (\n        encoding 'utf8',\n        location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/sector',\n        format 'csv',\n        delimiter ',',\n        access_key '',\n        secret_access_key '',\n        chunksize '64',\n        IGNORE_EXTRA_DATA 'on',\n        header 'on'\n);\n\nCREATE FOREIGN TABLE STORE_OBS\n(\n         like retail_data.store\n)\nSERVER gsmpp_server \nOPTIONS (\n         encoding 'utf8',\n         location 'obs:\/\/dws-demo-ap-southeast-3\/retail-data\/store',\n         format 'csv',\n         delimiter ',',\n        access_key '',\n        secret_access_key '',\n         chunksize '64',\n         IGNORE_EXTRA_DATA 'on',\n         header 'on'\n);\n\nINSERT INTO retail_data.store SELECT * FROM retail_obs_data.STORE_OBS;\ninsert into retail_data.sector select * from retail_obs_data.SECTOR_OBS;\ninsert into retail_data.paytype select * from retail_obs_data.PAYTYPE_OBS;\ninsert into retail_data.firm select * from retail_obs_data.FIRM_OBS;\ninsert into retail_data.flow select * from retail_obs_data.FLOW_OBS;\ninsert into retail_data.category select * from retail_obs_data.CATEGORY_OBS;\ninsert into retail_data.date select * from retail_obs_data.DATE_OBS;\ninsert into retail_data.pos select * from retail_obs_data.POS_OBS;\ninsert into retail_data.brand select * from retail_obs_data.BRAND_OBS;\ninsert into retail_data.sales select * from retail_obs_data.SALES_OBS;\n\n\nDROP FOREIGN TABLE {Foreign_table_name};\n<\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/sk><\/ak><\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE VIEW v_sales_flow_details AS \nSELECT \nFIRM.ID FIRMID, FIRM.NAME FIRNAME, FIRM. CITYCODE,\nCATEGORY.ID CATEGORYID, CATEGORY.NAME CATEGORYNAME, \nSECTOR.ID SECTORID, SECTOR.SECTORNAME,\nBRAND.ID BRANDID, BRAND.BRANDNAME,\nSTORE.ID STOREID, STORE.STORENAME, STORE.RENTAMOUNT, STORE.RENTAREA,\nDATE.DATEKEY, SALES.TOTALAMOUNT, DISCOUNTAMOUNT, ITEMCOUNT, PAIDAMOUNT, INFLOWVALUE\nFROM SALES\nINNER JOIN STORE ON SALES.STOREID = STORE.ID\nINNER JOIN FIRM ON STORE.FIRMID = FIRM.ID\nINNER JOIN BRAND ON STORE.BRANDID = BRAND.ID\nINNER JOIN SECTOR ON BRAND.SECTORID = SECTOR.ID\nINNER JOIN CATEGORY ON SECTOR.CATEGORYID = CATEGORY.ID\nINNER JOIN DATE ON SALES.DATEKEY = DATE.ID\nINNER JOIN FLOW ON FLOW.DATEKEY = DATE.ID AND FLOW.STOREID = STORE.ID;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE TABLE task1 AS \nSELECT DATE_TRUNC('month',datekey) \nAT TIME ZONE 'UTC' AS __timestamp,\nSUM(paidamount)\nAS sum__paidamount\nFROM v_sales_flow_details\nGROUP BY DATE_TRUNC('month',datekey) AT TIME ZONE 'UTC'\nORDER BY SUM(paidamount) DESC;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE TABLE task2 AS \nSELECT firname AS firname,\nstorename AS storename,\nSUM(paidamount)\nAS sum__paidamount,\nAVG(RENTAMOUNT)\/SUM(PAIDAMOUNT)\nAS rentamount_sales_rate\nFROM v_sales_flow_details\nGROUP BY firname, storename\nORDER BY SUM(paidamount) DESC;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE TABLE task3 AS \nSELECT citycode AS citycode,\nSUM(paidamount)\nAS sum__paidamount\nFROM v_sales_flow_details\nGROUP BY citycode\nORDER BY SUM(paidamount) DESC;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
SELECT brandname AS brandname,\nfirname AS firname,\nSUM(PAIDAMOUNT)\/AVG(RENTAREA) AS sales_rentarea_rate,\nSUM(ITEMCOUNT)\/SUM(INFLOWVALUE) AS poscount_flow_rate,\nAVG(RENTAMOUNT)\/SUM(PAIDAMOUNT) AS rentamount_sales_rate\nFROM v_sales_flow_details\nGROUP BY brandname,  firname\nORDER BY sales_rentarea_rate DESC;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE TABLE task4 AS \nSELECT categoryname AS categoryname,\nbrandname AS brandname,\nSUM(paidamount) AS sum__paidamount\nFROM v_sales_flow_details\nGROUP BY categoryname,\nbrandname\nORDER BY sum__paidamount DESC;\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n
\n
CREATE TABLE task5 AS \nSELECT brandname AS brandname,\nDATE_TRUNC('day', datekey) AT TIME ZONE 'UTC' AS __timestamp,\nSUM(paidamount) AS sum__paidamount\nFROM v_sales_flow_details\nWHERE datekey >= '2016-01-01 00:00:00'\nAND datekey <\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\"\u062a\u0648\u0636\u06cc\u062d\u0627\u062a<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
Summarize this content to 400 words in Persian Lang *\u0633\u0631\u0648\u06cc\u0633 \u0627\u0646\u0628\u0627\u0631 \u062f\u0627\u062f\u0647* *\u0627\u06cc\u062c\u0627\u062f \u06cc\u06a9 \u0633\u0637\u0644 OBS* 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. \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f 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 …<\/p>\n","protected":false},"author":2,"featured_media":72733,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"","fifu_image_alt":"","footnotes":""},"categories":[339],"tags":[],"class_list":["post-72732","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dev"],"_links":{"self":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/posts\/72732","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/comments?post=72732"}],"version-history":[{"count":0,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/posts\/72732\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/media\/72733"}],"wp:attachment":[{"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/media?parent=72732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/categories?post=72732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nabfollower.com\/blog\/wp-json\/wp\/v2\/tags?post=72732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}