Hive Sql中两个字段顺序无关去重

1.背景

在用户点击网页中,会存在 链路A->B->A ,用户点了A页面,进入B页面,又返回A页面,此时子页面(page_id)和父页面(refer_id)的关系,可以形成表:
 page_id  refer_id
 B            A
 A				B

  这两条数据,假如将 B,A  A,B看做一条数据,该如何去重?

2.案例

create table page(
page_id string,
refer_id string
)
row format delimited fields terminated  by ',' lines terminated by '\n';

vim /opt/page.log
E,A
B,A
F,A
K,A
G,A
A,E
A,B
A,F

load data local inpath '/opt/page.log' into table page;

3.去重方法

   //1.将page表中本身重复的数据去重
   spark-sql (default)> select * from page group by page_id,refer_id;
    page_id	refer_id
	E	A
	B	A
	F	A
	K	A
	G	A
	A	E
	A	B
	A	F
	Time taken: 0.208 seconds, Fetched 8 row(s)

  //2.sql输出结果
	  select collect_list(page_id)[size(collect_list(page_id))-1],collect_list(refer_id)[size(collect_list(refer_id))-1]  from 
	(
	select page_id,refer_id,
	case when  hex(page_id)<hex(refer_id) then concat(page_id,refer_id)
	else concat(refer_id,page_id)
	end  as concat_page
	from page 
	) a group by concat_page;

//输出结果
A	E
G	A
A	B
K	A
A	F
Time taken: 5.011 seconds, Fetched 5 row(s)


//结果分析
(1)hex(X)是取X的16进制值
(2) 子查询结果
   select page_id,refer_id,
	case when  hex(page_id)<hex(refer_id) then concat(page_id,refer_id)
	else concat(refer_id,page_id)
	end  as concat_page
	from page 
    
     	page_id	refer_id	concat_page
		E				A				AE
		B				A				AB
		F				A				AF
		K				A				AK
		G				A				AG
		A				E				AE
		A				B				AB
		A				F				AF
		Time taken: 4.855 seconds, Fetched 8 row(s)
(3)  collect_list(page_id)[size(collect_list(page_id))-1] 是取相同时的最后一条,也可以按具体场景取第一条 collect_list(page_id)[0]

4. Mysql实现同样功能

   由于mysql没有collect_list函数,使用SUBSTRING_INDEX和GROUP_CONCAT完成同样功能
	 select concat_page,SUBSTRING_INDEX(group_concat(',',page_id),',',-1),SUBSTRING_INDEX(group_concat(',',refer_id),',',-1)
	from (
	select page_id,refer_id,
	case when  hex(page_id)<hex(refer_id) then concat(page_id,refer_id)
	else concat(refer_id,page_id)
	end  as concat_page
	from page
	)  a group by concat_page ;

参考资料

Hive SQL去重a,b和b,a类型
https://blog.51cto.com/darrenmemos/2175665

MySQL中HEX函数的用法
https://blog.csdn.net/jiangnan2014/article/details/18818425

SQL：两个字段的（顺序无关）去重
https://blog.csdn.net/shixinwenwu/article/details/54895869

SQL查询两列值相同，位置相反去重问题
https://blog.csdn.net/sinat_32133675/article/details/83019513