常用hive开窗函数示例

个人博客原文链接

简介

普通的聚合函数每组(group by)只返回一个值，而开窗函数则可以为每行都返回一个值。简而言之，相当于对查询的结果添加新的一列值，这一列可以是聚合值，也可以是排序号。开窗函数的关键字是over()。

准备测试数据

1. 建表
   create table if not exists student_scores(
   id string,
   studentId string,
   language string,
   math string,
   english string,
   classId string,
   departmentId string
   );

2. 插入数据
   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’,‘department2’),
   (‘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’,‘class2’,‘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()

1. 以符合条件的所有行为作为窗口
   select studentId,language,math,english,classId,departmentId,count(math) over() as count1
   from student_scores;
   结果：
   111 68 69 90 class1 department1 20
   112 73 80 96 class1 department1 20
   113 90 74 75 class1 department1 20
   114 89 94 93 class1 department2 20
   115 99 93 89 class1 department1 20
   121 96 74 79 class2 department1 20
   122 89 86 85 class2 department1 20
   123 70 78 61 class2 department1 20
   124 76 70 76 class2 department1 20
   211 89 93 60 class1 department2 20
   212 76 83 75 class1 department2 20
   213 71 94 90 class2 department2 20
   214 94 94 66 class1 department2 20
   215 84 82 73 class1 department2 20
   216 85 74 93 class1 department2 20
   221 77 99 61 class2 department2 20
   222 80 78