2022.10.19期中

1.数据导入

 

load data local inpath '/opt/module/ceshi.csv' into table data;

 

2.数据清洗

 

insert overwrite table sale2
select date_add('2022-10-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;

 

3.统计sql语句

 

insert into a1 select collect_set(type)[0],id,count(*) as total from data group by id order by total desc limit 10;

create table if not exists tb_ip
comment "ip"
as
select
ip,
type,
id,
count(*) as paixu from city1 group by ip,type,id
order by paixu desc;

create table if not exists c3
comment "traffic"
as
select
type,
id,
sum(traffic) as liuliang from data
group by type,id
order by liuliang desc
limit 10;

 

4.可视化 ：fineBI