devbox
菜鸟追梦旅行
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Hive统计函数总结_hive 统计函数

作者：菜鸟追梦旅行 | 2024-02-25 13:44:17

赞

踩

hive 统计函数

行列转换

行转列

  • 顾名思义，将每行的某个列值，转换为一行的列值
  • 可以对其进行去重统计，不去重统计

原始数据

去重统计

  • concat_ws(’,’,collect_set(col_name))
  • SQL
select id ,concat_ws(',',collect_set(value)) as rows2col from rows2cols group by id;

  • 1
  • 结果
    去重行转列

不去重行转列

  • concat_ws(’,’,collect_list(col_name))
  • SQL
select id ,concat_ws(',',collect_list(value)) as rows2col from rows2cols group by id;

  • 1
  • 结果
    不去重行转列

列转行

  • 将某一行的一列值[可拆分]进行拆分转换为多行值
  • SQL lateral view explode(split(要拆分列,’,’)) num as 新列名
select * from default.cols2row lateral view explode(split(value,',')) num as tag ;

  • 1
  • 结果
    列转行

开窗函数

测试数据

  • 创建测试表，对一些院系学生的英语、高数成绩进行统计
create table student_info(
               id int,
               studentId int,
              english int,
              math int,
              className string,
              deptName string
              );

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 测试数据
insert into table student_info
                 (11,211,68,69,90,'计算机科学与技术技术','计算机学院'),
                 (12,212,73,80,96,'计算机科学与技术技术','计算机学院'),
                 (13,213,90,74,75,'计算机科学与技术技术','计算机学院'),
                 (14,214,89,94,93,'计算机科学与技术技术','计算机学院'),
                 (15,215,79,93,89,'计算机科学与技术技术','计算机学院'),
                 (16,221,96,74,79,'软件工程','计算机学院'),
                 (17,222,89,86,85,'软件工程','计算机学院'),
                 (18,223,70,78,61,'软件工程','计算机学院'),
                 (19,224,76,70,76,'软件工程','计算机学院'),
                 (20,311,89,93,60,'计算机科学与技术技术','软件学院'),
                 (21,312,76,83,75,'计算机科学与技术技术','软件学院'),
                 (22,313,71,94,90,'计算机科学与技术技术','软件学院'),
                 (23,314,94,94,66,'计算机科学与技术技术','软件学院'),
                 (24,315,84,82,73,'计算机科学与技术技术','软件学院'),
                 (25,316,85,74,43,'计算机科学与技术技术','软件学院'),
                 (26,321,77,99,61,'软件工程','软件学院'),
                 (27,322,80,76,56,'软件工程','软件学院'),
                 (28,323,79,74,96,'软件工程','软件学院'),
                 (29,324,75,80,78,'软件工程','软件学院'),
                 (30,325,82,85,63,'软件工程','软件学院');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

聚合开窗函数

count开窗函数

按照同专业统计参加高数考试的人数
  • SQL
select studentid,math,classname,deptname,count(math) over(partition by classname) as class_math from default.student_info;

  • 1
  • 结果
    count
按同专业分组统计参加高数成绩人数，并在分组内按高数成绩升序排序
  • SQL
select studentid,math,classname,deptname,count(math) over(partition by classname order by math) as class_math from default.student_info;

  • 1
  • 结果
    sortByMath
以当前行、以及前2行和后2行【共5行】按照班级为单位统计高数成绩，并按高数成绩排序
  • SQL
select studentid,math,classname,deptname,count(math) over(partition by classname order by math row between 2 preceding and 2 following) as class_math  from default.student_info;

  • 1
  • 结果，第一行统计的是当前行，后2行；第二行统计的是前1行，当前行，后2行，共4四行，第三行统计的是前2行，当前行，后2行，共4行
    rowBetweenAnd

Sum开窗函数

按专业分组统计高数总成绩

  • SQL
 select studentid,math,classname,deptname,sum(math) over(partition by classname) as class_math from default.student_info;

  • 1
  • 结果
    sumByMath
按同专业分组，统计每个专业的总成绩，并以成绩升序排序
  • SQL
select studentid,math,classname,deptname,sum(math) over(partition by classname order by math) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
  • 按同专业分组，统计每个专业的总成绩，统计时加上当前行的前后2行，并以成绩升序排序
  • SQL
select studentid,math,classname,deptname,sum(math) over(partition by classname order by math rows  between 2 preceding and 2 following) as class_math from default.student_info where deptname ='计算机学院';

  • 1
  • 结果，410的值为 第3行的前后2行及其本身之和
    在这里插入图片描述

max函数

按专业分组统计高数最高分
  • SQL
select studentid,math,classname,deptname,max(math) over(partition by classname) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
按专业分组统计高数最高分，并按成绩降序排列
  • SQL
select studentid,math,classname,deptname,max(math) over(partition by classname order by math ) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
按专业分组统计高数最高分，并找出当前行及前后2行的最大值，并按成绩降序排列
  • SQL
select studentid,math,classname,deptname,max(math) over(partition by classname order by math rows between 2 preceding and 2 following) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述

min函数

按专业分组统计高数最低分
  • SQL
select studentid,math,classname,deptname,min(math) over(partition by classname) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
按专业分组统计高数最低分，并按成绩降序排列
  • SQL
select studentid,math,classname,deptname,min(math) over(partition by classname order by math) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
按专业分组统计高数最低分，并找出当前行及前后2行的最低值，并按成绩降序排列
  • SQL
select studentid,math,classname,deptname,min(math) over(partition by classname order by math rows between 2 preceding and 2 following) as class_math from default.student_info;

  • 1
  • 结果
    在这里插入图片描述

avg

按专业分组统计高数，求其平均分
  • SQL
select studentid,math,classname,deptname,avg(math) over(partition by classname) as class_math  from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
按专业分组统计高数最低分，求其平均分，并按成绩降序排列
  • SQL
select studentid,math,classname,deptname,avg(math) over(partition by classname order by math) as class_math  from default.student_info;

  • 1
  • 结果
    在这里插入图片描述
按专业分组统计高数最低分，并根据当前行及前后2行的最低值，然后求其平均分，并按成绩降序排列
  • SQL
select studentid,math,classname,deptname,avg(math) over(partition by classname order by math rows between 2 preceding and  2 following) as class_math  from default.student_info;

  • 1
  • 结果
    在这里插入图片描述

取值函数

first_value

  • 取分区中的第一个值
  • SQL
select studentid,math,classname,deptname,
/不分组情况下去第一个
first_value(math) over() as first_value1,
//按照班级分组后，获取该分组的第一个数学成绩
first_value(math) over(partition by classname) as fv_no_sort,
//按照班级分组后，并排序，获取该分组的第一个数学成绩
first_value(math) over(partition by classname order by math) as fv_sort,
//按照班级分组后，并排序，以当前行及其前后各2行[共5行]，获取该分组的第一个数学成绩
first_value(math) over(partition by classname order by math rows between 2 preceding and  2 following) as fv_sort_rows_limit  
from default.student_info;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 结果
    在这里插入图片描述

last_value

  • 获取分区后的最后一个值
  • SQL
select studentid,math,classname,deptname,
/不分组情况下去最后一个数学成绩值
first_value(math) over() as first_value1,
//按照班级分组后，获取该分组的最后一个数学成绩值
first_value(math) over(partition by classname) as fv_no_sort,
//按照班级分组后，并排序，获取该分组的最后一个数学成绩值
first_value(math) over(partition by classname order by math) as fv_sort,
//按照班级分组后，并排序，以当前行及其前后各2行[共5行]，获取该分组的最后一个数学成绩值
first_value(math) over(partition by classname order by math rows between 2 preceding and  2 following) as fv_sort_rows_limit  
from default.student_info;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 结果
    在这里插入图片描述

lag

  • lag(col,n,default) 用于统计窗口内往上第n个值，col:列名，n:第n行,default:往上第n行为NULL，取默认值，否则为NULL

  • SQL

select studentid,math,classname,deptname,
// 以专业班级坐分组，然后对高数成绩排序，取窗口往上第2个的高数成绩，设置填充值
lag(math,2,-1) over(partition by classname order by math) as lag_with_fill_value,
// 以专业班级坐分组，然后对高数成绩排序，取窗口往上第2个的高数成绩，不设置填充值
lag(math,2) over(partition by classname order by math) as lag_no_fill_value from default.student_info;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 结果
    在这里插入图片描述

lead

  • lag(col,n,default) 用于统计窗口内往下第n个值，col:列名，n:第n行,default:往下第n个为NULL，取默认值，否则为NULL
  • SQL
select studentid,math,classname,deptname,
lead(math,2,-1) over(partition by classname order by math) as lead_with_fill_value,l
ead(math,2) over(partition by classname order by math) as lead_no_fill_value 
from default.student_info;

  • 1
  • 2
  • 3
  • 4
  • 结果
    在这里插入图片描述

排序开窗函数

rank

  • 基于over子句中order by 确定一组中一个值的排名，如果存在partition by，则为分组中的每个值排名；排名不存在并列情况，如果并列第2，则下个排名为第4
  • SQL
select studentid,math,classname,deptname,
// 根据成绩排序
rank() over( order by math) as rank_no_sort,
//按照专业班级分区，根据成绩排序
rank() over(partition by classname order by math) as rand_classname_sort ,
//按照院系、专业班级分区，根据成绩排序
rank() over(partition by  deptname,classname order by math) as rank_classDept_sort 
from default.student_info;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 结果
    在这里插入图片描述

dense_rank

  • 基于over子句中order by 确定一组中一个值的排名，如果存在partition by，则为分组中的每个值排名；排名允许并列情况，如果并列第2，则下个排名为第3
  • SQL
select studentid,math,classname,deptname,
// 根据成绩排序
dense_rank() over( order by math) as dense_rank_no_sort,
//按照专业班级分区，根据成绩排序
dense_rank() over(partition by classname order by math) as dense_rank_classname_sort ,
//按照院系、专业班级分区，根据成绩排序
dense_rank() over(partition by  deptname,classname order by math) as dense_rank_classDept_sort
 from default.student_info;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 结果

在这里插入图片描述

percent_rank
  • 根据给定的百分比进行排名。可以得到超过了百分之多少的数据
  • SQL
select studentid,math,classname,deptname,
              row_number() over(partition by deptname,classname order by math) as row_numm,
             percent_rank() over(partition by  deptname,classname order by math) as percent_rank
              from  student_info;

  • 1
  • 2
  • 3
  • 4
  • 结果
    在这里插入图片描述

分箱函数

类似机器学习的分箱操作把数据按分、比例分成若干份

  • 1

ntile

  • 将数据直接按数值分成几份，可能存在最后一个分配不均的问题，无法均分的情况不够精确
  • 测试数据
001,001,math,15
001,002,math,20
001,003,math,35
001,004,math,40
001,005,math,48
001,006,math,60
001,007,math,69
001,008,math,80
001,009,math,89
001,010,math,100
001,001,english,99
001,002,english,100
001,003,english,87
001,004,english,10
001,005,english,50
001,006,english,30
001,007,english,58
001,008,english,68
001,009,english,78
001,010,english,89
002,001,math,15
002,002,math,20
002,003,math,35
002,004,math,40
002,005,math,48
002,006,math,60
002,007,math,69
002,008,math,80
002,009,math,89
002,010,math,100
002,001,english,99
002,002,english,100
002,003,english,87
002,004,english,10
002,005,english,50
002,006,english,30
002,007,english,58
002,008,english,68
002,009,english,78
002,010,english,89

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • SQL
 select stuid,classid,course,score,ntile(5) over (partition by classId order by score) as bucket from student where course = 'math';

  • 1
  • 结果
    在这里插入图片描述

cume_dist

  • 将数据直接按比例分成几份，不存在最后一个分配不均的问题，无法均分的情况相对精确
  • SQL
//按班级分组、并根据数学成绩排序后，获取钱百20
select stuid,classid,course,score,bucket from (select stuid,classid,course,score,cume_dist() over (partition by classId order by score) as bucket from student where course = 'math') tmp  where tmp.bucket <= 0.2

  • 1
  • 2
  • 结果
    在这里插入图片描述

编号函数

  • 从1开始进行数据排序,用于对记录编号进行分页，排序等操作
  • SQL
select studentid,math,classname,deptname,row_number()  over(partition by deptname,classname order by math) as row_num from student_info;

  • 1
  • 结果
    在这里插入图片描述

维度下钻,上卷

cube

  • 数据挖掘的下钻操作，在一个维度基础上再加一个维度进行分组统计
  • 因为在聚合时，为得到对于null的汇总结果，对这种情况，无法判断null时原来就有还是在聚合中产生的，为解决该冲突，需要使用GROUPING__ID来标记那些是数据自带的null，哪些是聚合产生的null
  • SQL
//分别按照院系所有值分组，院系和专业所有值组合分组进行聚合
select deptname,classname,grouping_id,count(1) from student_info group by deptname,classname with cube;

  • 1
  • 2

  • 结果
    在这里插入图片描述

  • 一般SQL【不建议】

//分别安装院系，院系和专业进行成绩人数统计
//不分组
 select 'ALL' classname,'ALL' deptname ,count(1) from student_info
union all
//根据院系分组
 select deptname,'ALL' classname,count(1) from student_info group by deptname
union all
//根据院系和专业分组
select deptname,classname,count(1) from student_info group by deptname,classname;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 结果
    在这里插入图片描述

rollup

  • 先根据一个维度值分组进行聚合，然后进行两两维度值分组进行聚合，以此类推，最后组合所有维度值进行分组聚合
  • SQL
select deptname,classname,GROUPING__ID,count(1) stu_cnt from student_info group by deptname,classname with rollup;

  • 1
  • 结果
    在这里插入图片描述
  • 一般SQL实现
//根据院系和专业维度的列值组合进行聚合
 select deptname,classname,count(1) from student_info group by deptname,classname
union all
//根据院系维度的列值组合进行聚合
 select deptname,'ALL' classname,count(1) from student_info group by deptname
 union all
 //不分组进行聚合
select 'ALL' classname,'ALL' deptname ,count(1) from student_info ;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 结果
    在这里插入图片描述

GROUPING SETS

  • 等同于使用 group by 后union起来的结果，这样做语句简洁很多
  • SQL
select 
deptname,classname,GROUPING__ID,count(1)
 from student_info 
 group by deptname,classname 
 GROUPING SETS((deptname,classname),deptname,classname);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 结果
    在这里插入图片描述
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/141125
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号