Hive开窗函数同环比

转载

mob64ca1402a190 2024-12-01 06:58:58

文章标签 Hive开窗函数同环比 hive 开窗函数建表自定义 文章分类 Hive 大数据

不说了，直接使用别人的表来做一个测试表吧

-- 建表
create table student_scores(
id int,
studentId int,
language int,
math int,
english int,
classId string,
departmentId string
);
-- 写入数据
insert into table student_scores values 
  (1,111,68,69,90,'class1','department1'),
  (2,112,73,80,96,'class1','department1'),
  (3,113,90,74,75,'class1','department1'),
  (4,114,89,94,93,'class1','department1'),
  (5,115,99,93,89,'class1','department1'),
  (6,121,96,74,79,'class2','department1'),
  (7,122,89,86,85,'class2','department1'),
  (8,123,70,78,61,'class2','department1'),
  (9,124,76,70,76,'class2','department1'),
  (10,211,89,93,60,'class1','department2'),
  (11,212,76,83,75,'class1','department2'),
  (12,213,71,94,90,'class1','department2'),
  (13,214,94,94,66,'class1','department2'),
  (14,215,84,82,73,'class1','department2'),
  (15,216,85,74,93,'class1','department2'),
  (16,221,77,99,61,'class2','department2'),
  (17,222,80,78,96,'class2','department2'),
  (18,223,79,74,96,'class2','department2'),
  (19,224,75,80,78,'class2','department2'),
  (20,225,82,85,63,'class2','department2');

聚合开窗函数

count开窗函数

先把department1的给弄出来

Hive开窗函数同环比_Hive开窗函数同环比

测试1:

select studentid,math,departmentld,classid,
 count(math) over() as count1 from student_scores where departmentld='department1';

从这里可以看到，我们如果简单的使用count(math)是不行的，所以需要开窗，为每个属于department1条件的行都过来成为一组，然后count(math)默认将整个筛选出来的成为一组，除非里面有分区函数，那么直接将所有的行math聚合，然后作为一列给每一行都附上

Hive开窗函数同环比_开窗函数_02

测试2

select studentid,math,departmentld,classid,
count(math) over(partition by classid) as count2 from student_scores where departmentld='department1';

**这个结果就有意思了，可以看到这个SQL我们在over里面根据classid分区了，那么count2这一列，就是class1的都是5，class2的都是4，看看class1的个数是5，class2的总数也确实是4，那么的确可以哦，根据分组了之后仍然可以为每一行做一个汇总

Hive开窗函数同环比_自定义_03

测试3

select studentid,math,departmentld,classid,
count(math) over(partition by classid order by math) as count3 from student_scores where departmentld='department1';

看一看，这一次除了分区外，我还加了一个order by，这个就会又一个累计过程，比如对于class1来说，数学分数从低到高分别是69，74，80，93，94，那么这个count(math)就会又一个累计过程，相当于每一行记录的是目前为止的math count数，比如对于69分数来说，它和它之前只有1个，对于74来说就只有2个，那么就相当于一个累计过程，到最后一个94，就相当于累计了5个了。

对于class2也是一样的

Hive开窗函数同环比_开窗函数_04

测试4

select studentid,math,departmentld,classid,
count(math) over(partition by classid order by math
 rows between 1 preceding and 2 following)
  as count4 from student_scores where departmentld='department1';

这个语句可以看到，除了上面的order by之外，我还添加了一个语句就是 rows between 1 preceding and 2 following ，这个rows between代表之间的，而preceding代表往前的行数，following代表往后的行数。
看看，对于class1来说，它首先根据class分组了，然后根据math排序了，那么rows between 1 preceding and 2 following 就是计算前一行一直到后两行一共有多少个。比如，对于math为69的来说，也就是第一行，那么往前一行，没有，往后两行，也就是第三行，这三行做count(math)是3，同理第二行，前一行有一直到后两行一直是4行，而对于最后一行，即从前一行一直到自己这一行，因为后面属于class1的没有了，那么就只有两行了。
那么细心的就会发现，上面的没有这个rows between的就是默认从当前组的期待到末尾