相关查询sql语句——时间、排序
时间
时间戳与日期格式的相互转换
- UNIX时间戳转换成日期:FROM_UNIXTIME()
select FROM_UNIXTIME(1493138520)
输出为:2017-04-26 00:42:00
- 日期转换为UNIX时间戳:UNIX_TIMESTAMP()
select UNIX_TIMESTAMP('2017-04-26 00:42:00','yyyy-mm-dd hh:mm:ss')
输出为:1485362520
- 时间戳转日期,自定义返回日期格式:FROM_UNIXTIME(unix_timestamp,format)
select FROM_UNIXTIME(1545711900,'yyyy-mm-dd hh:mm:ss')
输出为:2018-12-25 12:25:00
增减天数
- 日期增加一个时间间隔:date_add()
select date_sub('1998-01-01 00:42:00',1)
输出为:1997-12-31
- 日期增减少一个时间间隔:date_sub()
使用方法与date_add一样。但是这种函数hive sql不支持时间类的增减。mysql可以使用为
select date_add(@dt, interval '1 01:15:30' day_second);
- 日期、时间类相减:datediff(date1,date2), timediff(time1,time2)
select datediff('2019-04-01','2019-03-28')
输出为:4
hive 不支持 timediff()
排序
ROW_NUMBER 函数
row_number会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。row_number用法实例:
select row_number() over(order by SubTime desc) as row_num,* from Order
图中的row_num列就是row_number函数生成的序号列,其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。如以下sql,over子句中根据SubTime降序排列,Sql语句中则按TotalPrice降序排列。
select row_number() over(order by SubTime desc) as row_num,* from Order order by TotalPrice desc
- row_number 函数分组:row_number() o)ver(partition by 列名 order by 列名 desc)
row_number() over(partition by dt,uuid order by page_ts desc) as rank
RANK 函数
rank函数用于返回结果集的分区内每行的排名, 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号。
select rank() over (order by UserId) as rank,* from Order
DENSE_RANK函数
dense_rank函数在生成序号时是连续的,出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。
select DENSE_RANK() OVER(order by UseId) as den_rank,* from Order