数据仓库之 案例

/*****************************************************
            create database sales_source
******************************************************/
drop database  if exists sales_source;
create database sales_source default charset utf8 collate utf8_general_ci;
use sales_source;

/*****************************************************
            create table 
******************************************************/
-- Table:Customer
drop table if exists Customer;
create table customer(
    customer_number int primary key not null auto_increment,
    customer_name varchar(32) not null,
    customer_street_address varchar(256) not null,
    customer_zip_code int not null,
    customer_city varchar(32) not null,
    customer_state varchar(32) not null
);

-- Table:Product
drop table if exists product;
create table product(
    product_code int primary key not null auto_increment,
    product_name varchar(128) not null,
    product_category varchar(32) not null
);

-- Table:Sales_order
drop table if exists sales_order;
create table sales_order(
    order_number int primary key not null auto_increment,
    customer_number int not null,
    product_code int not null,
    order_date date not null,
    entry_date date not null,
    order_amount int not null
);

-- add constraint
alter table sales_order add constraint fk_cust_order 
    foreign key (customer_number) references customer(customer_number);
alter table sales_order add constraint fk_product_order 
    foreign key (product_code) references product(product_code);
    

/*************************************************
                insert data
***********************************************/
-- insert customer
insert into customer
(
    customer_name,customer_street_address,customer_zip_code,
    customer_city,customer_state
)
values
(‘Big Customers‘,‘7500 Louise Dr.‘,17050,‘Mechanicsbrg‘,‘PA‘),
(‘Small Stroes‘,‘2500 Woodland St.‘,17055,‘Pittsubtgh‘,‘PA‘),
(‘Medium Retailers‘,‘1111 Ritter Rd.‘,17055,‘Pittsubtgh‘,‘PA‘),
(‘Good Companies‘,‘9500 Scott St.‘,17050,‘Mechanicsbrg‘,‘PA‘),
(‘Wonderful Shops‘,‘3333 Rossmoyne Rd.‘,17050,‘Mechanicsbrg‘,‘PA‘),
(‘Loyal Clients‘,‘7070 Ritter Rd.‘,17055,‘Mechanicsbrg‘,‘PA‘);

-- insert product
insert into product (product_name,product_category) values
(‘Hard Disk‘,‘Storage‘),
(‘Floppy Driver‘,‘Storage‘),
(‘Icd panel‘,‘monitor‘);
-- insert sales_orders 
-- customer_numer int,product_code int,order_date,entry_date,order_amount
drop procedure if exists proc_generate_saleorder;
delimiter $$
create procedure proc_generate_saleorder()
begin
    -- create temp table 
    drop table if exists temp;
    create table temp as select * from sales_order where 1=0;
    -- declare var 
    set @begin_time := unix_timestamp(‘2018-1-1‘);
    set @over_time := unix_timestamp(‘2018-11-23‘);
    set @i :=1;
    while @i <= 100000 do
        set @cust_number := floor(1+rand()*6);
        set @product_code := floor(1+rand()*3);
        set @tmp_data := from_unixtime(@begin_time+rand()*(@over_time-@begin_time));
        set @amount := floor(1000+rand()*9000);
        insert into temp values(@i,@cust_number,@product_code,@tmp_data,@tmp_data,@amount);
        set @i := @i+1;
    end while;
    -- clear sales_orders
    truncate table sales_order;
    insert into sales_order select null,customer_number,product_code,order_date,entry_date,order_amount from temp;
    commit;
    drop table temp;
end$$
delimiter ;
call proc_generate_saleorder();