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

以下内容是自己近期咋在公司内进行的培训PPT的内容，由于示例SQL脚本涉及到公司的一些库表，所以已经去除。

Hive SQL概述

为什么要学SQL？

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

什么是Hive？

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

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

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

什么是Hive SQL？

Hive SQL = HiveQL = HQL

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

数据库基础概念

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

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

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

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

Hive 中的常见数据类型

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

类型 描述 字面量示例

BOOLEAN True/False True

TINYINT 8位有符号整型。

取值范围：-128~127。

1Y、-127Y

SMALLINT 16位有符号整型。

取值范围：-32768~32767。

32767S、-100S

INT 32位有符号整型。

取值范围：-2^{31}~2^{31}-1。

1000、-15645787

BIGINT 64位有符号整型。

取值范围：-2^{63}+1~2^{63} -1。

100000000000L、-1L

FLOAT 32位二进制浮点型。 1.0

DOUBLE 64位二进制浮点型。 1.0

DECIMAL(precision,scale) 10进制精确数字类型。

precision：表示最多可以表示多少位的数字。取值范围：1 <= precision <= 38。

scale：表示小数部分的位数。取值范围：0 <= scale <= 38。

如果不指定以上两个参数，则默认为decimal(10,0)。

1.0

STRING 字符串，变长 “abc”、”bcd”

VARCHAR(n) 变长字符类型，n为长度。

取值范围：1~65535。

“abc”、”bcd”

CHAR(n) 固定长度字符类型，n为长度。最大取值255。长度不足则会填充空格，但空格不参与比较。 “abc”、”bcd”

BINARY 二进制数据类型，目前长度限制为8MB。 unhex(‘FA34E10293CB42848573A4E39937F479’)

TIMESTAMP 与时区无关的时间戳类型。

取值范围：0000-01-01 00:00:00.000000000~9999-12-31 23:59:59.999999999，精确到纳秒。

‘2017-11-11 00:00:00.123456789’

DATE 日期类型，格式为yyyy-mm-dd。

取值范围：0000-01-01~9999-12-31。

‘2017-11-11’

DATETIME 日期时间类型。

取值范围：0000-01-01 00:00:00.000~9999-12-31 23:59:59.999，精确到毫秒。

‘2017-11-11 00:00:00’

INTERVAL 时间频率间隔

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

类型 定义方法 构造方法

ARRAY array<int>

array<struct<a:int, b:string>>

array(1, 2, 3)

array(array(1, 2), array(3, 4))

MAP map<string, string>

map<smallint, array<string>>

map(“k1”, “v1”, “k2”, “v2”)

map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’))

STRUCT struct<x:int, y:int>

struct<field1:bigint, field2:array<int>, field3:map<int, int>>

named_struct(‘x’, 1, ‘y’, 2)

named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200))

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 以后的聚合函数的值。
• 聚合后的字段不是数据表里的真实字段。

聚集函数

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

函数名 描述

count(1)

count(*)

count(col)

count(distinct col)

count(1),count(*)：统计所有的行数，包含NULL
count(col)：统计不是NULL的行数量
count(distinct col)：统计去重后非NULL的行数量

sum(col), sum(DISTINCT col) 求和

avg(col), avg(DISTINCT col) 求平均

min(col) 最小值

max(col) 最大值

variance(col), var_pop(col) 求方差

var_samp(col) 求无偏样本方差

stddev_pop(col) 求标准差

stddev_samp(col) 求无偏样本标准差

covar_pop(col1, col2) 返回组内两个数字列的总体协方差

covar_samp(col1, col2) 返回组内两个数字列的样本协方差

corr(col1, col2) 返回组内两个数字列的皮尔逊相关系数

percentile(BIGINT col, p) 返回组内某个列精确的第p位百分数，p必须在0和1之间

percentile(BIGINT col, array(p1 [, p2]…)) 返回组内某个列精确的第p1，p2，……位百分数，p必须在0和1之间

percentile_approx(DOUBLE col, p [, B]) 返回组内数字列近似的第p位百分数（包括浮点数），参数B控制近似的精确度，B值越大，近似度越高，默认值为10000。当列中非重复值的数量小于B时，返回精确的百分数

percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B]) 同上，但接受并返回百分数数组

histogram_numeric(col, b) 使用b个非均匀间隔的箱子计算组内数字列的柱状图（直方图），输出的数组大小为b，double类型的(x,y)表示直方图的中心和高度

collect_set(col) 返回消除了重复元素的数组，去重

collect_list(col) 返回允许重复元素的数组，不去重

ntile(INTEGER x) 该函数将已经排序的分区分到x个桶中，并为每行分配一个桶号。这可以容易的计算三分位，四分位，十分位，百分位和其它通用的概要统计

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

数值处理函数

用法 功能说明

round(DOUBLE a) 四舍五入到整数

round(DOUBLE a, INT d) 四舍五入到指定小数位

floor(DOUBLE a) 向下取整

ceil(DOUBLE a), ceiling(DOUBLE a) 向上取整

rand(), rand(INT seed) 生成一个0~1之间的随机数

字符串处理函数

用法 描述

concat(string|binary A, string|binary B…) 字符串连接

get_json_object(string json_string, string path) JSON字符串解析

length(string A) 字符串长度

regexp_extract(string subject, string pattern, int index) 正则表达式提取

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) 正则替换

split(string str, string pat) 字符串拆分

substr(string|binary A, int start) substring(string|binary A, int start) 字符截取

substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) 字符截取

trim(string A) 去除前后空字符

字符串截取：

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

JSON字符串解析：

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

正则表达式

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

字符：

语法 说明 表达式实例 完整匹配的字符串

一般字符 匹配自身 abc abc

. 匹配除换行符”\n”外的任何字符 a.c abc

\ 转移字符，使后一个字符改变原来的意思 a\.c a.c

[…] 字符集。对应的位置可以是字符集中任一字符

可以逐个给出，也可给出范围，如[abc]或[a-c]

第一个字符如果是^则表示取反，如[^abc]

a[bcd]e abe

预定义字符集：

语法 说明 表达式实例 完整匹配的字符串

\d 数字:[0-9] a\dc a1c

\D 非数字:[^\d] a\Dc abc

\s 空白字符:[<空格>\t\r\n\f\v] a\sc abc

\S 非空白字符:[^\s] a\Sc abc

\w 单词字符:[A-Za-z0-9_] a\wc abc

\W 非单词字符:[^\w] a\Wc a c

备注：HIVE SQL中所有 \ 需要使用 \\ 替换，如 \d → \\d

数量词（用在字符或(…)之后）：

语法 说明 表达式实例 完整匹配的字符串

* 匹配前一个字符0或无限次 abc* ab

+ 匹配前一个字符1或无限次 abc+ abc

? 匹配前一个字符0次或1次 abc? ab

{m} 匹配前一个字符m次 ab{2}c abbc

{m,n} 匹配前一个字符m至n次，可省略m或n ab{1,2}c abc

开发结尾：

语法 说明 表达式实例 完整匹配的字符串

^ 匹配字符串开头 ^abc abc

$ 匹配字符串末尾 abc$ abc

SQL 时间函数

常见时间格式

类型 说明 示例

Unix时间戳 10位，单位为秒

13位，单位为微秒

1605191559

1605191559123

DATE类型 日期 2020-11-12

DATETIME类型 日期+时间 2020-11-12 22:39:08

STRING 字符串

2020-11-12、2020/11/12、20201112

2020-11-12 22:39:08

INT整数 数值型 20201112

时间处理函数

获取当前时间：

函数 返回内容 示例

UNIX_TIMESTAMP() 当前时间的时间戳，单位秒 1605194959

CURRENT_TIMESTAMP() 当前DATETIME类型时间 2020-11-12 23:18:51

CURRENT_DATE

CURRENT_DATE()

获取当前日期 2020-11-12 00:00:00

日期/时间格式化

时间格式转化方法：

需求 方法

将Unix时间戳转化为DATETIME类型时间 from_unixtime(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’)

from_unixtime(CAST(microsecond/1000 AS INT), ‘yyyy-MM-dd’)

将DATETIME类型时间转化为Unix时间戳 unix_timestamp(‘2020-11-12 22:39:08’, ‘yyyy-MM-dd HH:mm:ss’)

将DATETIME类型时间转为DATE类型的日期 TO_DATE(‘2020-11-12 22:39:08’)

将字符创类型时间转化为DATE类型 CAST(date_string as DATE)

常用格式化符号：

符号 含义 示例

d 一月中的某一天（1-31） 1、20

dd 一月中的某一天（01-31） 01、31

D 一年中的某一天（1-366） 3、80、100

DD 一年中的某一天（01-366） 03、80、366

DDD 一年中的某一天（001-366） 003

e 一周中的某一天（1-7） 2

h 用 AM 或 PM 表示的小时（1-12） 6

hh 用 AM 或 PM 表示的小时（01-12） 06

H 24 小时格式的时间（0-23） 7

HH 24 小时格式的时间（00-23） 07

m 分 4

mm 分 04

M 月（数值） 5、12

MM 月（数值） 05、12

s 秒 5

ss 秒 05

S 分秒 7

SS 厘秒 70

w 一年中的某一周 7、53

ww 一年中的某一周 07、53

W 一月中的某一周 2

yy 年 06

yyyy 年 2006

日期/时间提取

函数 返回内容 示例

YEAR(‘2020-11-12 22:39:08’) 获取日期中的年份 2020

MONTH(‘2020-11-12 22:39:08’) 获取日期中的月份 11

DAY(‘2020-11-12 22:39:08’) 获取月份中的天数 12

DAYOFMONTH(‘2020-11-12 22:39:08’) 获取月份中的天数 12

HOUR(‘2020-11-12 22:39:08’) 获取小时数 22

MINUTE(‘2020-11-12 22:39:08’) 获取分钟数 39

SECOND(‘2020-11-12 22:39:08’) 获取秒数 8

WEEKOFYEAR(‘2020-11-12 22:39:08’) 获取日期属于第几周 46

日期/时间计算

函数 返回内容 示例

DATEDIFF(‘2020-11-12’, ‘2020-01-01’) 计算两个日期的天数差 316

DATE_ADD(‘2020-11-12’, 5) 日期+天数，可传负数 2020-11-17

DATE_SUB(‘2020-11-12’, 5) 日期-天数，可传负数 2020-11-07

ADD_MONTHS(‘2020-11-12’, 2) 日期+月数，可传负数 2021-01-12

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 ] ) 

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

常用窗口函数

函数 说明

COUNT() 计数

AVG() 求平均值

MAX() 取最大值

MIN() 取最大值

MEDIAN() 取中位数

STDDEV() 计算标准差

STDDEV_SAMP() 计算样本标准差

SUM() 求汇总值

CUME_DIST() 计算累计分布

ROW_NUMBER() 计算行号

DENSE_RANK () 计算连续排名

RANK() 计算跳跃排名

PERCENT_RANK() 计算一组数据中某行的相对排名

LAG() 按偏移量取当前行之前第几行的值

LEAD() 按偏移量取当前行之后第几行的值

CLUSTER_SAMPLE() 用于分组抽样

NTILE() 将分组数据按照顺序切片并返回切片值