Hive练习题11-15

11、第11题 

info 表
date result
2005-05-09 win
2005-05-09 lose 
2005-05-09 lose 
2005-05-09 lose 
2005-05-10 win 
2005-05-10 lose 
2005-05-10 lose 
如果要生成下列结果, 该如何写sql语句? win lose
2005-05-09  2   2 
2005-05-10  1   2 
答案： 
(1) select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date; 
(2) select a.date, a.result as win, b.result as lose from (select date, count(result) as result from info where result = "win" group by date) as a join (select date, count(result) as result from info where result = "lose" group by date) as b 
on a.date = b.date;

12、第12题

原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句
select courseid, coursename ,score ,if(score>=60, "pass","fail")  as mark from course

13、第13题

面试题：怎么把这样一个
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1  m2  m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 答案
select year, 
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

14、第14题

学生表 如下:
自动编号   学号  姓名 课程编号 课程名称 分数
1     2005001 张三   0001   数学   69
2     2005002 李四   0001   数学   89
3     2005001 张三   0001   数学   69
删除除了自动编号不同, 其他都相同的学生冗余信息A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

15、第15题

交易表结构为user_id(用户ID),order_id(订单ID),pay_time(付款时

间),order_amount(金额)

1. 写sql查询过去一个月付款用户量（提示：用户量需去重）最高的三天分别是哪几天？

2. 写sql查询昨天每个用户最后付款的订单ID及金额

select
date_format(pay_time,'%Y-%m-%d') days ,
count(distinct user_id)
from table
where pay_time>=date_sub(now(),interval 1 month) #过去一个月
group by date_format(pay_time,'%Y-%m-%d')
order by count(distinct user_id) desc
limit 3

思路：求最高的三天，肯定是先排序，后limit. 先求出每天的付款用户量，既然每天，那肯定要按天分

组了；按照题目要求过滤条件有：1.过去一个月 2.付款用户（即要排除未付款的用户），另外求用户量

需要去重，题目中也有提示，因为存在同一个用户每天有多笔消费记录的情况；返回排在前三的付款用

户量及对应的时间（天）

select
a.user_id, a.order_amount
from
(select
user_id, order_amount,
row_number() over(partition by user_id order by pay_time desc) as rank
from table
where date_format(pay_time,"%Y-%m-%d")=date_sub(curdate(),interval 1 day) #昨天
) as
awhere rank=1