Hive中的窗口函数你了解吗？一文带你解决开窗函数的疑惑我们知道Hive是一个hadoop的数据仓库工具,它可以将hdf

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第26天，点击查看活动详情

我们知道Hive是一个hadoop的数据仓库工具,它可以将hdfs上的结构化的数据映射成一张表,并提供了类sql(HQL)语法来分析数据。所以本文着重讲解一下窗口函数的知识与应用。

select的真实含义

不管是写sql，还是hql，首先需要用到select，那么select的真实含义是什么呢？

其实，select就相当于‘打印’，拿python举例说就是‘print’,只不过Python的语法和逻辑基本一致，例如：print("Hello World")，这使得写代码变得相对容易。但是MySQL的书写顺序和执行逻辑，却是不一致的，所以这就是很多人写不出很好的SQL代码原因。

所以首先要记住一句话：把MySQL中的select，看成Python中的 print。

Hive窗口函数

1. 窗口函数

其实窗口函数就是高阶函数。
开窗函数在mysql 5.6，5.7中是没有的，5.8里面有了，但是功能是收费的，而hive中的窗口函数是一直免费开源的。

主要包括以下函数：
（1）窗口函数

lead
lag
first_value
last_value

（2）聚合函数

max
min
sum
avg
count

（3）排名分析函数

rank
row_number
dense_rank
ntile

2. 窗口函数的语法

（1）语法：函数+窗口；
其中函数：代表计算的逻辑，窗口：表示函数的计算范围。计算流程就是把窗口数据给到函数做计算，窗口函数是一行一行走的

（2）书写形式：函数 + over([partition by ...] [order by ...] [窗口子句])；

over：表示开窗默认有一个窗口大小就是所有数据；
partition by：表示根据字段再划分一个细窗口，相同字段进入同一个细窗口里面，每个窗口之间相互独立，窗口子句对于每个细窗口独立生效；
order by：表示窗口内按什么排序。如果只有over，表示直接最大窗口排序；如果有partition by，那么每个细窗口单独排序

（3）窗口函数本身也有执行顺序，如下：

over
partition
order by
窗口子句
函数

（4）窗口子句默认值

当有 order by 但是缺少窗口子句时范围是上无边界到当前行
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
当 order by 和窗口子句都缺少时范围上无边界到下无边界
ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

demo

假设我有一张数据表business，表结构数据如下，

需求1，查询在2017年4月份购买过的顾客及总人数
开窗函数第一种（上无边界到下无边界）

 select
     name,
     count(*)over()
 from 
     business
 where date_format(orderdate,'yyyy-MM')='2017-04'
 group by name；

开窗函数第二种（上无边界到当前行）

select
    name,
    count(*) over(rows between UNBOUNDED PRECEDING and current row)
from 
    business
where substring(orderdate,1,7)='2017-04'
group by name；

从这个两个不同的结果中，我们可以看出不同的窗口函数的限定范围，最后打印出的结果也不同。

需求2，查询在购买过的顾客及累加人次

select 
 name,
 orderdate,
 cost,
 count(*)over(rows between  UNBOUNDED PRECEDING and current row)
 from business;

需求3，查询顾客的购买明细及月购买总额

select
 name,
 orderdate,
 cost,
 sum(cost)over(partition by month(orderdate))
from business