HIVE数据库（Hive内置函数）

1、聚合函数

1.1 统计员工信息表employess_table中的部门数。

SELECT 
COUNT(DISTINCT staff_dept) dept_num 
FROM hive_database.employess_table;

1.2 获取商品销售表sales_table中每个省份销售额最高的城市。

SELECT 
province,SUM(sales_amount) province_amount 
FROM hive_database.sales_table GROUP BY province;

1.3 获取商品销售表sales_table中每个省份销售额最高的城市。

SELECT province,city,sales_amount 
FROM hive_database.sales_table 
WHERE sales_amount IN(
SELECT MAX(sales_amount) 
FROM hive_database.sales_table 
GROUP BY province);

1.4 获取商品销售表sales_table中每个省份包含的城市。

SELECT province,COLLECT_SET(city) city_set 
FROM hive_database.sales_table 
GROUP BY province;

2、数学函数

获取商品销售表sales_table中每个省份的平均销售额。

SELECT province,ROUND(AVG(sales_amount),2) 
FROM hive_database.sales_table GROUP BY province;

3、集合函数

判断学生考试成绩表student_exam_table中意向大学填写了Peking University的学生。

SELECT student_name,
ARRAY_CONTAINS(intent_university,"Peking University")
FROM hive_database.student_exam_table;

4、类型转换函数

获取员工信息表employess_table中员工薪资的整数数据。

SELECT staff_salary,CAST(staff_salary AS INT) 
FROM hive_database.employess_table;

5、日期函数

在数据库hive_database中创建日期表date_table。

CREATE EXTERNAL TABLE IF NOT EXISTS 
hive_database.date_table(
start_date STRING,
end_date STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/hive_data/test_date';

向日期表date_table中插入两条数据。

INSERT INTO TABLE 
hive_database.date_table VALUES 
("2020-01-08 12:23:43","2020-06-16 06:13:23"),
("2020-05-25 10:11:22","2020-11-28 11:53:03");

5.1 计算日期表date_table中，开始日期与结束日期相差的月以及开始日期与当前日期相差的月。

SELECT 
start_date,
end_date,
CURRENT_TIMESTAMP() now_date,
ROUND(MONTHS_BETWEEN(CURRENT_TIMESTAMP(),start_date),1) between_now_date,
ROUND(MONTHS_BETWEEN(end_date,start_date),1) between_start_end_date 
FROM hive_database.date_table;

5.2 在日期表date_table中，将开始日期延迟7天，结束日期提前5天。

SELECT 
start_date,
end_date,
DATE_ADD(start_date,7) add_startdate,
DATE_SUB(end_date,5) sub_enddate
FROM hive_database.date_table;

5.3 将日期表date_table中，开始日期的时间格式化为yyyy/MM/dd HH的形式。

SELECT 
start_date,
DATE_FORMAT(start_date,'yyyy/MM/dd HH') format_date
FROM hive_database.date_table;

6、条件函数

6.1 根据员工信息表employess_table中员工年龄数据，判断员工属于中年还是青年。

SELECT staff_name,
IF(staff_age >= 40,"middle age","youth") 
FROM  employess_table;

6.2 根据员工信息表employess_table中员工薪资数据，判断员工薪资级别。

SELECT staff_name,CASE 
WHEN staff_salary < 5000 THEN "low"
WHEN staff_salary >= 5000
AND staff_salary < 8000 THEN "middle"
ELSE "high"
END
FROM hive_database.employess_table;

7、字符串函数

7.1 将商品销售表sales_table中省份名和城市名拼接在一起。

SELECT CONCAT_WS(':',province,city) FROM  hive_database.sales_table;

7.2 去除员工信息表employess_table中员工姓名中的空格。

SELECT staff_name,
REGEXP_REPLACE(staff_name,'\\s','')
FROM hive_database.employess_table;

7.3 获取员工信息表employess_table中员工的姓氏。

SELECT SUBSTR(staff_name, INSTR(staff_name,' ')) FROM  hive_database.employess_table;

 7.4 重命名员工信息表部门名称，去除部门名称后的“ Department”字符串，在部门名
称前添加“DEPT-”字符串。

SELECT staff_dept,
LPAD(SUBSTR(staff_dept, 0, (LENGTH(staff_dept)-LENGTH(SUBSTR(staff_dept, INSTR(staff_dept,' '))))),
(LENGTH(staff_dept)-LENGTH(SUBSTR(staff_dept, INSTR(staff_dept,' ')))+5),'DEPT-')
FROM hive_database.employess_table;

8、表生成函数

8.1 拆分学生成绩表student_exam_table的意向大学数据。

SELECT 
intent_university,university_new 
FROM hive_database.student_exam_table 
LATERAL VIEW explode(intent_university) 
intent_university AS university_new;

8.2 拆分学生成绩表student_exam_table的文综或理综数据。

SELECT 
humanities_or_sciences,key,value 
FROM hive_database.student_exam_table 
LATERAL VIEW explode(humanities_or_sciences) 
humanities_or_sciences AS key,value;