hive 窗口函数/分析函数/开窗函数 over （以用户访问流量为例）

╰+攻爆jí腚メ 2023-06-16 13:57 19阅读 0赞

### hive 窗口函数/分析函数/开窗函数 over （以用户访问流量为例） ###

题目：  
**现有这么一批数据，现要求出：  
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数**

三个字段的意思：  
用户名，月份，访问次数

A,2015-01,5
    A,2015-01,15
    B,2015-01,5
    A,2015-01,8
    B,2015-01,25
    A,2015-01,5
    A,2015-02,4
    A,2015-02,6
    B,2015-02,10
    B,2015-02,5
    A,2015-03,16
    A,2015-03,22
    B,2015-03,23
    B,2015-03,10
    B,2015-03,11

**最后结果展示:**

<table> 
 <thead> 
  <tr> 
   <th align="center">用户</th> 
   <th align="right">月份</th> 
   <th align="left">最大访问次数</th> 
   <th align="left">总访问次数</th> 
   <th align="left">当月访问次数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">A</td> 
   <td align="right">2015-01</td> 
   <td align="left">33</td> 
   <td align="left">33</td> 
   <td align="left">33</td> 
  </tr> 
  <tr> 
   <td align="center">A</td> 
   <td align="right">2015-02</td> 
   <td align="left">33</td> 
   <td align="left">43</td> 
   <td align="left">10</td> 
  </tr> 
  <tr> 
   <td align="center">A</td> 
   <td align="right">2015-03</td> 
   <td align="left">38</td> 
   <td align="left">81</td> 
   <td align="left">38</td> 
  </tr> 
  <tr> 
   <td align="center">B</td> 
   <td align="right">2015-01</td> 
   <td align="left">30</td> 
   <td align="left">30</td> 
   <td align="left">30</td> 
  </tr> 
  <tr> 
   <td align="center">B</td> 
   <td align="right">2015-02</td> 
   <td align="left">30</td> 
   <td align="left">45</td> 
   <td align="left">15</td> 
  </tr> 
  <tr> 
   <td align="center">B</td> 
   <td align="right">2015-03</td> 
   <td align="left">44</td> 
   <td align="left">89</td> 
   <td align="left">44</td> 
  </tr> 
 </tbody> 
</table>

**分析：  
由给出的结果可以知道总访问次数和最大访问次数都依赖于当月访问次数，所以先求出当月访问次数：**  
由给出的数据可以看出用户在同一个月份有多条记录，首先对每个用户进行分组求和，算出每个用户在某个月所有的访问次数。

create table exercise01_step01 as 
    select name,month,sum(pv) visit
    from exercise01
    group by name,month;
    hive> select * from exercise01_step01;
    OK
    exercise01_step01.name  exercise01_step01.month exercise01_step01.visit
    A       2015-01 33
    A       2015-02 10
    A       2015-03 38
    B       2015-01 30
    B       2015-02 15
    B       2015-03 44

第二步，利用开窗函数：

hive> select 
        > name,month,visit,
        > max(visit) over(partition by name order by month) max,
        > sum(visit) over(partition by name order by month) sum   
        > from exercise01_step01;
    
    OK
    name    month   visit   max     sum
    A       2015-01 33      33      33
    A       2015-02 10      33      43
    A       2015-03 38      38      81
    B       2015-01 30      30      30
    B       2015-02 15      30      45
    B       2015-03 44      44      89

分析函数 over（开窗依据distribute by |partition by 每一个窗口的排序规则 sort by |order by ）子句/窗口函数/开窗函数.