HiveSQL实战——大数据开发面试高频SQL题

查询每个区域的男女用户数

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 数据分析

select
       sex ,
       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
from
     t1_stu_table
group by sex;

3 小结

略

==========================*****==========================

每个季度绩效得分大于70分的员工

0 问题描述

  计算每个季度绩效得分都大于70分的员工

1 数据准备

create table t2_score_info_table
(
    id  int,