常用相关查询sql语句——时间、排序

相关查询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 函数

https://www.cnblogs.com/52XF/p/4209211.html

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