Project1_DA1.sql

--1. Chuyển đổi kiểu dữ liệu phù hợp cho các trường (sử dụng câu lệnh ALTER)
ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN ORDERNUMBER TYPE INTEGER USING ORDERNUMBER::INTEGER;

ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN QUANTITYORDERED TYPE INTEGER USING QUANTITYORDERED::INTEGER;

ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN PRICEEACH TYPE DECIMAL USING ROUND(CAST(PRICEEACH AS DECIMAL), 2);

ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN ORDERLINENUMBER TYPE INTEGER USING ORDERLINENUMBER::INTEGER;

ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN SALES TYPE DECIMAL USING ROUND(CAST(SALES AS DECIMAL), 2);

ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN ORDERDATE TYPE TIMESTAMP USING TO_TIMESTAMP(ORDERDATE, 'MM/DD/YYYY HH24:MI');

ALTER TABLE SALES_DATASET_RFM_PRJ
ALTER COLUMN MSRP TYPE NUMERIC(10, 2) USING MSRP::NUMERIC;

--2.Check NULL/BLANK (‘’)  ở các trường: ORDERNUMBER, QUANTITYORDERED, PRICEEACH, ORDERLINENUMBER, SALES, ORDERDATE.
SELECT *
FROM SALES_DATASET_RFM_PRJ
WHERE ORDERNUMBER IS NULL
   OR QUANTITYORDERED IS NULL
   OR ORDERLINENUMBER IS NULL;

SELECT *
FROM SALES_DATASET_RFM_PRJ
WHERE PRICEEACH IS NULL
   OR SALES IS NULL
   OR MSRP IS NULL;

SELECT *
FROM SALES_DATASET_RFM_PRJ
WHERE ORDERDATE IS NULL;

--3. Thêm cột CONTACTLASTNAME, CONTACTFIRSTNAME được tách ra từ CONTACTFULLNAME . 
Chuẩn hóa CONTACTLASTNAME, CONTACTFIRSTNAME theo định dạng chữ cái đầu tiên viết hoa, chữ cái tiếp theo viết thường. 
ALTER TABLE SALES_DATASET_RFM_PRJ
ADD COLUMN CONTACTLASTNAME VARCHAR,
ADD COLUMN CONTACTFIRSTNAME VARCHAR;

UPDATE SALES_DATASET_RFM_PRJ
SET
  CONTACTFIRSTNAME = INITCAP(SUBSTRING(CONTACTFULLNAME, POSITION('-' IN CONTACTFULLNAME) + 1)),
  CONTACTLASTNAME = INITCAP(SUBSTRING(CONTACTFULLNAME, 1, POSITION('-' IN CONTACTFULLNAME) - 1))
WHERE CONTACTFULLNAME LIKE '%-%';

--4. Thêm cột QTR_ID, MONTH_ID, YEAR_ID lần lượt là Qúy, tháng, năm được lấy ra từ ORDERDATE 
ALTER TABLE SALES_DATASET_RFM_PRJ
ADD COLUMN QTR_ID INTEGER,
ADD COLUMN MONTH_ID INTEGER,
ADD COLUMN YEAR_ID INTEGER;

UPDATE SALES_DATASET_RFM_PRJ
SET
  QTR_ID = EXTRACT(QUARTER FROM ORDERDATE),
  MONTH_ID = EXTRACT(MONTH FROM ORDERDATE),
  YEAR_ID = EXTRACT(YEAR FROM ORDERDATE);

--5. Tìm và xử lý outlier
---sử dụng z-score tìm outlier
WITH Stats AS (
  SELECT
    AVG(QUANTITYORDERED) AS mean_quantity,
    STDDEV(QUANTITYORDERED) AS stddev_quantity
  FROM SALES_DATASET_RFM_PRJ
)
SELECT *
FROM SALES_DATASET_RFM_PRJ s
CROSS JOIN Stats
WHERE ABS(s.QUANTITYORDERED - Stats.mean_quantity) / Stats.stddev_quantity > 3;

---xử lý 1: xóa outlier
DELETE FROM SALES_DATASET_RFM_PRJ s
USING (
  SELECT
    AVG(QUANTITYORDERED) AS mean_quantity,
    STDDEV(QUANTITYORDERED) AS stddev_quantity
  FROM SALES_DATASET_RFM_PRJ
) AS Stats
WHERE ABS(s.QUANTITYORDERED - Stats.mean_quantity) / Stats.stddev_quantity > 3;
---xử lý 2: cập nhật lại ngưỡng outlier
WITH Stats AS (
  SELECT
    AVG(QUANTITYORDERED) AS mean_quantity,
    STDDEV(QUANTITYORDERED) AS stddev_quantity
  FROM SALES_DATASET_RFM_PRJ
)
UPDATE SALES_DATASET_RFM_PRJ 
SET QUANTITYORDERED = Stats.mean_quantity + 3 * Stats.stddev_quantity
FROM Stats
WHERE ABS(s.QUANTITYORDERED - Stats.mean_quantity) / Stats.stddev_quantity > 3;

--6. (chờ bài đc review)