Hàm NTH_VALUE – Oracle/PLSQL
Hàm NTH_VALUE – Oracle/PLSQL là một trong các hàm trong danh sách được cung cấp sẵn cho người sử dụng khi thao tác với hệ quản trị cơ sở dữ liệu Oracle.
Hàm NTH_VALUE – Oracle/PLSQL – Chức năng
The Oracle/PLSQL NTH_VALUE function returns the nth value in an ordered set of values from an analytic window. It is similar to the FIRST_VALUE and LAST_VALUE functions except the NTH_VALUE lets you find a specific position in the analytic window such as 2nd, 3rd, or 4th value.
Hàm NTH_VALUE – Oracle/PLSQL – Cú pháp
Cú pháp Hàm NTH_VALUE trong Oracle/PLSQL như sau :
NTH_VALUE (measure_column, n) [FROM FIRST | FROM LAST] [RESPECT NULLS | IGNORE NULLS] OVER ([query_partition_clause] [order_by_clause [windowing_clause]])
Tham số
- measure_column
- The column or expression that you wish to return.
- n
- It is the nth value of measure_column in the analytic window that you wish to return.
- FROM FIRST | FROM LAST
- Optional. It determines whether the calculation starts at the first row of the analytic window or the last row of the analytic window. If this parameter is omitted, the default is FROM FIRST which starts at the first row.
- RESPECT NULLS | IGNORE NULLS
- Optional. It determines whether NULL values are included or ignored in the analytic window calculation. If this parameter is omitted, the default is RESPECT NULLS which includes NULL values.
- query_partition_clause
- Optional. It is used to partition the results into groups based on one or more expressions.
- order_by_clause
- Optional. It is used to order the data within each partition.
- windowing_clause
-
Optional. It determines the rows in the analytic window to evaluate and it is important that you use the correct windowing_clause or you could get unexpected results. It can be a value such as:
windowing_clause Description RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Last row in the window changes as the current row changes (default) RANGE BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING First row in the window changes as the current row changes RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING All rows are included in the window, regardless of the current row
Trong quá trình sử dụng hàm NTH_VALUE trong Oracle/PLSQL hãy nhớ :
Hàm NTH_VALUE – Oracle/PLSQL – Chú ý
- If there are less than n rows in the data source window, the NTH_VALUE function will return NULL.
- If the parameter n is NULL, the NTH_VALUE function returns an error.
Hàm NTH_VALUE – Oracle/PLSQL – Phạm vi
Hàm NTH_VALUE có thể sẵn sàng sử dụng trong Oracle/PLSQL với các phiên bản Oracle như sau:
Oracle 12c, Oracle 11g Release 2
Hàm NTH_VALUE – Oracle/PLSQL – Ví dụ
Dưới đây là các ví dụ minh họa cách sử dụng hàm NTH_VALUE trong Oracle/PLSQL như sau:
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Tham khảo các hàm built-in trong Oracle/PLSQL khác
| STT | Tên hàm | Phạm vi | Chi tiết |
|---|---|---|---|
| 1 | ABS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 2 | ACOS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 3 | ADD_MONTHS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 4 | ASCII | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 5 | ASCIISTR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 6 | ASIN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 7 | ATAN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 8 | ATAN2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 9 | AVG | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 10 | BFILENAME | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 11 | BIN_TO_NUM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 12 | BITAND | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 13 | CARDINALITY | Oracle 12c, Oracle 11g, Oracle 10g | xem |
| 14 | CASE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 15 | CEIL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 16 | CHR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 17 | COALESCE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 18 | CONCAT | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 19 | Concat with || | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 20 | CORR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 21 | COS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 22 | COSH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 23 | COVAR_POP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 24 | COVAR_SAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 25 | CUME_DIST | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 26 | CURRENT_DATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 27 | CURRENT_TIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 28 | DBTIMEZONE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 29 | DECODE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 30 | DECOMPOSE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 31 | DENSE_RANK | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 32 | DUMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 33 | EMPTY_BLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 34 | EMPTY_CLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 35 | EXP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 36 | EXTRACT | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 37 | FIRST_VALUE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 38 | FLOOR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 39 | FROM_TZ | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 40 | GREATEST | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 41 | GROUP_ID | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 42 | HEXTORAW | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 43 | INITCAP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 44 | INSTR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 45 | INSTR2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 46 | INSTR4 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 47 | INSTRB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 48 | INSTRC | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 49 | LAG | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 50 | LAST_DAY | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 51 | LAST_VALUE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 52 | LEAD | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 53 | LEAST | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 54 | LENGTH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 55 | LENGTH2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 56 | LENGTH4 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 57 | LENGTHB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 58 | LENGTHC | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 59 | LISTAGG | Oracle 12c, Oracle 11g Release 2 | xem |
| 60 | LN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 61 | LOCALTIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 62 | LOG | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 63 | LOWER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 64 | LPAD | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 65 | LTRIM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 66 | MAX | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 67 | MEDIAN | Oracle 12c, Oracle 11g, Oracle 10g | xem |
| 68 | MIN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 69 | MONTHS_BETWEEN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 70 | NANVL | Oracle 12c, Oracle 11g, Oracle 10g | xem |
| 71 | NEW_TIME | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 72 | NEXT_DAY | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 73 | NTH_VALUE | Oracle 12c, Oracle 11g Release 2 | xem |
| 74 | NULLIF | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 75 | NUMTODSINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 76 | NUMTOYMINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 77 | NVL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 78 | NVL2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 79 | POWER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 80 | RANK | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 81 | REGEXP_COUNT | Oracle 12c, Oracle 11g | xem |
| 82 | REGEXP_INSTR | Oracle 12c, Oracle 11g, Oracle 10g | xem |
| 83 | REGEXP_REPLACE | Oracle 12c, Oracle 11g, Oracle 10g | xem |
| 84 | REGEXP_SUBSTR | Oracle 12c, Oracle 11g, Oracle 10g | xem |
| 85 | REPLACE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 86 | ROUND (numbers) | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 87 | ROWNUM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 88 | RPAD | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 89 | RTRIM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 90 | SESSIONTIMEZONE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 91 | SIGN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 92 | SIN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 93 | SINH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 94 | SQRT | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 95 | STDDEV | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 96 | SUBSTR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 97 | SUM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 98 | SYSDATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 99 | SYSTIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 100 | TAN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 101 | TANH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 102 | TO_CHAR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 103 | TO_CLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 104 | TO_DATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 105 | TO_DSINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 106 | TO_LOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 107 | TO_MULTI_BYTE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 108 | TO_NCLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 109 | TO_NUMBER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 110 | TO_SINGLE_BYTE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 111 | TO_TIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 112 | TO_TIMESTAMP_TZ | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 113 | TO_YMINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
| 114 | TRANSLATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 115 | TRIM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 116 | TRUNC (dates) | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 117 | TRUNC (numbers) | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 118 | UID | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 119 | UPPER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 120 | USER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 121 | USERENV | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 122 | VAR_POP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 123 | VAR_SAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 124 | VARIANCE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
| 125 | VSIZE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
Blog được lập ra với mục đích để chia sẻ đề thi tới toàn bộ học sinh, sinh viên ở Việt Nam. Trong quá trình đăng bài không thể tránh được những sai sót, Nếu các bạn phát hiện ra sai sót có thể comment ở bên dưới hoặc có đề hay muốn chia sẻ tới tất cả mọi người có thể gửi thông tin vào email [email protected]
Mít&Béo
