大数据（3e）HIVE基础查询

1、单行拆分为多行

1.1、EXPLOOE

SELECT EXPLODE(course) FROM school.student;

1.2、LATERAL VIEW

SELECT t1.s_id,t2.c
FROM school.student t1
LATERAL VIEW EXPLODE(course) t2 as c;

2、多行合并为单行

关键字COLLECT_SET

SELECT
    grade,
    COLLECT_SET(s_id)
FROM school.student
GROUP BY grade;

3、函数

show functions;

4、时间类

今天

SELECT CURRENT_DATE();

昨天

SELECT DATE_SUB(CURRENT_DATE(),1);

时间差：第一个减第二个

SELECT DATEDIFF(CURRENT_DATE(),"2020-12-27");

现在

SELECT UNIX_TIMESTAMP();
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss');

5、窗口函数

https://yellow520.blog.youkuaiyun.com/article/details/116151366

6、聚合函数+条件

创建数据，需求：求出不同班男女各多少人

--删库
DROP DATABASE IF EXISTS school CASCADE;
--建库
CREATE DATABASE school LOCATION '/school';
--建表
CREATE TABLE school.student(
name   STRING  COMMENT "姓名",
major  STRING  COMMENT "专业",
sex    STRING  COMMENT "性别"
)COMMENT "学生表"
LOCATION '/school/student';
--插数据
INSERT INTO TABLE school.student VALUES
('剑圣','敏捷','男'),
('先知','智力','男'),
('巫妖','智力','男'),
('守望者','敏捷','女'),
('大法师','智力','女'),
('追风者','敏捷','女');
--查看
SELECT * FROM school.student;

查询（关键字CASE和WHEN）

SELECT 
    major,
    CONCAT_WS(',',COLLECT_SET(CASE sex WHEN '男' THEN name END)) male,
    CONCAT_WS(',',COLLECT_SET(CASE sex WHEN '女' THEN name END)) female
FROM
    school.student
GROUP BY
    major;

major	male	female
敏捷	剑圣	守望者,追风者
智力	先知,巫妖	大法师

SELECT 
    major,
    SUM(CASE sex WHEN '男' THEN 1 ELSE 0 END) male_count,
    SUM(CASE sex WHEN '女' THEN 1 ELSE 0 END) female_count
FROM
    school.student
GROUP BY
    major;

major	male_count	female_count
敏捷	1	2
智力	2	1

7、JSON解析

取第1个元素{"name":"剑圣","lv":10}

SELECT GET_JSON_OBJECT(
    '[{"name":"剑圣","lv":10},{"name":"易大师","lv":18}]',
    '$[0]'
)

取第2个元素的name值

SELECT GET_JSON_OBJECT(
    '[{"name":"剑圣","lv":10},{"name":"易大师","lv":18}]',
    '$[1].name'
)

8、排序

• ORDER BY是全局排序，通常生产环境不用
• SORT BY是分区内排序
  DISTRIBUTE BY进行分区
  通常SORT BY和DISTRIBUTE BY联用

SELECT * FROM student ORDER BY birth;

SET mapreduce.job.reduces=2; -- 配两个ruduce(默认1个)
SELECT * FROM student SORT BY birth;

SELECT * FROM student DISTRIBUTE BY grade SORT BY birth;