### BAT数据分析面试题详解
#### 一、用一条SQL语句查询出每门课都大于80分的学生姓名（5分）
**题目描述**：给定一张成绩表，需要找出所有科目成绩均超过80分的学生姓名。
**解题思路**：首先需要明确题目中的表结构，这里给出的数据似乎没有明确的表结构，我们假设有一个表`scores`，其中包含学生姓名`name`、课程名称`subject`以及分数`score`。
**SQL解答**：
```sql
SELECT name
FROM scores
GROUP BY name
HAVING MIN(score) > 80;
```
**解释**：此SQL语句首先根据学生姓名进行分组，然后使用`HAVING`子句筛选出最小成绩也大于80分的学生。
#### 二、按照分数对数据进行排序（5分）
**题目描述**：对于一张名为`scores`的成绩表，需要按照分数从高到低进行排序，并添加排名。
**解题思路**：使用窗口函数`RANK()`来实现排名功能。
**SQL解答**：
```sql
WITH RankedScores AS (
SELECT name, score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM scores
)
SELECT * FROM RankedScores;
```
**解释**：此SQL语句使用了CTE(Common Table Expression，公共表表达式)来定义一个名为`RankedScores`的临时视图，该视图包含原始表中的`name`和`score`字段，并通过`RANK()`函数计算每个学生的排名。最终选择所有字段展示出来。
#### 三、Hive表按条件排序（10分）
**题目描述**：对于一张名为`tmp_row_num`的Hive表，需要按`muid`分组，然后在每个分组内按`time`升序排序。
**解题思路**：利用Hive中的`ROW_NUMBER()`窗口函数实现排序。
**HiveQL解答**：
```hql
WITH RowSorted AS (
SELECT muid, channel, time,
ROW_NUMBER() OVER (PARTITION BY muid ORDER BY time) AS row_num
FROM tmp_row_num
)
SELECT * FROM RowSorted;
```
**解释**：此HiveQL语句同样使用CTE定义了一个临时视图`RowSorted`，在每个`muid`分组内部使用`ROW_NUMBER()`函数按`time`进行排序，并添加了一个名为`row_num`的列来表示排序后的顺序。
#### 四、Hive中SortBy、OrderBy、ClusterBy、DistributeBy的含义（5分）
1. **SortBy**：在Hive中用于指定文件内部的记录排序方式。
2. **OrderBy**：与SQL中的`ORDER BY`相似，用于全局排序整个结果集。
3. **ClusterBy**：用于创建集群索引，使数据在写入时按照指定字段进行排序。
4. **DistributeBy**：指定数据如何在MapReduce任务的Reducer之间进行分配。
#### 五、Hive一行变多行（5分）
**题目描述**：将表`tb_split`中的某一行数据转换为多行数据。
**HiveQL解答**：
```hql
SELECT date, A, SUBSTRING_INDEX(SUBSTRING_INDEX(B, '|', n), '|', -1) AS B
FROM tb_split CROSS JOIN
(SELECT number AS n FROM (SELECT 1 AS number UNION ALL SELECT 2 UNION ALL SELECT 3) t1) t2
WHERE B LIKE CONCAT('%|%', n, '|%') OR B LIKE CONCAT('%', n) OR B LIKE CONCAT(n, '%')
ORDER BY date, A, n;
```
**解释**：通过使用`SUBSTRING_INDEX()`函数并结合自连接的方式，可以将字符串`B`中的多个值拆分成多行。
#### 六、自动统计分布（10分）
**题目描述**：对于一张包含订单ID和预估时长的表，需要按照特定规则统计不同区间内的订单数量。
**HiveQL解答**：
```hql
WITH Distribution AS (
SELECT eta,
CASE
WHEN eta < 30 THEN CONCAT('[', FLOOR(eta / 5) * 5, ',', FLOOR(eta / 5) * 5 + 5, ')')
WHEN eta < 60 THEN CONCAT('[', FLOOR(eta / 10) * 10, ',', FLOOR(eta / 10) * 10 + 10, ')')
WHEN eta < 300 THEN CONCAT('[', FLOOR(eta / 30) * 30, ',', FLOOR(eta / 30) * 30 + 30, ')')
ELSE '[300,+]'
END AS eta_range
FROM table_name
)
SELECT eta_range, COUNT(*) AS order_count
FROM Distribution
GROUP BY eta_range
ORDER BY eta_range;
```
**解释**：首先使用`CASE`语句对不同的区间进行划分，然后使用`COUNT(*)`统计每个区间的订单数量。
#### 七、GROUP BY, WITH ROLLUP, WITH CUBE的作用（10分）
1. **GROUP BY**：用于将数据分组，通常与聚合函数一起使用。
2. **WITH ROLLUP**：除了基本的分组外，还会额外生成一个包含所有数据的汇总行。
3. **WITH CUBE**：除了基本的分组和WITH ROLLUP的效果外，还会生成所有可能的分组组合的汇总。
#### 八、按比例抽样（10分）
**HiveQL解答**：
1. **抽样10%**：
```hql
SELECT * FROM table_name WHERE RAND() < 0.1;
```
2. **抽样50%**：
```hql
SELECT * FROM table_name WHERE RAND() < 0.5;
```
**解释**：利用`RAND()`函数生成随机数，并根据条件筛选出相应的样本。
#### 扩展知识点
- **皮尔逊相关系数**：度量两个变量线性关系的强度和方向的统计量，取值范围[-1,1]。
- **辛普森悖论**：指在一个分类变量的子集中观察到的趋势与总体趋势相反的现象。避免方法包括调整分析方法、考虑潜在的混杂因素等。
- **闵科夫斯基距离**：一种测量两个点之间的距离的方法，欧氏距离和曼哈顿距离都是其特例。
- **协同过滤**：推荐系统的一种算法，基于用户或物品的相似度进行推荐。
- **n-grams**：自然语言处理中的一种技术，用于表示连续的n个词语。
- **MapReduce**：一种编程模型，用于处理大规模数据集，主要包括Map和Reduce两个阶段。
- **余弦距离**：用来衡量两个非零向量之间的夹角，常用于文本相似度的计算。
这些题目涵盖了SQL和HiveQL的基本操作及高级应用，对于准备BAT数据分析面试的人来说非常有价值。
