10 手写HQL 第10题

1.用一条SQL语句查询出每门课都大于80分的学生姓名

name   kecheng   fenshu

张三    语文    81

张三    数学    75

李四    语文    76

李四    数学     90

王五    语文    81

王五    数学    100

王五    英语    90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)

B：select name from table group by name having min(fenshu)>80

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

A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

3.一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.

答：select a.name, b.name
from team a, team b
where a.name < b.name

4.面试题：怎么把这样一个
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

*********************************************************************
5.说明：复制表(只复制结构,源表名：a新表名：b) 

SQL: select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容)
ORACLE:create table b

As

Select * from a where 1=2

[<>（不等于）(SQL Server Compact)

比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。]

6. 

原表:
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

7.表名：购物信息

购物人      商品名称     数量

A            甲          2

B            乙          4

C            丙          1

A            丁          2

B            丙          5

……

给出所有购入商品为两种或两种以上的购物人记录

答：select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);

8.

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;

11 手写HQL 第11题

有一个订单表order。已知字段有：order_id(订单ID), user_id(用户ID),amount(金额), pay_datetime(付费时间),channel_id(渠道ID),dt(分区字段)。

1. 在Hive中创建这个表。

2. 查询dt=‘2018-09-01‘里每个渠道的订单数，下单人数（去重），总金额。

3. 查询dt=‘2018-09-01‘里每个渠道的金额最大3笔订单。

4. 有一天发现订单数据重复，请分析原因

create external table order(
    order_id int,
    user_id int,
    amount double,
    pay_datatime timestamp,
    channel_id int
)partitioned by(dt string)
row format delimited fields terminated by '\t';

select
    count(order_id),
    count(distinct(user_id))
    sum(amount)
from
    order
where dt="2019-09-01"

select
    order_id
    channel_id
    channel_id_amount
from(
    select
        order_id
        channel_id,
        amount,
        max(amount) over(partition by channel_id)
        min(amount) over(partition by channel_id)
        row_number()
        over(
            partition by channel_id
            order by amount desc
        )rank
    from
        order
    where dt="2019-09-01"
)t
where t.rank<4

订单属于业务数据，在关系型数据库中不会存在数据重复

hive建表时也不会导致数据重复，

我推测是在数据迁移时，迁移失败导致重复迁移数据冗余了

t_order订单表

order_id,//订单id

item_id, //商品id

create_time,//下单时间

amount//下单金额

t_item商品表

item_id,//商品id

item_name,//商品名称

category//品类

t_item商品表

item_id,//商品id

item_name,//名称

category_1,//一级品类

category_2,//二级品类

1. 最近一个月，销售数量最多的10个商品

select
    item_id,
    count(order_id)a
from 
    t_order
where
    dataediff(create_time,current_date)<=30
group by 
    item_id
order by a desc;

2. 最近一个月，每个种类里销售数量最多的10个商品

#一个订单对应一个商品 一个商品对应一个品类

with(
    select
        order_id,
        item_id,
        item_name,
        category
    from
         t_order
    join
        t_item
    on
        t_order.item_id = t_item.item_id
) t
select
    order_id,
    item_id,
    item_name,
    category,
    count(item_id)over(
        partition by category
    )item_count
from
     t
group by category
order by item_count desc
limit 10;

计算平台的每一个用户发过多少日记、获得多少点赞数

with t3 as(
    select * from 
    t1 left join t2 
    on t1.log_id = t2.log_id
)
select
    uid,//用户Id
    count(log_id)over(partition by uid)log_cnt,//
    count(like_uid)over(partition by log_id)liked_cnt//获得多少点赞数
from
    t3

处理产品版本号

1、需求A:找出T1表中最大的版本号

思路：列转行 切割版本号 一列变三列

主版本号  子版本号 阶段版本号

with t2 as(//转换
    select
        v_id v1,//版本号
        v_id v2 //主
    from
        t1
    lateral view explode(v2) tmp as v2
)
select //第一层 找出第一个
    v1,
    max(v2)
from 
    t2

1、需求A:找出T1表中最大的版本号

select
    v_id,//版本号
    max(split(v_id,".")[0]) v1,//主版本不会为空
    max(if(split(v_id,".")[1]="",0,split(v_id,".")[1]))v2,//取出子版本并判断是否为空，并给默认值
    max(if(split(v_id,".")[2]="",0,split(v_id,".")[2]))v3//取出阶段版本并判断是否为空，并给默认值
from
    t1

2、需求B：计算出如下格式的所有版本号排序，要求对于相同的版本号，顺序号并列：

select
    v_id,
    rank() over(partition by v_id order by v_id)seq
from
    t1