今天我对大型数据库练习题进行了复习,
相关sql代码如下:
create database db001;标签:总结,string,nbr,每日,sale,day,table,id From: https://www.cnblogs.com/baijingyun/p/17828662.html
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);