hive sql数据分析面试整理

1.写作目的说明

hive sql是从事数据分析的同学的基本功。无论是秋招、春招或者是实习，sql都是面试官考察的重点，拿刚刚过去的19秋招来说，搜狐、网易、京东等在数据分析师岗位面试时都考了sql，而拼多多在数据分析笔试时就安排了四到五道复杂的sql题，虽然实习的难度会比秋招要小，可是sql仍然是重头戏。因此可以说数据分析的敲门砖之一就是sql
在工作中，也有人戏称数据分析师是sql提数机，也有职场前辈说数据分析师不应该甘心成为一个提数机，但是在沉淀业务理解之前，快速准确的提数也是数据分析师必经的阶段。
下面进入正题

2.hive简介

hive是基于hadoop构建的一套数据仓库查询系统，支持使用sql语句对存储在hadoop里面的分布式文件系统进行分析，将结构化的数据映射成一张表，通过将sql语句转化成mapreduce任务进行运行，从而实现查询分析的功能

3.简单常用函数

其中3.1/3.2/3.3需要了解，3.4的内容是必须掌握

3.1 创建临时表

CREATE  TABLE IF NOT EXISTS table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
  [STORED AS file_format] 

3.2 删除新表

DELETE TABLE IF EXISTS table_name;

3.3 插入新字段

ALTER TABLE table_name ADD COLUMNS 
[(col_name data_type [COMMENT col_comment]，...)]

3.4 查询字段

查询语句是面试中的重头戏，需要面试者将查询函数组合在一起实现某个需求，比如给出查询三班女生数学成绩前五名的学生姓名这个问题，应该怎么分析呢？
在解答上面的问题之前，需要牢记hive sql语句的执行顺序，从前到后依次是：

• from + 表名
• on +字段
• （left/right）join +表名
• where +条件
• group by +字段
• having + 条件
• select +字段
• distinct +字段
• union +结果集
• order by +字段
• limit +数量
  我的习惯是执行顺序就是我思考分析的顺序，但是代码最终的呈现顺序和执行顺序略有不同，对上面这个问题来说，首先在问题中可以知道最终选出的字段是学生姓名，那么就明确了select的字段只有一个，就是name
  接着想到执行顺序第一个是from，那么一定有一个表，无论是中间表还是原始表，一定可以接着写下这样的语句

FROM scores //假设scores是表名

接着执行顺序到了on和join,这时候就要考虑是不是需要连表的问题，假设在这个问题中需要连表，因为会有一张表basic记录了全校学生的性别，那么这时候就需要把score和basic连在一起，接下来就可以这么写

FROM scores // 这是之前写过的
LEFT JOIN basic
ON scores.name = basic.name

接着执行顺序到了where,where的限定条件有三个，分别是三班，数学，女生，那么写下来就是

FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'

接着执行顺序到了group by ,having ，这个问题不需要这个函数，那么接下来执行顺序到了select,如之前所说，代码的书写顺序和执行顺序，所以select语句要写在前面

SELECT scores.name
FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'

可是我们并没有实现前五名的查找，我们目前只是查到了所有人的名字，所以需要根据成绩来对名字进行排序，select之后的执行顺序是distinct和union这里我们并不需要，因此要用order by 进行排序

(SELECT scores.name,scores.score // 增加了一个scores.score字段
FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'
ORDER BY scores.score desc)a

你会有疑问为什么要加上score字段，因为order by是在select 之后执行，他需要在select出来的那些字段里进行排序，如果我们没有加上score,那么order by就找不到要排序的字段了，因此我们要在a表的基础上再进行一次select才可以，只把name字段选择出来，并且通过limit把前5名选出来,

SELECT a.name
FROM
(SELECT scores.name,scores.score // 增加了一个scores.score字段
FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'
ORDER BY scores.score desc)a
LIMIT 5

刚才的分析问题解答完毕。

4.复杂窗口函数

在sql中有聚合函数，它的作用是把多行数据聚成一行，比如count,sum,avg,max,min就是常见的聚合函数，但是当我们既想要保留聚合前的数据又想要看到聚合后新字段的数据时，就需要用到窗口函数，那么窗口函数的执行顺序是怎样的呢？窗口函数的执行顺序靠后，在order by之前。
窗口函数有以下几种形式

4.1 普通型

1. 聚合函数+over()
2. 聚合函数+over(partition by ... )
3. 聚合函数+over(partition by ... order by ...)
4. 聚合函数+over(partition by ... order by ...) rows between A and B，A/B

可选择的如下：

- UNBOUNDED PRECEDING 起点
- UNBOUNDED FOLLOWING 终点
- CURRENT ROW 当前行
- 数字+PRECEDING 前几行
- 数字+FOLLOWING 后几行

4.2 排序型

- dense_rank over(partition by ... order by ...)
//eg:1,2,3,3,4 
- rank over(partition by ... order by ...)
//eg:1,2,3,3,5
- row_number over (partition by ... order by ...)
//eg:1,2,3,4,5

4.3 前后型

- lag(colname,num,default) over(partition by ... order by ...) :前几行
- lead(colname,num,default) over (partition by ... order by ...):后几行

4.4 分组排序后

- first_value(colname) over(partition by ... order by ...):分组排序后第一行
- last_value(colname) over(partition by ... order by ...):分组排序后最后一行

4.5 切片型

- ntile(3) over() :对全局数据切片
- ntile(3) over(partition by ...):对分组之后的数据切片
- ntile(3) over(order by ...):对数据按照升序后切片
- ntile(3) over(partition by ... order by ...):对数据分组并按照升序后切片

4.6 百分型

1.小于等于当前值的行数/分组内总行数

- CUME_DIST over (order by ...)
- CUME_DIST over (partition by ... order by ...)

2.分组内当前行的排名-1/分组内总行数-1

- PERCENT_RANK over(order by ...)
- PERCENT_RANK over(partition by ... order by ...)

5.专题--日期函数

- to_date:日期时间转成日期函数
-from_unixtime:时间戳转成制定格式的日期
-unix_timestamp:日期转化成时间戳
-year/month/day/hour/minute/second
-weekofyear
-datediff
-date_sub
-date_add
-from_unixtime+unix_timestamp

6.参考资料

[hive详解]
（https://blog.csdn.net/hguisu/article/details/7256833）
[hive官方手册]（https://cwiki.apache.org/confluence/display/Hive/LanguageManual）
[hive窗口函数]
（https://blog.csdn.net/qq_26937525/article/details/54925827）
[hive常用函数大全（二）]
（https://blog.csdn.net/scgaliguodong123_/article/details/60135385）
[hive日期函数]
（https://blog.csdn.net/u013421629/article/details/80450047）

作者：木木木有想法
链接：https://www.jianshu.com/p/7bc58aa08185
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。