Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.