产品&运营分析技能培训：Hive SQL

HiveSQL概述

为什么要学SQL？

• 性价比高：学习一周，受用终生。（有小学英语能力即可，相当简单，不用害怕学不会）
• 高效便捷：免去数据需求的排期与沟通，可根据自己需要及时调整取数逻辑
• 思维拓展：了解业务存储逻辑，理解状态变更或数据流转，更好的理解业务
• 发现先知：深入细节，发现新的数据维度和思考模式

什么是Hive？

Hive不是数据库。Hive是一个将结构化数据映射成数据表，将SQL翻译成MapReduce任务的工具。

所谓的结构化数据，简单的理解就是比较规整的数据，类似将Excel文件保存为csv的数据。

所谓的MapReduce任务，其实就是将现有的SQL转化成分布式任务，就是将一条SQL拆分到多台服务器运行，完成后在将数据合并。

什么是HiveSQL？

HiveSQL=HiveQL=HQL

• 以SQL-92标准语法为蓝本建立，查询语法与标准SQL基本一致
• 不支持行级别的增、删、改。
• 支持分析超大数据集，执行时间长。

数据库基础概念

数据库中的库、表、字段理解起来也非常的简单。我们可以这样直观的认为：

• 库=Excel文件
• 表=Excel Sheet
• 字段=Excel中列名

在日常使用Hive的过程中还会遇到一个分区（Partitions）的概念，在查询时必须制定。

分区也比较容易理解，分区的目的就是规避全表扫描，把查询限制在一定的分区范围内，类似词典中的”按字母索引”。

Hive中的常见数据类型

数据类型指的是存储的数据的格式，以下为常见的Hive数据格式：

复杂数据类型：（不常用）

SQL查询语法

SQL查询的核心关键字：SELECT、FROM、JOIN、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT（JOIN、GROUP BY、HAVING涉及的内容理解起来比较麻烦，后面会单独讲解）

• 通过 SELECT 指定想要查询的字段
  • 查询多个字段，用逗号(，)隔开
  • 查询表的所有字段，可用星号(*)，非必要，不推荐使用
  • 可结合 DISTINCT 关键词对字段进行去重，类似 Excel 中的删除重复项
  • 可使用 as 关键词对字段设置别名，as 关键词可省略，可读性查，不推荐
  • 字段别名也可以用中文，中文需要用(“)括起来。
  • 可对选择的字段应用 SQL 函数，函数部分会在后面的课程中单独讲解
• 通过 FROM 指定想要查询的库与表
• 通过 WHERE 筛选查询内容
  • 可使用逻辑关键词 AND、OR、NOT 来组合多个条件
  • 可使用=、>、<、>=、<=、!= 和 <> 进行比较操作，!= 和 <> 都表示“不等于”
  • 条件中包含字符串时，需要用单引号(”)括起来，否则会被误认为字段名或关键词
  • 可使用 IN 关键词实现多项匹配，匹配项放在括号()中，用逗号(,)分隔
  • 可使用 BETWEEN…AND 关键词匹配一个范围，由于各数据库边界取值逻辑不同，不推荐使用
  • 可使用 LIKE 进行字符串模糊匹配，% 代表任意个字符，_ 代表一个字符
  • 可使用 IS NULL/IS NOT NULL 判断字段是否缺失。注意：字段缺失和空字符串是完全不同的概念
• 通过 ORDER BY 设定结果集的排序规则
  • 可以不使用 ORDER BY，查询结果可能会乱序或按数据库默认的顺序排序
  • 指定排序的字段，默认按照升序(ASC)排序，即 ASC 可省略不写
  • 降序排序必须显式声明，使用 DESC 关键词
  • 可指定多个字段进行多重排序，中间用逗号(,)分隔
  • 小技巧：抽样分析时，可使用随机排序 rand()，再限定数据量来获取。
• 通过 LIMIT 限定返回的数据条数
  • 从头部的 Y 个数据：LIMIT Y
  • 跳过 X 个数据，读取 Y 个数据：LIMIT Y OFFSET X；简写形式：LIMIT X,Y

学习要点：

• SQL 中的关键词顺序不能乱，比如：WHERE 必须在 GROUP BY 前面
• 查询语句中无需包含所有关键词，示例: SELECT 1;
• SQL 中的关键词不区分大小写，示例: select 1;
• SQL 语句以“;”结尾，只有 1 个语句时可省略

SQL 汇总统计

汇总统计类似 Excel 中的透视表功能，汇总统计基本流程：

• 指定要分组的列
• 使用聚集函数统计想要的结果

关键词：GROUP BY

• 分组关键词 GROUP BY 允许指定一个或多个字段进行分组
• 汇总统计中 GROUP BY 可不使用，即只汇总不分组
• 只有 GROUP BY 的字段才能使用 SELECT 原值取出，非 GROUP BY 的字段必须使用聚合函数

关键词：HAVING

• 和 WHERE 条件不同的是它针对的是使用 GROUP BY 以后的聚合函数的值。
• 聚合后的字段不是数据表里的真实字段。

聚集函数

聚集函数，简单的理解就是分组后用于统计的函数。常见聚集函数如下：（大小写不敏感）

SQL 条件判断

• 条件判断在一般查询中也会使用，但在分组统计中用的较多。
• 由 CASE 关键词开始，END 关键词结束
• 条件语句 WHEN 和 THEN 成对出现，WHEN 后边为条件，THEN 后为最终输出的值
• 使用关键词 ELSE 作为兜底逻辑，直接给出值，可没有（没有被条件覆盖的值为 NULL）
• 在 SELECT 中使用时，最好起个别名

SQL多表连接

多表连接，类似Excel中的vlookup，用法是将不同的表格中的数据关联起来。

多表连接基础语法：

• 指定想要关联的表
  • 在FROM关键词后面的为主表
  • 关联的表放在主表后面，使用LEFT JOIN/INNER JOIN等指定关联方式连接
  • 多张表可能存在相同的字段名，一般都会给表设置别名(AS关键词可省略)，如示例中的a,b
• 指定需要关联的“键”
  • 使用ON关键词声明需要关联的键
  • 关键词ON后面必须是等值条件，即两表中可关联的相同字段
  • 关键词ON后面可跟多个条件，中间需加AND
  • 取各表字段时，加入表别名，如：pageid
• 添加过滤条件和选择想要的字段
  • 关键词WHERE和SELECT的语法同一般查询语句
  • 选取字段时字段名前加上表别名，如productid
  • 多表SELECT时，如出现字段重复，使用AS给字段取别名

常见表连接方式：

红色为左表，蓝色为右表，灰色（包括淡灰）部分为最终结果。

学习要点：

• 最常使用的连接类型INNER JOIN和LEFT JOIN
• 使用INNER JOIN时，INNER可以省略但不建议

数据集合：并集、交集、差集…

SQL多表合并

SQL多表合并，相当于将两个有相同列名的Excel数据合并在一起。使用的关键词词是UNION。

SQL多表连接：UNION是纵向合并，JOIN是横向拓展

UNION与UNION ALL的区别：

UNION是去重合并（相当于Excel中的去除重复项），UNION ALL是不去重合并。

注意：多表合并的字段名要一样

SQL子查询

子查询=将查询结果视为新表

• 子查询也叫嵌套查询，即将查询结果用于新的查询语句
• 子查询必须放在圆括号内
• 子查询语句中不能使用ORDER BY，子查询括号内不能出现ORDER BY
• 子查询可出现在FROM语句后面
• 子查询同样可出现在SELECT、WHERE语句后面（标量子查询）

标量子查询=返回单一值的子查询

子查询出现在SELECT后面：

SELECT name, math, math-(
SELECT avg(math)
FROM students
) AS diff_math
FROM students

子查询出现在WHERE后面：

SELECT name, math
FROM students
WHERE math>(
SELECT avg(math)
FROM students
)

内存临时表

CTE语法相当于生成内存临时表，临时表在SQL执行时创建，SQL执行完毕后自动删除，优点：

• 提高代码可读性（结构清晰）
• 提高代码执行效率（with字句只需执行一次）

学习要点：可同时定义多个临时表，但只能用一个WITH，多个CTE中间用逗号(,)分隔。WITH t1 as(…), t2 as(…)…

实体临时表

将查询结果保存为实体表：

DROP TABLE IF EXISTS tmp_db.qw_hotel_info_1000528_20220112;
CREATE TABLE tmp_db.qw_order_list_1000528_20220112
AS
SELECT *
FROM hotel_db.hotel_info
WHERE is_vaild=1;

学习要点：

• 以上SQL示例其实是2个语句（注意分号）
• 创建实体表前需要有数据库写入权限
• 创建实体表前需要确保数据库里没有同名表，使用DROP TABLE IF EXISTS…删除可能存在的同名表
• 使用CREATE TABLE…AS…将查询结果保存为实体表

使用建议：

• 表命名使用统一的规则（个人标识_数据标识_身份ID_创建日期），方便后期管理
• 为节约服务器空间，定期清理不再使用的实体表，查询方法：SHOW TABLES FROM tmp_cvg LIKE ‘qw_*’;

SQL函数

类型转化

隐式转化：

• Hive在需要时会对数值型数据进行隐式转化
• 任何隐式转化都会转化成更大范围的数值
• 字符串也可以隐式转化，比如字符串转化为日期

显式转化：

• 使用CAST关键词显式的将一个类型的数据转化为另一个数据类型
• CAST的语法为CAST(value AS TYPE)
• 显式转化可以将大范围值转化为小范围数值（截取），如double转化为int

数值处理函数

字符串处理函数

concat(string|binary A, string|binary B…)

字符串截取：

• SUBSTR()语法：substr(string A, int start, int length)
• string A：需要处理的字符串
• int start：开始截取的位置索引，注意：索引从1开始
• int length：截取的长度

JSON字符串解析：

• 第一个参数填写json对象变量，第二个参数使用$表示json变量标识
• 用.或[]读取对象或数组
• 如果输入的json字符串无效，那么返回NULL

正则表达式

正则表达式与SQL是完全独立的内容，在这里介绍的主要是在SQL处理文本时经常遇到，所以也做下介绍。

字符：

预定义字符集：

备注：HIVE SQL中所有\需要使用\\替换，如\d→\\d数量词（用在字符或(…)之后）：

开发结尾：

SQL时间函数

常见时间格式

时间处理函数

获取当前时间：

日期/时间格式化

时间格式转化方法：

常用格式化符号：

日期/时间提取

日期/时间计算

SQL 窗口函数

什么是窗口函数（Window Function）？

不同于普通函数和聚合函数，它为每行数据进行一次计算：输入多行（一个窗口）、返回一个值。

窗口函数出现在SELECT子句的表达式列表中，它最显著的特点就是OVER关键字。

语法：

window_function(expression) OVER(
[PARTITION BY part_list]
[ORDER BY order_list]
[{ROWS | RANGE} BETWEEN frame_start AND frame_end])

作用：同时具有分组和排序的功能，且不减少原表的行数常用窗口函数