oraclerollup分组没有数据时为0_Hive入门数据分析基础5

六, 常用优化技巧---主要内容和思路来源:开课吧学习笔记

1, 用 group by 代替 distinct 去重

在数据量大, 重复值多时, 能先使用 group by 去重的, 使用 group by 去重后再计算, 比之后再去重计算效率更高.

例如前面的例题中, 查询 2019 年购买后又退款的用户, 可以这样优化:

select a.user_name
from
(select user_name
from user_trade
where year(dt)&＃61;2019
group by user_name) a
join
(select user_name
from user_refund
where year(dt)&＃61;2019
group by user_name) b
on a.user_name&＃61;b.user_name;


2, 多维度聚合

需要对多个字段进行聚合运算, 一个字段一个字段的写 SQL 计算, 不如使用多维度聚合计算语句: grouping sets, cube 和 rollup.

2.01, 分别查询用性别, 城市, 等级分布:

select sex, city, level,
count(user_id)
from user_info
group by sex, city, level;


上述 sql 查询出来的是同一性别, 不同城市, 不同等级的分布, 而我们需要的结果是三个字段的结果是分开的:

select sex, city, level,
count(user_id)
from user_info
group by sex, city, level
grouping sets (sex, city, level);


a, grouping sets() 相当于将不同 group by 聚合的结果进行 union all, 可以在括号中指明聚合规则.

2.02, 查询性别分布, 以及不同性别的城市分布:

select sex, city,
count(user_id)
from user_info
group by sex, city
grouping sets (sex, (sex, city));


城市一列为 null 的是性别分布, 其他的是每个性别的城市分布.

b, with cube将分组聚合的所有维度都进行计算:

2.03, 查询性别, 城市, 等级各种组合的用户分布情况:

-- 方法一
select sex, city, level,
count(user_id)
from user_info
group by sex, city, level
grouping sets (sex, city, level,
(sex, city), (sex, level), (city, level),
(sex, city, level));

-- 方法二
select sex, city, level,
count(user_id)
from user_info
group by sex, city, level
with cube;


方法二将所有用户数进行了统计, 方法一没有.

c, with rollup以最左侧的字段为主, 进行层级聚合, 结果是 with cube 的子集.

2.04, 查询每个月的支付金额及每年的支付金额:

-- 方法一
select a.dt,
sum(a.year_amount),
sum(a.month_amount)
from
(select substr(dt, 1, 4) dt,
sum(pay_amount) year_amount,
0 month_amount
from user_trade
where dt>&＃39;0&＃39;
group by substr(dt, 1, 4)
union all
select substr(dt, 1, 7) dt,
0 year_mount,
sum(pay_amount) month_amount
from user_trade
where dt>&＃39;0&＃39;
group by substr(dt, 1, 7)
) a
group by a.dt;


第一列是年的总额.

-- 方法二
select year(dt) year,
month(dt) month,
sum(pay_amount)
from user_trade
where dt>&＃39;0&＃39;
group by year(dt), month(dt)
with rollup;


第一列是全部的总额, 第二列是年的总额. with rollup计算了全部的总额和以 year 字段为主的两个维度 year, (year, month)的总额.

3, 转换解题思路

前面的例题, 查询 2017 和 2018 都购买的用户, 可以如下优化:

select a.user_name
from (select user_name,
count(distinct year(dt)) year_num
from user_trade
where year(dt) in (2017, 2018)
group by user_name) a
where a.year_num&＃61;2;

-- 还可再优化
select user_name,
count(distinct year(dt)) year_num
from user_trade
where year(dt) in (2017, 2018)
group by user_name
having count(distinct year(dt))&＃61;2;


4, 有 union all 查询时, 开启并发执行

开启参数设置: set hive.exec.parallel&＃61;true

查看是否设置成功: set hive.exec.parallel;

多台服务器时开启才有效.

5, 同一字段的数据展开, 或按分组归类

有用户购买的商品表 user_goods_category:

部分数据:

a, 同一字段的数据展开:

基表 lateral view explode(列表字段) 单列表 as 列名1


得到的结果是一个将 基表 的 列表字段 展开得到列名为 列名1 的 单列表 与 基表 合并的特殊表.

5.01, 查询每个商品品类的购买用户数:

select b.category,
count(distinct a.user_name)
from user_goods_category a
lateral view explode(split(category_detail, &＃39;,&＃39;)) b as category
group by b.category;


split()将字符串分割并以列表形式返回.

explode()将值为列表形式的字段展开成多行.

from语句后面得到的特殊表如下:

b, 同一字段按分组归类:

5.02, 查看每个商品品类都有哪些用户购买:

select b.category,
collect_set(distinct a.user_name)
from user_goods_category a
lateral view explode(split(category_detail, &＃39;,&＃39;)) b as category
group by b.category;


collect_set()将分组后分到同一组的值全部放到一个列表里显示出来, 还可以再用 concat_ws(&＃39;分隔符&＃39;, ...) 将列表中的值连接成字符串.

6, 表连接优化

• 使用相同的连接键
  对3个以上表进行 join 连接, on 条件使用相同的连接键, 只会产生一个 MapReduce job.

• 尽早进行数据过滤
  例如数据只选择需要的区段和字段, 分组去重等.

• 逻辑过于复杂时引入中间表

7, 防止数据倾斜

数据倾斜: 任务执行过程中, 大部分任务执行完成, 少数任务一直在执行中的情况.

• 空值产生的数据倾斜
  表连接时, 连接字段有空值, 增加空值过滤条件, 例如:on a.id&＃61;b.id and a.id is not null

• 表连接时, 连接字段数据类型不一致
  将数据类型转换一致, 例如:on a.id&＃61;cast(b.id as string)

end