大数据--面经2--2021年阿里巴巴大数据复盘以及面经总结

目录

一：在a表，不在b表

二：及格率

三：智力题

四：数仓和维度建模

五：宽窄依赖

六：mysql

七：题目

八：反问阶段：

---

一：在a表，不在b表

1. 有2张表t1和t2，每张表都只有一列(列名：id)，现在想抽取仅存在于t1的id，请写出sql语句

    【注意】：1）注意格式,最外层的id，我们需要指定其对应的表是a还是b，我们需要明确

                      2）不要使用无谓的临时表

    select a.id
    from
    (
        select id from t1
    )a 
    left outer join 
    (
        select id from t2
    )b
    on a.id = b.id
    where b.id is null

二：及格率

2. 表t1有3列：id, gender, score，3列均为int型，id表示员工工号，
gender表示性别（0女1男)，score代表员工某次考试的成绩(0-100之间)，
若成绩大于等于60为及格，请使用sql语句查询不同性别员工的及格率

方法一： 一个子查询查询及格的人数，一个子查询查询所有人数，外层查询计算及格率

select goodids / allids as reate
from
(
select gender , count(id) as goodids
from t1 
where score >= 60
group by gender
)a
left outer join
(
select gender , count(id) as allids
from t1
group by gender
)b
on a.gender = b.gender

方法二： 一个select查询，使用case when

【感悟】：当我们不想把约束条件放在最后的where里面，而是想根据约束条件在前面就进行查询

select gender,
count(case when score>=60 then id end ) / count(id)
group by gender

或者
select gender
sum(case when score>=60 then 1 else 0 end) / count(id)
group by gender

三：智力题

3. 任意连续N个正整数相乘，乘积结果的个位数有且仅有2种可能，求N

答：
    这N个正整数末尾数字不能出现5吧，感觉出现5,结果就只能是0这一种情况。 所以n <5;
                    也不能是0，不然结果也只可能是0了
    1*2 = 2； 2*3 = 6； 4*5 = 20 所以n不可能为2
    1*2*3 = 6； 2*3*4 = 24； 6*7*8 = 336 ； 7*8*9 = 504；
    // 如果含5，那么会出现结果0，个位一共就有三种情况了
    1*2*3*4 = 24；  6*7*8*9= 3034  。包含0这在结果，刚好个位数只能为4或是0，一共两种情况
    所以n =4；

四：数仓和维度建模

4.1五层：

ODS(operating data store)

DWD(data warehouse detail)

DWS(data warehouse service)

DWT(data warehouse topic)

ADS(Application data store)

4.2雪花模型和星型模型的理解以及对比

    星型模型是一组多维的数据关系，它由一个事实表和一组维度表组成。

将多个维度聚合到一个事实表，从而形成一个宽表。

    雪花模型：当有一个或者多个维度表没有直接连接到事实表上面，而是通过其他维度表连接在事实表上面。

雪花模型更符合数据库范式，可以减少数据冗余。

参考：https://www.huaweicloud.com/articles/00363de15581554282f2006c1efbf7c9.html

五：宽窄依赖

宽窄依赖的理解，为什么要分宽窄依赖哪些算子走宽依赖，哪些走窄依赖，怎么理解rdd

 

六：mysql

sql

数据库三大范式

mysql的acid：

原子性，一致性，隔离性，持久性。

四种隔离级别：

Read Uncommitted（读取未提交内容）

Read Committed（读取提交内容）

Repeatable Read（可重读）

Serializable（可串行化）

七：题目

写一个SQL吧。student(id, name, sex, age), book(id, name, stu_id(借阅学生id))，输出一下前十个18周岁男孩中借书最多的名字以及书本数量。

select  student.name  , count(book.id) 

from 

(select s1.id , s1.name from student s1) 

left outer join 

(select book.id , book.stu_id from book) 

on s1.id = book.stu_id

八：反问阶段：