# hive练习题（商店市场调查）

### 建表：

store_details表

create external table store_details(
store_id INT,
store_name STRING,
employee_number INT
)
row format delimited
fields terminated by ','

load data local inpath '/root/kb08/hive/store/store_details.csv' into table store_details;


store_review表

create external table store_review(
transaction_id BIGINT,
store_id INT,
review_score INT
)
row format delimited
fields terminated by ','

load data local inpath '/root/kb08/hive/store/store_review.csv' into table store_review;


transaction_details表

create external table transaction_details(
transaction_id BIGINT,
customer_id BIGINT,
store_id INT,
price DOUBLE,
product STRING,
date STRING,
time STRING
)
row format delimited
fields terminated by ','

load data local inpath '/root/kb08/hive/store/transaction_details.csv' into table transaction_details;


customer_datails表

create table if not exists customer_datails(
customer_id INT,
first_name STRING,
last_name STRING,
email STRING,
gender STRING,
country string,
language STRING,
job STRING,
credit_type STRING,
credit_type STRING,
credit_no STRING
)
row format delimited fields terminated by ','
lines terminated by '\n'

load data local inpath '/root/kb08/hive/store/customer_datails.csv' into table customer_datails;


### 1、找出顾客最常用的信用卡

select credit_type from (
select credit_type,dense_rank() over(order by num DESC) rnk from (
select credit_type,count(1) num from customer_details group by credit_type order by num DESC
)T
)T
where rnk=1


### 2、找出客户资料中排名前五的的职位名称

select job from(
select job,dense_rank() over(order by num DESC) rnk from(
select job,count(1) num from customer_datails group by job order by num DESC
)T
)T
where rnk<=5


### 3、在美国女性最常用的信用卡

select credit_type from(
select credit_type,dense_rank() over(order by num DESC) rnk from (
select credit_type,count(1) num from customer_datails
where country='United States' and gender='Female'
group by credit_type order by num DESC
)T
)T
where rnk=1


### 4、按性别和国家进行客户统计/国家/工作/邮箱/语言/信用卡

select country,gender,concat_ws(',',collect_set(dn)) hdns from(
select country,gender,dn from(
select country,gender,dn,enum,rank() over(partition by country,gender) rnk from(
select country,gender,dn,count(1) enum from(
select country,gender,regexp_extract(email,'(\\w+)@(.+)',2) dn
from customer_datails
)T
group by country,gender,dn
)T
)T
where rnk=1
)T
group by country,gender


### 5、计算每月总收入

select month(to_date(regexp_replace(date,'/','-'))) month,sum(price) from transaction_details group by month(to_date(regexp_replace(date,'/','-'))) order by month


### 6、计算每个季度的总收入

select quarter,sum(price) from
(select ceil(month(to_date(regexp_replace(date,'/','-')))/3) quarter,price from transaction_details)r1
group by quarter


### 7、按年计算总收入

select year(to_date(regexp_replace(date,'/','-'))) year,sum(price) from transaction_details group by year(to_date(regexp_replace(date,'/','-'))) order by year


### 8、按工作日计算总收入

	周日：1
周一：2
周二：3
周三：4
周四：5
周五：6
周六：7
select dayofweek,sum
from
(select dayofweek(to_date(regexp_replace(date,'/','-'))) dayofweek,sum(price) sum
from transaction_details
group by dayofweek(to_date(regexp_replace(date,'/','-'))))r1
where dayofweek>=2 and dayofweek<=6


### 9、按时间段计算总收入

select cast(substr(time,0,instr(time,':')-1) as INT) hour,sum(price) sum_prive from transaction_details group by substr(time,0,instr(time,':')-1) order by hour


### 10、按时间段计算平均消费

select cast(substr(time,0,instr(time,':')-1) as INT) hour,avg(price) avg_price from transaction_details group by substr(time,0,instr(time,':')-1) order by hour


### 11、按工作日计算平均消费

select dayofweek,sum
from
(select dayofweek(to_date(regexp_replace(date,'/','-'))) dayofweek,avg(price) avg_price
from transaction_details
group by dayofweek(to_date(regexp_replace(date,'/','-'))))r1
where dayofweek>=2 and dayofweek<=6


### 12、计算年、月、日的交易总数

with
r1 as (select year(to_date(regexp_replace(date,'/','-'))) time,count(transaction_id) from transaction_details group by year(to_date(regexp_replace(date,'/','-'))) order by time DESC),
r2 as (select month(to_date(regexp_replace(date,'/','-'))) time,count(transaction_id) from transaction_details group by month(to_date(regexp_replace(date,'/','-'))) order by time DESC),
r3 as (select day(to_date(regexp_replace(date,'/','-'))) time,count(transaction_id) from transaction_details group by day(to_date(regexp_replace(date,'/','-'))) order by time DESC)
select * from r1 union all select * from r2 union all select * from r3


### 13、找出交易量最大的10个客户

select customer_id,num,rnk from
(select customer_id,dense_rank() over(order by num DESC) rnk from
(select customer_id,count(1) num from transaction_details group by customer_id)r1)r2
where rnk<=10


### 14、找出消费最多的前10位顾客

select customer_id,,sum_pricernk from
(select customer_id,dense_rank() over(order by sum_price DESC) rnk from
(select customer_id,sum(price) sum_price from transaction_details group by customer_id)r1)r2
where rnk<=10


### 15、统计该期间交易数量最少的用户

select customer_id,rnk from
(select customer_id,dense_rank() over(order by num) rnk from
(select customer_id,count(1) num from transaction_details group by customer_id)r1)r2
where rnk=1


### 16、计算每个季度的独立客户总数

select quarter,count(customer_id) num from(
select case when month<=3 then 1 when month<=6 then 2 when month<=9 then 3 else 4 end quarter,customer_id from
(
select month(to_date(regexp_replace(date,'/','-'))) month,customer_id from transaction_details
)r1 group by month,customer_id)r2 group by quarter


### 17、计算每周的独立客户总数

select weekofyear,count(customer_id) num from
(select weekofyear,customer_id from
(select weekofyear(to_date(regexp_replace(date,'/','-'))) weekofyear,customer_id from transaction_details
)r1 group by weekofyear,customer_id)r2 group by weekofyear


### 18、计算整个活动客户平均花费的最大值

select customer_id,avg_price,rnk from
(select customer_id,avg_price,dense_rank() over(order by avg_price DESC) rnk from
(select customer_id,avg(price) avg_price from transaction_details group by customer_id)r1)r2 where rnk=1


### 19、统计每月花费最多的客户

select month,customer_id,sum_price,rnk
from
(
select month,customer_id,sum_price,dense_rank() over(partition by month order by sum_price DESC) rnk
from
(
select month,customer_id,sum(price) sum_price
from
(select month(to_date(regexp_replace(date,'/','-'))) month,customer_id,price from transaction_details
)r1 group by month,customer_id
)r2)r3
where rnk=1


### 20、统计每月访问次数最多的客户

select month,customer_id,rnk from
(
select month,customer_id,dense_rank() over(partition by month order by num DESC) rnk from
(
select month,customer_id,count(transaction_id) num from
(
select
month(to_date(regexp_replace(date,'/','-'))) month,
customer_id,transaction_id from
transaction_details
)r1
group by month,customer_id
)r2)r3
where rnk=1


### 21、按总价找出最受欢迎的5种产品

select product,sum_price,rnk from
(
select product,sum_price,dense_rank() over(order by sum_price DESC) rnk from
(
select product,sum(price) sum_price from transaction_details group by product
)r1)r2
where rnk<=5


### 22、根据购买频率找出最畅销的5种产品

select product,num,rnk from
(
select product,num,dense_rank() over(order by num DESC) rnk from
(
select product,count(transaction_id) num from transaction_details group by product
)r1)r2
where rnk<=5


### 23、根据客户数量找出最受欢迎的5种产品

select product,num,rnk from
(
select product,num,dense_rank() over(order by num DESC) rnk from
(
select product,count(customer_id) num from transaction_details group by product
)r1)r2
where rnk<=5


### 24、按客流量找出最受欢迎的商店

select store_id,num,rnk from
(select store_id,num,dense_rank() over(order by num DESC) rnk
from(
select store_id,count(transaction_id) num from transaction_details group by store_id
)r1)r2 where rnk=1


### 25、根据顾客消费价格找出最受欢迎的商店

select store_id,sum(price) sum_price from transaction_details group by store_id order by sum_price DESC limit 1

select store_id,sum_price,rnk from
(
select store_id,sum_price,dense_rank() over(order by sum_price) rnk from
(
select store_id,sum(price) sum_price from transaction_details group by store_id
)r1)r2
where rnk=1


### 26、根据顾客交易情况找出最受欢迎的商店

select store_id,rnk from
(select store_id,dense_rank() over(order by num) rnk from
(select store_id,count(transaction_id) num from transaction_details group by store_id)r1)r2
where rnk =1


### 27、根据商店和唯一的顾客id获取最受欢迎的产品

select store_id,product,rnk from
(select store_id,product,dense_rank() over(partition by store_id order by num DESC) rnk from
(select store_id,product,count(distinct customer_id) num from transaction_details group by store_id,product)r1)r2 where rnk = 1


### 28、获取每个商店的员工与顾客比

select S.store_id,customer_num,employee_number,customer_num/employee_number as % from
(
select store_id,count(customer_id) customer_num from transaction_details group by store_id
)r1
inner join store_details S
on S.store_id=r1.store_id


### 29、按年和月计算每家店的收入

select store_id,year,month,sum(price) sum_price from
(
select store_id,
year(to_date(regexp_replace(date,'/','-'))) year,
month(to_date(regexp_replace(date,'/','-'))) month,price
from transaction_details
)r1
group by store_id,year,month


### 30、按店铺制作总收益饼图

select store_id,sum(price) sum_price from transaction_details group by store_id


### 31、找出每个商店最繁忙的时间段

select store_id,hours,sum,rnk
from
(select store_id,hours,sum,dense_rank() over(partition by store_id order by sum DESC) rnk
from
(select
store_id,
case when hour<=6 then 1 when hour<=12 then 2 when hour<=18 then 3 else 4 end hours,
sum(num) sum
from
(select
store_id,
cast(substr(time,0,instr(time,':')-1)as INT) hour,
count(transaction_id) num
from
transaction_details
group by store_id,cast(substr(time,0,instr(time,':')-1)as INT)
)r1
group by
store_id,case when hour<=6 then 1 when hour<=12 then 2 when hour<=18 then 3 else 4 end)r2
)r3
where rnk=1


### 32、找出每家店的忠实顾客

select store_id,customer_id,rnk from
(
select store_id,customer_id,dense_rank() over(partition by store_id order by num DESC) rnk from
(
select store_id,customer_id,count(*) num from transaction_details group by store_id,customer_id
)r1)r2
where rnk=1


### 33、根据每位员工的最高收入找出明星商店

select r3.store_id,sum_price,employee_number,wage,rnk from
(select r2.store_id,sum_price,employee_number,wage,dense_rank() over(order by wage DESC) rnk from
(select S.store_id,sum_price,employee_number,(sum_price/employee_number)as wage from
(select store_id,sum(price) sum_price from transaction_details group by store_id)r1
inner join store_details S
on r1.store_id=S.store_id)r2)r3 where rnk=1


### 34、在ext_store_review中找出存在冲突的交易映射关系

select transaction_id from transaction_details group by transaction_id having count(1)>1


### 35、了解客户评价的覆盖率

select store_id,count(1) from v_store_review group by store_id


### 36、根据评分了解客户的分布情况

select store_id,review_score,count(1) from v_store_review group by store_id,review_score


### 37、根据交易了解客户的分布情况

select store_id,customer_id,count(1) from transaction_details group by store_id,customer_id


### 38、客户给出的最佳评价是否总是同一家门店

select store_id,count(1) from
(select store_id,review_score from v_store_review where review_score='5')r1
group by store_id

01-13 316

04-13 3488
04-09 4773
09-24 1982
05-18 946
12-03 665
06-20 6510
10-20 1万+
10-23 3360
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试