【数据分析面试】大厂高频SQL笔试题（一）

01、写在前面

秋高气爽，金桂飘香，十月份即将结束，对于求职的小伙伴们来说，“金九银十”已经接近尾声，不知道小伙伴们有没有找到自己心仪的工作呢？相信大多数的小伙伴都经历过数据分析的面试流程了，在数据分析的面试过程中，除了常规的业务问题外，还会重点考查数据分析的技术能力，这里就不得不提SQL了，SQL作为数据分析的笔试必考题，常常被用来检验候选人的技术水平，而且有些题目也是非常的刁钻，这对于初入职场的“小白”也是非常不友好。不过不用担心，结合自己多年的数据分析的求职和工作经验，我们接下来会有一系列文章，详细讲解数据分析面试中那些高频出现的SQL笔试题以及各大厂的SQL笔试题，学习了这些笔试题的常见套路和解法，把这些题目都刷一遍，在接下来的笔试中应该可以一往无前，势如破竹!，收割offer啦！
数据分析笔试中比较常考查的一个知识点就是行/列转换，以及一行/多行转换，因为在实际的业务中数据原本的存储方式可能不便于我们进行分析，所以我们需要进行行/列的转换，以及一行/多行的转换，这个转换过程非常常见也比较的tricky，所以也是数据分析面试中经常考查的内容！下面我们就通过实际的案例来进行讲解。

02、行/列转换

行/列转换在做数据分析时会经常遇到，所谓的行/列转换就是如下图所示两种展示形式的互相转换。

我们假设有4款产品ABCD，分别在三个电商平台天猫、淘宝和京东上进行销售，下表分别以两种形式记录了某个月各产品（Product）在各个平台（Platform）的销售数量（Quqntity）。我们为了分析的方便，需要对两种形式进行转换，也就是我们常说的行/列转换。

行转列
假如我们有下表，由Product、Platform、Quantity三个字段组成，但是为了方便展示，我们需要将Platform中的每一平台展示为一列，也就是行转列。

方法一：PIVOT
在SQL SERVER中，提供了专门进行行列转换的函数：PIVOT，PIVOT的一般语法是：
FROM table_source – 表名称，即数据源
PIVOT(
聚合函数（value_column） – value_column 要转换为 列值 的列名
FOR pivot_column – pivot_column 指定要转换的列
IN(column_list) – column_list 自定义的目标列名
)
使用PIVOT进行行转列的代码如下：
SELECT *
FROM table
PIVOT (
SUM(quantity) FOR platform IN (‘天猫’,‘淘宝’,‘京东’)
)
通过上面 SQL 语句即可得到下面的结果：

PIVOT 后跟一个聚合函数SUM来拿到结果，FOR 后面跟的平台Platform是我们要转换的列，这样的话平台中的天猫、淘宝、京东就就被转换为列。IN 后面跟的就是具体的平台名称。

方法二：聚合函数+CASE WHEN
需要注意的是PIVOT是SQL SERVER的内置函数，对于行列转换非常方便，但Oracle、MySQL是不支持PIVOT函数的。我们可以用聚合函数配合CASE语句实现。使用 CASE WHEN 可以得到和 PIVOT 同样的结果，但是没有 PIVOT 简单直观。
SELECT Product,
MAX(CASE WHEN Platform=‘天猫’ THEN quantity ELSE 0 END) AS “天猫”,
MAX(CASE WHEN Platform=‘淘宝’ THEN quantity ELSE 0 END) AS “淘宝”,
MAX(CASE WHEN Platform=‘京东’ THEN quantity ELSE 0 END) AS “京东”
FROM table
GROUP BY Product

列转行
反过来，假设我们有下表 table，各个平台已经作为一列出现，我们需要将各个平台汇总为一列，也就是列转行。

方法一：UNPIVOT
与行转列类似，在SQL SERVER中，提供了列转行的函数：UNPIVOT，UNPIVOT的一般语法是：
FROM table_source – 表名称，即数据源
UNPIVOT(
value_column – value_column 要转换为 行值 的列名
FOR pivot_column – pivot_column 指定要转换为指定的列
IN(column_list ) – column_list 目标列名
)
使用UNPIVOT进行列转行的代码如下：
SELECT *
FROM table
UNPIVOT (
quantity FOR Platform IN (“天猫”,“淘宝”,“京东”)
)
通过 UNPIVOT 可得到如下结果：

方法二：聚合函数+UNION
同理，Oracle，MySQL也是不支持UNPIVOT函数的，对于这种情况我们可以通过聚合函数+UNION的方式将数据组合起来。
SELECT
Product,
‘天猫’ AS Platform ,
MAX(“天猫”) AS Quantity
FROM table GROUP BY Product
UNION
SELECT
Product,
‘淘宝’ AS Platform ,
MAX(“淘宝”) AS Quantity
FROM table GROUP BY Product
UNION
SELECT
Product,
‘京东’ AS Platform ,
MAX(“京东”) AS Quantity
FROM table GROUP BY Product

03、一行/多行转换

在数据分析中，还有一个非常常见的场景，就是需要将一行数据拓展为多行数据，或者将多行数据汇总为一行数据，这种一行/多行的数据转换问题也是面试中频繁考查的知识点之一，举例如下图所示。

一行转多行
原数据如下：table表中有两个字段，产品（Product）和供应商（Supplier），每个产品有多个供应商，各个供应商以逗号分割汇总为一行。现在需要将各个供应商分别展开为一行。

针对这种情况，Hive中提供了一个很高效的方法：lateral view和explode等函数一起使用，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，使用的时候需要注意：
1、explode接受一个数组（或一个map）作为输入，并将数组元素（map）作为单独的行输出。UDTF可以在SELECT表达式列表中使用，也可以作为LATERAL VIEW的一部分使用。
2、LATERAL VIEW是配合explode（或者其他的UDTF），把单行数据拆解成多行后的数据结果集。
3、LATERAL VIEW explode要放置于from之后，where之前
SELECT DISTINCT Product,Supplier
FROM table
LATERAL VIEW explode (split(Supplier,’,’)) as t – t为新表别名
拆成后结果：

多行转一行
反过来，我们想把多行再变回一行，需要怎么处理呢？我们可以使用：collect_set +concat_ws来实现。代码如下：
SELECT Product,concat_ws(’,’,collect_set(Supplier)) Product_Supplier
FROM table
GROUP BY Product
最终还原为一行数据如下：

注意：上述用的到的collect_set函数，有两个作用，第一个是去重，去除group by后的重复元素，
第二个是形成一个集合，将group by后属于同一组的集合起来成为一个集合。与contact_ws结合使用就是将这些元素以逗号分隔的形式形成字符串。
以上就是数据分析面试—SQL高频面试题部分的内容，部分数据分析文章请移步 共粽号：【数据分析星球】，更多数据分析面试笔试的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞哈~