每日总结

今天我对大型数据库练习题进行了复习，

 

相关sql代码如下：

create database db001;
create table sale_sample(
    day_id string comment '日期编号',
    sale_nbr string comment '卖出方代码',
    buy_nbr string comment '买入方代码',
    cnt string comment '数量',
    round string comment '金额'
)row format delimited
fields terminated by ',';

load data inpath '/sale_sample/data/104W' into table sale_sample;
drop table sale_sample;

insert overwrite table sale_sample select date_add('2021-09-00',cast(day_id as int)) as day_id,sale_nbr as sale_nbr,buy_nbr as buy_nbr,cnt as cnt,round as round from sale_sample;

select * from sale_sample tablesample ( 600 rows );

create table jichang(day_id string,sale_nbr string,cnt string,round string) row format delimited fields terminated by ',';

 insert into table jichang select day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sale_sample group by sale_nbr,day_id having sale_nbr like 'C%';

create table dailishang(day_id string,sale_nbr string,cnt string,round string) row format delimited fields terminated by ',';

insert into table dailishang select day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sale_sample group by sale_nbr,day_id having sale_nbr like 'O%';

create table dailishang_mc(day_id string,sale_nbr string,count int) row format delimited fields terminated by ',';

insert into table dailishang_mc select day_id,sale_nbr,count(*) as count from sale_sample where sale_nbr like 'O%' group by sale_nbr,day_id;

create table dailishang_mr(day_id string,sale_nbr string,count int) row format delimited fields terminated by ',';

insert into table dailishang_mr select day_id,buy_nbr as sale_nbr,count(*) as count from sale_sample where buy_nbr like 'O%' group by buy_nbr,day_id;

create table dailishang_h(day_id string,sale_nbr string,sale_number int) row format delimited fields terminated by ',';

create table daiout(day_id string,sale_nbr string,cnt int,round int) row format delimited fields terminated by ',';

create table daiin(day_id string,buy_nbr string,cnt int,round int) row format delimited fields terminated by ',';

insert into table daiout select day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sale_sample where sale_nbr like 'O%' group by day_id,sale_nbr;

create table daili(day_id string,sale_nbr string,incnt int,inround int,outcnt int,outround int,lirun int) row format delimited fields terminated by ',';

insert into table daili select daiout.day_id as day_id,daiout.sale_nbr as sale_nbr,daiin.cnt as incnt,daiin.round as inround,daiout.cnt as outcnt,daiout.round as outround,daiout.round-daiin.round as lirun from daiout join daiin on (daiin.buy_nbr=daiout.sale_nbr);