Common functions of calculating time difference in Oracle
Common functions of calculating time difference in Oracle
Two Date type fields: START_DATE,END_DATE, to calculate the time difference between the two dates (in days, hours, minutes, seconds, milliseconds, respectively):
Days:
ROUND (TO_NUMBER (END_DATE-START_DATE))
Hour
ROUND (TO_NUMBER (END_DATE-START_DATE) * 24)
Points
ROUND (TO_NUMBER (END_DATE-START_DATE) * 24 * 60)
Second
ROUND (TO_NUMBER (END_DATE-START_DATE) * 24 * 60 * 60)
Millisecond
ROUND (TO_NUMBER (END_DATE-START_DATE) * 24 * 60 * 60 * 1000)
Ten minutes ago.
Select sysdate-10 / (60,24) from dual
Select
Status
Continued_time, / * time unit is seconds * /
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, / * time is seconds * /
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)-collect data within 10 minutes of the current time
)
Temp
Where temp.row_flg=1
Oracle removes duplicates (values of a column) and takes a piece of data that is up-to-date (the latest in the date field)
Solution: use the row_number () over function of Oracle to solve the problem.
Resolution process:
1. View duplicate records in the table
Select
T.id
T.device_id
T.update_dtm
T.test_result
From DEVICE_INFO_TBL t
two。 Mark duplicate records
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. Filter duplicate data to get the latest records
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) means grouping according to COL1, sorting according to COL2 within the group, and the value calculated by this function represents the sorted sequence number within each group (continuous and unique within the group).
The difference between rownum and rownum is that when sorting with rownum, the pseudo column rownum is added to the result set and then sorted, while this function sorts first and then calculates the row number after including the sort clause.
Row_number () is similar to rownum, but a little more powerful (it can be sorted from 1 within each group).
Rank () is a jump sort, with two second places followed by a fourth place (also within each group).
Dense_rank () l is a sequential sort, with two second places still following the third place. By contrast, row_number has no duplicate value.
Lag (arg1,arg2,arg3):
Arg1 is an expression returned from other lines
Arg2 is the offset of the current row partition you want to retrieve. Is a positive offset that retrieves the number of previous rows back.
Arg3 is the value returned when the number represented by arg2 is out of the range of the grouping.
