HiveSql面试题5-版本号命名规则比较问题（order by多健值比较排序问题)

目录

0 需求分析

1 需求实现

3 总 结

---

0 需求分析

• 版本号信息存储在数据表中，每行一个版本号。
• 版本号命名规则如下：
• 产品版本号由三个部分组成
• 如：v9.11.2
• 第一部分9为主版本号，为1-99之间的数字；
• 第二部分11为子版本号，为0-99之间的数字；
• 第三部分2为阶段版本号，为0-99之间的数字（可选）；
• 已知T1表有若干个版本号：

v9.9.2

v8.1

v9.92

v9.9.2

v31.0.1

v31.0.1

v8.2.1

v9.99.1

v9.1.99

请使用hive sql编程实现如下2个小需求：

1.需求A：找出T1表中最大的版本号。
2.需求B：计算出如下格式的所有版本号排序，要求对于相同的版本号，顺序号并列：

1 需求实现

（1）数据准备

v9.9.2
v8.1
v9.92
v9.9.2
v31.0.1
v31.0.1
v8.2.1
v9.99.1
v9.1.99

（2）创建hive表

drop table if exists version
CREATE TABLE dan_test.version ( 
        version_id string
        )
ROW format delimited FIELDS TERMINATED BY ",";

(3) 加载数据

load data local inpath "/home/centos/dan_test/version.txt" into table version;

（4）实现

对于问题1，和问题2实际上是一个问题。

对于问题2，要求对版本号进行排序，相同的版本号名次一致，且有跳跃，因而是一种跳跃排序，使用rank()

由于版本号是一种字符串，如果仅仅是对字符串进行排序，字符串将按照字典排序，那么此时就会出现V9大于V31的情况，显示不是我们想要的，因而需要先对字符串进行解析。字符串解析函数split().由于split()函数后返回依然是字符串，所以需要cast（）函数将其转换为int型，具体解析sql如下：

select  cast(substring(split(version_id,".")[0],2) as int) as main_version
        ,cast(split(version_id,".")[1] as int) as sub_version
        ,cast(split(version_id,".")[2] as int)as sec_version
        ,version_id
from version

--------------------------------------------
OK
NULL	NULL	NULL	v9.9.2
NULL	NULL	NULL	v8.1
NULL	NULL	NULL	v9.92
NULL	NULL	NULL	v9.9.2
NULL	NULL	NULL	v31.0.1
NULL	NULL	NULL	v31.0.1
NULL	NULL	NULL	v8.2.1
NULL	NULL	NULL	v9.99.1
NULL	NULL	NULL	v9.1.99

我们可以看到解析出数值全为NULL值，这是由于"."在解析时候为特殊字符，需要转义字符进行解析。如：

例:  "." 点

split('192.168.0.1','.')

得到的结果：

[]

正确的写法：

split('192.168.0.1','\\.')

得到的结果：

["192","168","0","1"]

需要注意的是：

当然当split包含在 "" 之中时 需要加4个\

如 hive -e "....  split('192.168.0.1','\\\\.') ... "  不然得到的值是null

或者SQL放在shell脚本中时用""包起来时候也需要\\\\.进行解析。

同样的 | 等特殊符号也需要做类似 处理。

因而上述SQL语句正确写法为：

  select cast(substring(split(version_id,"\\.")[0],2) as int) as main_version
        ,cast(split(version_id,"\\.")[1] as int) as sub_version
        ,cast(split(version_id,"\\.")[2] as int)as sec_version
        ,version_id
  from version


---------------------------------------
9	9	2	v9.9.2
8	1	NULL	v8.1
9	92	NULL	v9.92
9	9	2	v9.9.2
31	0	1	v31.0.1
31	0	1	v31.0.1
8	2	1	v8.2.1
9	99	1	v9.99.1
9	1	99	v9.1.99

 对上述表中的 main_version，sub_version，sec_version进行跳跃排序

select a.version_id as version_id
      ,rank() over(order by a.main_version desc
      ,a.sub_version desc, a.sec_version desc) as rn    
 from(
  select cast(substring(split(version_id,"\\.")[0],2) as int) as main_version
        ,cast(split(version_id,"\\.")[1] as int) as sub_version
        ,cast(split(version_id,"\\.")[2] as int)as sec_version
        ,version_id
  from version
 ) a


排序后结果如下：
v31.0.1	1
v31.0.1	1
v9.99.1	3
v9.92	4
v9.9.2	5
v9.9.2	5
v9.1.99	7
v8.2.1	8
v8.1	9

 至此上述第二个需求已经完成，对于第一个需求有很多种解法，这里我们借助第二问的需求，稍作变换即可求得。我们使用row_number()对版本号进行降序排序，此时就不会出现重复名次，并过滤出排名第一的记录就是我们要找的答案

select b.version_id
from(
 select a.version_id as version_id
      ,row_number() over(order by a.main_version desc
      ,a.sub_version desc, a.sec_version desc) as rn    
 from(
  select cast(substring(split(version_id,"\\.")[0],2) as int) as main_version
        ,cast(split(version_id,"\\.")[1] as int) as sub_version
        ,cast(split(version_id,"\\.")[2] as int)as sec_version
        ,version_id
  from version
 ) a

) b
where rn = 1

------------------
OK
v31.0.1

Tips: 解决此题需要对窗口函数及排名函数有所认识，另外需要对order by后跟多个健进行排序有所了解。

order by id desc,time desc

上述表示先按照id进行降序排序，如果id相同再按照时间进行降序排序

order by id ,time desc

上述表示先按照id进行升序排序，如果id相同再按照时间进行降序排序

如果有三个排序键的话依次类推

总结：

order by 多个字段时，用逗号分隔每一个字段，如果字段不指明排序方式，默认是增序。

排序的方法是按照order by后定义的字段顺序，先按照第一个字段进行比较、排序，如果遇到第一个字段相同，则再比较第二个字段，按照第二个字段来再次排序，依次类推，从而产生最终排序结果。多个字段排序时，最多不超过16个。

该题版本号的比较问题其本质就是对order by后跟多个字段进行比较排序的考察，具有典型的代表意义。也就说在遇到不同位置数字的比较（不同字段）排序时，需要用到order by后跟多字段进行排序。

另外本题作者曾试着用 first_value（）函数来简化SQL，但遇到如下报错：

select 
      first_value(a.version_id) over(order by a.main_version desc
      ,a.sub_version desc, a.sec_version desc)     
 from(
  select cast(substring(split(version_id,"\\.")[0],2) as int) as main_version
        ,cast(split(version_id,"\\.")[1] as int) as sub_version
        ,cast(split(version_id,"\\.")[2] as int)as sec_version
        ,version_id
  from version
 ) a

FAILED: SemanticException Range based Window Frame can have only 1 Sort Key

后来作者用max(),sum()等等其他分析函数尝试，发现都报相同的错误

也就是说在使用分析函数时，除了排名函数，其他的分析函数在使用order by时后面的健值只能是一个。

3 总 结

     本文分析了版本号比较问题，并对该题中相关问题进行了总结和归纳，本题需要注意的点：

•     （1）order by 后跟多个字段排序问题
•     （2）split()函数解析字符串问题及其特殊符号的解析问题
•     （3）排名函数的使用。

参考链接：

hive函数 -- split 字符串分割函数_oO寒枫Oo的博客-CSDN博客_hive中split

欢迎关注石榴姐公众号"我的SQL呀"，关注我不迷路