昨天開(kāi)發(fā)找到我們DBA，要我們寫(xiě)一條Hive SQL。

在西秀等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶(hù)提供做網(wǎng)站、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作按需定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),成都全網(wǎng)營(yíng)銷(xiāo)推廣,外貿(mào)網(wǎng)站制作,西秀網(wǎng)站建設(shè)費(fèi)用合理。

需求：

有一個(gè)t表，主要有機(jī)場(chǎng)名稱(chēng)airport，機(jī)場(chǎng)的經(jīng)緯度distance這兩個(gè)列組成，想得到所有距離小于100的兩個(gè)機(jī)場(chǎng)名。

其實(shí)寫(xiě)這個(gè)SQL的邏輯并不是很困難，難點(diǎn)是如何去重復(fù)值，

我用MySQL模擬的一個(gè)表，其實(shí)Hive語(yǔ)法和SQL差不多，插入了三條數(shù)據(jù)，a, b, c 分別代表三個(gè)機(jī)場(chǎng)名稱(chēng)，結(jié)構(gòu)如下：

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)

通過(guò)!=篩選掉本機(jī)場(chǎng)自己之間的比較，用abs函數(shù)取絕對(duì)值得到位置小于100的兩個(gè)機(jī)場(chǎng)

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)

但是問(wèn)題來(lái)了，(b,a) 與(a,b)，(c,a)與(a,c)，(c,b)與(b,c)這里被我們視為重復(fù)值，我們只需要得到其中某一行的數(shù)據(jù)，就知道是哪兩個(gè)機(jī)場(chǎng)名了，那么，如何去掉這個(gè)重復(fù)值呢？

貌似distinct，group by都派不上用場(chǎng)了，最后咨詢(xún)了一位資深的SQL高手，找到了這么一個(gè)函數(shù)hex(),可以把一個(gè)字符轉(zhuǎn)化成十六進(jìn)制，Hive也有對(duì)應(yīng)的函數(shù)，效果如下：

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)

這樣我們就可以通過(guò)比較機(jī)場(chǎng)1和機(jī)場(chǎng)2的大小，來(lái)去掉重復(fù)值了

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)

最后再優(yōu)化一下，結(jié)果如下：

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并不復(fù)雜，沒(méi)有太多表的join和子查詢(xún)，但是之前遇到去掉重復(fù)值用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