CEIL Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

---

[ CEIL 함수 ]

 

---

CEIL 함수는 인수에서 지정한 수치를 올림 하여 정수를 구하는 함수이다. 즉, 인수로 지정한 수치보다 크고 가까운 정수를 반환한다. 음의 수치를 부여하면 음수 측의 가장 가까운 정수를 반환한다.

 

• 함수 설명 : CEIL 오라클 함수 링크

 

---

1. Oracle(오라클)

다음 예제는 15.7 이상의 가장 작은 정수를 반환 :

Oracle Programming

SELECT CEIL(15.7) "Ceiling" 
FROM   DUAL;

 

Results

Ceiling
-----------------
16

 

---

2. Python Pandas(파이썬)

 

Python Programming

math.ceil(15.7)

 

Results

16

 

---

 

Python Programming

import math

withmooc.assign( sal_point =   lambda x : x.sal / 1000, 
                 sal_ceil  = ( lambda x : x.sal.apply(lambda x : math.ceil(x / 1000) ) )  ).head()

 

Results

	empno	ename	job		mgr	hiredate	sal	comm	deptno	sal_point	sal_ceil
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20	0.800		1
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1.600		2
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	1.250		2
3	7566	JONES	MANAGER		7839.0	1981/04/02	2975	NaN	20	2.975		3
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	1.250		2

 

 

 

---

3. R Programming (R Package)

 

R Programming

%%R

ceiling(15.7)

 

Results

[1] 16

 

---

 

R Programming

%%R

withmooc <- emp

withmooc <- transform(withmooc, sal_point = sal / 1000, 
                                sal_ceil  = ceiling(sal / 1000) )

withmooc[1:5, ]

 

Results

  empno  ename      job  mgr   hiredate  sal comm deptno sal_point sal_ceil
1  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20     0.800        1
2  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30     1.600        2
3  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30     1.250        2
4  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20     2.975        3
5  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30     1.250        2

 

 

 

---

4. R Dplyr Package

 

R Programming

%%R

emp %>%
  dplyr::mutate( sal_point = sal / 1000 ,
                 sal_ceil  = ceiling(sal_point))

 

Results

# A tibble: 14 x 10
   empno ename  job         mgr hiredate     sal  comm deptno sal_point sal_ceil
                             
 1  7369 SMITH  CLERK      7902 1980-12-17   800    NA     20      0.8         1
 2  7499 ALLEN  SALESMAN   7698 1981-02-20  1600   300     30      1.6         2
 3  7521 WARD   SALESMAN   7698 1981-02-22  1250   500     30      1.25        2
 4  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20      2.98        3
 5  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30      1.25        2
 6  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30      2.85        3
 7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10      2.45        3
 8  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20      3           3
 9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10      5           5
10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30      1.5         2
11  7876 ADAMS  CLERK      7788 1983-01-12  1100    NA     20      1.1         2
12  7900 JAMES  CLERK      7698 1981-12-03   950    NA     30      0.95        1
13  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20      3           3
14  7934 MILLER CLERK      7782 1982-01-23  1300    NA     10      1.3         2

 

 

 

---

5. R sqldf Package

 

R Programming

%%R

sqldf(" SELECT CEIL(15.7) as Ceiling ")

 

Results

  Ceiling
1      16

 

---

 

R Programming

%%R

sqldf(" SELECT empno, 
               sal,
               sal / 1000 as sal_point,
               CEIL(sal / 1000 ) as Ceiling 
        from emp")[1:5]

 

Results

   empno  sal sal_point Ceiling
1   7369  800     0.800       1
2   7499 1600     1.600       2
3   7521 1250     1.250       2
4   7566 2975     2.975       3
5   7654 1250     1.250       2
6   7698 2850     2.850       3
7   7782 2450     2.450       3
8   7788 3000     3.000       3
9   7839 5000     5.000       5
10  7844 1500     1.500       2
11  7876 1100     1.100       2
12  7900  950     0.950       1
13  7902 3000     3.000       3
14  7934 1300     1.300       2

 

 

 

---

6. Python pandasql Package

 

Python Programming

ps.sqldf(" select ceil(15.7) as Ceiling ")

 

Python Programming

import pandasql as ps
var = math.ceil(15.7)

query = "SELECT {} FROM emp ".format(var)
ps.sqldf(query, globals()).head()

 

---

Results

	16
----------
0	16
1	16
2	16
3	16
4	16

 

 

 

---

7. R data.table Package

 

R Programming

%%R

DT      <- data.table(emp)
dept_DT <- data.table(dept)

DT[, `:=`(sal_point = sal / 1000 ,
          sal_ceil  = ceiling(sal / 1000) )][1:5, ]

 

Results

   empno  ename      job  mgr   hiredate  sal comm deptno sal_point sal_ceil
1:  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20     0.800        1
2:  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30     1.600        2
3:  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30     1.250        2
4:  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20     2.975        3
5:  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30     1.250        2

 

---

8. Python DuckDB의 SQL

 

Python Programming

%%sql
  SELECT CEIL(15.7) "Ceiling"

 

Python Programming

print( duckdb.sql(" SELECT CEIL(15.7) Ceiling ").df() )

 

Results

   Ceiling
0     16.0

 

---

  --------------------------------------------  

 

[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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크