首页 > 其他分享 >hive函数

hive函数

时间:2024-11-07 20:17:52浏览次数:6  
标签:函数 -- over hive orderdate order select name

一、查看函数

show functions;   --查看所有的函数

desc function functionName; -查看某个具体的函数如何使用

二、基础函数

2.1、日期函数

1.current_date();        #当前系统日期        格式:"yyyy-MM-dd"

2.current_timestamp();    #当前系统时间戳:    格式:"yyyy-MM-dd HH:mm:ss.ms"

3.unix_timestamp();    #当前系统时间戳    格式:距离1970年1月1日0点的秒数。

4.from_unixtime #时间戳转日期函数

5.#计算时间差函数

datediff()   #数据是前面的时间减去后面的时间,相差的天数

months_between()   #前面的时间减去后面时间的月数 ,可以精确到小数

6.date_add() #日期相加

7.date_sub  #日期相减

8.add_months() #月份相加

9.year()、month()、day()、hour()、minute()、second()  # 日期时间分量函数

10.last_day()、next_day() # 日期定位函数

11.to_date() #字符串转日期

12.date_format() #将日期转为字符串

2.2、字符串函数 

-- lower(转小写)
select lower('ABC');
--upper(转大写)
select upper('abc');
--length(字符串长度,字符数)
select length('abc');
-- concat(字符串拼接)
select concat("A", 'B');
-- concat_ws(指定分隔符)
select concat_ws('-','a' ,'b','c');
-- substr(求子串)
select substr('abcde',3);
-- split(str,regex) 切分字符串,返回数组 
select split("a-b-c-d-e-f","-");
select concat_ws('&',split('a,b,c,d,f',','));
select replace('a,b,c,d,f',',','&');

2.3、数学函数

--round 四舍五入((42.3 =>42))
select round(42.3);
--ceil 向上取整(42.3 =>43)
select ceil(42.3);
--floor 向下取整(42.3 =>42)
select floor(42.3);
-- 求绝对值
select abs(-1);

三、窗口函数

   窗口函数(Window Functions)是 SQL 中的一种特殊类型的函数,用于在一组相关行上进行计算,而不是在整个表上进行计算。窗口函数允许你在不改变数据集的情况下,对数据进行聚合和排序等操作。这使得窗口函数在处理复杂的数据分析任务时非常有用。

3.1准备数据

order.txt

姓名,购买日期,购买数量
saml,2018-01-01,10
saml,2018-01-08,55
tony,2018-01-07,50
saml,2018-01-05,46
tony,2018-01-04,29
tony,2018-01-02,15
saml,2018-02-03,23
mart,2018-04-13,94
saml,2018-04-06,42
mart,2018-04-11,75
mart,2018-04-09,68
mart,2018-04-08,62
neil,2018-05-10,12
neil,2018-06-12,80

建表并且加载数据

-1. 创建order表:
create table if not exists t_order
(
    name      string,
    orderdate string,
    cost      int
)  row format delimited fields terminated by ',';
-2. 加载数据:
load data local inpath "/home/hivedata/order.txt" into table t_order; 

3.2、distribute by子句

在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录

语法: over(distribute by colname[,colname.....])

需求:查看顾客的购买明细及月购买总额

select *,sum(cost) over(distribute by substr(orderdate,1,7) ) from t_order ;

3.3、sort by子句

sort by子句会让输入的数据强制排序 (强调:当使用排序时,窗口会在组内逐行变大

语法:  over([distribute by colname] [sort by colname [desc|asc]])

需求:查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序

select *,sum(cost) over(distribute by name,month(orderdate) sort by orderdate desc ) from t_order ;

可以使用partition by + order by 组合来代替distribute by+sort by组合 

select *,sum(cost) over(partition by name,month(orderdate) order by orderdate desc ) from t_order ;

3.4、window子句

如果要对窗口的结果做更细粒度的划分,那么就使用window子句,常见的有下面几个
PRECEDING:往前 
FOLLOWING:往后 
CURRENT ROW:当前行 
UNBOUNDED:起点,
UNBOUNDED PRECEDING:表示从前面的起点, 
UNBOUNDED FOLLOWING:表示到后面的终点 

select name,orderdate,cost,
       sum(cost) over() as sample1, -- 所有行相加
       sum(cost) over(partition by name) as sample2,-- 按name分组,组内数据相加
       sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组,组内数据累加
       sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,-- 与sample3一样,由起点到当前行的聚合
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, -- 当前行和前面一行做聚合
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,-- 当前行和前边一行及后面一行
       sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
from t_order; 

四、序列函数

4.1、NTILE 

ntile 是Hive很强大的一个分析函数。可以看成是:它把有序的数据集合 平均分配 到 指定的数量(num)个桶中, 将桶号分配给每一行。如果不能平均分配,则优先分配较小编号的桶,并且各个桶中能放的行数最多相差1

select name,orderdate,cost,
ntile(3) over(partition by name) -- 按照name进行分组,在分组内将数据切成3份
from t_order;

4.2、LAG和LEAD函数

ag返回当前数据行的前第n行的数据

语法:lag(colName,n[,default value]): 取字段的前第n个值。如果为null,显示默认值

lead返回当前数据行的后第n行的数据

需求:查询顾客上次购买的时间

select * ,lag(orderdate,1) over( partition by name order by orderdate ) from t_order;

4.3、FIRST_VALUE和LAST_VALUE

first_value 取分组内排序后,截止到当前行,第一个值

last_value 分组内排序后,截止到当前行,最后一个值

select name,orderdate,cost,
   first_value(orderdate) over(partition by name order by orderdate) as time1,
   last_value(orderdate) over(partition by name order by orderdate) as time2
from t_order;

五、排名函数

5.1、数据准备

stu_score.txt

1 gp1808 80
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86

建表加载数据

create table if not exists stu_score(
userid int,
classno string,
score int
)
row format delimited 
fields terminated by ' ';

load data local inpath '/home/hivedata/stu_score.txt' overwrite into table stu_score;

5.2、row_number()

row_number从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

需求:获取每次考试的排名情况

select *,
-- 没有并列,相同名次依顺序排
row_number() over(distribute by classno sort by score desc) rn1
from stu_score; 

5.3、rank()

 生成数据项在分组中的排名,排名相等会在名次中留下空位

 需求:获取每次考试的排名情况

select *,
-- rank():有并列,相同名次空位
rank() over(distribute by classno sort by score desc) rn1
from stu_score;

5.4、dense_rank()

生成数据项在分组中的排名,排名相等会在名次中不会留下空位

需求:对每个班级的每次考试按照考试成绩倒序

select *,dense_rank() over(partition by classno order by  score desc) from stu_score;

标签:函数,--,over,hive,orderdate,order,select,name
From: https://blog.csdn.net/xieyichun_/article/details/143577622

相关文章

  • js 的generator函数是什么
    在JavaScript中,Generator函数(生成器函数)是一种特殊类型的函数,它可以暂停执行并且可以在后续的某个时刻恢复执行。与普通函数不同,Generator函数不会在调用时立即执行,而是返回一个Generator对象,你可以通过该对象控制函数的执行过程。1.如何定义一个Generator函数Generato......
  • hive基础知识分享(二)
    写在前面今天继续学习hive部分的知识。以下是您提供的内容转成的Markdown格式:Hive相关知识hive中不同的count区别selectclazz,count(distinctid)ascnt,count(*)ascnt,count(1)ascnt_1,count(id)ascnt_idfromstudentsgroupby......
  • 函数的间断点问题
    函数是数学分析中的重要概念,而函数的连续性与间断性是研究函数行为的基础。今天,我们来聊一聊函数的间断点,介绍什么是函数的连续性、不同类型的间断点,以及一些特殊的讨论情况。函数的连续性和间断性我们首先来回顾一下函数在某一点连续的定义。设有函数\(f(x)\),如果\(x=a\)......
  • 【Hive SQL】如何判断一个字段是否包含某个特定的值
    在HiveSQL中,如果你需要判断一个字段是否包含某个特定的值,你可以使用 LIKE 或RLIKE 关键字来进行字符串匹配。此外,Hive也支持一些字符串函数,如 INSTR 和LOCATE,这些都可以用于不同的需求场景。以下是一些常见的方法来判断一个字段是否包含某个值:数据准备——创建表格......
  • 构造函数原型对象语法、原型链、原型对象
    目录一、前言二、编程思想面向过程面向对象三、构造函数四、原型对象constructor属性对象原型原型继承原型链一、前言  通过本篇博客,我们将了解面向对象编程的一般特征,掌握基于构造函数原型对象的逻辑封装,掌握基于原型对象实现的继承,理解什么原型链及其作用......
  • 生产环境中使用:带有核函数的 SVM 处理非线性问题
            在逻辑回归中,我们可以通过引入 核方法(KernelTrick) 来处理非线性关系。虽然逻辑回归本身不直接支持核方法,但我们可以借助特征转换工具来手动实现类似的效果。不过,更常见的是在 支持向量机(SVM) 中应用核方法,这里我们将介绍如何使用 带有核函数的SVM 来处......
  • [python turtle summary] Python 海龟画图 函数总结
    Turtle文档导入turtleimportturtleastimportturtlefromturtleimport*Turtle函数方法移动和绘制penup()抬笔pendown()落笔goto(x,y)移动forward(distance)|fd(distance)前进backward(distance)|back(distance)|bk(distance)后退right(angle)|rt(ang......
  • python画图|hist()函数深层体验
    【1】引言前述学习已经掌握hist()函数的基本运用技巧,可通过下述链接直达:python画图|hist()函数画直方图初探-CSDN博客 python画图|hist()函数画直方图进阶-CSDN博客我们已经理解hist()函数本质上画的是概率分布图,相关知识属于数理统计范畴,日常运用较多。为进一步实现运用......
  • c语言中函数体中的变量声明不能使用和形参相同的变量名
     001、[root@PC1test]#lstest.c[root@PC1test]#cattest.c#include<stdio.h>intmax(inta,intb)//创建一个名为max的函数{intk=100;if(a>b){returna;}......
  • 函数基础(上)
    九函数基础(上)9.1函数简介之前一直有使用内置函数print()、input()、sum()、len()、max()函数:可以用来保存代码,在需要的时候,对这些可执行代码进行重复利用总结遇到重复功能的时候,直接调用即可,减少工作代码量提升项目结构性,分工明确提高可读性维护起来,方便9......