广州招投标中心官网合肥百度推广优化
查询每个区域的男女用户数
0 问题描述
每个区域内男生、女生分别有多少个
1 数据准备
use wxthive;
create table t1_stu_table
(id int,name string,class string,sex string
);
insert overwrite table t1_stu_table
values(4,'张文华','二区','男'),(3,'李思雨','一区','女'),(1,'王小凤','一区','女'),(7,'李智瑞','三区','男'),(6,'徐文杰','二区','男'),(8,'徐雨秋','三区','男'),(5,'张青云','二区','女'),(9,'孙皓然','三区','男'),(10,'李春山','三区','男'),(2,'刘诗迪','一区','女');2 数据分析
selectsex ,count(case when class = '一区' then sex else null end ) as 1area,count(case when class = '二区' then sex else null end ) as 2area,count(case when class = '三区' then sex else null end ) as 3area
fromt1_stu_table
group by sex;
3 小结
略
==========================*****==========================
每个季度绩效得分大于70分的员工
0 问题描述
计算每个季度绩效得分都大于70分的员工
1 数据准备
create table t2_score_info_table
(id int,name string,subject string,score int
);insert overwrite table t2_score_info_table
values(1,'王小凤','第一季度',88),(1,'王小凤','第二季度',55),(1,'王小凤','第三季度',72),(3,'徐雨秋','第一季度',92),(3,'徐雨秋','第二季度',77),(3,'徐雨秋','第三季度',93),(2,'张文华','第一季度',70),(2,'张文华','第二季度',77),(2,'张文华','第三季度',91);
2 数据分析
selectid,name,min(score) as min_score
fromt2_score_info_table
group byid,name
having min_score > 70;
ps：只要保证每个季度的每位员工的最小绩效得分大于70分，就可以说明这位员工该季度的绩效得分都大于70分。
3 小结
==========================*****==========================
行列互换
0 问题描述
把下表所示的纵向存储数据的方式 改成 横向存储数据的方式（行转列）
1 数据准备
create table t3_row_col_table
(year_num int,month_num int,sales int
);insert overwrite table t3_row_col_table
values
(2019,1,100),
(2019,2,200),
(2019,3,300),
(2019,4,400),
(2020,1,200),
(2020,2,400),
(2020,3,600),
(2020,4,800);2 数据分析
selectyear_num,sum(case when month_num= 1 then sales end ) as m1,sum(case when month_num= 2 then sales end ) as m2,sum(case when month_num= 3 then sales end ) as m3,sum(case when month_num= 4 then sales end ) as m4
from t3_row_col_table
group by year_num
3 小结
==========================*****==========================
计算用户留存情况（*）
0 问题描述
计算用户的次日留存数、三日留存数、七日留存数
1 数据准备
create table t4_user_login
(uid int,login_time string
);insert overwrite table t4_user_login
values
(1,'2021-4-21 6:00'),
(1,'2021-4-24 10:00'),
(1,'2021-4-25 19:00'),
(2,'2021-4-22 10:00'),
(2,'2021-4-28 9:00'),
(2,'2021-4-29 14:00'),
(3,'2021-4-27 8:00'),
(3,'2021-4-28 10:00');2 数据分析
selectcount(case when day_value = 1 then uid else null end ) as cnt1,count(case when day_value = 3 then uid else null end ) as cnt2,count(case when day_value = 7 then uid else null end ) as cnt3
from(selectuid,max(date_format(login_time,'yyyy-MM-dd')),min(date_format(login_time,'yyyy-MM-dd')),datediff(max(date_format(login_time,'yyyy-MM-dd')), min(date_format(login_time,'yyyy-MM-dd'))) as day_valuefrom t4_user_logingroup by uid) t1;
3 小结
==========================*****==========================
筛选最受欢迎的课程
0 问题描述
筛选最受欢迎的课程course
1 数据准备
create table t5_course_table
(uid int,name string,grade string,course string
);insert overwrite table t5_course_table
values
(1,'王小凤','一年级','心理学'),
(2,'刘诗迪','二年级','心理学'),
(3,'李思雨','三年级','社会学'),
(4,'张文华','一年级','心理学'),
(5,'张青云','二年级','心理学'),
(6,'徐文杰','三年级','计算机'),
(7,'李智瑞','一年级','心理学'),
(8,'徐雨秋','二年级','计算机'),
(9,'孙皓然','三年级','社会学'),
(10,'李春山','一年级','社会学');2 数据分析
-- 解法一：
selectcourse,count(course) as course_count
from t5_course_table
group by course
order by course_count desc
limit 1;-- 解法二：
selectcourse,course_count
from (selectcourse,count(course) as course_count,row_number()over(order by count(course) desc) as rnfrom t5_course_tablegroup by course)t1
where rn = 1;
3 小结
==========================*****==========================
筛选出每个年级最受欢迎的三门课程
0 问题描述
筛选出每个年级最受欢迎的三门课程
1 数据准备
create table t5_course_table
(uid int,name string,grade string,course string
);insert overwrite table t5_course_table
values
(1,'王小凤','一年级','心理学'),
(2,'刘诗迪','二年级','心理学'),
(3,'李思雨','三年级','社会学'),
(4,'张文华','一年级','心理学'),
(5,'张青云','二年级','心理学'),
(6,'徐文杰','三年级','计算机'),
(7,'李智瑞','一年级','心理学'),
(8,'徐雨秋','二年级','计算机'),
(9,'孙皓然','三年级','社会学'),
(10,'李春山','一年级','社会学');2 数据分析
selectgrade,course
from(selectgrade,course,cnt,row_number() over(partition by grade order by cnt desc) as rnfrom(selectgrade,course,count(uid) as cntfrom t5_course_tablegroup by grade, course)t1)t2
where rn <= 3;
3 小结
==========================*****==========================
参考文章：
大数据开发面试必会的SQL 30题！！！_大数据sql面试题-CSDN博客