Hive作业

　　最近学习Hive。实现了其中几个作业，如下。Hql语句没怎么调整格式，见谅。

Hive练习二中的题目

(1). 2017 年4 月1 日各个商品品牌的交易笔数，按照销售交易从多到少排序

select 
brand,
count(*) as totalCount 
from 
record 
join brand_dimension on record.bid = brand_dimension.bid 
where record.trancation_date= '2017-04-01' 
group by brand_dimension.brand
order by totalCount desc;

输出

+------------+-------------+--+
|   brand    | totalcount  |
+------------+-------------+--+
| SAMSUNG    | 2           |
| WULIANGYE  | 1           |
| PUMA       | 1           |
| OPPO       | 1           |
| DELL       | 1           |
+------------+-------------+--+

(2). 不同性别消费的商品类别情况(不同性别消费不同商品类别的总价)

select 
gender,
category, 
sum(price) as totalPrice
from record 
join user_dimension on record.uid = user_dimension.uid
join brand_dimension on record.bid = brand_dimension.bid
group by gender, 
category
order by 
gender,
category,
totalPrice;

输出

+---------+------------+-------------+--+
| gender  |  category  | totalprice  |
+---------+------------+-------------+--+
| M       | computer   | 252         |
| M       | food       | 429         |
| M       | sports     | 120         |
| M       | telephone  | 1669        |
+---------+------------+-------------+--+

Hive练习三中的题目

(1). 谁不是经理

select 
name 
from 
employees 
where 
size(subordinates)<=0;

输出

+-------------------+---------------+--+
|       name        | subordinates  |
+-------------------+---------------+--+
| Todd Jones        | []            |
| Bill King         | []            |
| Stacy Accountant  | []            |
+-------------------+---------------+--+
3 rows selected (0.092 seconds)

(2). 谁住在邮编比60500 大的地区

select name,address.zip from employees where address.zip> 60500;

输出

+-------------------+--------+--+
|       name        |  zip   |
+-------------------+--------+--+
| John Doe          | 60600  |
| Mary Smith        | 60601  |
| Todd Jones        | 60700  |
| Stacy Accountant  | 60563  |
+-------------------+--------+--+
4 rows selected (0.123 seconds)

(3). 联邦税超过0.15 的雇员

select 
name, 
deductions['Federal Taxes'] 
from 
employees 
where deductions['Federal Taxes'] > 0.15+1e-5;

输出

+---------------+---------------+--+
|     name      | federaltaxes  |
+---------------+---------------+--+
| John Doe      | 0.2           |
| Mary Smith    | 0.2           |
| Boss Man      | 0.3           |
| Fred Finance  | 0.3           |
+---------------+---------------+--+
4 rows selected (0.126 seconds)

(4). 谁住在Drive 或Par 街道

select 
name,
address 
from 
employees 
where address.street rlike '^.*(Drive|Par).*$';

输出

---------------+------------------------------------------------------------------------------+--+
|     name      |                                   address                                    |
+---------------+------------------------------------------------------------------------------+--+
| Boss Man      | {"street":"1 Pretentious Drive.","city":"Chicago","state":"IL","zip":60500}  |
| Fred Finance  | {"street":"2 Pretentious Drive.","city":"Chicago","state":"IL","zip":60500}  |
+---------------+------------------------------------------------------------------------------+--+
2 rows selected (0.218 seconds)

Hive练习十中的题目

1. 建表

创建并 使用 database tmall

create database tmall;
use tmall;

建表 product，格式 text

create table if not exists product(
item_id string,
pic_url string,
category string,
brand_id string,
seller_id string
)
row format delimited
fields terminated by ','
lines terminated by '
'
stored as textfile;

建表 review，格式 text

create table if not exists review(
item_id string,
user_id string,
feedback int,
feedback_time timestamp,
feedback_pic_url string
)
row format delimited
fields terminated by ','
lines terminated by '
'
stored as textfile;

建表 log_orc，格式 orc

# log as textfile
create table if not exists log(
item_id string,
user_id string,
action string,
action_time timestamp
)
row format delimited
fields terminated by ','
lines terminated by '
'
stored as textfile;

# log as orc
create table if not exists log_orc
like log
stored as orc;

2. 载入数据

#product
load data local inpath '/home/zkpk/test/tmall/tmall_product.csv' overwrite into table product;
#验证：select * from product limit 100;
#review
load data local inpath '/home/zkpk/test/tmall/tmall_review.csv' overwrite into table review;
#验证：select * from review limit 100;
#log
load data local inpath '/home/zkpk/test/tmall/tmall_log.csv' overwrite into table log;
#验证：select * from log limit 100;
insert into table log_orc select * from log;

3. 载入数据

• 热度前十名的商家（商品被浏览的最多）

  select 
  p.seller_id, 
  count(*) as click_count 
  from log_orc l 
  inner join product p on l.item_id = p.item_id 
  where l.action='click' 
  group by p.seller_id 
  order by click_count desc 
  limit 10;

  输出

  +--------------+--------------+--+
  | p.seller_id | click_count |
  +--------------+--------------+--+
  | s403 | 143 |
  | s190 | 116 |
  | s284 | 86 |
  | s161 | 78 |
  | s227 | 59 |
  | s61 | 59 |
  | s29 | 57 |
  | s82 | 45 |
  | s464 | 42 |
  | s261 | 42 |
  +--------------+--------------+--+

• 好评率(feedback=5为好评)低于60%的商品

  select 
  item_id, 
  count(case when feedback =5 then 1 else null end) as best, 
  count(1) as total, 
  count(case when feedback =5 then 1 else null end)/ count(1) as best_rate 
  from review 
  group by item_id 
  having count(case when feedback =5 then 1 else null end) < count(1) *0.6 ;

  输出

  +----------+-------+--------+----------------------+--+
  | item_id  | best  | total  |      best_rate       |
  +----------+-------+--------+----------------------+--+
  | 221      | 0     | 1      | 0.0                  |
  | 256      | 0     | 4      | 0.0                  |
  | 287      | 1     | 3      | 0.3333333333333333   |
  | 288      | 2     | 6      | 0.3333333333333333   |
  | 378      | 0     | 1      | 0.0                  |
  | 379      | 1     | 6      | 0.16666666666666666  |
  | 397      | 0     | 2      | 0.0                  |
  | 398      | 0     | 1      | 0.0                  |
  | 423      | 2     | 7      | 0.2857142857142857   |
  | 449      | 1     | 5      | 0.2                  |
  | 45       | 0     | 6      | 0.0                  |
  | 450      | 0     | 1      | 0.0                  |
  | 451      | 0     | 3      | 0.0                  |
  | 453      | 1     | 2      | 0.5                  |
  | 505      | 1     | 2      | 0.5                  |
  +----------+-------+--------+----------------------+--+

• 找出潜在购买用户（收藏了商品，但是没有购买）

  select distinct 
  m.item_id, 
  m.user_id 
  from log_orc m 
  left outer join log_orc s 
  on (m.item_id = s.item_id 
  and m.user_id = s.user_id 
  and s.action='alipay') 
  where m.action='collect' 
  and s.item_id is null;

  输出

  +------------+------------+--+
  | m.item_id  | m.user_id  |
  +------------+------------+--+
  | 152        | 3286       |
  | 24         | 3389       |
  | 242        | 39         |
  | 422        | 3423       |
  | 468        | 2727       |
  +------------+------------+--+