hive窗⼝函数分组排序并取第⼀个值_数据分析⾯试必备——
SQL窗⼝函数你会了吗？...
内推码WXWC5RD，⽤内推码投 前⾯插⼀句，字节跳动直播业务招聘数据分析师中！欢迎投递，欢迎备注“for 直播！”~
前⾯插⼀句，字节跳动直播业务招聘数据分析师中！欢迎投递，欢迎备注“for 直播！”~ 内推码WXWC5RD，⽤内推码投递简历，可以联系我帮助看进度、给你介绍字节跳动相关的信息哦~
12000+收藏（收藏是之前写过⼀篇sql的⽂章，⾯向基础的sql操作（⽆眠：数据分析⾯试必备——SQL你准备好了吗？），⽬前已经有12000+收藏
点赞的5倍，你们可真狠⼼哪），也可以看出众多同学对sql学习的热情。但是这篇⽂章的评论区也有很多声⾳说，想看sql的进阶知识（例如：窗⼝函数）担⼼只掌握基本的sql查询不能cover到⼯作的⽅⽅⾯⾯。所以我⼜来啦！
上篇sql基础主要讲了以下内容：
SQL查询-基础篇
本次sql主要会讲以下这四类常⽤函数：
SQL查询-函数篇
复杂的查询简单化，我有遇到⼩伙伴对排序很迷茫，或者是错位取数不知道怎么取。这些原因都是因为你不清楚这些函数可以帮助我们将复杂的查询简单化
hive sql写起来就会轻松很多了。hive⾥⾯有内嵌的函数可以直接使⽤，当你清楚的知道了函数都分别可以帮助你做到怎样的事情，hive sql写起来就会轻松很多了
读完本⽂，你会知道：
（1）hive sql进⾏查询过程中90%你觉得棘⼿的问题其实可以通过函数解决；
（2）如果你想学习hive sql⾥使⽤的函数，你应该学习哪些？
（3）这些函数分别可以怎样帮助我？具体怎么写呢？
OK，接下来让我们进⼊正题。(注：本⽂所有⽰例结果都经本⼈实际操作使⽤hive运⾏得出）
datediff是字符型函数⼀、查询分析的利器——窗⼝函数
开篇就得讲窗⼝函数，因为窗⼝函数有个很明显的特点：不知道它的时候，完全不知道怎么写sql取到⾃⼰想要的数；知道了以后，就恍然⼤悟：“原来可以这么写啊！”所以我给它起名叫，查询分析的利器。
1. 窗⼝排序
row_number/rank/dense_rank
窗⼝函数当然是这篇⽂章的重点了。窗⼝函数中，排序函数⼜是最常⽤到的。
窗⼝排序主要是指⾮全局排序，需要在某个维度下进⾏排序。进⼊这part之前，先建个临时表来帮助理解。例如说现在想看各个department内部cost最多的⼈，这时候不能全局order by了，该怎么取？
select
*,
row_number() over ( partition by department order by cost desc ) as row_number_result,
rank() over ( partition by department order by cost desc) as rank_result,
dense_rank() over (partition by department order by cost desc) as dense_rank_result
from
table
分组排序的结果
row_number() 则在排序相同时不重复，会根据顺序排序。
rank()排序相同时会重复，总数不会变 ，意思是会出现1、1、3这样的排序结果；
dense_rank() 排序相同时会重复，总数会减少，意思是会出现1、1、2这样的排序结果。
2. 分区最⼤/最⼩值
first_value/last_value取的是分组内排序后，截⽌到当前⾏第⼀个/最后⼀个值
select
*,
first_value(name) over (
PARTITION BY department
ORDER BY
cost desc
) as max_cost_user, ## 分组取每个组的最⼤值对应的⼈
first_value(name) over (
PARTITION BY department
ORDER BY
cost
) as min_cost_user ## 分组取每个组的最⼤值对应的⼈
from
table
最后就会得到每个组⾥的cost最多和最少的⼈：
分组取最⼤/最⼩值的结果
3. 累积百分⽐
cume_dist / sum() over
累积百分⽐的应⽤场景也很多，⽐如说，想看前XX%的⽤户贡献了XX%的总额。
这个地⽅需要两个函数的使⽤，1是XX%的⽤户，2是XX%的总额。
select
*,
cume_dist() OVER (
PARTITION BY department
ORDER BY
cost desc
)
as cum_dist,
sum(cost) OVER (
PARTITION BY department
ORDER BY
cost desc
)/sum(cost) OVER (PARTITION BY department) as s
from
table
where
department = 'A'
cume_dist返回的是⼩于等于当前值的⾏数/分组内总⾏数，但我倒序排的话，也就是⼤于等于了。sum()over是算的累积值的占⽐。
但A组这种⽆重复值的，结果是很好理解的。最后可以得到A部门的cost由⾼到低排序，XX%的⼈累积贡献了XX%多少。
PS. 不过我⽤sum()over的时候发现⼀个⼩问题，就是如果cost有两个相同的值，会出现他们的cume_dist返回值和sum()over返回值是⼀样的，并没有累积去算，⽐如说B组cost为100的两个⼈，他们是并列倒数第⼀，这个时候，cume_dist和sum over都返回的是1，这个部分我⽬前还没有特别明⽩这种情况怎么处理。继续查阅再看。
4. 错位
lead/lag函数
lead和lag函数，这两个函数⼀般⽤于计算差值，最适⽤的场景是计算花费时间。举个例⼦，有数据是每个⽤户浏览⽹页的时间记录，将记录的时间错位之后，进⾏两列相减就可以得到每个⽤户浏览每个⽹页实际花费的时间。
lead是⽤于统计窗⼝内往下第n⾏值，lag是⽤于统计窗⼝内往上第n⾏值。
虽然⽬前我们这个数据不是时间数据，也可以使⽤这个函数操作⼀下。例如说，现在计算按cost排序后，每个department的⼈他们的花费，以及和⽐他们花费排名更⾼⼀名的⼈的值，可以计算差值。
select *,
lead(cost) over(partition by department order by cost) next_cost
from table
⼆、必须掌握的⽇期函数
⽇期函数，主要是靠表⽰⽇期类型的花样繁多来让我们觉得复杂。个⼈觉得我们不需要记住太多细碎的⽇期函数，因为当我们取数据表中的
怎么转换、怎么加减，得到我们需要的⽇期。
数据时，⼀般都是存好了的⽇期或时间戳，我们只需要记得：⼏种常见的⽇期格式中，怎么转换、怎么加减，得到我们需要的⽇期。
⼏种常见的⽇期格式
1. ⽇期（2020-03-21 17:13:39）和unix时间戳（1584782175）之间怎么相互转换
当你存储的是⽇期，希望转化为UNIX时间戳时，使⽤unix_timestamp函数，命令格式：unix_timestamp(string date, string pattern) ，表⽰转换pattern格式的⽇期到时间戳
当你存储的是时间戳，希望转化为⽇期，使⽤from_unixtime函数，命令格式：from_unixtime(bigint unixtime, [string format])
## ⽇期转化为时间戳 ##
select unix_timestamp('2020-03-21 17:13:39')：得到 1584782019
select unix_timestamp('20200321 13:01:03','yyyyMMdd HH:mm:ss') 得到 1584766863
select unix_timestamp('20200321','yyyyMMdd') 得到 1584720000
## 时间戳转化为⽇期 ##
select from_unixtime (1584782175) 得到 2020-03-21 17:16:15
select from_unixtime (1584782175,'yyyyMMdd') 得到 20200321
select from_unixtime (1584782175,'yyyy-MM-dd')得到 2020-03-21
## ⽇期和⽇期之间，也可以通过时间戳来进⾏转换 ##
select from_unixtime(unix_timestamp('20200321','yyyymmdd'),'yyyy-mm-dd') 得到 2020-03-21
select from_unixtime(unix_timestamp('2020-03-21','yyyy-mm-dd'),'yyyymmdd')得到 20200321
需要注意的是：
unix_timestamp(string date)默认转换格式为"yyyy-MM-dd HH:mm:ss"的⽇期；如果格式不对，例如说你直接⽤
unix_timestamp('20200321')会得到NULL的~~
如果⾃⼰有特定希望转换的⽇期格式，需要使⽤unix_timestamp(string date, string pattern)转换pattern格式的⽇期。⽐如
说，unix_timestamp('20200321','yyyyMMdd') 就可以得到 1584720000了~
2. ⽇期（2020-03-21 17:13:39）怎么转换为想要的格式（2020-03-21）
可以直接使⽤to_date函数，也可以使⽤字符串提取函数。
select to_date('2020-03-21 17:13:39') 得到 2020-03-21
select substr('2020-03-21 17:13:39',1,10) 得到 2020-03-21
3. ⽇期之间怎么进⾏加减操作？
使⽤datesub/date_add/datediff函数
## 使⽤date_sub (string startdate, int days)得到开始⽇期startdate减少days天后的⽇期##
select date_sub('2012-12-08', 10) 得到 2012-11-28
## 使⽤date_add(string startdate, int days)得到开始⽇期startdate增加days天后的⽇期 ##
select date_add('2012-12-08', 10) 得到 2012-12-18
## 使⽤datediff(string enddate, string startdate)得到结束⽇期减去开始⽇期的天数 ##
select datediff('2012-12-08','2012-05-09') 得到 213

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。