提供服务的网站,网站开发文献资料,深圳外贸公司有哪些公司,国外设计文章的网站查询每个区域的男女用户数
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…查询每个区域的男女用户数
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博客
