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：指定多个分组集合，一次查询得到多个分组结果。

-- 统计不同部门、不同职位的员工数量，以及部门总计和职位总计
SELECT 
    deptno,
    job,
    COUNT(*) as emp_count
FROM 
    emp
GROUP BY 
    GROUPING SETS ((deptno, job), (deptno), (job));

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

-- 对部门和职位的所有组合进行聚合，统计员工数量
SELECT 
    deptno,
    job,
    COUNT(*) as emp_count
FROM 
    emp
GROUP BY 
    deptno, job
WITH CUBE;

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

-- 按部门和职位进行层次化聚合，统计员工数量
SELECT 
    deptno,
    job,
    COUNT(*) as emp_count
FROM 
    emp
GROUP BY 
    deptno, job
WITH ROLLUP;

其他高阶函数

• 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()：使用外部脚本对数据进行处理。

自定义函数

特性	UDF	UDAF	UDTF
全称	User-Defined Function	User-Defined Aggregation Function	User-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