如何1小时快速上手SQL?(小白篇)

如何1小时快速上手SQL?

为了帮大家尽快实现学习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等聚合函数使用,来实现有条件的计数/汇总

作者:张彦龙(源自本人公众号:数据老铁匠)

相关推荐

Kafka生产者的一个Bug会导致部分消息一直无法发送

Node-0 准备开始遍历它下面的几个队列中的ProducerBatch,遍历一次 则drainIndex+1,发现遍历了一个队列之后,就装满了这一批次的请求。因为发送一次的请求量是有限的(max.request.size), 所以一次可能只能发几个ProducerBatch. 那么这次发送了之后, 需要记录一下这里是遍历到了哪个Batch, 下次再次遍历的时候能够接着上一次遍历发送。那么开始遍历Node-1,这个时候则drainIndex=1,首先遍历到的是 第二个TopicPartition。

java06051515的博客 1980

NVIDIA A100 深度解密(一):GPU 峰值计算那些事

1、背景介绍2020 年 5 月 14日,NVIDIA 创始人兼首席执行官黄仁勋在自家厨房直播带货,哦不对应该是 NVIDIA GTC 2020 主题演讲中热情洋溢地介绍了新鲜出炉的基于最新 Ampere 架构的 NVIDIA A100 GPU,号称史上最豪华的烧烤。NVIDIA A100 Tensor Core GPU 基于最新的 Ampere 架构,其核心为基于台积电 7nm 工艺制造的 GA100,内有 542 亿晶体管,裸片尺寸为 826mm^2,而前代 GV100 裸片尺寸 815mm^2,

java06051515的博客 1858

免费测试|滴滴云开放基于NVIDIA A100的GPU裸金属服务器测试

免费测试|滴滴云开放基于NVIDIA A100的GPU裸金属服务器测试北京时间9月1日起,企业用户可申请测试滴滴云基于NVIDIA A100 GPU的裸金属服务器产品,有意向的企业用户扫描文末二维码,联系滴滴云同学申请测试吧~NVIDIA A100 GPU介绍NVIDIA A100融合了超540亿个晶体管,是目前全世界最大尺寸的7nm制程处理器,内有6912个CUDA核心、40GB运行内存,以及内存网络带宽达1.6TB/s,张量计算核心内容性能大幅度提高,TF32性能达156万亿次/秒,如果利用稀疏性

java06051515的博客 626

ceph-immmutable-object-cache

一、简介目前官方提供了ceph-immmutable-object-cache守护进程负责将内容缓存到本地缓存目录上。为了获得更好的性能,建议使用SSD作为底层存储介质。二、IO流程ceph-immmutable-object-cache守护进程启动进行初始化,并且时监听本地域套接字,并等待来自librbd客户端的连接。客户端librbd通过域套接字来连接缓存守护进程, 并且向缓存守护进...

java06051515的博客 384

GRPC连接池的设计与实现

前言在分布式高并发服务器中,client到server以及server中的多个节点之间的连接往往使用连接池来管理。简单来说就是将提前创建好的连接保存在池中,当有请求到来时,直接使用连接池中的连接对server端访问,省去了创建连接和销毁连接的开销(TCP建立连接时的三次握手和释放连接时的四次挥手),从而提高了性能。目录设计原则基本原理GRPC特性GRPC调优实现细则延伸阅读设...

java06051515的博客 4443

突破 0 和 1 的思维:量子计算介绍

突破 0 和 1 的思维:量子计算介绍1 什么是量子计算?量子计算是一种使用量子逻辑进行通用计算的方法,被普遍认为是一种更新型的计算机技术。在传统计算机中,信息量的基本单位是比特,它只能取 0 或 1 中的一个值。在量子计算机中,信息量的基本单位为量子比特或量子位。通过量子力学现象,这些量子位可以同步进行大量计算。理论上,利用量子力学现象,量子计算可以极大地改进信息存储和处理方式,算法比传统...

java06051515的博客 2350

Docsm——将你从频繁的文案修改中解救出来

Docsm——将你从频繁的文案修改中解救出来1 前言今天要和大家分享下最近我们开源的DocsM(Documents Management),对就是文案管理,一个帮助你如何解决静态文案管理的方案,感兴趣的小伙伴可以去github一探究竟。那么接下来就要给大家来个深度剖析了,看看我们到底是怎么做的。2 背景作为FE,我想大家都有一个共同的体验“在代码功能上线后,因为一两句文案或提示信息描述不准...

java06051515的博客 538

实现企业数字化转型,你只差一味药引子

实现企业数字化转型,你只差一味药引子数据的权威性和重要性,伴随着时代的发展而来,已经不需要我们再论证了。“去年,我们计划通过数据分析带来3千万美元的收入,到去年底,实时上我们做到了1.05亿美元” – Hilton CDO“如果没有数据,那你顶多算是一个持有不同观点的人而已。我们相信上帝,除此之来,都请用数据说话” – W.Edwards Deming,质量管理之父。“我们会收集所有数据,...

java06051515的博客 353

Kubernetes 是什么?

Kubernetes 是什么都9102年了,你再不了解 Kubernetes 就真的 out 了!!!(贩卖焦虑体kubernetes 是什么?Kubernetes 这个词来源于希腊语,有主管、舵手、船长的意思,我们从中能听到一丝管理的意味,从图标中也能看出来。在 kubernetes 的网站上,描述 kubernetes 是:生产级别的容器编排系统从这个定义我们可以提炼出三个关键字...

java06051515的博客 855

webpack 透视——提高工程化(实践篇)

wepack 透视——提高工程化(实践篇)webpack 是我们前端工程师必须掌握的一项技能,我们的日常开发已经离不开这个配置。关于这方面的文章已经很多,但还是想把自己的学习过程总结记录下来。上一篇文章介绍了webpack 构建原理,这篇文章将基于这个原理之上,讲述在我们实际工程配置中可以去优化的2 个方向。提升构建速度,也就是减少整个打包构建的时间,优化构建输出,也就是减小我们最终构建...

java06051515的博客 441

webpack 透视——提高工程化(原理篇)

wepack 透视——提高工程化(原理篇)webpack 是我们前端工程师必须掌握的一项技能,我们的日常开发已经离不开这个配置。关于这方面的文章已经很多,但还是想把自己的学习过程总结记录下来。一共两篇文章,分为原理篇和实践篇,从 webpack 构建原理开始,然后基于这个原理之上,明确我们实际工程配置中可以去优化的方向。构建原理篇,先帮助大家知道整体打包构建的流程,已经了解的可以略过这篇,...

java06051515的博客 348

前端演进思考

0. 从模型说起原生JS/jQuery 这时候开发者需要手动处理 Bind、Change 和 Update,jQuery 相比原生 JS,简化了操作,屏蔽了浏览器的兼容性。三大框架 这时候开发者只需处理 Bind 和 Change,框架自动完成 Update。框架使用一套高效的 diff 算法最小化更新 DOM,因此除了简化开发者工作,还带来了前端性能的提升。1. 组件思想除此之外,框架...

java06051515的博客 316

vue-ssr在项目中的实践

写在文前由于前端脚手架、打包工具、Node等版本的多样性,本文无法同时兼顾,文中所述皆基于以下技术栈进行。脚手架:vue-cli3打包工具:webpack4,集成在vue-cli3中,通过修改vue.config.js的方式进行配置Node框架:koa2简介​ 服务器端渲染,即采用“同构”的策略,在服务器端对一部分前端代码进行渲染,减少浏览器对页面的渲染量。通常服务器端渲染的优点和...

java06051515的博客 1809
上一篇: 前端演进思考
下一篇: webpack 透视——提高工程化(原理篇)
「已注销」
「已注销」 企业官方账号 企业官方账号
博客等级 码龄7年 13粉丝 14原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值