Hql的经典例题(详细分类)

参考：窗口函数详解https://www.icode9.com/content-4-140834.html参考：百度安全验证https://baijiahao.baidu.com/s?id=1711652301264801200&wfr=spider&for=pc

理论：

一、窗口函数over()和group by 的区别：

聚合函数作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。   group by 分组汇总会改变行数（默认返回每组中的第一行）；而partition by和rank()函数不会减少原表中的行数。

2、窗口函数的语法

over()用于指定分析函数工作时的数据窗口大小，这个数据窗口大小可能随着行的变化而变化。

窗口函数的基本语法：

 <窗口函数>  over(partition by <用于分组的列名>

                              order by <用于排序的列名>)

上述中窗口函数处可以放两类函数(当然还有其他的函数)

1）专用窗口函数：包括dense_rank(),rank(),row_number()等专用窗口函数

2）聚合函数：例如sum(),avg(),max(),min(),count()

3）位移函数：例如LEAD(列名，1,null)返回指定列的当前行的后一行内容, LAG(列名，1,null)返回指定列的当前行的前一行内容

【注意】：可以注意到专用窗口函数后面括号里是空的，什么也没有写，

                 但是聚合函数后面括号可不能为空，需要指定聚合的列名。

                 rank()、sum()等等这些函数叫做分析函数，而over()才叫做窗口函数;

【注】：因为窗口函数是对where或者group by子句处理后的结果进行的操作，所以窗口函数

              原则上只能写在select子句中。

4）分片函数：ntile(n)：将总的数据分为n片，返回第一片。一般用于取前20%等等。比如取总成绩的前20%， ntile(5) over(order by total_score) 。

3、窗口函数的作用范围

看以下的例子：

SUM(sale_sum) OVER() as total_sale_sum ：其中sum是求和，over()中不添加参数，则默认对所有数据进行求和

SUM(sale_sum) OVER(PARTITION BY product_category) as category_sale_sum：求当前产品所在类别的总销售额

SUM(sale_sum) OVER(PARTITION by product_category ORDER BY sale_sum DESC) as order_sale_sum：求各产品类别的累计销售额

常见案例：

一、分组求最大

1.求每个点击url的最新的那条信息(字节面试题)

字段：url city login_time type...（点击url，城市，点击时间，类型等等）

方式一：先查询出每一个url的最大(最新)时间是多少，在通过where .. in来限制输出的行

select * from table where login_time in(

select max(login_time) from table group by url);

方式二:把每一组url时间排名最大(最新)的记录筛选出来

select * from (
select *,row_number() over(partition by url order by login_time desc) as rn) as t1
where t1.rn = 1;

注意：这种方式的外层的select * 不仅把所有表的字段返回了，而且还返回了rn排名字段。

二、两个表分组求最大

1.求每个部门工资最高的员工

select Department.name AS 'Department',Employee.name AS 'Employee',Salary
from Employee joinDepartment on Employee.DepartmentId = Department.Id
where (Employee.DepartmentId , Salary) in(
select
DepartmentId, max(Salary)
from Employee group by DepartmentId
);

三、分组求Top N

1.求每个部门工资前三高的员工

select temp.Department, temp.Employee, temp.Salary
from 
(
select d.Name as 'Department', e.Name as 'Employee', e.Salary as 'Salary',
dense_rank() over(partition by e.departmentId order by e.Salary desc ) a
from employee e
join department d
on e.departmentid = d.id
) temp
where temp.a <4;

2.(1)求每门学科成绩排名   (2)求每门学科成绩前三名学生的信息 (3)求每个学生总成绩排名

（1）求每门学科成绩排名

select *, row_number() over(partition by subject order by score desc) as rn from score;

（2）求每门学科成绩前三名学生的信息

select * from (select *, row_number() over(partition by subject order by score desc) 
as rn from score) t1 where t1.rn<=3;

（3）求每个学生的各科总成绩的排名

思路:先按照学生name分组求总成绩，然后使用row_number() over()窗口函数再开窗，此时注意over()里面不用写partition by了，因为要对全局进行排序、排名，加了partition by 就是对组内进行排序、排名了。
 

select name, t1.total_score, row_number() over(order by t1.total_score desc) as rn from (

   select name,sum(score) as total_score from score group by name)t1;

(4) 求每门课程都大于平均成绩的学生

select
    t2.uid
from(
    select
        t1.uid,
        t1.subject_id,
        t1.score,
        t1.avg_score,
        if(t1.score > t1.avg_score,0,1) as flag --每科成绩大于某科平均分
    from(
        select
            uid,
            subject_id,
            score,
            cast(avg(score) over(partition by subject_id) as int) as avg_score
        from
            score
        )t1
    )t2
group by t2.uid
having sum(t2.flag)=0;  -- 只要sum(flag)=0，说明全部的课程都大于平均值

四、用row_number()和over()求解分组Top N问题 以及 求⽤户连续登录最长天数

1.求每门学科成绩前三名学生的信息

 见上面

2.求用户连续登陆最长天数

思路：

（1）（重要）用每一行的日期减去按顺序（升序）排列的1,2,3,4,5…（这些数字用排名函数配合开窗函数生成），连续的日期相减之后的值是一样的（因为日期也是按照升序排列的），否则会不一样，出现断层。这些一样的数量就是连续登陆的天数。

其中，date_sub('2022-03-20', 2) = 2022-03-18。

（2）就是求和了，同时按uid和dt1进行分组，求出每个uid相同dt1的数量

 

 （3）求最大值了，即连续登陆的最大天数

 

select B.uid uid,max(B.num) cnt_days
from
(select A.uid,A.dt1,count(A.dt1) num
from
(select uid, date_sub(dt,row_number() over(partition by uid order by dt)) as dt1
from user_login) A
group by A.uid,A.dt1) B
group by B.uid;

五、case when ... then ...else ... end的使用

1.求每个国家男、女人数各多少

select country, 
       sum(case when p.sex='1' then p.population else 0 end) as '男',
       sum(case when p.sex='2' then p.population else 0 end) as '女'
from t_b_population p
group by country;

 六、登录表table：t_login: user_id,login_time,login_ip

 1.查询每个用户最近登录的那条信息(类似于查询每个url最新的那条信息)

select * from (
   select * ,row_number() over(partition by user_id order by login_time desc) as rn from table
) t1
where t1.rn = 1;

注释：如果只有三个字段，那么可以使用user_id，login_time，login_ip来替换* 位置。

注意：(1) 第一种方式的外层的select * 不仅仅把所有字段都返回了，而且还包含rn排名字段。

           (2) 需要外面嵌套select *再 where过滤的原因：如果只是用select * ,row_number() over(partition by user_id order by login_time desc) as rn from table where rn= 1是错误的，因为where中不能使用select中的别名。

另一种方式：

select * from table where login_time in (
select max(login_time) from table);

七、利用explode()进行行转列

select name,category_type from movie_info lateral view explode(split(category,','))
 temp_table as category_type;

解析：explode(col_name)：将hive中的⼀列的array或者map分成多⾏，如果是字符串类型则利⽤split将其变成数组；表movie_info与虚表temp_table利⽤lateral view进⾏相关联，其中temp_table表中的字段为category_type。   

总结：因为炸裂开之后需要与原表的字段相关联，所以要使用lateral view进行侧写，相当于：explode将原有的array型的字段炸裂形成一长列，该单独的列形成一个临时表，之后再于原表进行相关联。

八、列转行

1.把相同id的订单号按照逗号转为一行

select user_id,
concat_ws(',',collect_list(order_id)) as order_value
from table
group by user_id;

解析：collect_list(字段):此函数只接受基本数据类型的字段，主要是将字段进行不去重汇总，产生array类型字段。而collect_list(id)的结果不去重。

concat_ws(',',string 1, string 2,...)：使用逗号将各个字段进行拼接；

九、其他面试真题

1、表sal_sum，每次统计前三个月的销售额
month        sal
1             5
2             3
3             5
4             7

.......

12 6

1+2+3，2+3+4，3+4+5 。。。

SELECT
MONTH,
SUM(sal) over(ORDER BY MONTH ROWS BETWEEN 2 preceding AND current ROW) sum_3
FROM test1

解析：rows between：限制窗口的大小；其中，2 preceding指前两行，current row 指当前行，unbounded preceding 起始行，unbounded following 结尾行。

2、求第二高的薪水

力扣

 注意：（1）要求第二高，所以要排序之后去重；（2）要求第二高的，所以要通过limit语句限制输出哪一行。

select (
    select distinct Salary 
    from Employee
    order by Salary desc
    limit 1 offset 1 ) 
as SecondHighestSalary;

 解析：limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据。而 limt i,n：是跳过i取n条数据。limit 20,20 是跳过20条取20条数据，即从21开始到40。