为什么考察SQL？
大数据分析工程师80%的时间都在与SQL打交道，通过SQL完成业务方的各种临时性需求分析和常规性报表统计。熟练的SQL技能能够大大提高工作效率。本文将SQL/SparkSql/HiveQL放在一起来梳理一份常见题型的面试题库。
面试题库
01SQL基础知识考察
对于面试初级数据分析师来说，SQL的面试重点会放在基础知识的考察，如果最基本的基础概念和语法都不能熟练回答出来的话，通过面试的几率就会很低。下面两张图是SQL基础概念和基础语法的考题大纲图，接下来围绕图中提到的概念来列举几个常见面试题。
图1 基础概念
图2 基础语法
考题模拟
题1：你觉得SQL是一种什么样的语言，说说你对它的认识。
答：SQL是Structured Query Language(结构化查询语言)的缩写。是一种专门用来与数据库沟通的语言，与大多数编程语言不同，SQL中只有很少的关键字，SQL语言的设计只为了达到一个目的---提供一种从数据库中读写数据的简单有效的方法。
SQL有如下优点：
(1)SQL不是某个特定数据库提供商专有的语言。几乎所有重要的DBMS都支持SQL，所以学习此语言使你几乎能与所有数据库打交道。
(2)SQL简单易学。它的语句全都是由有很强描述性的英语单词组成，而且这些单词的数目不多。
(3)SQL虽然看上去很简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。
题2：你是怎么理解数据库和表的？
答：数据库是一个以某种有组织的方式存储的数据集合。可以将数据库想象为一个文件柜，这个文件柜是一个存放数据的物理位置，不管数据是什么，也不管数据是如何组织的。通常容易将数据库和数据库软件的概念相混淆，数据库软件应称为数据库管理系统(DBMS)。数据库是通过DBMS创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。
往文件柜里放资料时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，将相关的资料放入特定的文件中。在数据库领域中，这种文件称为表。是一种结构化的文件，可用来存放某种特定类型的数据。关于表的概念有以下注意事项：
数据库中的每个表都有一个名字来标识自己。这个名字是唯一的，即数据库中没有其他表具有相同的名字。
PS：每次回答面试官所提问的概念性问题时，最好在答出概念后，总结性的阐述一下相关注意事项，这样能够很快体现出你对这个概念的认知是很清楚的，且给面试官思路清晰，总结和表述能力不错的印象。
题3：手写一下如何创建一张表Products，该表有5个字段，产品id，供应商id，产品名称，产品价格，产品描述。
答：
CREATE TABLE Products( prod_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '产品id', vend_id INT NOT NULL COMMENT '供应商id', prod_category VARCHAR(254) COMMENT '产品类别', prod_name VARCHAR(254) COMMENT '产品名称', prod_price DECIMAL(8,2) COMMENT '产品价格', prod_desc VARCHAR(1000) COMMENT '产品描述', create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time datetime COMMENT '更新时间');本题看似简单，却可考察出面试者的三个方面：
1.平时创建表时考虑是否周全，比如对各个字段该定义为什么类型，可否为空是否都有思量；
2.建表是否有新建主键意识；
3.如果最后能自己主动补上create_time和update_time两字段，可说明面试者的确是有实际工作经验的。
题4：给刚刚定义的表Products增加一列，表示供应商的手机号码。
答：
ALTER TABLE Products add vend_phone VARCHAR(20);题5：把刚新加的列删除掉。
答：
ALTER TABLE Products DROP COLUMN vend_phone;PS：诸如以上题4题5题6，面试官要么会直接问你怎么新加一个字段，怎么删除一个字段，这种只需你口头回答的题型，要么就会给个示例，让你实际书写一下。总之，无论会怎么面，面试者需要对基本的DDL语言十分熟悉这是肯定的，类似考题比如怎么给表重命名呀，怎么删除一张表呀，怎么给表插入数据呀，等等，这里就不一一列举了，如果真的忘了，可即刻上网百度复习一下。
题6：假设以上所建表中已插入实际数据，请你检索一下产品价格大于20，供应商id为28，产品名称里包含饼干的数据，结果按产品id排序，只需显示10行。
答：当我们看到这样一个典型的数据检索面试题时，首先我们将面试官所描述的所有内容快速记录下来，如果面试官没有给你准备面试草稿纸，那么，你最好事先自己准备几张，一个事前能够做足准备的面试者，给面试官的印象分只会增不会减。将内容记录下来以后，接下来，你先花点时间观察一下这些内容，迅速对应一下自己所学知识结构，想想，每一个具体的内容描述，面试官具体是想考察什么。以本题为例，首先说的是检索一下，那么也就是select，产品价格大于20，供应商id为28，显然这是限制条件，且它们之间是组合关系(且)，产品名称里包含饼干，包含，什么意思呢，就是模糊查询的概念，那么自然想到like，结果排序，也就是order by，且并没有说按升序还是降序，那就只要使用默认排序即可，显示10行，limit。
SELECT *FROM ProductsWHERE prod_price > 20 and vend_id = '28' and prod_name like '%饼干%'ORDER BY prod_idLIMIT 10；题7：以上明细结果基础上按产品类别分组，统计每组记录数，结果按照记录数进行降序。
答：
SELECT prod_category，count(*) as cntFROM ProductsWHERE prod_price > 20 and vend_id = '28'and prod_name like '%饼干%'GROUP BY prod_categoryORDERBY prod_category DESC;题8：将产品名称和价格以空格分隔，合并为一个字段。
答：使用CONCAT函数。
CONCAT(prod_name,' ',prod_price)题9：将产品价格按照1-10元，11-100元，100元以上分为三组，进行分组统计记录数。
答：
SELECTcase when prod_price between 1 and 10 then '1~10'when prod_price between 11 and 100 then '11~100'when prod_price > 100 then '大于100' end as price_group, count(*) as cntFROM Productsgroup by price_groupPS：在题7考查了基本语法结构以后，会再进一步考查分组，汇总，使用函数，创建计算字段等更为常见使用更为频繁的SQL进阶知识点，如题8/9/10。其他常见的聚集函数，文本处理函数，日期、时间处理函数和数值处理函数等，考察情况类似，大家可自行前去总结了解一下，也可直接阅读本公众号SQL相关文章，里面都有罗列总结。
02SQL实操考察
SQL基础知识考察结束后，如果面试者整体表现尚佳，面试官就会加大考题难度，以实际工作中的SQL应用程度来考察大家，具体体现为对多张表之间的检索考察，相关知识点涉及：表联结、子查询、组合查询。
图3 实操考察
考题模拟
题10：什么叫左外连接、什么叫右外连接、什么叫内外连接 或 什么叫全外连接？
答：
内连接(INNER JOIN)，通常可以省略掉INNER不写，它的含义是左右两个集合相乘后，只保留满足ON后面关联条件的记录。所以，可以利用内连接计算两个集合的交集，只需要把集合元素的字段都写在ON后面的关联条件里即可。
左外连接(LEFT OUTER JOIN)，OUTER通常可以省略不写，它的含义是，左右两个集合相乘后，保留满足ON后面关联条件的记录加上左表中原有的但未关联成功的记录。因此，左外连接，可以用来计算集合的差集，只需要过滤掉关联成功的记录，留下左表中原有的但未关联成功的记录，就是我们要的差集。
右外连接(RIGHT OUTER JOIN)，与左外连接含义相同，只是方向不同而已，通常也是省略OUTER不写。
全外连接(FULL OUTER JOIN)，含义是，左右两个集合相乘后，保留满足ON后面关联条件的记录加上左表和右表中原有的但未关联成功的记录。
题11：假设除了以上Products表，还有一张存储销售产品供应商的表Vendors，表中字段信息如下，如何得到表Products和Vendors两表能关联上的部分中Products的数据。
答：
//方式一：SELECT Products.*FROM Vendors，ProuctsWHERE Vendors.vend_id = Products.vend_id;//方式二：SELECT Products.*FROM ProductsWHERE vend_id IN (SELECT DISTINCT vend_id FROM Vendors); //方式三：SELECT a.* FROM Products a (INNER) JOIN Vendors b ON a.vend_id = b.vend_id;题12：检索供应商id不在Products中的Vendors表中的数据。
答：
SELECT a.*FROM Vendors aLEFT JOIN Products bON a.vend_id = b.vend_idWHERE b.vend_id is null题13：组合查询有哪两种？组合查询有什么特点？
答：组合查询有两种，一种是相同记录去重组合查询，一种是相同记录不去重组合查询。对应语法是UNION和UNION ALL。
UNION使用注意事项：
(1)使用UNION必须有两条或者两条以上的SELECT语句组成，语句之间用UNION关键字分割；
(2)使用UNION关联的每个子查询必须包含相同数量的字段；
(3)列数据类型必须兼容;类型不必完全相同，但必须是DBMS可以隐含转换的类型(不同的数值类型或者不同的日期类型)；
(4)使用组合查询,当需要对结果进行排序时，只能指定一条order By语句，这条语句只能放在最后一条SELECT语句的后面。
以上所列是SQL中的UNION使用注意事项，HiveSql和SparkSql中的使用规则如何读者最好再自行总结对比一下。
PS：以上几个示例分别演示了表联结、子查询、组合查询的重要知识点，实际考题可能千变万化，本小结相当于给大家将相关知识点总结了一下，大家可多刷刷题加强认知和使用的熟练程度。
03SparkSql/HiveSql专业知识考查
实际面试中，前两节SQL知识点的面试范围和语法题型同样适用于SparkSql/HiveSql，至于SparkSql和HiveSql独有的特性考察，在面试一个初级数据分析师的时候，会体现在以下知识点。
图4 SparkSql专业考点
图5 HiveSql专业考点
考题模拟
题14：SparkSql支持读入的数据类型有哪些？
答：SparkSql支持读入的数据类型有parquet、csv、json、jdbc、table和text等。
题15：具体阐述一下SparkSql想做到跟SQL一样查看目标文件里的3条记录，需要怎么做？
答：
(1)构建入口import org.apache.spark.sql.SparkSessionval spark = SparkSession.builder() .appName("Spark SQL basic example") .config("spark.some.config.option", "some-value") .getOrCreate() (2)创建DataFrame，例如从一个json文件创建一个DataFrame。val df = spark.read.json("examples/src/main/resources/people.json")// 显示出DataFrame的内容 //df.show()// +----+-------+//| age| name|// +----+-------+// |null|Michael|// | 30| Andy|// | 19| Justin|// +----+-------(3)执行SQL查询。// 将DataFrame注册成一个临时视图df.createOrReplaceTempView("people")val sqlDF = spark.sql("SELECT * FROM people LIMIT 3")sqlDF.show()// +----+-------+// | age| name|// +----+-------+// |null|Michael|// | 30| Andy|// | 19| Justin|// +----+-------+其他使用语法示例。import spark.implicits._ //导入隐式转换的包//打印schemadf.printSchema()// root// |-- age: long (nullable = true)// |-- name: string (nullable = true)//选择一列进行打印df.select("name").show()//选取年龄大于20的df.filter($"age" > 20).show()//聚合操作df.groupBy("age").count().show题16：SparkSql的数据保存语法是什么样的？
答：
//例如选取DataFrame中的两列保存到json文件中，可指定保存文件的格式df.select("name", "favorite_color").write.format("json").save("namesAndFavColors.json")//选取DataFrame中的两列保并追加到parquet文件中，可指定模式为追加，另还有覆盖模式df.select("name", "favorite_color").write.mode(SaveMode.append) .save("namesAndFavColors.parquet")题17：谈谈Hive分区的概念，它的作用是什么，如何添加和删除分区。
答：在Hive中，表中的一个分区(Partition)对应于表下的一个目录，分区的作用就是辅助查询，缩小查询范围，在HiveSql中限制分区条件可加快数据的检索速度。
//添加分区ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt' PARTITION (dt='2008-08-08', hour='09') location '/path/pv2.txt';//删除分区ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');题18：如何自定义一个函数供HiveSql使用，具体步骤分为哪几步？
答：Hive自定义临时通用函数的步骤如下，以一个简单示例我们一起来操作一下：
1. 在类中创建自定义函数。自定义UDF需要继承org.apache.hadoop.hive.ql.exec.UDF，实现evaluate函数，evaluate函数支持重载。
package com.yqz.udf;import org.apache.hadoop.hive.ql.exec.UDF;import org.apache.hadoop.io.Text; public class ConcatString extends UDF { // string can not translation in hadooppublic Text evaluate(Text a, Text b) { return new Text(a.toString() + "*******" + b.toString()); }}查询执行过程中，查询中对应的每个应用到这个函数的地方都会对这个类进行实例化。对于每行输入都会调用evaluate()函数。
2.将该类所在的包导出成jar包，放入linux目录下。
//先删除旧包delete jar /data/yqz/hive/contactString.jar;//添加新的jar包add jar /data/yqz/hive/contactString.jar;需要注意的是，jar文件路径是不需要用引号括起来的。这个路径需要是当前文件系统的全路径。Hive不仅仅将这个jar文件放入到classpath中，同时还将其加入到分布式缓存中，这样，整个集群的机器都是可以获得该jar文件的。
3.创建临时函数，指向jar包中的类。
//语法：create temporary function as 'java类名';//示例create temporary function myconcat as 'com.yqz.udf.ConcatString';需要注意的是，create temporary function中的temporary 关键字表示的是当前会话中声明的函数只会在当前会话中有效。因此用户需要在每个会话中都添加jar，然后再创建函数。如果用户需要长期频繁的使用同一个jar和函数的话，可以将相关语句增加到$HOME/.hiverc文件中去。
4.使用临时函数
//语法：使用：select (参数);删除；删除：drop temporary function if exists ;//示例select myconcat('HELLO','world');删除自定义临时函数时，加上if exists，这样即使该函数不存在，也不会报错。
题19：SQL和HiveSql中都有窗口函数，能讲讲窗口函数的基本语法吗？
答：
()OVER( [PARTITION BY ] [ORDER BY ][ASC/DESC] (ROWS | RANGE) )题20：HiveSql使用到的数据跟传统SQL数据库中的数据存储方式不同，你能讲解下HiveSql中使用到的数据源的数据组织方式吗？
答：
(1)Hive的数据组织包括数据库、表、视图、分区、分桶和表数据等。数据库，表，分区等等都对应 HDFS上的一个目录。分桶和表数据对应 HDFS 对应目录下的文件。
(2)Hive 中所有的数据都存储在 HDFS 中，没有专门的数据存储格式，因为 Hive 是读模式 (Schema On Read)，可支持 TextFile，SequenceFile，RCFile 或者自定义格式等。
(3)只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。
(4)Hive 中包含以下数据模型：
database：在 HDFS 中表现为${hive.metastore.warehouse.dir}目录下一个文件夹；
table：在 HDFS 中表现所属 database 目录下一个文件夹；
partition：在 HDFS 中表现为 table 目录下的子目录；
bucket：在 HDFS 中表现为同一个表目录或者分区目录下根据某个字段的值进行 hash 散列之后的多个文件；
view：与传统数据库类似，只读，基于基本表创建；
(5)Hive 的元数据存储在 RDBMS中，除元数据外的其它所有数据都基于 HDFS 存储。
PS：需要重点强调的是，虽然以上示例16-22中，窗口函数只是通过示例给出了语法结构，但在实际面试中窗口函数却是被考察最多的，其考点内容不一，没有统一的面试模板，都是面试官随机出题考察，通常会以实际案例形式给出。窗口函数在HiveSql的实际使用中能够实现各种复杂查询且语法结构简洁高效。所以强烈建议大家一定要多加练习，本公众号之前有一篇专门写Hive窗口函数的文章《Hive窗口函数使用指南》，讲解细致且示例很多，大家可自行查阅一下。
小结
本文将SQL/SparkSql/HiveSql放在一起梳理了一份常见题型的面试题库。每一小节前都给出了考点大纲，依据大纲，下面都会出几道模拟考题，每一个考题的讲解都尽量全面和详细，以带大家将相关知识点温习一遍，所选考题只是作为典型样例，其他类推知识考点或周边知识细节还需要大家再多加练习和总结，唯有充足的准备才能如愿找到理想的工作，希望本文对你有一定的帮助哦。
