hiveQL复习

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
…