HiveSQL去重a,b和b,a类型-创新互联

昨天开发找到我们DBA，要我们写一条Hive SQL。

成都创新互联公司服务项目包括忻州网站建设、忻州网站制作、忻州网页制作以及忻州网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，忻州网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到忻州省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

需求：

有一个t表，主要有机场名称airport，机场的经纬度distance这两个列组成，想得到所有距离小于100的两个机场名。

其实写这个SQL的逻辑并不是很困难，难点是如何去重复值，

我用MySQL模拟的一个表，其实Hive语法和SQL差不多，插入了三条数据，a, b, c 分别代表三个机场名称，结构如下：

mysql> show create table t\G *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `airport` varchar(10) DEFAULT NULL,   `distant` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from t; +---------+---------+ | airport | distant | +---------+---------+ | a       |     130 | | b       |     140 | | c       |     150 | +---------+---------+ 3 rows in set (0.00 sec)

通过!=筛选掉本机场自己之间的比较，用abs函数取绝对值得到位置小于100的两个机场

mysql> select t1.airport, t2.airport from t t1,t t2 where t1.airport != t2.airport and abs(t1.distant-t2.distant) < 100; +---------+---------+ | airport | airport | +---------+---------+ | b       | a       | | c       | a       | | a       | b       | | c       | b       | | a       | c       | | b       | c       | +---------+---------+ 6 rows in set (0.00 sec)

但是问题来了，(b,a) 与(a,b)，(c,a)与(a,c)，(c,b)与(b,c)这里被我们视为重复值，我们只需要得到其中某一行的数据，就知道是哪两个机场名了，那么，如何去掉这个重复值呢？

貌似distinct，group by都派不上用场了，最后咨询了一位资深的SQL高手，找到了这么一个函数hex(),可以把一个字符转化成十六进制，Hive也有对应的函数，效果如下：

mysql> select t1.airport,hex(t1.airport), t2.airport,hex(t2.airport) from t t1,t t2 where t1.airport != t2.airport and abs(t1.distant-t2.distant) < 100; +---------+-----------------+---------+-----------------+ | airport | hex(t1.airport) | airport | hex(t2.airport) | +---------+-----------------+---------+-----------------+ | b       | 62              | a       | 61              | | c       | 63              | a       | 61              | | a       | 61              | b       | 62              | | c       | 63              | b       | 62              | | a       | 61              | c       | 63              | | b       | 62              | c       | 63              | +---------+-----------------+---------+-----------------+ 6 rows in set (0.00 sec)

这样我们就可以通过比较机场1和机场2的大小，来去掉重复值了

mysql> select t1.airport, t2.airport from t t1,t t2 where t1.airport != t2.airport and hex(t1.airport) < hex(t2.airport) and abs(t1.distant-t2.distant) < 100; +---------+---------+ | airport | airport | +---------+---------+ | a       | b       | | a       | c       | | b       | c       | +---------+---------+ 3 rows in set (0.00 sec)

最后再优化一下，结果如下：

mysql> select t1.airport, t2.airport from t t1,t t2 where hex(t1.airport) < hex(t2.airport) and abs(t1.distant-t2.distant) < 100; +---------+---------+ | airport | airport | +---------+---------+ | a       | b       | | a       | c       | | b       | c       | +---------+---------+ 3 rows in set (0.00 sec)

SQL并不复杂，没有太多表的join和子查询，但是之前遇到去掉重复值用distinct或者group by就可以解决了，这次貌似不太适用，所以记录一下，欢迎拍砖。

参考链接

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_hex

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inFunctions

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享文章：HiveSQL去重a,b和b,a类型-创新互联
网站URL：http://6mz.cn/article/dcgdds.html