Oracle计算时间差常用函数

Oracle计算时间差常用函数

两个Date类型字段：START_DATE，END_DATE，计算这两个日期的时间差(分别以天，小时，分钟，秒，毫秒)：

天：

ROUND(TO_NUMBER(END_DATE - START_DATE))

时

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)

分

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)

秒

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)

毫秒

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

十分钟前

select sysdate -10/(60*24) from dual

select

status,

continued_time, /*时间单位为秒*/

sid,

sql_id,

username,

FIRST_REFRESH_TIME,

LAST_REFRESH_TIME,

SQL_TEXT,

ERROR_NUMBER,

ERROR_MESSAGE

from

(

select

status,

ROUND(TO_NUMBER(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) * 24 * 60 * 60 ) as continued_time , /*时间为秒*/

sid,

sql_id,

username,

FIRST_REFRESH_TIME,

LAST_REFRESH_TIME,

SQL_TEXT,

ERROR_NUMBER,

ERROR_MESSAGE,

row_number() OVER(PARTITION BY sql_id ORDER BY t.LAST_REFRESH_TIME desc) as row_flg

from

gv$sql_monitor t

----where t.LAST_REFRESH_TIME > sysdate -10/(60*24) ------采集当前时间10分钟内的

)

temp

where temp.row_flg=1

Oracle去除重复(某一列的值重复),取最新(日期字段最新)的一条数据

解决思路：用Oracle的row_number() over函数来解决该问题。

解决过程：

1.查看表中的重复记录

select

t.id,

t.device_id,

t.update_dtm,

t.test_result

from DEVICE_INFO_TBL t

2.标记重复的记录

select

t.id,

t.device_id,

t.update_dtm,

t.test_result,

row_number() OVER(PARTITION BY device_id ORDER BY t.update_dtm desc) as row_flg

from DEVICE_INFO_TBL t

3.过滤重复数据，取得最新的记录

select

temp.id,

temp.device_id,

temp.update_dtm,

temp.test_result

from (

select

t.id,

t.device_id,

t.update_dtm,

t.test_result,

row_number() OVER(PARTITION BY device_id ORDER BY t.update_dtm desc) as row_flg

from DEVICE_INFO_TBL t ) temp

where temp.row_flg = '1'

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).

与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码.

row_number()和rownum差不多，功能更强一点(可以在各个分组内从1开时排序).

rank()是跳跃排序，有两个第二名时接下来就是第四名(同样是在各个分组内).

dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .

lag(arg1,arg2,arg3):

arg1是从其他行返回的表达式

arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。

arg3是在arg2表示的数目超出了分组的范围时返回的值。