
CREATE DATABASE IF NOT EXISTS dw DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT ALL PRIVILEGES ON dw.* TO 'atguigu'@'%';
USE dw;


CREATE TABLE dim_region
(
    region_id   VARCHAR(20) PRIMARY KEY,
    province    VARCHAR(50),
    region_name VARCHAR(50),
    country     VARCHAR(50)
);

INSERT INTO dim_region (region_id, province, region_name, country)
VALUES ('R001', '广东省', '华南', '中国'),
       ('R002', '浙江省', '华东', '中国'),
       ('R003', '四川省', '西南', '中国'),
       ('R004', '北京市', '华北', '中国'),
       ('R005', '上海市', '华东', '中国'),
       ('R006', '湖北省', '华中', '中国');


CREATE TABLE dim_customer
(
    customer_id   VARCHAR(20) PRIMARY KEY,
    customer_name VARCHAR(50),
    gender        VARCHAR(10),
    member_level  VARCHAR(20)
);

INSERT INTO dim_customer (customer_id, customer_name, gender, member_level)
VALUES ('C001', '李伟', '男', '黄金'),
       ('C002', '王芳', '女', '白银'),
       ('C003', '张敏', '女', '黄金'),
       ('C004', '刘洋', '男', '青铜'),
       ('C005', '陈静', '女', '铂金'),
       ('C006', '赵磊', '男', '白银'),
       ('C007', '黄秀英', '女', '青铜'),
       ('C008', '吴斌', '男', '黄金'),
       ('C009', '周燕', '女', '铂金'),
       ('C010', '徐浩', '男', '白银'),
       ('C011', '孙丽', '女', '黄金'),
       ('C012', '马强', '男', '青铜'),
       ('C013', '朱玲', '女', '白银'),
       ('C014', '胡杰', '男', '黄金'),
       ('C015', '高梅', '女', '铂金'),
       ('C016', '林峰', '男', '青铜'),
       ('C017', '何娜', '女', '白银'),
       ('C018', '郭涛', '男', '黄金'),
       ('C019', '邓慧', '女', '青铜'),
       ('C020', '曹瑞', '男', '铂金');



CREATE TABLE dim_product
(
    product_id   VARCHAR(20) PRIMARY KEY,
    product_name VARCHAR(200),
    category     VARCHAR(50),
    brand        VARCHAR(50)
);

INSERT INTO dim_product (product_id, product_name, category, brand)
VALUES ('P001', 'iPhone 15 Pro', '手机数码', '苹果'),
       ('P002', 'Galaxy S24 Ultra', '手机数码', '三星'),
       ('P003', 'Mate 60 Pro', '手机数码', '华为'),
       ('P004', '戴森 V15 吸尘器', '家用电器', '戴森'),
       ('P005', '美的空调 KFR-35GW', '家用电器', '美的'),
       ('P006', '耐克 Air Max 270 运动鞋', '鞋靴', '耐克'),
       ('P007', '阿迪达斯 Ultraboost 跑鞋', '鞋靴', '阿迪达斯'),
       ('P008', '优衣库 Heattech 保暖夹克', '服饰', '优衣库'),
       ('P009', '李维斯 501 牛仔裤', '服饰', '李维斯'),
       ('P010', '雀巢金牌速溶咖啡', '食品饮料', '雀巢'),
       ('P011', '蒙牛纯牛奶 250ml*12', '食品饮料', '蒙牛'),
       ('P012', '乐事原味薯片 150g', '休闲零食', '乐事'),
       ('P013', '奥利奥巧克力夹心饼干', '休闲零食', '奥利奥'),
       ('P014', 'Kindle Paperwhite 电子书', '手机数码', '亚马逊'),
       ('P015', 'Instant Pot 多功能电压力锅', '家用电器', 'Instant Pot');



CREATE TABLE dim_date
(
    date_id INT PRIMARY KEY,
    year    INT,
    quarter VARCHAR(2),
    month   INT,
    day     INT
);

INSERT INTO dim_date (date_id, year, quarter, month, day)
VALUES (20250101, 2025, 'Q1', 1, 1),
       (20250102, 2025, 'Q1', 1, 2),
       (20250103, 2025, 'Q1', 1, 3),
       (20250104, 2025, 'Q1', 1, 4),
       (20250105, 2025, 'Q1', 1, 5),
       (20250106, 2025, 'Q1', 1, 6),
       (20250107, 2025, 'Q1', 1, 7),
       (20250108, 2025, 'Q1', 1, 8),
       (20250109, 2025, 'Q1', 1, 9),
       (20250110, 2025, 'Q1', 1, 10),
       (20250111, 2025, 'Q1', 1, 11),
       (20250112, 2025, 'Q1', 1, 12),
       (20250113, 2025, 'Q1', 1, 13),
       (20250114, 2025, 'Q1', 1, 14),
       (20250115, 2025, 'Q1', 1, 15),
       (20250116, 2025, 'Q1', 1, 16),
       (20250117, 2025, 'Q1', 1, 17),
       (20250118, 2025, 'Q1', 1, 18),
       (20250119, 2025, 'Q1', 1, 19),
       (20250120, 2025, 'Q1', 1, 20),
       (20250121, 2025, 'Q1', 1, 21),
       (20250122, 2025, 'Q1', 1, 22),
       (20250123, 2025, 'Q1', 1, 23),
       (20250124, 2025, 'Q1', 1, 24),
       (20250125, 2025, 'Q1', 1, 25),
       (20250126, 2025, 'Q1', 1, 26),
       (20250127, 2025, 'Q1', 1, 27),
       (20250128, 2025, 'Q1', 1, 28),
       (20250129, 2025, 'Q1', 1, 29),
       (20250130, 2025, 'Q1', 1, 30),
       (20250131, 2025, 'Q1', 1, 31),
       (20250201, 2025, 'Q1', 2, 1),
       (20250202, 2025, 'Q1', 2, 2),
       (20250203, 2025, 'Q1', 2, 3),
       (20250204, 2025, 'Q1', 2, 4),
       (20250205, 2025, 'Q1', 2, 5),
       (20250206, 2025, 'Q1', 2, 6),
       (20250207, 2025, 'Q1', 2, 7),
       (20250208, 2025, 'Q1', 2, 8),
       (20250209, 2025, 'Q1', 2, 9),
       (20250210, 2025, 'Q1', 2, 10),
       (20250211, 2025, 'Q1', 2, 11),
       (20250212, 2025, 'Q1', 2, 12),
       (20250213, 2025, 'Q1', 2, 13),
       (20250214, 2025, 'Q1', 2, 14),
       (20250215, 2025, 'Q1', 2, 15),
       (20250216, 2025, 'Q1', 2, 16),
       (20250217, 2025, 'Q1', 2, 17),
       (20250218, 2025, 'Q1', 2, 18),
       (20250219, 2025, 'Q1', 2, 19),
       (20250220, 2025, 'Q1', 2, 20),
       (20250221, 2025, 'Q1', 2, 21),
       (20250222, 2025, 'Q1', 2, 22),
       (20250223, 2025, 'Q1', 2, 23),
       (20250224, 2025, 'Q1', 2, 24),
       (20250225, 2025, 'Q1', 2, 25),
       (20250226, 2025, 'Q1', 2, 26),
       (20250227, 2025, 'Q1', 2, 27),
       (20250228, 2025, 'Q1', 2, 28),
       (20250301, 2025, 'Q1', 3, 1),
       (20250302, 2025, 'Q1', 3, 2),
       (20250303, 2025, 'Q1', 3, 3),
       (20250304, 2025, 'Q1', 3, 4),
       (20250305, 2025, 'Q1', 3, 5),
       (20250306, 2025, 'Q1', 3, 6),
       (20250307, 2025, 'Q1', 3, 7),
       (20250308, 2025, 'Q1', 3, 8),
       (20250309, 2025, 'Q1', 3, 9),
       (20250310, 2025, 'Q1', 3, 10),
       (20250311, 2025, 'Q1', 3, 11),
       (20250312, 2025, 'Q1', 3, 12),
       (20250313, 2025, 'Q1', 3, 13),
       (20250314, 2025, 'Q1', 3, 14),
       (20250315, 2025, 'Q1', 3, 15),
       (20250316, 2025, 'Q1', 3, 16),
       (20250317, 2025, 'Q1', 3, 17),
       (20250318, 2025, 'Q1', 3, 18),
       (20250319, 2025, 'Q1', 3, 19),
       (20250320, 2025, 'Q1', 3, 20),
       (20250321, 2025, 'Q1', 3, 21),
       (20250322, 2025, 'Q1', 3, 22),
       (20250323, 2025, 'Q1', 3, 23),
       (20250324, 2025, 'Q1', 3, 24),
       (20250325, 2025, 'Q1', 3, 25),
       (20250326, 2025, 'Q1', 3, 26),
       (20250327, 2025, 'Q1', 3, 27),
       (20250328, 2025, 'Q1', 3, 28),
       (20250329, 2025, 'Q1', 3, 29),
       (20250330, 2025, 'Q1', 3, 30),
       (20250331, 2025, 'Q1', 3, 31);



CREATE TABLE fact_order
(
    order_id       VARCHAR(30) PRIMARY KEY,
    customer_id    VARCHAR(20),
    product_id     VARCHAR(20),
    date_id        INT,
    region_id      VARCHAR(20),
    order_quantity INT,
    order_amount   FLOAT
);

INSERT INTO fact_order (order_id, customer_id, product_id, date_id, region_id, order_quantity, order_amount)
VALUES ('ORD20250101001', 'C001', 'P001', 20250101, 'R001', 1, 8999.00),
       ('ORD20250101002', 'C005', 'P003', 20250101, 'R005', 1, 6999.00),
       ('ORD20250102001', 'C003', 'P010', 20250102, 'R002', 5, 125.00),
       ('ORD20250102002', 'C008', 'P006', 20250102, 'R004', 1, 899.00),
       ('ORD20250103001', 'C012', 'P011', 20250103, 'R003', 12, 60.00),
       ('ORD20250103002', 'C015', 'P014', 20250103, 'R005', 1, 1399.00),
       ('ORD20250104001', 'C002', 'P012', 20250104, 'R001', 8, 40.00),
       ('ORD20250105001', 'C007', 'P008', 20250105, 'R006', 2, 299.00),
       ('ORD20250106001', 'C010', 'P002', 20250106, 'R002', 1, 9499.00),
       ('ORD20250107001', 'C019', 'P013', 20250107, 'R003', 10, 35.00),
       ('ORD20250108001', 'C004', 'P005', 20250108, 'R001', 1, 3200.00),
       ('ORD20250109001', 'C011', 'P009', 20250109, 'R004', 1, 599.00),
       ('ORD20250110001', 'C006', 'P007', 20250110, 'R005', 1, 1299.00),
       ('ORD20250111001', 'C013', 'P004', 20250111, 'R002', 1, 5499.00),
       ('ORD20250112001', 'C017', 'P015', 20250112, 'R006', 1, 899.00),
       ('ORD20250113001', 'C020', 'P001', 20250113, 'R005', 1, 8999.00),
       ('ORD20250114001', 'C009', 'P010', 20250114, 'R004', 3, 75.00),
       ('ORD20250115001', 'C014', 'P003', 20250115, 'R001', 1, 6999.00),
       ('ORD20250116001', 'C001', 'P012', 20250116, 'R001', 6, 30.00),
       ('ORD20250117001', 'C005', 'P006', 20250117, 'R005', 1, 899.00),
       ('ORD20250118001', 'C003', 'P011', 20250118, 'R002', 10, 50.00),
       ('ORD20250119001', 'C008', 'P014', 20250119, 'R004', 1, 1399.00),
       ('ORD20250120001', 'C012', 'P008', 20250120, 'R003', 1, 199.00),
       ('ORD20250121001', 'C015', 'P002', 20250121, 'R005', 1, 9499.00),
       ('ORD20250122001', 'C002', 'P013', 20250122, 'R001', 12, 42.00),
       ('ORD20250123001', 'C007', 'P005', 20250123, 'R006', 1, 3200.00),
       ('ORD20250124001', 'C010', 'P009', 20250124, 'R002', 2, 1198.00),
       ('ORD20250125001', 'C019', 'P007', 20250125, 'R003', 1, 1299.00),
       ('ORD20250126001', 'C004', 'P004', 20250126, 'R001', 1, 5499.00),
       ('ORD20250127001', 'C011', 'P015', 20250127, 'R004', 1, 899.00),
       ('ORD20250128001', 'C006', 'P001', 20250128, 'R005', 1, 8999.00),
       ('ORD20250129001', 'C013', 'P010', 20250129, 'R002', 4, 100.00),
       ('ORD20250130001', 'C017', 'P003', 20250130, 'R006', 1, 6999.00),
       ('ORD20250131001', 'C020', 'P012', 20250131, 'R005', 7, 35.00),
       ('ORD20250201001', 'C009', 'P011', 20250201, 'R004', 8, 40.00),
       ('ORD20250202001', 'C014', 'P006', 20250202, 'R001', 1, 899.00),
       ('ORD20250203001', 'C001', 'P014', 20250203, 'R001', 1, 1399.00),
       ('ORD20250204001', 'C005', 'P008', 20250204, 'R005', 1, 199.00),
       ('ORD20250205001', 'C003', 'P002', 20250205, 'R002', 1, 9499.00),
       ('ORD20250206001', 'C008', 'P013', 20250206, 'R004', 9, 31.50),
       ('ORD20250207001', 'C012', 'P005', 20250207, 'R003', 1, 3200.00),
       ('ORD20250208001', 'C015', 'P009', 20250208, 'R005', 1, 599.00),
       ('ORD20250209001', 'C002', 'P007', 20250209, 'R001', 1, 1299.00),
       ('ORD20250210001', 'C007', 'P004', 20250210, 'R006', 1, 5499.00),
       ('ORD20250211001', 'C010', 'P015', 20250211, 'R002', 1, 899.00),
       ('ORD20250212001', 'C019', 'P001', 20250212, 'R003', 1, 8999.00),
       ('ORD20250213001', 'C004', 'P010', 20250213, 'R001', 6, 150.00),
       ('ORD20250214001', 'C011', 'P003', 20250214, 'R004', 1, 6999.00),
       ('ORD20250215001', 'C006', 'P012', 20250215, 'R005', 10, 50.00),
       ('ORD20250216001', 'C013', 'P011', 20250216, 'R002', 15, 75.00),
       ('ORD20250217001', 'C017', 'P006', 20250217, 'R006', 1, 899.00),
       ('ORD20250218001', 'C020', 'P014', 20250218, 'R005', 1, 1399.00),
       ('ORD20250219001', 'C009', 'P008', 20250219, 'R004', 2, 398.00),
       ('ORD20250220001', 'C014', 'P002', 20250220, 'R001', 1, 9499.00),
       ('ORD20250221001', 'C001', 'P013', 20250221, 'R001', 11, 38.50),
       ('ORD20250222001', 'C005', 'P005', 20250222, 'R005', 1, 3200.00),
       ('ORD20250223001', 'C003', 'P009', 20250223, 'R002', 1, 599.00),
       ('ORD20250224001', 'C008', 'P007', 20250224, 'R004', 1, 1299.00),
       ('ORD20250225001', 'C012', 'P004', 20250225, 'R003', 1, 5499.00),
       ('ORD20250226001', 'C015', 'P015', 20250226, 'R005', 1, 899.00),
       ('ORD20250227001', 'C002', 'P001', 20250227, 'R001', 1, 8999.00),
       ('ORD20250228001', 'C007', 'P010', 20250228, 'R006', 5, 125.00),
       ('ORD20250301001', 'C010', 'P003', 20250301, 'R002', 1, 6999.00),
       ('ORD20250302001', 'C019', 'P012', 20250302, 'R003', 9, 45.00),
       ('ORD20250303001', 'C004', 'P011', 20250303, 'R001', 10, 50.00),
       ('ORD20250304001', 'C011', 'P006', 20250304, 'R004', 1, 899.00),
       ('ORD20250305001', 'C006', 'P014', 20250305, 'R005', 1, 1399.00),
       ('ORD20250306001', 'C013', 'P008', 20250306, 'R002', 1, 199.00),
       ('ORD20250307001', 'C017', 'P002', 20250307, 'R006', 1, 9499.00),
       ('ORD20250308001', 'C020', 'P013', 20250308, 'R005', 14, 49.00),
       ('ORD20250309001', 'C009', 'P005', 20250309, 'R004', 1, 3200.00),
       ('ORD20250310001', 'C014', 'P009', 20250310, 'R001', 1, 599.00),
       ('ORD20250311001', 'C001', 'P007', 20250311, 'R001', 1, 1299.00),
       ('ORD20250312001', 'C005', 'P004', 20250312, 'R005', 1, 5499.00),
       ('ORD20250313001', 'C003', 'P015', 20250313, 'R002', 1, 899.00),
       ('ORD20250314001', 'C008', 'P001', 20250314, 'R004', 1, 8999.00),
       ('ORD20250315001', 'C012', 'P010', 20250315, 'R003', 7, 175.00),
       ('ORD20250316001', 'C015', 'P003', 20250316, 'R005', 1, 6999.00),
       ('ORD20250317001', 'C002', 'P012', 20250317, 'R001', 12, 60.00),
       ('ORD20250318001', 'C007', 'P011', 20250318, 'R006', 18, 90.00),
       ('ORD20250319001', 'C010', 'P006', 20250319, 'R002', 1, 899.00),
       ('ORD20250320001', 'C019', 'P014', 20250320, 'R003', 1, 1399.00),
       ('ORD20250321001', 'C004', 'P008', 20250321, 'R001', 3, 597.00),
       ('ORD20250322001', 'C011', 'P002', 20250322, 'R004', 1, 9499.00),
       ('ORD20250323001', 'C006', 'P013', 20250323, 'R005', 16, 56.00),
       ('ORD20250324001', 'C013', 'P005', 20250324, 'R002', 1, 3200.00),
       ('ORD20250325001', 'C017', 'P009', 20250325, 'R006', 2, 1198.00),
       ('ORD20250326001', 'C020', 'P007', 20250326, 'R005', 1, 1299.00),
       ('ORD20250327001', 'C009', 'P004', 20250327, 'R004', 1, 5499.00),
       ('ORD20250328001', 'C014', 'P015', 20250328, 'R001', 1, 899.00),
       ('ORD20250329001', 'C001', 'P001', 20250329, 'R001', 1, 8999.00),
       ('ORD20250330001', 'C005', 'P010', 20250330, 'R005', 4, 100.00),
       ('ORD20250331001', 'C003', 'P003', 20250331, 'R002', 1, 6999.00),
       ('ORD20250305002', 'C007', 'P012', 20250305, 'R006', 20, 100.00),
       ('ORD20250312002', 'C012', 'P011', 20250312, 'R003', 24, 120.00),
       ('ORD20250318002', 'C015', 'P013', 20250318, 'R005', 30, 105.00),
       ('ORD20250325002', 'C002', 'P010', 20250325, 'R001', 10, 250.00),
       ('ORD20250330002', 'C008', 'P012', 20250330, 'R004', 15, 75.00),
       ('ORD20250210002', 'C019', 'P011', 20250210, 'R003', 20, 100.00),
       ('ORD20250120002', 'C014', 'P013', 20250120, 'R001', 25, 87.50),
       ('ORD20250105002', 'C010', 'P010', 20250105, 'R002', 8, 200.00),
       ('ORD20250220002', 'C006', 'P012', 20250220, 'R005', 18, 90.00),
       ('ORD20250301002', 'C017', 'P011', 20250301, 'R006', 30, 150.00),
       ('ORD20250315002', 'C020', 'P013', 20250315, 'R005', 22, 77.00),
       ('ORD20250130002', 'C009', 'P010', 20250130, 'R004', 6, 150.00),
       ('ORD20250228002', 'C004', 'P012', 20250228, 'R001', 14, 70.00),
       ('ORD20250322002', 'C011', 'P011', 20250322, 'R004', 16, 80.00),
       ('ORD20250329002', 'C005', 'P013', 20250329, 'R005', 18, 63.00),
       ('ORD20250110002', 'C003', 'P010', 20250110, 'R002', 5, 125.00),
       ('ORD20250205002', 'C008', 'P012', 20250205, 'R004', 12, 60.00),
       ('ORD20250310002', 'C013', 'P011', 20250310, 'R002', 20, 100.00),
       ('ORD20250125002', 'C018', 'P001', 20250125, 'R003', 1, 8999.00),
       ('ORD20250215002', 'C016', 'P003', 20250215, 'R001', 1, 6999.00),
       ('ORD20250320002', 'C018', 'P014', 20250320, 'R003', 1, 1399.00),
       ('ORD20250108002', 'C016', 'P005', 20250108, 'R001', 1, 3200.00);