1. 统计类
pv: count(f1)
uv: count(distinct f1) | count(1) from ( select f1 group by f1)
2. 多表join
3. 窗口函数
over(),开窗,并可自由控制窗口大小,其可以操作分组前的数据
order表
name | orderdate | cost |
---|---|---|
jack | 2017-01-05 | 46 |
jack | 2017-01-08 | 55 |
jack | 2017-01-01 | 10 |
jack | 2017-04-06 | 42 |
jack | 2017-02-03 | 23 |
mart | 2017-04-13 | 94 |
mart | 2017-04-11 | 75 |
mart | 2017-04-09 | 68 |
mart | 2017-04-08 | 62 |
neil | 2017-05-10 | 12 |
neil | 2017-06-12 | 80 |
tony | 2017-01-04 | 29 |
tony | 2017-01-02 | 15 |
tony | 2017-01-07 | 50 |
select
sum(cost) -- 窗口范围是整个表
from
tb_order ;
select
* ,
sum(cost) over() -- 开窗口范围是整个表
-- sum(cost) over(rows between unbounded preceding and unbounded following) -- 开窗口范围是整个表
from
tb_order ;
select
* ,
sum(cost) over(partition by name) -- 开窗口范围是分区
from
tb_order ;
select
* ,
sum(cost) over(partition by name rows) -- 开窗口范围是分区
from
tb_order ;
select
* ,
sum(cost) over(partition by name rows between 1 preceding and current row ) -- 开窗口范围是分区内,但前行前一行,和当前行
from
tb_order ;
select
* ,
sum(cost) over(partition by name rows between 1 preceding and 1 following ) -- 开窗口范围是分区内,但前行前一行,当前行和当前行后一行
from
tb_order ;
select
* ,
sum(cost) over(partition by name order by cost) -- 开窗口范围是分区,sum结果在分区内逐条累加
from
tb_order ;
3.1 求订单的总金额
3.2 求订单明细和总金额
3.3 求 最近三笔订单的总金额
3.4 求每个人的消费总额
3.5 求订单的总金额
3.6 求订单的总金额
4. 编号函数
- RANK() 排序相同时会重复,总数不会变
如:
数据:1,3,5,5,6,7
编号:1,2,3,3,5,6
- DENSE_RANK() 排序相同时会重复,总数会减少
如:
数据:1,3,5,5,6,7
编号:1,2,3,3,4,5
- ROW_NUMBER() 会根据顺序计算
如:
数据:1,3,5,5,6,7
编号:1,2,3,4,5,6
5. 行列转换
表studen
stude.name | stude.subject | stude.score |
---|---|---|
小叶 | 语文 | 97.0 |
小叶 | 数学 | 88.0 |
小叶 | 英语 | 99.0 |
小军 | 数学 | 98.0 |
小军 | 英语 | 68.0 |
小军 | 语文 | 95.0 |
表student
student.name | student.math | student.english | student.chinse |
---|---|---|---|
小军 | 98.0 | 68.0 | 95.0 |
小叶 | 88.0 | 99.0 | 97.0 |
列转行 stude --> student
套路:收集列数据(巧妙设计好数据结构),拆分收集的数据,(k,v)关系的数据可用stt_to_map巧妙利用map字段
with temp as (
select
name,
str_to_map(concat_ws(",", collect_list(
concat_ws(":",subject, cast(score as string))
))) as info
from stude
group by name
)
select
name ,
info["数学"] math ,
info["英语"] english,
info["语文"] chinse
from temp;
行转列 student —> stude
套路,收集列数据(巧妙设计好数据结构) —> 侧窗炸裂(later view explode() tempTable as field)将列数据与行固定字段多次匹配
create table student as
with temp as (
select
name,
str_to_map(concat_ws(",", collect_list(
concat_ws(":",subject, cast(score as string))
))) as info
from stude
group by name
)
with temp as (
select
name,
split(concat_ws(",",concat_ws("_","math",math),concat_ws("_","english",english),concat_ws("_","chines",chines)), ",") as scores
from
student
)
select
name,
case split(subjects, "_")[0]
when "math" then "数学"
when "english" then "英语"
when "chines" then "语文"
end as subject,
split(subjects, "_")[1]
from temp lateral view explode(scores) tt as subjects
;
6. 下压和上压(lag,lead)
LAG(col,n):往后第n行数据 lag 参数一 字段 n
LEAD(col,n):往前第n行数据 lead
7.面试题
7.1 编写两段SQL来计算女性员工占比
position
company_id | people_id | start_date | end_date |
---|---|---|---|
2a0a0700a3cf1c40a45c92839694390f | cdab114a8710180dbafc1f61e4980a62 | 2019-04-30 0:00:00 | null |
2a0a0700a3cf1c40a45c92839694390f | 9bdc35b14a5cbebfb4d6a3167ab5d6a2 | 2019-04-30 0:00:00 | null |
2a0a0700a3cf1c40a45c92839694390f | 9da257186d8f76a21d531d0a565a4ecf | 2019-04-30 0:00:00 | null |
2a0a0700a3cf1c40a45c92839694390f | 33a7d11fc538b5314154b99b2d09361e | 2019-04-30 0:00:00 | null |
2a0a0700a3cf1c40a45c92839694390f | d220601176d02766f20094ec47fb7e98 | 2019-04-30 0:00:00 | null |
2a0a0700a3cf1c40a45c92839694390f | dbfd5a2b5c7d7a184f0a7cd43a70b2db | 2019-04-30 0:00:00 | null |
8a507c27a1d64dd429c919e424031a51 | 51fb80e9410e8b10baad7f98013fd5c6 | 2017-09-25 0:00:00 | 2019-09-24 0:00:00 |
8a507c27a1d64dd429c919e424031a51 | 9de35bedd084cbb7cb60df1902515b08 | 2017-09-25 0:00:00 | 2019-09-24 0:00:00 |
8a507c27a1d64dd429c919e424031a51 | fd86bc39627f18ec98842c2afb9f317a | 2017-09-25 0:00:00 | 2019-09-24 0:00:00 |
8a507c27a1d64dd429c919e424031a51 | bd72d1d6bd9d47505bfa22ef61d96ded | 2017-09-25 0:00:00 | 2020-09-24 0:00:00 |
8a507c27a1d64dd429c919e424031a51 | b4cbd8418939c862e97c769e7a8fd60e | 2017-09-25 0:00:00 | 2020-09-24 0:00:00 |
8a507c27a1d64dd429c919e424031a51 | 72cfb720b6b6fe1d0101b131a2ecf198 | 2017-09-25 0:00:00 | 2020-09-24 0:00:00 |
8a507c27a1d64dd429c919e424031a51 | fe9e348160edbdcc4363a2f9f4de87f3 | 2017-09-25 0:00:00 | 2020-09-24 0:00:00 |
people表:
people_id | people_name | gender |
---|---|---|
8710180dbafc1f61eefcsadfe4980a62 | 孙鹏 | MALE |
9bdc35b14a5cbebfb4d6a3167ab5d6a2 | 董艳 | FEMALE |
9da257186d8f76a21d531d0a565a4ecf | 刘鸿玲 | FEMALE |
33a7d11fc538b5314154b99b2d09361e | 黄智锋 | MALE |
d220601176d02766f20094ec47fb7e98 | 陈方汉 | FEMALE |
dbfd5a2b5c7d7a184f0a7cd43a70b2db | 王辅 | MALE |
51fb80e9410e8b10baad7f98013fd5c6 | 孙颖 | FEMALE |
9de35bedd084cbb7cb60df1902515b08 | 胥雪瑾 | FEMALE |
fd86bc39627f18ec98842c2afb9f317a | 沈岚 | FEMALE |
bd72d1d6bd9d47505bfa22ef61d96ded | 龚佩 | FEMALE |
b4cbd8418939c862e97c769e7a8fd60e | 郝妍燕 | FEMALE |
72cfb720b6b6fe1d0101b131a2ecf198 | 张来娣 | FEMALE |
fe9e348160edbdcc4363a2f9f4de87f3 | 曹建成 | MALE |
7.1.1计算每家公司所有时间内的女性员工占比
with temp as (
select
company_id,
gender,
count(1) as count
from people join position on people.people_id = position.people_id
group by company_id,gender
),
temp1 as (
select
company_id,
count(1) total
from position
group by company_id
)
select
gender,
temp.company_id,
temp.count / total
from temp left join temp1
on temp.company_id = temp1.company_id
;
-- 男和女的都算了,算女的话可以先计算出每个公司的女员工,在left join上公司总数即可得到
select
tt.company_id,
totalPeople,
ft.FamleTotalPeople,
ft.FamleTotalPeople / tt.totalPeople
from
(
select
ps.company_id,
gender,
count(1) FamleTotalPeople
from
Position ps left join People pp on ps.people_id = pp.people_id
GROUP BY ps.company_id, gender having gender = " FEMALE"
) as ft
left join
(
select
ps.company_id,
count(1) totalPeople
from
position ps
GROUP BY ps.company_id
) as tt
on tt.company_id = ft.company_id
7.1.2计算每家公司每年的女性员工占比(当年是否在职条件: 任职区间是否包含该年最后一天)
可能用到一张记录了所有年份的表
|Year |Series Table
years |
---|
… |
2012 |
2013 |
2014 |
2015 |
2016 |
2017 |
… |
![]() |