掌握Hive函数[3]：从基础到高级应用

目录

窗口函数（开窗函数）

 概述

 常用窗口函数

1）聚合函数

2）跨行取值函数

（1）LEAD 和 LAG

（2）FIRST_VALUE 和 LAST_VALUE

3）排名函数

 案例演示

1. 数据准备

1）表结构

2）建表语句

3）装载语句

2. 需求

1）统计每个用户截至每次下单的累积下单总额

（1）期望结果

（2）需求实现

2）统计每个用户截至每次下单的当月累积下单总额

（1）期望结果

（2）需求实现

3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

（1）期望结果

（2）需求实现

4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

（1）期望结果

（2）需求实现

5）为每个用户的所有下单记录按照订单金额进行排名

（1）期望结果

（2）需求实现

 自定义函数

自定义UDF函数

---

窗口函数（开窗函数）

 概述

窗口函数是一种在SQL中处理数据的强大工具，它允许用户在结果集的一个特定窗口或范围内执行计算，而不是在整个查询结果上进行操作。

 常用窗口函数

按照功能，常用窗口函数可以划分为以下几类：聚合函数、跨行取值函数、排名函数。

1）聚合函数

• MAX：最大值。
• MIN：最小值。
• SUM：求和。
• AVG：平均值。
• COUNT：计数。

2）跨行取值函数

（1）LEAD 和 LAG

• LEAD：向前查看，即获取当前行之后的某一行的值。
• LAG：向后查看，即获取当前行之前的一行的值。

 

（2）FIRST_VALUE 和 LAST_VALUE

• FIRST_VALUE：返回窗口中第一个行的表达式的值。
• LAST_VALUE：返回窗口中最后一个行的表达式的值。

 

3）排名函数

• RANK：根据一个或多个列的值对行进行排序，并跳过中间的空位。
• DENSE_RANK：与 RANK 类似，但是不会跳过任何排名。
• ROW_NUMBER：给每一行分配一个唯一的整数。

 案例演示

1. 数据准备

1）表结构

order_id	user_id	user_name	order_date	order_amount
1	1001	小元	2022-01-01	10
2	1002	小海	2022-01-02	15
3	1001	小元	2022-02-03	23
4	1002	小海	2022-01-04	29
5	1001	小元	2022-01-05	46
6	1001	小元	2022-04-06	42
7	1002	小海	2022-01-07	50
8	1001	小元	2022-01-08	50
9	1003	小辉	2022-04-08	62
10	1003	小辉	2022-04-09	62
11	1004	小猛	2022-05-10	12
12	1003	小辉	2022-04-11	75
13	1004	小猛	2022-06-12	80
14	1003	小辉	2022-04-13	94

2）建表语句

CREATE TABLE order_info (
  order_id   STRING, --订单id
  user_id    STRING, -- 用户id
  user_name  STRING, -- 用户姓名
  order_date STRING, -- 下单日期
  order_amount INT    -- 订单金额
);

3）装载语句

INSERT OVERWRITE TABLE order_info
VALUES 
('1', '1001', '小元', '2022-01-01', '10'),
('2', '1002', '小海', '2022-01-02', '15'),
('3', '1001', '小元', '2022-02-03', '23'),
('4', '1002', '小海', '2022-01-04', '29'),
('5', '1001', '小元', '2022-01-05', '46'),
('6', '1001', '小元', '2022-04-06', '42'),
('7', '1002', '小海', '2022-01-07', '50'),
('8', '1001', '小元', '2022-01-08', '50'),
('9', '1003', '小辉', '2022-04-08', '62'),
('10', '1003', '小辉', '2022-04-09', '62'),
('11', '1004', '小猛', '2022-05-10', '12'),
('12', '1003', '小辉', '2022-04-11', '75'),
('13', '1004', '小猛', '2022-06-12', '80'),
('14', '1003', '小辉', '2022-04-13', '94');

2. 需求

1）统计每个用户截至每次下单的累积下单总额

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	sum_so_far
1	1001	小元	2022-01-01	10	10
5	1001	小元	2022-01-05	46	56
8	1001	小元	2022-01-08	50	106
3	1001	小元	2022-02-03	23	129
6	1001	小元	2022-04-06	42	171
2	1002	小海	2022-01-02	15	15
4	1002	小海	2022-01-04	29	44
7	1002	小海	2022-01-07	50	94
9	1003	小辉	2022-04-08	62	62
10	1003	小辉	2022-04-09	62	124
12	1003	小辉	2022-04-11	75	199
14	1003	小辉	2022-04-13	94	293
11	1004	小猛	2022-05-10	12	12
13	1004	小猛	2022-06-12	80	92

（2）需求实现

SELECT
  order_id,
  user_id,
  user_name,
  order_date,
  order_amount,
  SUM(order_amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_so_far
FROM order_info;

2）统计每个用户截至每次下单的当月累积下单总额

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	sum_so_far
1	1001	小元	2022-01-01	10	10
5	1001	小元	2022-01-05	46	56
8	1001	小元	2022-01-08	50	106
3	1001	小元	2022-02-03	23	23
6	1001	小元	2022-04-06	42	42
2	1002	小海	2022-01-02	15	15
4	1002	小海	2022-01-04	29	44
7	1002	小海	2022-01-07	50	94
9	1003	小辉	2022-04-08	62	62
10	1003	小辉	2022-04-09	62	124
12	1003	小辉	2022-04-11	75	199
14	1003	小辉	2022-04-13	94	293
11	1004	小猛	2022-05-10	12	12
13	1004	小猛	2022-06-12	80	80

（2）需求实现

SELECT
  order_id,
  user_id,
  user_name,
  order_date,
  order_amount,
  SUM(order_amount) OVER (PARTITION BY user_id, SUBSTRING(order_date, 1, 7) ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_so_far
FROM order_info;

3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	diff
1	1001	小元	2022-01-01	10	0
5	1001	小元	2022-01-05	46	4
8	1001	小元	2022-01-08	50	3
3	1001	小元	2022-02-03	23	26
6	1001	小元	2022-04-06	42	62
2	1002	小海	2022-01-02	15	0
4	1002	小海	2022-01-04	29	2
7	1002	小海	2022-01-07	50	3
9	1003	小辉	2022-04-08	62	0
10	1003	小辉	2022-04-09	62	1
12	1003	小辉	2022-04-11	75	2
14	1003	小辉	2022-04-13	94	2
11	1004	小猛	2022-05-10	12	0
13	1004	小猛	2022-06-12	80	33

（2）需求实现

SELECT
  order_id,
  user_id,
  user_name,
  order_date,
  order_amount,
  NVL(DATEDIFF(order_date, last_order_date), 0) diff
FROM
(
  SELECT
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    LAG(order_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY order_date) last_order_date
  FROM order_info
) t1;

4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	first_date	last_date
1	1001	小元	2022-01-01	10	2022-01-01	2022-01-08
5	1001	小元	2022-01-05	46	2022-01-01	2022-01-08
8	1001	小元	2022-01-08	50	2022-01-01	2022-01-08
3	1001	小元	2022-02-03	23	2022-02-03	2022-02-03
6	1001	小元	2022-04-06	42	2022-04-06	2022-04-06
2	1002	小海	2022-01-02	15	2022-01-02	2022-01-07
4	1002	小海	2022-01-04	29	2022-01-02	2022-01-07
7	1002	小海	2022-01-07	50	2022-01-02	2022-01-07
9	1003	小辉	2022-04-08	62	2022-04-08	2022-04-13
10	1003	小辉	2022-04-09	62	2022-04-08	2022-04-13
12	1003	小辉	2022-04-11	75	2022-04-08	2022-04-13
14	1003	小辉	2022-04-13	94	2022-04-08	2022-04-13
11	1004	小猛	2022-05-10	12	2022-05-10	2022-05-10
13	1004	小猛	2022-06-12	80	2022-06-12	2022-06-12

（2）需求实现

SELECT
  order_id,
  user_id,
  user_name,
  order_date,
  order_amount,
  FIRST_VALUE(order_date) OVER (PARTITION BY user_id, SUBSTRING(order_date, 1, 7) ORDER BY order_date) first_date,
  LAST_VALUE(order_date) OVER (PARTITION BY user_id, SUBSTRING(order_date, 1, 7) ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_date
FROM order_info;

5）为每个用户的所有下单记录按照订单金额进行排名

（1）期望结果

order_id	user_id	user_name	order_date	order_amount	rk	drk	rn
8	1001	小元	2022-01-08	50	1	1	1
5	1001	小元	2022-01-05	46	2	2	2
6	1001	小元	2022-04-06	42	3	3	3
3	1001	小元	2022-02-03	23	4	4	4
1	1001	小元	2022-01-01	10	5	5	5
7	1002	小海	2022-01-07	50	1	1	1
4	1002	小海	2022-01-04	29	2	2	2
2	1002	小海	2022-01-02	15	3	3	3
14	1003	小辉	2022-04-13	94	1	1	1
12	1003	小辉	2022-04-11	75	2	2	2
9	1003	小辉	2022-04-08	62	3	3	3
10	1003	小辉	2022-04-09	62	3	3	4
13	1004	小猛	2022-06-12	80	1	1	1
11	1004	小猛	2022-05-10	12	2	2	2

（2）需求实现

SELECT
  order_id,
  user_id,
  user_name,
  order_date,
  order_amount,
  RANK() OVER (PARTITION BY user_id ORDER BY order_amount DESC) rk,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_amount DESC) drk,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) rn
FROM order_info;

 自定义函数

1）Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便地扩展。

2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

3）根据用户自定义函数类别分为以下三种：

• UDF（User-Defined-Function）
  • 一进一出。
• UDAF（User-Defined Aggregation Function）
  • 用户自定义聚合函数，多进一出。
  • 类似于：count/max/min。
• UDTF（User-Defined Table-Generating Functions）
  • 用户自定义表生成函数，一进多出。
  • 如 lateral view explode()。

4）官方文档地址

HivePlugins - Apache Hive - Apache Software Foundationhttps://cwiki.apache.org/confluence/display/Hive/HivePlugins

5）编程步骤

• （1）继承Hive提供的类
  • org.apache.hadoop.hive.ql.udf.generic.GenericUDF
  • org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
• （2）实现类中的抽象方法
• （3）在hive的命令行窗口创建函数
  • 添加jar。

    ADD JAR linux_jar_path;

  • 创建function。

    CREATE [TEMPORARY] FUNCTION [dbname.]function_name AS class_name;

• （4）在hive的命令行窗口删除函数

  DROP [TEMPORARY] FUNCTION [IF EXISTS] [dbname.]function_name;

自定义UDF函数

0）需求

自定义一个UDF实现计算给定基本数据类型的长度，例如：

hive(default)> SELECT my_len("abcd");

输出结果应为4。

1）创建一个Maven工程Hive

2）导入依赖

<dependencies>
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>3.1.3</version>
  </dependency>
</dependencies>

3）创建一个类

package com.lzl.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

/**
 * 我们需计算一个给定基本数据类型的长度
 */
public class MyUDF extends GenericUDF {
  /**
   * 判断传进来的参数的类型和长度
   * 约定返回的数据类型
   */
  @Override
  public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
    if (arguments.length != 1) {
      throw new UDFArgumentLengthException("Please give me only one arg.");
    }
    if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
      throw new UDFArgumentTypeException(1, "I need primitive type arg.");
    }
    return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
  }

  /**
   * 解决具体逻辑的
   */
  @Override
  public Object evaluate(DeferredObject[] arguments) throws HiveException {
    Object o = arguments[0].get();
    if (o == null) {
      return 0;
    }
    return o.toString().length();
  }

  @Override
  // 用于获取解释的字符串
  public String getDisplayString(String[] children) {
    return "";
  }
}

4）创建临时函数

• （1）打成jar包上传到服务器 /opt/module/hive/datas/myudf.jar。
• （2）将jar包添加到hive的classpath，临时生效

  hive (default)> ADD JAR /opt/module/hive/datas/myudf.jar;

• （3）创建临时函数与开发好的java class关联

  hive (default)> 
  CREATE TEMPORARY FUNCTION my_len 
  AS "com.lzl.hive.udf.MyUDF";

• （4）即可在HQL中使用自定义的临时函数

  hive (default)> 
  SELECT 
    ename,
    my_len(ename) ename_len 
  FROM emp;

  • （5）删除临时函数

    hive (default)> DROP TEMPORARY FUNCTION my_len;

    注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以使用，其他会话全都不能使用。

5）创建永久函数

• （1）创建永久函数 注意：因为ADD JAR本身也是临时生效，所以在创建永久函数的时候，需要指定路径（并且因为元数据的原因，这个路径还得是HDFS上的路径）。

  hive (default)> 
  CREATE FUNCTION my_len2 
  AS "com.lzl.hive.udf.MyUDF" 
  USING JAR "hdfs://hadoop12:8020/udf/myudf.jar";

• （2）即可在HQL中使用自定义的永久函数

  hive (default)> 
  SELECT 
    ename,
    my_len2(ename) ename_len 
  FROM emp;

• （3）删除永久函数

  hive (default)> DROP FUNCTION my_len2;

  注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。 永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。 永久函数使用的时候，需要在指定的库里面操作，或者在其他库里面使用的话加上 库名.函数名。