개인적인 정리

오라클 LAST_VALUE() LAST_VALUE() - LAST_VALUE(expr) OVER (analytic_clause) LAST_VALUE(expr RESPECT|IGNORE NULL) OVER (analytic_clause) 의 형태 LAST_VALUE is an analytic function that is useful for data densification. It returns the last value in an ordered set of values. {RESPECT | IGNORE} NULLS determine whether null values of expr are included in or eliminated from the calculation. the default is RE..

오라클 LAST_DAY() LAST_DAY() - 달의 마지막 날짜를 구해준다. LAST_DAY(date) 의 형태 LAST_DAY returns the date of the last day of the month that contains date. The last day of the month is defined by the session parameter NLS_CALENDAR. The return type is always DATE, regardless of the data type of date. SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL ; --> 2020/04/30 오후 2:42:01 SQL> SELECT LAST_DAY('2020-04-01') FROM DUAL;..

오라클 LAST() LAST() 함수는 FIRST() 함수를 참조한다. https://web-obj.tistory.com/393 ORACLE FIRST() 오라클 FIRST() 이런 함수가 있는지 처음 알았다. FIRST AND LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as th.. web-obj.tistory.com

오라클 INSTR() INSTR() - 문자열과 비교하여 문자열의 위치를 return 해준다. - 비교할 문자열의 위치와, 발견횟수등을 정하여서도 사용이 가능하다. (position, occurence) - 검색관련해서도 사용가능 INSTR(string, substring) INSTR(string, substring, position, occurence) 의 형태 The INSTR functions search string for substring. The search operation is defined as comparing the substring argument with substrings of string of the same length for equality until a match is f..

오라클 INSERTCHILDXML) INSERTCHILDXML() - INSERTCHILDXML inserts a user-supplied value into the target XML at the note indicated by the XPath expression.

오라클 INITCAP() INITCAP() - INITCAP(char) 의 형태 INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. char can be of any of the data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same data type as char. The database set the case of the initial characters b..

오라클 GROUP_ID() GROUP_ID() - grouping 할 때... group_id 를 숫자로 부여. - 음... 처음보지만 필요한 함수다. GROUP_ID() 의 형태 GROUP_ID distinguishes duplicate groups resulting from a GROUP BY sepcification. It is useful in filtering out duplicate groupings from the query result. It returns an Oracle NUMBER to uniquely identify duplicate groups. This function is applicable only in a SELECT statement that contains a GROUP..

STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP(), DATE_FORMAMT() 와 같은 날짜 함수에서 연, 월, 일등의 지정자로서는 아래와 같은 것들이 있다. The specifiers shown in the following table may be used in the format string. The % character is required before format specifier characters. The specifiers apply to other functions as well: STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP() Specifier Description %a Abbreviated weekday name (S..