网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
jack 6
mart 6
第二种方式:**distinct**
select distinct name,count(*) over() from order where substring(orderdate,1,7)=‘2015-04’;
结果如下:
jack 2
mart 2
### 五、partition by 子句
Over子句之后第一个提到的就是**Partition By**。Partition By子句也可以称为**查询分区子句**,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算。
实例
我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql。
select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from order
结果如下:
tony 2015-01-07 50 205
jack 2015-01-01 10 205
jack 2015-01-05 46 205
tony 2015-01-04 29 205
tony 2015-01-02 15 205
jack 2015-01-08 55 205
jack 2015-02-03 23 23
mart 2015-04-13 94 364
mart 2015-04-11 75 364
mart 2015-04-09 68 364
mart 2015-04-08 62 364
jack 2015-04-06 42 364
jack 2015-04-03 23 364
neil 2015-05-10 12 12
neil 2015-06-12 80 80
这里我们可以看到数据已经完全按照月份进行聚合。
### 六、order by 子句
上述的场景,假如我们想要将cost按照月进行累加,这时我们引入order by子句。
order by子句会让输入的数据强制排序(窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如`Row_Number()`,`Lead()`,`LAG()`等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则`Count()`,`Min()`等计算出来的结果就没有任何意义。
我们在上面的代码中加入order by
select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate)
from order;
结果如下:
jack 2015-01-01 10 10 // 10
tony 2015-01-02 15 25 // 10+15
tony 2015-01-04 29 54 // 25+29
jack 2015-01-05 46 100
tony 2015-01-07 50 150
jack 2015-01-08 55 205
jack 2015-02-03 23 23
jack 2015-04-03 23 23
jack 2015-04-06 42 65
mart 2015-04-08 62 127
mart 2015-04-09 68 195
mart 2015-04-11 75 270
mart 2015-04-13 94 364
neil 2015-05-10 12 12
neil 2015-06-12 80 80
### 七、window 子句
我们在上面已经通过使用partition by子句将数据进行了分组的处理,如果我们想要更细粒度的划分,我们就要引入window子句了。
我们首先要理解两个概念:
* 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合。
* 使用了order by子句,未使用window子句的情况下,默认从起点到当前行。
当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的,每个窗口函数应用自己的规则。
**window子句**:
* PRECEDING:往前
* FOLLOWING:往后
* CURRENT ROW:当前行
* UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点
我们按照name进行分区,按照购物时间进行排序,做cost的累加。
如下我们结合使用window子句进行查询
select name,orderdate,cost,
sum(cost) over() as fullagg, --所有行相加
sum(cost) over(partition by name) as fullaggbyname, --按name分组,组内数据相加
sum(cost) over(partition by name order by orderdate) as fabno, --按name分组,组内数据累加
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1 --和fabno一样,由最前面的起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as mw3, --当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as mw4 --当前行及后面所有行
from order;
结果如下:
jack 2015-01-01 10 684 199 10 10 10 56 199
jack 2015-01-05 46 684 199 56 56 56 111 189
jack 2015-01-08 55 684 199 111 111 101 124 143
jack 2015-02-03 23 684 199 134 134 78 101 88
jack 2015-04-03 23 684 199 157 157 46 88 65
jack 2015-04-06 42 684 199 199 199 65 65 42
mart 2015-04-08 62 684 299 62 62 62 130 299
mart 2015-04-09 68 684 299 130 130 130 205 237
mart 2015-04-11 75 684 299 205 205 143 237 169
mart 2015-04-13 94 684 299 299 299 169 169 94
neil 2015-05-10 12 684 92 12 12 12 92 92
neil 2015-06-12 80 684 92 92 92 92 92 80
tony 2015-01-02 15 684 94 15 15 15 44 94
tony 2015-01-04 29 684 94 44 44 44 94 79
tony 2015-01-07 50 684 94 94 94 79 79 50
### 八、窗口函数中的序列函数
主要序列函数是不支持window子句的。
hive中常用的序列函数有下面几个😀:
#### ntile
* NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值
* NTILE不支持ROWS BETWEEN,
比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING - AND CURRENT ROW)
* 如果切片不均匀,默认增加第一个切片的分布
这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数。
select name,orderdate,cost,
ntile(3) over() as sample1 , – 全局数据切片
ntile(3) over(partition by name), – 按照name进行分组,在分组内将数据切成3份
ntile(3) over(order by cost),-- 全局按照cost升序排列,数据切成3份
ntile(3) over(partition by name order by cost ) – 按照name分组,在分组内按照cost升序排列,数据切成3份
from order
得到的数据如下:
jack 2015-01-01 10 3 1 1 1
jack 2015-02-03 23 3 2 1 1
jack 2015-04-03 23 3 3 1 2
jack 2015-04-06 42 2 1 2 2
jack 2015-01-05 46 2 3 2 3
jack 2015-01-08 55 2 2 2 3
mart 2015-04-08 62 2 1 3 1
mart 2015-04-09 68 1 2 3 1
mart 2015-04-11 75 1 3 3 2
mart 2015-04-13 94 1 1 3 3
neil 2015-05-10 12 1 2 1 1
neil 2015-06-12 80 1 1 3 2
tony 2015-01-02 15 3 2 1 1
tony 2015-01-04 29 3 1 2 2
tony 2015-01-07 50 2 3 2 3
如上述数据,我们去sample4 = 1的那部分数据就是我们要的结果
#### row\_number
用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row\_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
#### rank
函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row\_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。
#### dense\_rank
函数的功能与rank函数类似,dense\_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense\_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense\_rank()是连续排序,有两个第一名时仍然跟着第二名。
这三个窗口函数的使用场景非常多
* `row_number()`:从1开始,按照顺序,生成分组内记录的序列,row\_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
* `rank()` :生成数据项在分组中的排名,排名相等会在名次中**留下空位**
* `dense_rank()` :生成数据项在分组中的排名,排名相等会在名次中**不会留下空位**
**注意:
rank和dense\_rank的区别在于排名相等时会不会留下空位**
select name,orderdate,cost,
row_number() over(partition by name order by cost) as rn1,
rank() over(partition by name order by cost) as rn2,
dense_rank() over(partition by name order by cost) as rn3
from order;
jack 2015-01-01 10 1 1 1
jack 2015-02-03 23 2 2 2
jack 2015-04-03 23 3 2 2
jack 2015-04-06 42 4 4 3
jack 2015-01-05 46 5 5 4
jack 2015-01-08 55 6 6 5
mart 2015-04-08 62 1 1 1
mart 2015-04-09 68 2 2 2
mart 2015-04-11 75 3 3 3
mart 2015-04-13 94 4 4 4
neil 2015-05-10 12 1 1 1
neil 2015-06-12 80 2 2 2
tony 2015-01-02 15 1 1 1
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
(img-8WN1LISA-1715814453485)]
[外链图片转存中…(img-Fs5VEzZN-1715814453485)]
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!