为了帮大家尽快实现学习Hive SQL、提数,在大数据领域大干一场的愿望,帮你准备好了数据,准备好了SQL样例,照着学,边学边练,主要是解决学一门新手艺起点高入手难的问题。
这个自编的课程分成如下的几个部分,欢迎大家选读选练。
- 特别说明
- 准备环境
- 两张示例表
- SQL示例
- 简单select查询
- 使用where语句按条件筛选
- 使用distinct语句排重
- 使用order by语句对查询结果排序
- 使用sum/count/avg计算总和/计数/平均值等
- 使用group by按照分组计算
- 子查询
- 使用join从多张表中查询数据
- 使用union all拼接多份数据
- 使用case when控制列输出
- SQL高级教程(待续)
- window函数
- 处理Json格式数据
- 处理XML格式数据
- 行列转换
- 使用with语法
- 数据抽样
特别说明
本文中使用的数据集是从网上下来的测试数据,数据地址:https://github.com/markgrover/cloudcon-hive
两张示例表
为了方便练习SQL语法,特地准备了两张hive表,ide_test.flights和 ide_test.airports, 表结构如下面的建表语句。另外两张表中各有数行测试数据,可以在练习SQL时直接使用。
Flights表
CREATE TABLE `ide_test.flights`(
`year` string COMMENT 'year',
`month` string COMMENT 'month',
`day` string COMMENT 'day',
`dep_time` string COMMENT '起飞时间',
`crs_dep_time` string COMMENT '计划起飞时间',
`arr_time` string COMMENT '到达时间',
`crs_arr_time` string COMMENT '计划到达时间',
`carrier_code` string COMMENT '航空公司代号',
`flight_num` string COMMENT '航班号',
`actual_elapsed_time` string COMMENT '实际耗时',
`crs_elapsed_time` string COMMENT '计划耗时',
`air_time` string COMMENT '飞行时间',
`arr_delay` string COMMENT '到达延迟',
`dep_delay` string COMMENT '起飞延迟',
`origin` string COMMENT '起飞机场',
`dest` string COMMENT '目标机场',
`distance` string COMMENT '距离')
COMMENT '测试数据-航班信息'
Airports表
CREATE TABLE `ide_test.airports`(
`name` string COMMENT 'name',
`country` string COMMENT 'country',
`area_code` string COMMENT 'area_code',
`code` string COMMENT 'code')
COMMENT '测试数据-机场信息'
SQL示例
简单select查询
使用select语句可以查看表里的数据
select用法
select name from ide_test.airports limit 5 --查询ide_test.airports表中的name列,返回5行
select * from ide_test.airports limit 5 --使用 '*'代表表中所有列
特别提示:限制SQL返回的行数是一个好习惯,如上面的 limit 5, 表示最多返回5行数据
资料:深入理解Hive Select语法
使用where语句按条件筛选
使用where语句,可以根据指定的条件刷选数据,例如查看航班号为335的航班信息
where用法
select
* -- 返回表里所有列
from
ide_test.flights
where
flight_num= '335' --返回flight_num列为'335'的记录
limit 5
Tips: where后面跟的是一个逻辑表达式,多个逻辑表达式用 AND、OR来连接
使用distinct语句排重
某一列(或多列)时常会有重复的值,有时候需要去重之后再查看,可以使用distinct语句来完成这个功能。例如查看ide_test.flights表中包含了那几年的数据,可以使用下面的SQL语句来完成:
distinct 用法
select
distinct year -- distinct会对year列的值去重之后,再返回
from
ide_test.flights
limit 10
Tips:查看某列包含了哪些值,有更好的写法,这里只是展示下distinct语句的用法
使用order by语句对查询结果排序
找出起飞时间最晚的5个航班
order by 用法
select
origin, dest, flight_num, dep_time
from
ide_test.flights
order by dep_time desc -- 按照起飞时间倒序排列, desc表示由大到小排序, asc(或者是省略不写)表示由小到大排序
limit 5
使用sum/count/avg计算总和/计数/平均值等
使用count()函数可以计数,比如要看一下ide_test.flights表里一共有多少个航班信息,则可以这么写:
count 用法
select
count(flight_num) as cnt -- count函数表示计数
from
ide_test.flights
limit 10
sum()表示按照指定的列求和,比如计算一下335号航班一共飞行了多少公里
sum 用法
select
sum(cast(distance as int)) as total_distance --sum函数表示求和
from
ide_test.flights
where
flight_num = '335'
limit 10
Tips: count/sum这种函数叫聚合函数,还有其他聚合函数,比如avg/max/min分别用户求平均值、最大值和最小值
资料:深入理解Hive函数
使用group by按照分组计算
group by语句的用处很大,它可以先对数据分组、然后再计算,看个例子就明白了。比如,要计算每个航班飞行的里程数,并按照总数由大到小取前10个,写法如下:
group by 用法
select
flight_num, sum(distance) as total_distance --除了sum/count/avg/min/max这些聚合函数之外,明确写在select之后的列,必须同时出现在group by的后面
from
ide_test.flights
group by flight_num --按照航班号分组,对每一组的里程数求和
order by total_distance desc
limit 10
还有一种情况,查询总飞行里程数大于3000的航班号,这时having语句就排上用场了
having 语法
select
flight_num --写在select之后的列,必须同时出现在group by的后面
from
ide_test.flights
group by flight_num
having sum(distance) > 3000 --刷选出飞行总里程大于3000的航班
limit 5
小思考:为什么这种情况下要使用having来作为筛选条件,而不是使用where?
资料:深入理解Hive Group by语法
子查询
要计算飞行总里程大于3000的航班数量,SQL可以这么写
子查询用法
select count(1) as cnt
from
(
select flight_num
from ide_test.flights
group by flight_num
having sum(distance) > 3000
) t
Tips: 子查询的用法和普通hive表是一样的,一般为了清晰,会使用括号'()'把子查询包含起来
资料:深入理解Hive 子查询
使用join从多张表中查询数据
假如,我们计划查询每个航班的飞行里程和起始机场名称,因为这两个信息分别存储在两张不同的hive表中,因此需要同时查询两张hive表才能得到结果,写法如下
join 用法
select
p.flight_num, -- 从flights表取出航班号
p.distance, --从flights表取出飞行里程
q.name --从airports表取出出发的机场名称
from
ide_test.flights p
left join ide_test.airports q
on p.origin = q.code
limit 10
Tips:
left join:左表关联右表,当坐标中存在某值而右表不存在时,则右表对应信息使用null代替
inner join:只有左表和右表都存在对应值的时候,才出现的结果中
right join:与left join相反
full outer join:当左表或右表不存在相应值时,使用null代替
注意:
如果A中有m条记录,B中有n条记录,A join B的时候,则最多会产生m*n条记录,想想为什么?
资料
深入理解Hive Join语法
使用union all拼接多份数据
UNION 语句符用于合并两个或多个 SELECT 语句的结果集。
选出飞行里程大于1000或小于100的航班,SQL写法如下:
注意:这里只是为了展示union用法,显然应该有更好的写法,自己思考下:)
union 用法
select flight_num
from ide_test.flights
where distance > 1000
union
select flight_num
from ide_test.flights
where distance < 100
Tips: 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同
资料:深入理解Hive Union语法
使用case when控制列输出
查询航班的飞行距离,按照距离分成“长、中、短”三个级别,可以借助case when语句来实现,示例写法:
case when 用法
select flight_num,
distance,
case when distance > 3000 then '长距离航班'
when distance > 1000 then '中距离航班'
else '近距离航班'
end as flight_level
from ide_test.flights
limit 10
Tips: 使用case when可以灵活自定义查询结果。另外case when还可以和count/sum等聚合函数使用,来实现有条件的计数/汇总
作者:张彦龙(源自本人公众号:数据老铁匠)
- 滴滴云全线标准型云服务器限时特惠,注册即送新手大礼包
![69857cbc2ca6d05a29e7597e810d425e.png](https://i-blog.csdnimg.cn/blog_migrate/d09806d6a4036a371d9f86d9bc380c2f.jpeg)
![69857cbc2ca6d05a29e7597e810d425e.png](https://i-blog.csdnimg.cn/blog_migrate/d09806d6a4036a371d9f86d9bc380c2f.jpeg)