포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ LEAST Oracle Function ]
LEAST함수는 인수 EXPR의 리스트 중에서 가장 작은 값을 반환을 한다. 첫번째 이후의 모든 expr들은 비교전에 첫번째 expr의 데이터 형으로 암묵적으로 변환된다. 만약 이 함수에 의해 반환된 값이 문자 데이터이면, 데이터 형은 항상 VARCHAR2이다.
- 함수설명 : LEAST 오라클 함수 링크
1. Oracle(오라클)
| Oracle Programming |
SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST"
FROM DUAL;
| Results |
LEAST
------------
HAROLD
2. Python Pandas(파이썬)
Min() 함수
| Python Programming |
min('HARRY','HARRIOT','HAROLD')
| Results |
'HAROLD'
Min() 함수
emp 테이블에서 ename과 job 변수 중에서 작은 값(알파벳 내림차순 기준)을 반환한다.
| Python Programming |
import copy
withmooc = copy.copy(emp)
withmooc['least_value'] = withmooc.loc[:, ['ename', 'job']].min(axis=1)
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno least_value
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 CLERK
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 ALLEN
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 SALESMAN
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 JONES
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 MARTIN
Min() 함수
| Python Programming |
import copy
withmooc = copy.copy(emp)
withmooc['least_value'] = withmooc[['ename', 'job']].apply(lambda x: min(x[0],x[1]), axis=1)
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno least_value
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 CLERK
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 ALLEN
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 SALESMAN
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 JONES
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 MARTIN
3. R Programming (R Package)
Min() 함수
| R Programming |
%%R
min('HARRY','HARRIOT','HAROLD')
| Results |
[1] "HAROLD"
Apply() 함수 와 min
| R Programming |
%%R
withmooc <- emp
withmooc['least_value'] = apply(withmooc[, c('ename','job')],1,min)
withmooc[1:10,]
| Results |
# A tibble: 10 x 9
empno ename job mgr hiredate sal comm deptno least_value
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 ALLEN
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESMAN
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 JONES
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 MARTIN
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 BLAKE
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 CLARK
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 ANALYST
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 KING
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 SALESMAN
pmin 함수
| R Programming |
%%R
transform(emp, least_value = pmin(ename, job))
| Results |
empno ename job mgr hiredate sal comm deptno least_value
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 ALLEN
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESMAN
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 JONES
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 MARTIN
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 BLAKE
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 CLARK
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 ANALYST
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 KING
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 SALESMAN
11 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 ADAMS
12 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 CLERK
13 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 ANALYST
14 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10 CLERK
4. R Dplyr Package
Min() 함수
| R Programming |
%%R
emp %>%
dplyr::rowwise() %>%
dplyr::mutate(least_value = min(ename,job)) %>%
head(10)
| Results |
# A tibble: 10 x 9
# Rowwise:
empno ename job mgr hiredate sal comm deptno least_value
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 ALLEN
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESMAN
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 JONES
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 MARTIN
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 BLAKE
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 CLARK
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 ANALYST
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 KING
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 SALESMAN
5. R sqldf Package
Min() 함수
| R Programming |
%%R
sqldf(" SELECT min('HARRY','HARRIOT','HAROLD') least_value ")
| Results |
least_value
1 HAROLD
| R Programming |
%%R
sqldf(" SELECT empno,ename,job,deptno, min(ename,job) least_value
from emp")
| Results |
empno ename job deptno least_value
1 7369 SMITH CLERK 20 CLERK
2 7499 ALLEN SALESMAN 30 ALLEN
3 7521 WARD SALESMAN 30 SALESMAN
4 7566 JONES MANAGER 20 JONES
5 7654 MARTIN SALESMAN 30 MARTIN
6 7698 BLAKE MANAGER 30 BLAKE
7 7782 CLARK MANAGER 10 CLARK
8 7788 SCOTT ANALYST 20 ANALYST
9 7839 KING PRESIDENT 10 KING
10 7844 TURNER SALESMAN 30 SALESMAN
11 7876 ADAMS CLERK 20 ADAMS
12 7900 JAMES CLERK 30 CLERK
13 7902 FORD ANALYST 20 ANALYST
14 7934 MILLER CLERK 10 CLERK
6. Python pandasql Package
Min() 함수
| Python Programming |
ps.sqldf(" SELECT min('HARRY','HARRIOT','HAROLD') least_value ")
| Results |
least_value
0 HAROLD| Python Programming |
ps.sqldf(" SELECT empno,ename,job,deptno, min(ename,job) least_value \
from emp ").head(10)
| Results |
empno ename job deptno least_value
0 7369 SMITH CLERK 20 CLERK
1 7499 ALLEN SALESMAN 30 ALLEN
2 7521 WARD SALESMAN 30 SALESMAN
3 7566 JONES MANAGER 20 JONES
4 7654 MARTIN SALESMAN 30 MARTIN
5 7698 BLAKE MANAGER 30 BLAKE
6 7782 CLARK MANAGER 10 CLARK
7 7788 SCOTT ANALYST 20 ANALYST
8 7839 KING PRESIDENT 10 KING
9 7844 TURNER SALESMAN 30 SALESMAN
7. R data.table Package
Min() 함수
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[,least_value := min(ename,job),by=1:nrow(DT) ][1:10,]
| Results |
empno ename job mgr hiredate sal comm deptno least_value
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 ALLEN
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESMAN
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 JONES
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 MARTIN
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 BLAKE
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 CLARK
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 ANALYST
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 KING
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 SALESMAN
pmin
| R Programming |
%%R
DT <- data.table(emp)
DT[, least_value := do.call(pmin, c(.SD, list(na.rm=TRUE))), ,.SDcols = c('ename','job') ][1:10,]
| Results |
empno ename job mgr hiredate sal comm deptno least_value
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 ALLEN
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESMAN
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 JONES
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 MARTIN
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 BLAKE
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 CLARK
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 ANALYST
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 KING
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 SALESMAN
8. Python DuckDB의 SQL
| Python Programming |
%%sql
SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST"
| Python Programming |
duckdb.sql(" SELECT LEAST('HARRY','HARRIOT','HAROLD') AS LEAST_1 ").df()
| Results |
LEAST_1
0 HAROLD
| Python Programming |
%%sql
SELECT empno,ename,job,deptno,
LEAST(ename,job) AS least_value
from emp
LIMIT 6
| Python Programming |
duckdb.sql(" SELECT empno,ename,job,deptno, \
LEAST(ename,job) AS least_value \
from emp \
LIMIT 6 ").df()
| Results |
empno ename job deptno least_value
0 7369 SMITH CLERK 20 CLERK
1 7499 ALLEN SALESMAN 30 ALLEN
2 7521 WARD SALESMAN 30 SALESMAN
3 7566 JONES MANAGER 20 JONES
4 7654 MARTIN SALESMAN 30 MARTIN
5 7698 BLAKE MANAGER 30 BLAKE
--------------------------------------------
| [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크 |
| 오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크 |
| [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글