Hive SQL去重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
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Hadoop简介
Hadoop简介 Hadoop是使用Java编写,允许分布在集群,使用简单的编程模型的计算机大型数据集处理的Apache的开源框架。 Hadoop框架应用工程提供跨计算机集群的分布式存储和计算的环境。 Hadoop是专为从单一服务器到上千台机器扩展,每个机器都可以提供本地计算和存储。 hadoop2.9.0暂时是个不成熟的版本,建议使用先前的稳定版本hadoop2.8.0 hadoop安装部署的三种模式: (1)Local (Standalone) Mode 本地模式(又称单机模式):hadoop的默认配置模式 1)hadoop使用本地文件系统而非分布式文件系统(HDFS)。 2)不会启动任何hadoop守护进程,map任务和reduce任务作为同一个进程的不同部分来执行。 3)仅用于开发或调试MapReduce应用程序。 4)当首次解压hadoop的源码包时,默认选择了最低的配置。这时,三个xml文件均为空,当配置文件为空时,hadoop会完全运行在本地。 (2)Pseudo-Distributed Mode 伪分布式模式 1)所有的进程都运行在同一台机器上。 2)使用分布式文件系统...
- 下一篇
安装hadoop集群(Multi Cluster)
配置环境 本文档安装hadoop集群环境,一个master作为namenode节点,一个slave作为datanode节点: (1) master: os: CentOS release 6.5 (Final) ip: 172.16.101.58 user:root hadoop-2.9.0.tar.gz (2) slave: os: CentOS release 6.5 (Final) ip: 172.16.101.59 user:root hadoop-2.9.0.tar.gz 前提条件 (1) master和slave都安装好java环境,并配置好环境变量; (2)master节点解压好hadoop-2.9.0.tar.gz,并配置好环境变量; (3)本篇文档使用的是root用户安装,所以需要master上的root用户可以ssh无密码使用root用户登录slave节点; 配置集群文件 在 master节点上执行(本文档先在master节点上配置文件,然后通过scp拷贝到其他slave节点) (1)slaves文件:将作为 DataNode 的主机名或者ip写入该文件,每行一个,...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- MySQL8.0.19开启GTID主从同步CentOS8
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS8编译安装MySQL8.0.19
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- CentOS7,CentOS8安装Elasticsearch6.8.6
- Red5直播服务器,属于Java语言的直播服务器
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- CentOS6,CentOS7官方镜像安装Oracle11G
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装