跳到主要内容

Hive 函数

Hive 高阶函数​

模拟数据准备

-- 创建员工表
CREATE TABLE IF NOT EXISTS emp (
    empno INT,
    ename STRING,
    job STRING,
    sal DOUBLE,
    deptno INT
);

-- 插入员工表模拟数据
INSERT INTO emp VALUES
(1, 'Alice', 'Clerk', 2000, 10),
(2, 'Bob', 'Salesman', 2500, 10),
(3, 'Charlie', 'Manager', 3500, 20),
(4, 'David', 'Clerk', 2200, 20),
(5, 'Eve', 'Salesman', 2800, 30);

-- 创建员工技能表
CREATE TABLE IF NOT EXISTS emp_skills (
    empno INT,
    skills ARRAY<STRING>
);

-- 插入员工技能表模拟数据
INSERT INTO emp_skills VALUES
(1, ARRAY('Java', 'Python')),
(2, ARRAY('Sales', 'Communication')),
(3, ARRAY('Management', 'Leadership')),
(4, ARRAY('Database', 'SQL')),
(5, ARRAY('Sales', 'Negotiation'));

-- 创建包含数组的临时表
CREATE TABLE IF NOT EXISTS temp_table (
    fruits ARRAY<STRING>
);

-- 插入临时表模拟数据
INSERT INTO temp_table VALUES
(ARRAY('apple', 'banana', 'cherry'));

-- 创建销售表
CREATE TABLE IF NOT EXISTS sales (
    region STRING,
    store STRING,
    sales_amount DOUBLE
);

-- 插入销售表模拟数据
INSERT INTO sales VALUES
('North', 'Store1', 1000),
('North', 'Store2', 1500),
('South', 'Store3', 2000),
('South', 'Store4', 2500);

窗口函数​

窗口函数可以在一个查询结果集的 “窗口” 内进行计算，而不需要对结果集进行分组。

排序窗口函数​

  • ROW_NUMBER()：为结果集中的每一行分配一个唯一的、连续的整数，从 1 开始。
-- 为每个部门的员工按工资降序编号
SELECT 
    deptno,
    ename,
    sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as row_num
FROM 
    emp;
  • RANK()：为结果集中的行分配排名，相同的值会得到相同的排名，下一个排名会跳过相应的数量
-- 为每个部门的员工按工资降序排名
SELECT 
    deptno,
    ename,
    sal,
    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rank_num
FROM 
    emp;

  • DENSE_RANK()：与 RANK() 类似，但相同的值得到相同的排名，下一个排名是连续的

    -- 为每个部门的员工按工资降序密集排名
    SELECT 
        deptno,
        ename,
        sal,
        DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as dense_rank_num
    FROM 
        emp;

聚合窗口函数

  • SUM()：在窗口内对指定列求和。
-- 计算每个部门员工工资的累计和
SELECT 
    deptno,
    ename,
    sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY empno) as cumulative_sum
FROM 
    emp;
  • AVG()：在窗口内计算指定列的平均值。
  -- 计算每个部门员工工资的移动平均值
  SELECT 
      deptno,
      ename,
      sal,
      AVG(sal) OVER (PARTITION BY deptno ORDER BY empno ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as moving_avg
  FROM 
      emp;
  • COUNT()：在窗口内统计行数。
-- 统计每个部门的员工数量
SELECT 
    deptno,
    ename,
    COUNT(*) OVER (PARTITION BY deptno) as emp_count
FROM 
    emp;

分析窗口函数

  • LAG()：返回当前行之前第 n 行的值。
-- 获取每个员工的上一个员工的工资
SELECT 
    deptno,
    ename,
    sal,
    LAG(sal, 1) OVER (PARTITION BY deptno ORDER BY empno) as prev_sal
FROM 
    emp;
  • LEAD()：返回当前行之后第 n 行的值。
  -- 获取每个员工的下一个员工的工资
SELECT 
    deptno,
    ename,
    sal,
    LEAD(sal, 1) OVER (PARTITION BY deptno ORDER BY empno) as next_sal
FROM 
    emp;
  • NTILE()：将结果集分成 n 个桶，并为每一行分配桶编号。
-- 将每个部门的员工按工资分成 3 组
SELECT 
    deptno,
    ename,
    sal,
    NTILE(3) OVER (PARTITION BY deptno ORDER BY sal DESC) as bucket_num
FROM 
    emp;

行列转换函数​

行转列函数​

  • COLLECT_LIST()：将分组内的某列值收集到一个数组中，不进行去重。
-- 收集每个部门的员工姓名到一个数组中
SELECT 
    deptno,
    COLLECT_LIST(ename) as emp_names
FROM 
    emp
GROUP BY 
    deptno;
  • COLLECT_SET()：将分组内的某列值收集到一个数组中，并进行去重。
-- 收集每个部门的员工职位到一个去重数组中
SELECT 
    deptno,
    COLLECT_SET(job) as unique_jobs
FROM 
    emp
GROUP BY 
    deptno;
  • CONCAT_WS()：使用指定的分隔符连接多个字符串。
-- 将每个部门的员工姓名用逗号连接成一个字符串
SELECT 
    deptno,
    CONCAT_WS(',', COLLECT_LIST(ename)) as emp_names_str
FROM 
    emp
GROUP BY 
    deptno;

列转行函数​

  • EXPLODE()：将数组或映射类型的列拆分成多行。
-- 创建一个包含数组的表
WITH temp_table AS (
    SELECT 
        ARRAY('apple', 'banana', 'cherry') as fruits
)
SELECT 
    exploded_fruit
FROM 
    temp_table
LATERAL VIEW EXPLODE(fruits) exploded_table AS exploded_fruit;
  • LATERAL VIEW：与 EXPLODE() 等函数结合使用，将结果集与原始表进行连接。
-- 将员工掌握的技能拆分成多行
SELECT 
    empno,
    skill
FROM 
    emp_skills
LATERAL VIEW EXPLODE(skills) exploded_table AS skill;

聚合高阶函数​

GROUPING SETS​

允许在单个查询中执行多维度分组聚合，避免编写多个UNION ALL查询

1.基本概念

  • 是GROUP BY子句的扩展，可指定多个分组组合
  • 相比多个UNION ALL查询能显著提升性能

2.语法结构

SELECT col1, col2, agg_func(col3)
FROM table
GROUP BY GROUPING SETS ((col1,col2), (col1), (col2), ())

3.应用场景

  • 生成多维度报表时替代UNION ALL
  • 数据仓库中的交叉分析

4.执行原理

  • 对每个指定分组子集独立执行GROUP BY

5.性能优化建议

  • 合理选择分组组合数量避免过度计算
  • 与分区表结合使用提升效率

CUBE​

对分组列的所有可能组合进行聚合。

1.基本概念

CUBE会对指定维度生成所有可能的子集组合，包含全维度聚合、单一维度聚合及交叉组合聚合

2.核心语法

SELECT col1, col2, SUM(metric)
FROM table
GROUP BY CUBE(col1, col2)
ORDER BY GROUPING__ID;

3.应用场景

  • ‌商业智能分析‌：快速生成销售数据的地区+产品+时间全维度报表
  • ‌替代UNION ALL‌：简化多维度聚合查询的编写，减少代码冗余
  • ‌交叉维度验证‌：检测数据在不同维度组合下的分布异常

4.执行原理

  • 多维组合聚合
  • 自动生成所有子集分组
  • 优化计算合并执行

5.性能优化建议

  • 结合分区表减少扫描数据量
  • 合理选择维度数量（通常不超过5个）

ROLLUP​

对分组列进行层次化聚合，通常用于处理具有层次关系的数据。

1.基本概念

ROLLUP 是 Hive 中的多维聚合函数，按维度层级顺序生成渐进式汇总结果（如 年→月→日 生成 (年,月,日)、(年,月)、(年)、总计 的组合）

2.核心语法

-- 按层次聚合（产品→地区→销售额）
SELECT product, region, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(product, region);

‌输出结果‌：

  • (product, region)：细粒度产品-地区销售额
  • (product)：产品级汇总
  • ()：全局总计

应用场景

  1. 时间序列分析，例如年、月、日
  2. 地理层级统计，例如 省、市、县

区别

对比维度‌‌GROUPING SETS‌‌CUBE‌‌ROLLUP‌
‌核心逻辑‌手动指定任意维度组合进行聚合（如 (A,B), (B,C)）生成所有可能的维度组合（2^N 种组合，如 A,B,C 生成 (A,B,C)、(A,B)、(A,C) 等按维度顺序生成层次化聚合（如 A→B→C 生成 (A,B,C)、(A,B)、(A)、总计）
‌组合生成规则‌完全自定义组合数量和维度关系（用户显式定义）全维度笛卡尔积，覆盖所有子集组合68固定层次递缩，维度数为 N 时生成 N+1 种组合
‌组合数量‌由用户指定（如 GROUPING SETS((A,B), (C)) 生成 2 种组合）2^N（N 为维度数，维度较多时资源消耗大）N+1（维度数较少时效率更高
‌灵活性‌支持非连续的、业务核心维度组合的灵活聚合（如跳过中间层级）适用于需要全面分析所有维度交叉影响的场景（如多维分析仅支持连续层次化维度（如时间、地理层级
‌典型应用场景‌按需聚合特定组合（如同时分析 (产品,渠道) 和 (地区,用户等级)）全面探索所有维度关系（如用户画像的多维度交叉分析生成层级化汇总报表（如年→月→日销售额渐进汇总
‌性能消耗‌用户控制组合数，性能消耗与指定组合数正相关维度数较多时组合数指数级增长，资源消耗高（如 N=5 生成 32 种组合）组合数线性增长（维度数≤5 时推荐）

其他高阶函数​

  • SORT_ARRAY()：对数组进行排序。
-- 对员工掌握的技能数组进行排序
SELECT 
    empno,
    SORT_ARRAY(skills) as sorted_skills
FROM 
    emp_skills;
  • REFLECT()：调用 Java 类的静态方法。
-- 调用 Java 的 Math 类的 sqrt 方法计算平方根
SELECT 
    REFLECT('java.lang.Math', 'sqrt', 16) as square_root;
  • TRANSFORM()：使用外部脚本对数据进行处理。

自定义函数​

特性UDFUDAFUDTF
全称User-Defined FunctionUser-Defined Aggregation FunctionUser-Defined Table-Generating Function
输入单行输入多行输入单行输入
输出单个值单个聚合值多行和多列
主要用途转换或处理单个值聚合计算行转列，数据扩展
示例应用字符串处理，日期转换自定义平均值，中位数计算字符串拆分，JSON解析

经典SQL 面试题：​

  1. 查询每科成绩都大于 80 分的学生信息（CSDN 博客）：

  • 题目：有一张学生成绩表scdn_student_score_test，包含name（学生姓名）、subject（学科）、score（成绩）字段，求所有学科成绩都大于等于 80 分的学生姓名。

  • 答案：

-- 思路一：通过分组求最小成绩，再筛选
select t1.name 
from (
    select name, min(score) as min_score 
    from hdw_tmp_dev.scdn_student_score_test 
    group by name 
) as t1 
where t1.min_score >= 80;

-- 思路二：巧用左关联进行筛选（略，可参考相关资料进一步学习）
  1. 行列转换问题（CSDN 博客）：

  • 题目：

  • 行转列：有学生成绩表，结构同前，将每行的学科和成绩转换为列，即每个学生一行，包含数学、语文、英语等学科的成绩列。

  • 列转行：有一张表scdn_student_score_test_collect，包含name（学生姓名）和subject_list（选课列表，字符串形式，如 "数学，语文，英语"）字段，将其转换为每行一个学生的一门选课。

  • 答案：

-- 行转列
select name,
       max(case when subject = '数学' then score end) as math_score,
       max(case when subject = '语文' then score end) as china_score,
       max(case when subject = '英语' then score end) as english_score
from hdw_tmp_dev.scdn_student_score_test
group by name;

-- 列转行
select name, tmp.subject
from hdw_tmp_dev.scdn_student_score_test_collect
lateral view explode(split(subject_list, ',')) tmp as subject;
  1. 留存问题（CSDN 博客）：

  • 题目：有用户登录表csdn_user_logon_test，包含user_name（用户名）、logon_date（登录日期）等字段，查看当天登录后第 N 天是否登录。

  • 答案：

-- 方案一：利用lead函数
select t1.user_name, t1.logon_date,
       case when lead1_logon_date = date_add(logon_date, 1) then '1天留存' end as 1day_remain,
       case when lead3_logon_date = date_add(logon_date, 3) then '3天留存' end as 3day_remain
from (
    select user_name, logon_date,
           lead(user_name, 1) over(partition by user_name order by logon_date) as lead1_user_name,
           lead(logon_date, 1) over(partition by user_name order by logon_date) as lead1_logon_date,
           lead(user_name, 3) over(partition by user_name order by logon_date) as lead3_user_name,
           lead(logon_date, 3) over(partition by user_name order by logon_date) as lead3_logon_date
    from hdw_tmp_dev.csdn_user_logon_test
) as t1;
  1. 分组排序取 TopN：

  • 题目：有员工表tb_emp，包含empno（员工编号）、ename（员工姓名）、salary（薪资）和deptno（部门编号）字段，找出每个部门薪资最高的前两名员工。

  • 答案：

with ranked_emps as (
    select empno, ename, salary, deptno,
           row_number() over(partition by deptno order by salary desc) as rank
    from tb_emp
)
select empno, ename, salary, deptno
from ranked_emps
where rank <= 2;
  1. 模拟循环操作（CSDN 博客）：

  • 题目：不借助其他任何外表，实现产生连续数值，如产生 1 至 1000000 的连续数值。

  • 答案：

select row_number() over() as id
from (select split(space(999999), ' ') as x) t
lateral view explode(x) ex;
  1. 数据扩充与收缩（CSDN 博客）：

  • 题目：有表t6，包含字段a，内容为3,2,4等。实现数据扩充，如将3扩充为3、2、1，2扩充为2、1，4扩充为4、3、2、1等；以及数据扩充排除偶数等不同要求的操作。

  • 答案：以基本数据扩充为例

select t.a, concat_ws('、', collect_set(cast(t.rn as string))) as b
from (
    select t6.a, b.rn
    from t6
    left join (
        select row_number() over() as rn
        from (select split(space(5), ' ') as x) t  -- space(5)可根据t6表的最大值灵活调整
        lateral view explode(x) pe
    ) b on 1 = 1
    where t6.a >= b.rn
    order by t6.a, b.rn desc
) t
group by t.a;
  1. 级联累加求和：

  • 题目：有消费记录表tb_money，包含userid、mth（月份）和money（消费金额）字段，计算每个用户每个月的消费总额以及累计总额。

  • 答案：

select userid, mth, money as m_money,
       sum(money) over(partition by userid order by mth) as total_money
from tb_money;
  1. 连续登录用户统计：

  • 题目：有用户登录表tb_login，包含userid和logintime字段，找出连续两天登录的用户。

  • 答案：

with ordered_logins as (
    select userid, logintime, date_add(logintime, 1) as next_day,
           lead(logintime, 1) over(partition by userid order by logintime) as next_login
    from tb_login
)
select distinct userid
from ordered_logins
where next_day = next_login;
  1. 行列转换（复杂版）（CSDN 博客）：

  • 题目：表中记录了各年份各部门的平均绩效考核成绩，存在同一部门在不同年份有多个绩效的情况，要求实现多行转多列，以及将结果再转成源表的形式。

  • 答案：视具体表结构和数据情况而定，多行转多列通常使用case when结合group by和聚合函数，列转多行为其逆过程，可能用到union all或lateral view explode等操作。

  1. 排名中取他值（CSDN 博客）：

  • 题目：按某字段分组，取另一个字段在不同排名时对应的第三个字段的值，如按a分组取b字段最小时对应的c字段，按a分组取b字段排第二时对应的c字段等多种情况。

  • 答案：通常使用窗口函数进行排名，再通过条件筛选或with子句结合来实现。例如，按a分组取b字段最小时对应的c字段可以这样写：

select a, min_c
from (
    select a, c, row_number() over(partition by a order by b) as rank
    from your_table
) t
where rank = 1;

｜参考 https://blog.csdn.net/weixin_70577124/article/details/125733692

本页导航