HiveSQL题——聚合函数(sum/count/max/min/avg)

2024-01-31 08:36

本文主要是介绍HiveSQL题——聚合函数(sum/count/max/min/avg),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

目录

一、窗口函数的知识点

1.1 窗户函数的定义

1.2 窗户函数的语法

1.3 窗口函数分类

聚合函数

排序函数

前后函数 

头尾函数

1.4 聚合函数

二、实际案例

2.1 每个用户累积访问次数

0 问题描述

1 数据准备

2 数据分析

3 小结

2.2 各直播间最大的同时在线人数

0 问题描述

1 数据准备

2 数据分析

3 小结

2.3 历史至今每个小时内同时在线人数

0 问题描述

1 数据准备

2 数据分析

3 小结

2.4 某个时间段、每个小时内同时在线人数

0 问题描述

1 数据准备

2 数据分析

3 小结

2.5 学生各学科的成绩

0 问题描述

1 数据准备

2 数据分析

3 小结


一、窗口函数的知识点

1.1 窗户函数的定义

        窗口函数可以拆分为【窗口+函数】。窗口函数官网指路:

LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundationicon-default.png?t=N7T8https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics

  • 窗口:限定函数的计算范围(窗口函数:针对分组后的数据,从逻辑角度指定计算的范围,并没有从物理上真正的切分,只有group by 是物理分组,真正意义上的分组)
  • 函数:计算逻辑
  •  窗口函数的位置:跟sql里面聚合函数的位置一样,from -> join -> on -> where -> group by->select 后面的普通字段,窗口函数 -> having -> order by  -> lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。
  • sql 执行顺序:from -> join -> on -> where -> group by->select 后面的普通字段,聚合函数-> having -> order by -> limit

1.2 窗户函数的语法

      <窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )

  • window_name:给窗口指定一个别名。
  • over:用来指定函数执行的窗口范围,如果后面括号中什么都不写,即over() ,意味着窗口包含满足where 条件的所有行,窗口函数基于所有行进行计算。
  • 符号[] 代表:可选项;  | : 代表二选一
  •  partition by 子句: 窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行。分组间互相独立。
  • order by 子句:每个partition内部按照哪些字段进行排序,如果没有partition ,那就直接按照最大的窗口排序,且默认是按照升序(asc)排列。
  • 窗口子句:显示声明范围(不写窗口子句的话,会有默认值)。常用的窗口子句如下:
    rows between unbounded preceding and  unbounded following; -- 上无边界到下无边界(一般用于求 总和)rows between unbounded preceding and current row;  --上无边界到当前记录(累计值)rows between 1 preceding and current row; --从上一行到当前行rows between 1 preceding and 1 following; --从上一行到下一行rows between current row and 1 following; --从当前行到下一行

       ps: over()里面有order by子句,但没有窗口子句时 ,即: <窗口函数> over ( partition by 字段... order by 字段... )此时窗口子句是有默认值的 -->  rows between unbounded preceding and current row (上无边界到当前行)。

      此时窗口函数语法:<窗口函数> over ( partition by 字段... order by 字段... ) 等价于

     <窗口函数> over ( partition by 字段... order by 字段... rows between unbounded preceding and current row)
      需要注意有个特殊情况:当order by 后面跟的某个字段是有重复行的时候, <窗口函数> over ( partition by 字段... order by 字段... )  不写窗口子句的情况下,窗口子句的默认值是:range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。

    因此,遇到order by 后面跟的某个字段出现重复行,且需要计算【上无边界到当前行】,那就需要手动指定窗口子句 rows between unbounded preceding and current row ,偷懒省略窗口子句会出问题~

      ps: 窗口函数的执行顺序是在where之后,所以如果where子句需要用窗口函数作为条件,需要多一层查询,在子查询外面进行。

     【例如】求出登录记录出现间断的用户Id

selectid
from (selectid,login_date,lead(login_date, 1, '9999-12-31')over (partition by id order by login_date) next_login_date--窗口函数 lead(向后取n行)--lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据,如果没有就为nullfrom (--用户在同一天可能登录多次,需要去重selectid,date_format(`date`, 'yyyy-MM-dd') as login_datefrom user_loggroup by id, date_format(`date`, 'yyyy-MM-dd')) tmp1) tmp2
where  datediff(next_login_date, login_date) >=2
group by id;

1.3 窗口函数分类

      哪些函数可以是窗口函数呢?(放在over关键字前面的)

  • 聚合函数

sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over  (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句);

     ps : 高级聚合函数

             collect_list 收集并形成list集合,结果不去重;

             collect_set 收集并形成set集合,结果去重; 

      举例:

--每个月的入职人数以及姓名select 
month(replace(hiredate,'/','-')),count(*) as cnt,collect_list(name) as name_list
from employee
group by month(replace(hiredate,'/','-'));/*
输出结果
month  cn  name_list
4	    2	["宋青书","周芷若"]
6	    1	["黄蓉"]
7	    1	["郭靖"]
8	    2	["张无忌","杨过"]
9	    2	["赵敏","小龙女"]
*/
  • 排序函数

--  顺序排序——1、2、3
row_number() over(partition by .. order by .. )--  并列排序,跳过重复序号——1、1、3(横向加)
rank() over(partition by .. order by .. )-- 并列排序,不跳过重复序号——1、1、2(纵向加)
dense_rank()  over(partition by .. order by .. )
  • 前后函数 

-- 取得column列的前n行,如果存在则返回,如果不存在,返回默认值default
lag(column,n,default) over(partition by order by) as lag_test
-- 取得column列的后n行,如果存在则返回,如果不存在,返回默认值default
lead(column,n,default) over(partition by order by) as lead_test
  • 头尾函数

---当前窗口column列的第一个数值,如果有null值,则跳过
first_value(column,true) over (partition by ..order by.. 窗口子句) ---当前窗口column列的第一个数值,如果有null值,不跳过
first_value(column,false) over (partition by ..order by.. 窗口子句)--- 当前窗口column列的最后一个数值,如果有null值,则跳过
last_value(column,true) over (partition by ..order by.. 窗口子句) --- 当前窗口column列的最后一个数值,如果有null值,不跳过
last_value(column,false) over (partition by ..order by.. 窗口子句) 

1.4 聚合函数

       sum() /count() /max() /min() /avg()  函数,一般用于开窗求累积汇总值。

sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over  (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句);

二、实际案例

2.1 每个用户累积访问次数

0 问题描述

    统计每个用户累积访问次数

1 数据准备

create table if not exists table6
(userid         string comment '用户id',visitdate      string comment '访问时间',visitcount     int comment '访问次数'
)comment '用户访问次数';

2 数据分析

selectuserid,visit_date,vc1,--再求出用户历史至今的累积访问次数sum(vc1) over (partition by userid order by visit_date ) as vc2
from (   --先求出用户每个月的累积访问次数selectuserid,date_format(visitdate, 'yyyy-MM') as visit_date,sum(visitcount)  as vc1from table6group by userid, date_format(visitdate, 'yyyy-MM')) tmp1;

3 小结

2.2 各直播间最大的同时在线人数

0 问题描述

   根据直播间的用户访问记录,统计各直播间最大的同时在线人数。

1 数据准备

create table if not exists table7
(room_id      int comment '直播间id',user_id      int comment '用户id',login_time   string comment '用户进入直播间时间',logout_time  string comment '用户离开直播间时间'
)comment '直播间的用户访问记录';
INSERT overwrite table table7
VALUES (1,100,'2021-12-01 19:00:00', '2021-12-01 19:28:00'),(1,100,'2021-12-01 19:30:00', '2021-12-01 19:53:00'),(2,100,'2021-12-01 21:01:00', '2021-12-01 22:00:00'),(1,101,'2021-12-01 19:05:00', '2021-12-01 20:55:00'),(2,101,'2021-12-01 21:05:00', '2021-12-01 21:58:00'),(1,102,'2021-12-01 19:10:00', '2021-12-01 19:25:00'),(2,102,'2021-12-01 19:55:00', '2021-12-01 21:00:00'),(3,102,'2021-12-01 21:05:00', '2021-12-01 22:05:00'),(1,104,'2021-12-01 19:00:00', '2021-12-01 20:59:00'),(2,104,'2021-12-01 21:57:00', '2021-12-01 22:56:00'),(2,105,'2021-12-01 19:10:00', '2021-12-01 19:18:00'),(3,106,'2021-12-01 19:01:00', '2021-12-01 21:10:00');

2 数据分析

selectroom_id,max(num)
from (selectroom_id,sum(flag) over (partition by room_id order by dt) as numfrom (selectroom_id,user_id,login_time as dt,--对登入该直播间的人,标记 11          as flagfrom table7unionselectroom_id,user_id,logout_time as dt,--对退出该直播间的人,标记 -1-1          as flagfrom table7) tmp1) tmp2
--求出直播间最大的同时在线人数
group by room_id;

3 小结

    该题的关键点在于:对每个用户进入/退出直播间的行为进行打标签,再利用sum()over聚合函数计算最终的数值。

2.3 历史至今每个小时内同时在线人数

       由案例2.2 引申出来的案例 2.3和 案例2.4

0 问题描述

    根据直播间用户访问记录,不限制时间段,统计历史至今的各直播间​​​每个小时内的同时在线人数

1 数据准备

create table if not exists table7
(room_id      int comment '直播间id',user_id      int comment '用户id',login_time   string comment '用户进入直播间时间',logout_time  string comment '用户离开直播间时间'
)comment '直播间的用户访问记录';
INSERT overwrite table table7
VALUES (1,100,'2021-12-01 19:00:00', '2021-12-01 19:28:00'),(1,100,'2021-12-01 19:30:00', '2021-12-01 19:53:00'),(2,100,'2021-12-01 21:01:00', '2021-12-01 22:00:00'),(1,101,'2021-12-01 19:05:00', '2021-12-01 20:55:00'),(2,101,'2021-12-01 21:05:00', '2021-12-01 21:58:00'),(1,102,'2021-12-01 19:10:00', '2021-12-01 19:25:00'),(2,102,'2021-12-01 19:55:00', '2021-12-01 21:00:00'),(3,102,'2021-12-01 21:05:00', '2021-12-01 22:05:00'),(1,104,'2021-12-01 19:00:00', '2021-12-01 20:59:00'),(2,104,'2021-12-01 21:57:00', '2021-12-01 22:56:00'),(2,105,'2021-12-01 19:10:00', '2021-12-01 19:18:00'),(3,106,'2021-12-01 19:01:00', '2021-12-01 21:10:00');

2 数据分析

   完整代码如下:

with temp_data as (selectroom_id,user_id,login_time,logout_time,hour(login_time) as min_time,--  hour('2021-12-01 19:30:00') = 19hour(logout_time) as max_time,length(space(hour(logout_time) - hour(login_time))) as lg,split(space(hour(logout_time) - hour(login_time)), '') as disfrom table7
)selectroom_id,on_time,count(1) as cnt
from (select distinctroom_id,user_id,min_time,max_time,dis,dis_index,(min_time + dis_index) as on_timefrom temp_data lateral view posexplode(dis) n as dis_index,dis_dataorder by user_id,min_time,max_time,dis,dis_index) tmp1
group by room_id, on_time
order by room_id, on_time;

     代码拆解分析:

--以一条数据为例,room_id  user_id     login_time               logout_time1         100    '2021-12-01 19:00:00'     '2021-12-01 21:28:00'
(1)上述数据取时间hour(login_time) as min_time 、hour(logout_time)as max_time1(room_id),100(user_id),19(min_time),21(max_time)
(2)split(space(hour(logout_time) - hour(login_time)), '') 的结果:根据[21-19]=2,利用space函数生成长度是2的空格字符串,再用split拆分1(room_id),100(user_id),19(min_time),21(max_time),['','','']
(3)用posexplode经过转换增加行(列转行,炸裂),通过下角标index来获取 on_time时间,根据数组['','',''],得到index的取值是0,1,2炸裂得出下面三行数据(一行变三行)1(room_id),100(user_id),19(min_time),19 = 19+0 (on_time = min_time+index)1(room_id),100(user_id),19(min_time),20 = 19+1 (on_time = min_time+index)1(room_id),100(user_id),19(min_time),21 = 19+2 (on_time = min_time+index)炸裂的目的:将用户在线的时间段[19-21] 拆分成具体的小时,19,20,21;
(4)根据room_id,on_time进行分组,求出每个直播间分时段的在线人数 

3 小结

    上述代码中用到的函数有:

一、字符串函数1、空格字符串函数:space语法:space(int n)返回值:string说明:返回值是n的空格字符串举例:select length (space(10)) --> 10一般space函数和split函数结合使用:select split(space(3),'');  -->   ["","","",""]2、split函数(分割字符串)语法:split(string str,string pat)返回值:array说明:按照pat字符串分割str,会返回分割后的字符串数组举例:select split ('abcdf','c') from test; -> ["ab","df"]3、repeat:重复字符串语法:repeat(string A, int n)返回值:string说明:将字符串A重复n遍。举例:select repeat('123', 3); -> 123123123一般repeat函数和split函数结合使用:select split(repeat(',',4),',');  -->  ["","","","",""]二、炸裂函数explode 语法:lateral view explode(split(a,',')) tmp  as new_column返回值:string说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串举例:select student_score from test lateral view explode(split(student_score,',')) 
tmp as student_scoreposexplode语法:lateral view posexploed(split(a,',')) tmp as pos,item 返回值:string说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串(炸裂具备瞎下角标 0,1,2,3)举例:select student_name, student_score from testlateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_namelateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_scorewhere student_score_index = student_name_index

2.4 某个时间段、每个小时内同时在线人数

0 问题描述

    根据直播间用户访问记录,统计某个时间段的各直播间​​​每个小时内的同时在线人数,假设时间段是['2021-12-01 19:00:00', '2021-12-01 23:00:00']

1 数据准备

​create table if not exists table7
(room_id      int comment '直播间id',user_id      int comment '用户id',login_time   string comment '用户进入直播间时间',logout_time  string comment '用户离开直播间时间'
)comment '直播间的用户访问记录';
INSERT overwrite table table7
VALUES (1,100,'2021-12-01 19:00:00', '2021-12-01 19:28:00'),(1,100,'2021-12-01 19:30:00', '2021-12-01 19:53:00'),(2,100,'2021-12-01 21:01:00', '2021-12-01 22:00:00'),(1,101,'2021-12-01 19:05:00', '2021-12-01 20:55:00'),(2,101,'2021-12-01 21:05:00', '2021-12-01 21:58:00'),(1,102,'2021-12-01 19:10:00', '2021-12-01 19:25:00'),(2,102,'2021-12-01 19:55:00', '2021-12-01 21:00:00'),(3,102,'2021-12-01 21:05:00', '2021-12-01 22:05:00'),(1,104,'2021-12-01 19:00:00', '2021-12-01 20:59:00'),(2,104,'2021-12-01 21:57:00', '2021-12-01 22:56:00'),(2,105,'2021-12-01 19:10:00', '2021-12-01 19:18:00'),(3,106,'2021-12-01 19:01:00', '2021-12-01 21:10:00');​

2 数据分析

   完整代码如下:

with temp_data1 as (selectroom_id,user_id,login_time,logout_time,hour(login_time) as min_time,hour(logout_time)  as max_time,split(space(hour(logout_time) - hour(login_time)), '') as disfrom table7where login_time >= '2021-12-01 19:00:00'and login_time <= '2021-12-01 21:00:00'
)selectroom_id,on_time,count(1) as cnt
from (select distinctroom_id,user_id,min_time,max_time,dis_index,(min_time + dis_index) as on_timefrom temp_data1 lateral view posexplode(dis) n1 as dis_index, dis_dataorder by user_id,min_time,max_time,dis_index) tmp
group by room_id, on_time
order by room_id, on_time;

3 小结

    解题思路与2.3一致,只需要限制下时间区间

2.5 学生各学科的成绩

0 问题描述

    基于不同的窗口限定范围(窗口边界),统计各学生的学科成绩。

1 数据准备

create table if not exists table9
(name    string comment '学生名称',subject string comment '学科',score   int comment '分数'
)comment '学生分数';
INSERT overwrite table table9
VALUES ('a','数学',12),('b','数学',19),('c','数学',17),('d','数学',24),('a','英语',77),('c','英语',11),('d','英语',34),('a','语文',61);

2 数据分析

selectname,subject,score,--1.全局聚合sum(score) over () as sum1,--2.根据学科分组,组内全局聚合sum(score) over (partition by subject) as sum2,--3.根据学科分组,根据分数排序,计算由起点到当前行的累积值sum(score) over (partition by subject order by score)  as sum3,--4.根据学科分组,根据分数排序,计算由起点到当前行的累积值 (sum3跟sum4的结果是一样的)sum(score) over (partition by subject order by score rows between unbounded preceding and current row ) as sum4,--5.根据学科分组,根据分数排序,计算上一行到当前行的累积值sum(score) over (partition by subject order by score rows between 1 preceding and current row ) as sum5,--6.根据学科分组,根据分数排序,计算上一行到下一行的累积值sum(score) over (partition by subject order by score rows between 1 preceding and 1 following)  as sum6,--7.根据学科分组,根据分数排序,计算当前行到后面所有行的累积值sum(score) over (partition by subject order by score rows between current row and unbounded following ) as sum7
from table9;

3 小结

  窗口函数 = 窗口+ 函数,解题时需要梳理清楚函数的计算范围。

这篇关于HiveSQL题——聚合函数(sum/count/max/min/avg)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/663164

相关文章

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

最大流=最小割=最小点权覆盖集=sum-最大点权独立集

二分图最小点覆盖和最大独立集都可以转化为最大匹配求解。 在这个基础上,把每个点赋予一个非负的权值,这两个问题就转化为:二分图最小点权覆盖和二分图最大点权独立集。   二分图最小点权覆盖     从x或者y集合中选取一些点,使这些点覆盖所有的边,并且选出来的点的权值尽可能小。 建模:     原二分图中的边(u,v)替换为容量为INF的有向边(u,v),设立源点s和汇点t

C++操作符重载实例(独立函数)

C++操作符重载实例,我们把坐标值CVector的加法进行重载,计算c3=c1+c2时,也就是计算x3=x1+x2,y3=y1+y2,今天我们以独立函数的方式重载操作符+(加号),以下是C++代码: c1802.cpp源代码: D:\YcjWork\CppTour>vim c1802.cpp #include <iostream>using namespace std;/*** 以独立函数

函数式编程思想

我们经常会用到各种各样的编程思想,例如面向过程、面向对象。不过笔者在该博客简单介绍一下函数式编程思想. 如果对函数式编程思想进行概括,就是f(x) = na(x) , y=uf(x)…至于其他的编程思想,可能是y=a(x)+b(x)+c(x)…,也有可能是y=f(x)=f(x)/a + f(x)/b+f(x)/c… 面向过程的指令式编程 面向过程,简单理解就是y=a(x)+b(x)+c(x)

Jenkins构建Maven聚合工程,指定构建子模块

一、设置单独编译构建子模块 配置: 1、Root POM指向父pom.xml 2、Goals and options指定构建模块的参数: mvn -pl project1/project1-son -am clean package 单独构建project1-son项目以及它所依赖的其它项目。 说明: mvn clean package -pl 父级模块名/子模块名 -am参数

利用matlab bar函数绘制较为复杂的柱状图,并在图中进行适当标注

示例代码和结果如下:小疑问:如何自动选择合适的坐标位置对柱状图的数值大小进行标注?😂 clear; close all;x = 1:3;aa=[28.6321521955954 26.2453660695847 21.69102348512086.93747104431360 6.25442246899816 3.342835958564245.51365061796319 4.87

OpenCV结构分析与形状描述符(11)椭圆拟合函数fitEllipse()的使用

操作系统:ubuntu22.04 OpenCV版本:OpenCV4.9 IDE:Visual Studio Code 编程语言:C++11 算法描述 围绕一组2D点拟合一个椭圆。 该函数计算出一个椭圆,该椭圆在最小二乘意义上最好地拟合一组2D点。它返回一个内切椭圆的旋转矩形。使用了由[90]描述的第一个算法。开发者应该注意,由于数据点靠近包含的 Mat 元素的边界,返回的椭圆/旋转矩形数据

Unity3D 运动之Move函数和translate

CharacterController.Move 移动 function Move (motion : Vector3) : CollisionFlags Description描述 A more complex move function taking absolute movement deltas. 一个更加复杂的运动函数,每次都绝对运动。 Attempts to

✨机器学习笔记(二)—— 线性回归、代价函数、梯度下降

1️⃣线性回归(linear regression) f w , b ( x ) = w x + b f_{w,b}(x) = wx + b fw,b​(x)=wx+b 🎈A linear regression model predicting house prices: 如图是机器学习通过监督学习运用线性回归模型来预测房价的例子,当房屋大小为1250 f e e t 2 feet^

JavaSE(十三)——函数式编程(Lambda表达式、方法引用、Stream流)

函数式编程 函数式编程 是 Java 8 引入的一个重要特性,它允许开发者以函数作为一等公民(first-class citizens)的方式编程,即函数可以作为参数传递给其他函数,也可以作为返回值。 这极大地提高了代码的可读性、可维护性和复用性。函数式编程的核心概念包括高阶函数、Lambda 表达式、函数式接口、流(Streams)和 Optional 类等。 函数式编程的核心是Lambda