MYSql分组topN
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表包含公司所有部门的信息。 +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+ 编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回： +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释： IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
解析： SELECT D.Name, E1.Name, E1.Salary FROM Employee AS E1 JOIN Department AS D ON E1.DepartmentId = D.Id WHERE 3> ( SELECT COUNT(DISTINCT E2.`Salary`) FROM Employee E2 WHERE E2.Salary>E1.`Salary` AND E1.`DepartmentId`=E2.`DepartmentId` ) ORDER BY D.`Name`,E1.`Salary` DESC; 不妨假设e1=e2=[6,5,4,3]，则子查询的过程如下： 1、e1.Salary=3；则e2.Salary可以取4、5、6；COUNT(DISTINCT e2.Salary)=3 2、e1.Salary=4；则e2.Salary可以取5、6；COUNT(DISTINCT e2.Salary)=2 3、e1.Salary=5；则e2.Salary可以取6；COUNT(DISTINCT e2.Salary)=1 4、e1.Salary=6；则e2.Salary无法取值；COUNT(DISTINCT e2.Salary)=0 则要令COUNT(DISTINCT e2.Salary) < 3 的情况有上述的4、3、2. 也即是说，这等价于取e1.Salary最大的三个值。
三种方式处理sql的TOPN问题
1、自连接 SELECT * FROM aaa a1 WHERE 3> (SELECT COUNT(*) FROM aaa a2 WHERE a2.`num`>a1.num AND a1.`sex`=a2.`sex` ) ORDER BY a1.sex, a1.num DESC //解析 表内连接，在字段相同的情况下(AND a1.`sex`=a2.`sex`) 比较 数据处于表中的位置，然后（WHERE 3>）来确定要取值的N名 2 in select * from stu where score in (select max(score) ms from stu group by subject) //解析 先求出字段最大值的值，然后 字段 in （） //注意 方法只能查最大值 若有最大值重复，可以同时求出 3 union all (select * from stu where subject ='math' order by score desc limit 1) union all (select * from stu where subject ='english' order by score desc limit 1)") //解析 分别在每个字段中求出最大值然后union all //注意 该方法只能如果有重复值则结果出错建议用第一种
自连接排序
编写一个 SQL 查询来实现分数排名。 如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。 +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）： +-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+ 重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`
select s1.Score,count(distinct(s2.score)) Rank from Scores s1,Scores s2 where s1.score<=s2.score group by s1.Id order by Rank 例如数据有 1,2,3,3,4,5 s1.score<=s2.score s1=1时候 s2.score= 1,2,3,3,4,5 rank=6 去重=5 s1.score<=s2.score s1=2时候 s2.score= 2,3,3,4,5 rank=6 去重=4 s1.score<=s2.score s1=3时候 s2.score= 3,3,4,5 rank=6 去重=3 s1.score<=s2.score s1=4时候 s2.score= 4,5 rank=6 去重=2 s1.score<=s2.score s1=5时候 s2.score= 5 rank=6 去重=1 -- 19、按各科成绩进行排序，并显示排名 SELECT s1.c_id,s1.s_id,s1.s_score, COUNT(DISTINCT(s2.s_score)) Rank FROM Score s1,Score s2 WHERE s1.c_id=s2.c_id AND s1.s_score<s2.s_score GROUP BY s1.c_id,s1.s_id,s1.s_score ORDER BY s1.c_id,Rank
窗口函数
-- sum() select uid, month, amount, sum(amount) over( partition by uid order by month rows between unbounded preceding and current row ) accumulate--表示从当前行加到最前面一行( from( select uid, month, sum(amount) amount from t_accumulate group by uid, month ) tmp
--ntile 将数据分为n份 select a.user_id ,a.sum_pay_amount ,a.level from( select user_id ,sum(pay_amount) as sum_pay_amount ,ntile(5) over(order by sum(pay_amount) desc) as level from user_trade group by user_id ) a where a.level = 1
--提拉函数LAG()LEAD()
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
------------------------------------------
select
distinct Num as count
from
(
select Id,Num,
LAG(Num,1)OVER(order by Id) last_1,
LAG(Num,2)OVER(order by Id) last_2
from logs) as tmp
where
tmp.num=tmp.last_1
and
tmp.last_1=tmp.last_2
行转列
explode()函数，行转列函数
假如有以下数据
1,zhangsan,化学:物理:数学:语文
2,lisi,化学:数学:生物:生理:卫生
3,wangwu,化学:语文:英语:体育:生物
create table t_stu_subject(id int,name string,subjects array<string>)
row format delimited fields terminated by ','
collection items terminated by ":";
load data local inpath '/root/stu_sub.dat' into table t_stu_subject;
select explode(subjects) from t_stu_subject;
+------+--+
| col |
+------+--+
| 化学 |
| 物理 |
| 数学 |
| 语文 |
| 化学 |
| 数学 |
| 生物 |
| 生理 |
| 卫生 |
| 化学 |
| 语文 |
| 英语 |
| 体育 |
| 生物 |
+------+--+
select distinct tmp.subs from (select explode(subjects) subs from t_stu_subject) tmp;
+-----------+--+
| tmp.subs |
+-----------+--+
| 体育 |
| 化学 |
| 卫生 |
| 数学 |
| 物理 |
| 生物 |
| 生理 |
| 英语 |
| 语文 |
+-----------+--+
------------------------------------------------------------------------------------------------------------
1,zhangsan,化学:物理:数学:语文
2,lisi,化学:数学:生物:生理:卫生
3,wangwu,化学:语文:英语:体育:生物
lateral view函数 ##横向连接
select id,name,sub from t_stu_subject lateral view explode(subjects) tmp as sub
+-----+-----------+----------+--+
| id | name | tmp.sub |
+-----+-----------+----------+--+
| 1 | zhangsan | 化学 |
| 1 | zhangsan | 物理 |
| 1 | zhangsan | 数学 |
| 1 | zhangsan | 语文 |
| 2 | lisi | 化学 |
| 2 | lisi | 数学 |
| 2 | lisi | 生物 |
| 2 | lisi | 生理 |
| 2 | lisi | 卫生 |
| 3 | wangwu | 化学 |
| 3 | wangwu | 语文 |
| 3 | wangwu | 英语 |
| 3 | wangwu | 体育 |
| 3 | wangwu | 生物 |
+-----+-----------+----------+--+
炸map
select
id,name,key,value
from t_user lateral view explode(family) tmp as key,value
=========================================================
+-----+-----------+----------+--------+
| name | math | english |
+------------+-----------+----------+--+
| zhansgan | 32 | 88 |
| lisi | 88 | 93 |
+------------+-----------+----------+--+
转为
zhangsan math 32
zhangsan english 88
lisi math 88
lisi english 93
sql
select
test_stuinfo.name,
a.item,
a.score
from
test_stuinfo lateral view explode(
str_to_map(concat('math=', math, '&english=', english), '&', '=') --{"math:21","english:90"}
) a as item, score;
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
列转行
--case when select t.sname, max(case when t.cname="语文" then t.score end ) `语文`, max(case when t.cname="数学" then t.score end ) `数学`, max(case when t.cname="英语" then t.score end ) `英语` from (select s.s_name sname,sc.s_score score,c.c_name cname from ods_score sc,ods_student s,ods_course c where s.s_id=sc.s_id and sc.c_id = c.c_id) t group by t.sname 使用max和group by --
col_lie.user_id col_lie.order_id
104399 1715131
104399 2105395
104399 1758844
104399 981085
104399 2444143
104399 1458638
104399 968412
104400 1609001
104400 2986088
104400 1795054
-- 把相同user_id的order_id按照逗号转为一行
select user_id,
concat_ws(',',collect_list(order_id)) as order_value
from col_lie
group by user_id
limit 10
NVL函数
nvl(1,2) 1 第一个函数不为null 则取1 否则2
get_json_object
get_json_object(line,'$.mid') mid,
日期处理函数
date_format("2018-06-14",'yyyy-MM')转换格式
date_format("2018-06-14",'%Y-%m')转换格式
date_add('2018-10-29','-1)前一天
date_add('2018-10-29',1)后一天
next_day('2018-10-29','MO')下一个周一
date_add(next_day('2018-10-29','MO'),-7)本周一
last_day('2018-10-29')当月最后一天
date_format(regexp_replace('2020/12/11','/','-'),'yyyy-MM' ---->2020-12
datediff(date1,date2) 日期相减，date1
如果想把 20180123 转换成 2018-01-23，可以使用：
select from_unixtime(unix_timestamp('${p_date}','yyyymmdd'),'yyyy-mm-dd') from dual lim
时间戳转成日期
select distinct from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’) from test_date;
日期转成时间戳
select distinct unix_timestamp(‘20111207 13:01:03’) from test_date; // 默认格式为“yyyy-MM-dd HH:mm:ss“
select distinct unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’) from test_date;
collect函数
Hive笔记之collect_list/collect_set/concat_ws/concat函数（列转行）
create table t_visit_video (
username string,
video_name string
) partitioned by (day string)
row format delimited fields terminated by ',';
vi /home/hadoop/t_visit_video.txt
张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间
load data local inpath '/home/hadoop/t_visit_video.txt' into table t_visit_video partition (day='20200508');
hive (felix)> select * from t_visit_video;
OK
张三 大唐双龙传 20200508
李四 天下无贼 20200508
张三 神探狄仁杰 20200508
李四 霸王别姬 20200508
李四 霸王别姬 20200508
王五 机器人总动员 20200508
王五 放牛班的春天 20200508
王五 盗梦空间 20200508
Time taken: 1.445 seconds, Fetched: 8 row(s)
按用户分组，取出每个用户每天看过的所有视频的名字：
hive (felix)> select username, collect_list(video_name) from t_visit_video group by username;
张三 ["大唐双龙传","神探狄仁杰"]
李四 ["天下无贼","霸王别姬","霸王别姬"]
王五 ["机器人总动员","放牛班的春天","盗梦空间"]
Time taken: 26.414 seconds, Fetched: 3 row(s)
但是上面的查询结果有点问题，因为霸王别姬实在太好看了，所以李四这家伙看了两遍，这直接就导致得到的观看过视频列表有重复的，所以应该增加去重，使用collect_set，其与collect_list的区别就是会去重：
hive (felix)> select username, collect_set(video_name) from t_visit_video group by username;
OK
张三 ["神探狄仁杰","大唐双龙传"]
李四 ["霸王别姬","天下无贼"]
王五 ["盗梦空间","放牛班的春天","机器人总动员"]
Time taken: 24.167 seconds, Fetched: 3 row(s)
李四的观看记录中霸王别姬只出现了一次，实现了去重效果。
--实现了linux的rownum功能
hive (felix)> select username,video_name,day,row_number() over () rn from t_visit_video;
王五 盗梦空间 20200508 1
王五 放牛班的春天 20200508 2
王五 机器人总动员 20200508 3
李四 霸王别姬 20200508 4
李四 霸王别姬 20200508 5
张三 神探狄仁杰 20200508 6
李四 天下无贼 20200508 7
张三 大唐双龙传 20200508 8
突破group by限制
还可以利用collect来突破group by的限制，Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的，即select列必须是作为分组依据的列，但是有的时候我们想根据A进行分组然后随便取出每个分组中的一个B，代入到这个实验中就是按照用户进行分组，然后随便拿出一个他看过的视频名称即可：
hive (felix)> select username, collect_list(video_name)[0] from t_visit_video group by username;
张三 神探狄仁杰
李四 霸王别姬
王五 盗梦空间
Time taken: 23.027 seconds, Fetched: 3 row(s)
video_name不是分组列，依然能够取出这列中的数据。
concat、concat_ws 函数
1）concat只是将各个字符进行拼接
2）concat_ws是将各个字符串 通过一个指定的拼接符进行拼接，相当于Oracle的wm_concat和listagg
从数据库里取N个字段，然后组合到一起用“，”分割显示，起初想到用CONCAT()来处理，好是麻烦，没想到在手册里居然有提到CONCAT_WS()，非常好用。
它是一个特殊形式的 CONCAT()，第一个参数剩余参数间的分隔符，分隔符可以是与剩余参数一样的字符串，如果分隔符是 NULL，返回值也将为 NULL，这个函数会跳过分隔符参数后的任何 NULL 和空字符串，分隔符将被加到被连接的字符串之间。
hive (felix)> SELECT CONCAT_WS(",","First name","Second name","Last Name");
OK
First name,Second name,Last Name
Time taken: 0.055 seconds, Fetched: 1 row(s)
hive (felix)> SELECT CONCAT(",","First name","Second name","Last Name");
OK
,First nameSecond nameLast Name
Time taken: 0.044 seconds, Fetched: 1 row(s)
hive (felix)> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
OK
First name,Last Name
Time taken: 0.051 seconds, Fetched: 1 row(s)
collect_set 实例
-- 现在要生产订单号要关联出库单号，一个生产订单号可能有多个出库单号，如果join就会造成数据膨胀的问题 select * from (select * from 生产订单)a left join (select scddh, collect_set(out_general_code) out_general_code from 出库单表 group by scddh -- 只用group一个就可以了 ）b on a.scddh=b.scddh
str_to_map
使用两个分隔符转为键值对 第一个分隔符是键值对之间的分隔符，第二个分隔符是KV之间的分隔符
例:
select str_to_map('name:wangwenjie&sex:male','&',':')
结果: {"name":"wangwenjie","sex":"male"}
也可以通过K获取V
select str_to_map('name:wangwenjie&sex:male','&',':')['name']
结果:wangwenjie
reflect函数
-- hive新特性
--reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。
例如一张表数据
1,2
2,3
3,4
4,5
5,6
sql:select reflect("java.lang.Math","max",column1,column2) from table
--就是调用java的math中的max方法
结果：2,3，4,5,6
--使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123") from dual;
结果：true
regexp_replace
-- 把INITIAL_STRING中与PATTERN相匹配的子串替换为REPLACEMENT
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
ex:select regexp_replace('\n123\n','\n','456')
result:456123456
ex:select regexp_replace('\n123\n','\n|2','abc')
result:abc1abc3abc
用\\s替换空格，（最好） 用\\n替换\n，
hive> select regexp_replace('\n12 3 \n','\n|2|\s','abc') ;
abc1abc 3 abc
hive> select regexp_replace('\n12 3 \n','\n|2|\\s','abc') ;
abc1abcabcabc3abcabcabcabc
hive> select regexp_replace('\n12 3 \n','\\n|2|\\s','abc') ;
abc1abcabcabc3abcabcabcabc
explode和lateral view
-- 行转列 数据 100,200,300 400,500,600 sql: select explode(split(num,',')) from table result: 100, 200, 300, 400, 500, 600 -- 联合lateral view 数据： pageAds表 string pageid Array<int> adid_list "front_page" [1, 2, 3] "contact_page" [3, 4, 5] --要统计所有广告ID在所有页面中出现的次数。 首先分拆广告ID： select pageid,adid from pageAds lateral view explode(adid_list) adtable as adid 结果 string pageid int adid "front_page" 1 "front_page" 2 "front_page" 3 "contact_page" 3 "contact_page" 4 "contact_page" 5 SELECT adid, count(1) FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid GROUP BY adid; 执行结果如下： int adid count(1) 1 1 2 1 3 2 4 1 5 1 多个lateral view语句： 一个FROM语句后可以跟多个lateral view语句，后面的lateral view语句能够引用它前面的所有表和列名。 以下面的表为例： Array<int> col1 Array<string> col2 [1, 2] [a", "b", "c"] [3, 4] [d", "e", "f"] SELECT myCol1, col2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1; 的执行结果为： int mycol1 Array<string> col2 1 [a", "b", "c"] 2 [a", "b", "c"] 3 [d", "e", "f"] 4 [d", "e", "f"] 加上一个lateral view： SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(col2) myTable2 AS myCol2; 它的执行结果为： int myCol1 string myCol2 1 "a" 1 "b" 1 "c" 2 "a" 2 "b" 2 "c" 3 "d" 3 "e" 3 "f" 4 "d" 4 "e" 4 "f" 注意上面语句中，两个lateral view按照出现的次序被执行。
笛卡尔积应用
id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e 编写Hive的HQL语句来实现以下结果： 表中的1表示选修，表中的0表示未选修 id a b c d e f 1 1 1 1 0 1 0 2 1 0 1 1 0 1 3 1 1 1 0 1 0 -------------------------------------------------------------- 首先获取所有的课程 select collect_set(trim(course)) id_course from stuinfo group by id;--["a","b","c","d","e","f"] -------------------------------------------------------------- 求每一个学员的课程 select id,collect_set(trim(course)) id_course from interview04 group by id; -------------------------------------------------------------- 将所有课程和所选课程join形成笛卡尔积 select b.id id,b.id_course id_course,a.courses courses from (select collect_set(trim(course))courses from stuinfo ) a join (select id,collect_set(trim(course)) id_course from interview04 group by id ) b; id id_course courses 1 ["a","b","c","e"] ["a","b","c","e","d","f"] 2 ["a","c","d","f"] ["a","b","c","e","d","f"] 3 ["a","b","c","e"] ["a","b","c","e","d","f"] ------------------------------------------------------------- 最终判断coureses是否包含学生所选课程，包含为1不包含为0 select id, if(array_contains(id_course,courses[0]),1,0) a, --如果id_course(学生所选课程)包含courses[0](全部课程[0]) if(array_contains(id_course,courses[1]),1,0) b, if(array_contains(id_course,courses[2]),1,0) c, if(array_contains(id_course,courses[3]),1,0) e, if(array_contains(id_course,courses[4]),1,0) d, if(array_contains(id_course,courses[5]),1,0) f from (select b.id id,b.id_course id_course,a.courses from stuinfo from (select collect_set(trim(course))courses from stuinfo ) a --所有的课程 join (select collect_set(trim(course))id_course from stuinfo group by id)b )c--学生所选课程
连续登录
select
'2020-06-22',
concat(date_add('2020-06-22', -6), '|2020-06-22'),
count(*)
from
(
select
mid_id
from
(-- 查询差日期为同一天的结果大于等于3
select
mid_id,
count(*) cnts
from
( --日期减去排序，连续登陆则差相同
select
mid_id,
date_sub(dt, rk) date_diff
from
(-- 先根据ID给对应的日期排序
select
mid_id,
dt,
row_number() over(partition by mid_id order by dt) rk
from
dws_uv_detail_daycount
where
dt >= date_add('2020-06-22', -6)
and dt <= '2020-06-22'
) t1
) t2
group by
mid_id,
date_diff
having
cnts >= 3
) t3
group by
mid_id
) t4
树状结构两种情况
-- 层级相同 有最大层级 获取所有的层级
WITH t AS ( SELECT
parent_id
,id
,category_name,level
FROM
s_wxapp_swp_product_physic_category where ds=20210512 )
SELECT a.id lv1
,a.category_name lv1_name
,b.id lv2
,b.category_name lv2_name
,c.id lv3
,c.category_name lv3_name
,d.id lv4
,d.category_name lv4_name
FROM (
SELECT
parent_id
,id
,category_name
FROM
t
WHERE level=4
) a
LEFT JOIN t b
ON a.parent_id = b.id LEFT
JOIN t c
ON b.parent_id = c.id
LEFT JOIN t d
ON c.parent_id = d.id
;
-- 每个层级不固定，最大层级不确定只能最大循环，且只取最大最小
SELECT
IF(
g.pid IS NULL,
IF(
f.pid IS NULL,
IF(
e.pid IS NULL,
IF(
d.pid IS NULL,
IF(c.pid IS NULL, b.pid, c.pid),
d.pid
),
e.pid
),
f.pid
),
g.pid
) product,
a.bid
FROM
(
SELECT
bid
FROM
digui
WHERE
bid NOT IN (
SELECT
pid
FROM
digui
)
) a
LEFT JOIN digui b ON a.bid = b.bid
LEFT JOIN digui c ON b.pid = c.bid
LEFT JOIN digui d ON c.pid = d.bid
LEFT JOIN digui e ON d.pid = e.bid
LEFT JOIN digui f ON e.pid = f.bid
LEFT JOIN digui g ON g.pid = f.bid
GROUP BY
product,
bid
数据倾斜
文章链接
https://blog.csdn.net/u010670689/article/details/42920917?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162495266916780262577659%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162495266916780262577659&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~baidu_landing_v2~default-1-42920917.pc_search_result_control_group&utm_term=hive+%E6%95%B0%E6%8D%AE%E5%80%BE%E6%96%9C%E6%80%8E%E4%B9%88%E5%A4%84%E7%90%86&spm=1018.2226.3001.4187 https://blog.csdn.net/strongyoung88/article/details/100594082?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162495266816780357210641%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=162495266816780357210641&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-3-100594082.pc_search_result_control_group&utm_term=hive+%E6%95%B0%E6%8D%AE%E5%80%BE%E6%96%9C%E6%80%8E%E4%B9%88%E5%A4%84%E7%90%86&spm=1018.2226.3001.4187
3.1空值产生的数据倾斜
场景：如日志中，常会有信息丢失的问题，比如日志中的 user_id，如果取其中的 user_id 和 用户表中的user_id 关联，会碰到数据倾斜的问题。
解决方法1： user_id为空的不参与关联（红色字体为修改后）
select * from log a
join users b
on a.user_id is not null
and a.user_id = b.user_id
union all
select * from log a
where a.user_id is null;
解决方法2 ：赋与空值分新的key值
select *
from log a
left outer join users b
on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;
结论：方法2比方法1效率更好，不但io少了，而且作业数也少了。解决方法1中 log读取两次，jobs是2。解决方法2 job数是1 。这个优化适合无效 id (比如 -99 , ’’, null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。
3.2不同数据类型关联产生数据倾斜
场景：用户表中user_id字段为int，log表中user_id字段既有string类型也有int类型。当按照user_id进行两个表的Join操作时，默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。
解决方法：把数字类型转换成字符串类型
select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string)
3.3小表不小不大，怎么用 map join 解决倾斜问题
使用 map join 解决小表(记录数少)关联大表的数据倾斜问题，这个方法使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理。 以下例子:
select * from log a
left outer join users b
on a.user_id = b.user_id;
users 表有 600w+ 的记录，把 users 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。
解决方法：
select /*+mapjoin(x)*/* from log a
left outer join (
select /*+mapjoin(c)*/d.*
from ( select distinct user_id from log ) c
join users d
on c.user_id = d.user_id
) x
on a.user_id = b.user_id;
假如，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。
4总结
使map的输出数据更均匀的分布到reduce中去，是我们的最终目标。由于Hash算法的局限性，按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。在此给出较为通用的步骤：
1、采样log表，哪些user_id比较倾斜，得到一个结果表tmp1。由于对计算框架来说，所有的数据过来，他都是不知道数据分布情况的，所以采样是并不可少的。
2、数据的分布符合社会学统计规则，贫富不均。倾斜的key不会太多，就像一个社会的富人不多，奇特的人不多一样。所以tmp1记录数会很少。把tmp1和users做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。
3、map读入users和log，假如记录来自log,则检查user_id是否在tmp2里，如果是，输出到本地文件a,否则生成<user_id,value>的key,value对，假如记录来自member,生成<user_id,value>的key,value对，进入reduce阶段。
4、最终把a文件，把Stage3 reduce阶段输出的文件合并起写到hdfs。
如果确认业务需要这样倾斜的逻辑，考虑以下的优化方案：
1、对于join，在判断小表不大于1G的情况下，使用map join
2、对于group by或distinct，设定 hive.groupby.skewindata=true
3、尽量使用上述的SQL语句调节进行优化
485
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
