论坛分析

create database if not exists shopxo;

use shopxo;

create table if not exists goods (

    id int,

    title string,

    price double,

    views int,

    sales int,

    stock int

) row format delimited

fields terminated by ','

;

load data local inpath '/root/college020/goods.txt' overwrite into table goods;

select * from goods limit 10;

insert overwrite local directory '/root/college023/'

row format delimited fields terminated by '\t'

select * from goods

where price is null

;

create table goods1 as

select * from goods

where title not like '%连衣裙%' or title not like '%女士%' and

price is not null

;

insert overwrite local directory '/root/college024/'

row format delimited fields terminated by '\t'

select count(*) from goods1;

insert overwrite local directory '/root/college025/'

row format delimited fields terminated by '\t'

select distinct title,price from goods1

order by price desc

limit 3

;

insert overwrite local directory '/root/college026/'

row format delimited fields terminated by '\t'

select split(title," ")[0] as brand,count(*) as brand_count from goods1

group by split(title," ")[0]

order by brand_count desc

limit 10

;

insert overwrite local directory '/root/college027/'

row format delimited fields terminated by '\t'

select perona,count(*) as p_count from goods1

lateral view explode(split(title," "))brand as perona

where title rlike ""

group by perona

order by p_count desc

limit 6

;