hive sql语句和mysql用法区别存档

写在前面

mysql和hive版本：
mysql版本：5.6.17
hive版本：2.1.1

一、GROUP_CONCAT功能

本功能测试基于以下数据表test_group:

1、简单功能实现对比

需求如下： 写出一个sql语句，按照category分组，并把组内的name使用“; ”分隔符连接。

（1）mysql中

SELECT 
	category, 
	GROUP_CONCAT(name SEPARATOR "; ")   # SEPARATOR后面是分隔符
FROM 
	test_group 
GROUP BY 
	category 

结果如下：

GROUP_CONCAT()函数的API如下：

功能： 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法： group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明： 通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

（2）hive中

hive不提供GROUP_CONCAT函数，所以需要使用其他方法代替，我们可以使用CONCAT_WS()函数代替，如下：

SELECT 
	category, 
	CONCAT_WS("\; ", COLLECT_LIST(name)) 
FROM 
	test_group 
GROUP BY 
	category     

输出结果如下：

注意： COLLECT_LIST表示组内不去重，COLLECT_SET表示组内去重，表示将name字段放到一个list/set中，在使用concat_ws进行连接。

2、组内排序下的GROUP_CONCAT对比

需求如下： 写出一个sql语句，按照category分组，并把组内的name和level使用“name-level”格式使用“; ”分隔符连接，并在组内使用level降序排列。

①mysql中

SELECT 
	category, 
	GROUP_CONCAT(CONCAT(name, '-', level) ORDER BY level DESC SEPARATOR "; ") as res    # ORDER BY level DESC表示组内排序
FROM 
	test_group 
GROUP BY 
	category 

输出结果如下：

（2）hive中

SELECT 
	temp.category, 
	CONCAT_WS("\; ", COLLECT_LIST(CONCAT(tmp.name, '-', tmp.level)))  as res  # CONCAT(tmp.name, '-', tmp.level)表示将name字段和level字段使用‘-’连接
FROM 
	(
		SELECT * FROM test_group  ORDER BY level DESC
	) tmp 
GROUP BY 
	tmp.category 

结果如下：

二、字符串的split功能

1、hive中

在hive中，直接提供split函数，使用如下：

hive> select split("1,2,3", ',');     # 返回结果：
hive> select split("1,2,3", ',')[0];   # 返回结果：1
hive> select split("1,2,3", ',')[1];   # 返回结果：2
hive> select split("1,2,3", ',')[2];   # 返回结果：3

2、mysql中

mysql没有提供split函数，但是可以使用substring函数达到类似的效果。举例如下：

mysql> select substring_index("1,2,3",",",1);    # 返回结果：1
mysql> select substring_index("1,2,3",",",2);    # 返回结果：1,2
mysql> select substring_index("1,2,3",",",3);    # 返回结果：1,2,3
mysql> select substring_index("1,2,3",",",-1);   # 返回结果：3
mysql> select substring_index("1,2,3",",",-2);   # 返回结果：2,3
mysql> select substring_index("1,2,3",",",-3);   # 返回结果：1,2,3

负号表示反向取数，和py中功能一样。

但是如果我们想把2提取出来，该怎么做？用2个substring()，举例如下：

mysql> SELECT substring_index(substring_index("1,2,3",',',2),',',-1)    # 返回结果：2
mysql> SELECT substring_index(substring_index("1,2,3",',',-2),',',1)    # 返回结果：2
mysql> SELECT substring_index(substring_index("1,2,3",',',3),',',-1)    # 返回结果：3
mysql> SELECT substring_index(substring_index("1,2,3",',',-2),',',-1)   # 返回结果：3

三、row_number、rank和dense_rank()