sql专场练习（二）（11-15）

第十一题

生成以下结果

dt   　　   win lose
2005-05-09  1   3 
2005-05-10  1   2 

数据 

create table sql2_11(
    dt string,
    result string
)row format delimited
fields terminated by " ";
load data local inpath "/home/homedata/sql2/sql2_11.txt" into table sql2_11;
select * from sql2_11;

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

select dt,
       sum(case result when "win" then 1 else 0 end)  win,
       sum(case result when "lose" then 1 else 0 end) lose
       from sql2_11 group by dt;

结果

第十二题

将结果变成下面这种

1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass

数据

create table sql2_12(
    courseid int,
    coursename string,
    score int
)row format delimited
fields terminated by " ";
load data local inpath "/home/homedata/sql2/sql2_12.txt" into table sql2_12;


1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

sql

select *,case when score<60 then "fail" else "pass" end mark from sql2_12;

结果

第十三题

结果转换

year m1  m2  m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

数据

create table sql2_13(
    `year` int,
    `month` int,
    amount string
)row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
  'input.regex'='(\\d+)\\s+(\\d+)\\s+(.+?)'
);
load data local inpath "/home/homedata/sql2/sql2_13.txt" into table sql2_13;


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

sql

select `year`,
       sum(case `month` when 1 then amount else null end) m1,
       sum(case `month` when 2 then amount else null end) m2,
       sum(case `month` when 3 then amount else null end) m3,
       sum(case `month` when 4 then amount else null end) m4
       from sql2_13 group by `year`;

结果 

第十四题

学生表 如下:
自动编号   学号  姓名 课程编号 课程名称 分数
1     2005001 张三   0001   数学   69
2     2005002 李四   0001   数学   89
3     2005001 张三   0001   数学   69
删除除了自动编号不同, 其他都相同的学生冗余信息

数据

create table sql2_14(
    id int,
    stu_id int,
    name string,
    course_id int,
    course_name string,
    score double
)row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
  'input.regex'='(\\d+)\\s+(\\d+)\\s+(.+?)\\s+(\\d+)\\s+(.+?)\\s+([0-9]+\\.?[0-9]*)'
);
load data local inpath "/home/homedata/sql2/sql2_14.txt" into table sql2_14;


1     2005001 张三   0001   数学   69
2     2005002 李四   0001   数学   89
3     2005001 张三   0001   数学   69

sql

with t1 as (
    select min(id) id  from sql2_14 group by stu_id, name, course_id, course_name, score
)
select * from sql2_14 where not exists (select * from t1 where sql2_14.id = t1.id);

此时已经查到了应该删除的数据，将select 换成delete 使用delete的语法即可

第十五题

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

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

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

create table sql2_15(
    user_id int,
    order_id int,
    pay_time string,
    order_amount decimal(10,2)
)row format delimited
fields terminated by "\t";

没有数据 将pay_time 看作 2024-10-10 格式

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

select pay_time,count(distinct user_id ) count_user 
    from sql2_15 where pay_time >= add_months(`current_date`(),1) 
             group by pay_time order by count_user desc limit 3;

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

with t1 as (
    select last_value(order_id) over (partition by user_id order by pay_time) last_order_id
    from sql2_15 where pay_time = date_sub(`current_date`(),1)
)select user_id,order_id,order_amount from sql2_15 where exists (select * from t1 where last_order_id = order_id);