L3 Hive操作

示例：

• 1.建表

create table t_dml (
	detail_id bigint,
	sale_date date,
	province string,
	city string,
	product_id bigint
	cnt double,
	amt double,
)row format delimited
fields terminated by ','; //列分隔符
1
2
3
4
5
6
7
8
9
10

create table t_product(
	product_id bigint,
	product_name string,
	category_id bigint,
	category_name string,
	price double
)row format delimited
fields terminated ',';
1
2
3
4
5
6
7
8

• 2.加载数据

load data local inpath '/opt/data/t_dml.csv' into table t_dml;
load data local inpath '/opt/data/t_product.csv' into table t_product;
1
2

• 3.常见SQL语句

查询销售记录的时间段：

select max(sale_date),min(sale_date) form t_dml;
1

查询各商品类别的总销售额

select t.category_name, sum(t.amt) as total_money
from
(select a.product_id, a.amt, b.category_name
from t_dml a
join t_product b
on a.product_id == b.product_id
) t
group by t.category_name;
1
2
3
4
5
6
7
8

查询销售量排行榜

select a.product_name, t.cnt_total, rank() over(order by t.cnt_total desc) as rk
(select product_id, sum(cnt) as cnt_total
from t_dml
group by product_id
order by cnt_total desc
limit 10
) t
join t_product a
on a.product_id == t.product_id;
1
2
3
4
5
6
7
8
9

想知道各个市县的购买力，同时自己的商品在哪个地区最热卖，通过创建中间表，优化查询

• 创建中间表

create table t_city_amt(
	province string,
	city string,
	total_money double
);
create table t_city_prod
(
	province string,
	city string,
	product_id bigint,
	product_name string,
	category_id bigint,
	cnt bigint
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 插入数据

insert into t_city_amt
select province,city,sum(amt)
form t_dml 
group by province,city
1
2
3
4

insert into t_city_prod
select t.province, t.city, t.product_id, t.product_name, sum(t.cnt)
from 
(
select a.product_id, b.product_name, a.cnt, a.province, a.city
form t_dml a join t_product b
on a.product_id = b.product_id
) t
group by t.province, t.city, t.product_id, t.product_name
1
2
3
4
5
6
7
8
9

• 基于中间表进行查询

select
form 
(
	select province, city,  product_id, product_name,
	from t_city_prod
) t

1
2
3
4
5
6
7