HiveSQL优化技巧总结

SELECT  *     （必须）
FROM        表（数据源）
WHERE       条件
GROUP BY        字段
HAVING      条件
ORDER BY        字段(排序 默认 ASC)
LIMIT       限制数据条数

第一步：确定数据源
FROM
JOIN
ON 
第二步：过滤数据
WHERE 
GROUP BY (开始使用SELECT 中的别名，后面的语句中都可以使用)
avg，sum.......
HAVING
第三步：查询数据
SELECT
第四步：显示数据
DISTINCT
ORDER BY 
LIMIT

select a.*  
from a  
left join b on  a.uid = b.uid  
where a.ds='2020-08-10'  
and b.ds='2020-08-10'

select a.*  
from a  
left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10') 
where a.ds='2020-08-10'

select a.*  
from a  
left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10')  
where a.ds='2020-08-10'  
union all  
select a.* from a where a.uid is null

-- 未优化写法
select a,b,c
from table
where xxx
order by a
limit 10;
-- 优化写法
select a,b,c
from table
where xxx
distribute by a
sort by a
limit 10;

-- 取出user_trade表中全部支付用户
-- 原有写法
SELECT distinct user_name
FROM user_trade
WHERE dt>'0';
--测试时长 43 s
-- 优化写法
SELECT user_name
FROM user_trade
WHERE dt>'0'
GROUP BY user_name;
--测试时长 29 s

-- 原有写法
select count(distinct uid)  
from test  
where ds='2020-08-10' and uid is not null  
-- 优化写法
select count(a.uid)  
from   
(select uid 
 from test 
 where uid is not null and ds = '2020-08-10' 
 group by uid
) a

select a.*  
from  a  
left join b on  a.uid = b.uid  
where a.ds='2020-08-10'  
and b.ds='2020-08-10'
-- 转化为：
with test1 as 
(
select uid  
from b  
where ds = '2020-08-10' and uid is not null  
)  
select a.*  
from a  
left join test1 on a.uid = test1.uid  
where a.ds='2020-08-10' and a.uid is not null

-- 通常使用三条sql语句实现
-- 1. 性别分布
select sex,
count(distinct user_id)
from user_info
group by sex;
-- 2. 城市分布
select city,
count(distinct user_id)
from user_info
group by city;
-- 3. 等级分布
select level,
count(distinct user_id)
from user_info
group by level;

-- 使用 grouping sets 实现
select sex,city,level
         count(distinct user_id)
from user_info
group by sex,city,level
grouping sets (sex,city,level)

-- 统计性别、城市、等级等维度的各种组合的用户分布
SELECT sex,
   city,
   level,
   count(distinct 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(distinct user_id)
FROM user_info
GROUP BY sex,city,level
with cube;

SELECT a.dt,
   sum(a.year_amount),
   sum(a.month_amount)
FROM
  (SELECT substr(dt,1,4) as dt,
      sum(pay_amount) year_amount,
      0 as month_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY substr(dt,1,4)
   UNION ALL
   SELECT substr(dt,1,7) as dt,
      0 as year_amount,
      sum(pay_amount) as month_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY substr(dt,1,7)
  )a
GROUP BY a.dt;

SELECT year(dt) as year,
   month(dt) as month,
   sum(pay_amount)
FROM user_trade
WHERE dt>'0'
GROUP BY year(dt),
    month(dt)
with rollup;

set hive.exec.parallel=true;

SELECT a.user_name,
   sum(a.pay_amount),
   sum(a.refund_amount)
FROM
 ( SELECT user_name,
      sum(pay_amount) as pay_amount,
      0 as refund_amount
   FROM user_trade
   WHERE dt>'0'
   GROUP BY user_name
  UNION ALL
   SELECT user_name,
      0 as pay_amount,
      sum(refund_amount) as
refund_amount
   FROM user_refund
   WHERE dt>'0'
   GROUP BY user_name
 )a
GROUP BY a.user_name;
-- 时间对比：
-- 未开并发执行 103 s
-- 开启并发执行 64 s

-- 开启map端预聚合功能
set hive.map.aggr=true;  
-- 开启group by倾斜时自动进行负载均衡功能
set hive.groupby.skewindata = ture;

set hive.mapred.mode=strict