查询

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available
 starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

• [WITH CommonTableExpression (,CommonTableExpression)*]：可选子句，允许在查询中定义一个或多个临时的结果集，这些结果集称为公用表表达式
• SELECT [ALL | DISTINCT] select_expr, select_expr, ...：DISTINCT选项用于返回唯一不同的行
• [GROUP BY col_list]：用于聚合操作，它将数据分组，以便可以使用聚合函数（如SUM、COUNT、AVG等）对每个组进行计算
• [ORDER BY col_list]：用于对查询结果进行排序，col_list指定了排序的列和排序的方向（升序或降序）
• [CLUSTER BY col_list]：类似于DISTRIBUTE BY，它根据指定的列值对数据进行分组，使得具有相同列值的行会被放置在同一个reducer上进行处理
• [DISTRIBUTE BY col_list]：用于在MapReduce作业中显式地指定数据应该如何在reducer之间分布
• [SORT BY col_list]：用于在MapReduce作业中对数据进行全局排序，在reducer处理之后进行排序，而不是在单个reducer内部
• [LIMIT number]：限制查询结果的数量，只返回指定数量的行

一、基本查询

1、全表和特定列查询

select * from emp;
select empno, ename from emp;

• SQL语言大小写不敏感
• SQL可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写
• 使用缩进提高语句的可读性

2、列别名
重命名一个列，便于计算，可以再列明和别名之间加入关键字'AS'

select ename AS name, deptno dn from emp;

3、Limit语句
典型的查询会返回多行数据，limit子句用于限制返回的行数

select * from emp limit 5;

二、Where语句

• 使用where子句，将不满足条件的行过滤掉
• where子句紧随from子句

1、比较运算符（between/in/is null）
下表中描述了谓词操作符，这些操作符同样可以用于JOIN...ON和HAVING语句中

几个常见的正则表达式模式：

• $：匹配字符串的结束位置。例如，world$会匹配以"world"结尾的字符串。
• .：匹配除换行符以外的任意字符。例如，a.b会匹配"a+b"、"a@b"等。
• +：匹配前面的模式一次或多次。例如，a+b会匹配"ab"、"aab"、"aaab"等。
• ?：匹配前面的模式零次或一次。例如，a?b会匹配"b"、"ab"。
• []：定义字符集合。例如，[abc]会匹配"a"、"b"、"c"中的任意一个字符。
• \d：匹配数字。等价于[0-9]。
• \w：匹配字母、数字或下划线。等价于[A-Za-z0-9_]。
• \s：匹配空白字符，包括空格、制表符、换行符等。
• \b：匹配单词边界。例如，\btest\b会匹配单独的单词"test"。

mail REGEXP '^[A-Za-z][A-Za-z0-9_./-]*@leetcode\\.com$'

2、Like和RLike
（1）使用LIKE运算选择类似的值
（2）选择条件可以包含字符或数字
%：代表零个或多个字符
_：代表一个字符
（3）RLIKE子句是Hive这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件

3、逻辑运算符（and/or/not）

select * from emp where sal>1000 and deptno=30;

三、分组

1、GROUP BY语句
通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

从emp表中按部门编号分组，并计算每个部门的平均工资。查询结果将包含每个部门的编号和对应的平均工资avg_sal。
2、HAVING语句
（1）HAVING和WHERE不同点

• where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据
• where后面不能写分组函数，而having后面可以使用分组函数
• having只用于group by分组统计语句
• 求每个部门的平均薪水大于2000的部门

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

四、JOIN语句

1、等值JOIN
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接

select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

• from emp e：指定了查询的主要数据源，即emp表，并为其指定了别名e
• join dept d：指示要将emp表与dept表进行连接操作，dept表被赋予别名d

2、内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

3、左外连接
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回，确保左侧表（左表）的所有记录都会被返回，即使右侧表（右表）中没有匹配的记录（显示NULL）

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

4、右外连接
JOIN操作符右边表中符合WHERE子句的所有记录将会被返回，确保右侧表（右表）的所有记录都会被返回，即使左侧表（左表）中没有匹配的记录（显示NULL）

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

5、满外连接
将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

6、笛卡尔积
笛卡尔集会在以下条件下产生：
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
两个表进行连接操作时，第一个表的每一行与第二个表的每一行配对，产生所有可能的组合
7、连接谓词中不支持OR

五、排序

1、全局排序（ORDER BY）

• MapReduce作业中的排序：
  ① Map阶段：负责处理输入数据
  ② Shuffle阶段：负责数据的传输、排序和合并，确保数据以正确的顺序和分区发送到Reducer
  ③ Reduce阶段：负责执行具体的数据处理逻辑，生成最终的输出结果，可以有多个

（1）全局排序只有一个Reduce

• ASC（ascend）: 升序（默认）
• DESC（descend）: 降序
  （2）ORDER BY子句在SELECT语句的结尾
  2、每个MapReduce内部排序（SORT BY）
  对每个Reducer内部进行排序，对全局结果集来说不是排序
  （1）设置reduce个数

set mapreduce.job.reduces=3;

（2）查看设置reduce个数

set mapreduce.job.reduces;

3、分区排序（DISTRIBUTE BY）
Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前

SELECT region, amount
FROM sales
DISTRIBUTE BY region
SORT BY region, amount DESC;

• DISTRIBUTE BY region子句指示Hive根据region列的值将数据分配到不同的Reducers。这意味着具有相同region值的记录将被发送到同一个Reducer。
• SORT BY region, amount DESC子句首先按照region对数据进行排序，然后在每个地区内部按照amount降序排序。

4、CLUSTER BY

• 当distribute by和sorts by字段相同时，可以使用cluster by方式。
• cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

六、分桶及抽样查询

分区针对的是数据的存储路径；分桶针对的是数据文件。桶表是一种特殊的表，它将数据分布到固定数量的桶中，每个桶可以独立地进行查询和处理。
1、设置分桶属性

set hive.enforce.bucketing=true;

2、创建分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

• 指定id列为用于数据分布的列。Hive将根据id列的值的哈希值将数据分布到不同的桶中。

3、导入数据到分桶表，通过子查询的方式

insert into table stu_buck select id, name from stu;

• 分桶表只能通过insert插入数据，load读取数据是无效的

4、分桶抽样查询

select * from customers_buck1 tablesample(bucket 1 out of 4 on customer_fname);

tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)

• x是想要选择的桶的编号
• y是表中桶的总数

七、其他常用查询函数

1、空字段赋值
NVL(string1, replace_with)
如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL，则返回NULL。
2、CASE WHEN

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

• conditionN：是要评估的条件
• resultN：当相应的条件为真时返回的结果
• default_result：当所有条件都不为真时返回的默认结果（可选）
  3、窗口函数
• OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
• CURRENT ROW：当前行
• n PRECEDING：往前n行数据
• n FOLLOWING：往后n行数据
• UNBOUNDED：起点，UNBOUNDED PRECEDING表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
• LAG(col,n)：往前第n行数据
• LEAD(col,n)：往后第n行数据
• NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号，n必须为int类型。

4、Rank

• RANK()：排序相同时会重复，总数不会变
• DENSE_RANK()：排序相同时会重复，总数会减少
• ROW_NUMBER()：会根据顺序计算