[데이터 정렬 - 오름차순 정렬] 수치형 데이터 정렬 - 36 (오라클 SQL, R, Python, SAS)

36. Display the names of employees in order of salary i.e. the name of the employee earning lowest salary should appear first.

 

* emp테이블에서 근무하는 직원의 급여를 기준으로 오름차순(작은 급여 우선)으로 정렬하고 해당 직원의 이름을 출력하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
• [데이터 정렬] 수치형 데이터를 기준으로 오름차순 정렬

Oracle : order by 파이썬 Pandas : sort_values() R 프로그래밍 : order() R Dplyr Package : dplyr::arrange() R sqldf Package : order by Python pandasql Package : order by R data.table Package : order() SAS Proc SQL : order by SAS Data Step : proc sort Python Dfply Package : arrange() 파이썬 Base 프로그래밍 :

 

---

1. 오라클(Oracle)

• Order by 절

Oracle Programming

select ename 
from   emp 
order 
   by  sal;

 

---

2. 파이썬(Pandas)

• sort_values()를 사용하여서 데이터를 정렬한다.

Python Programming

emp.sort_values(by=["sal"], ascending=[True])[['ename']]

 

Results

	ename
0	SMITH
11	JAMES
10	ADAMS
2	WARD
4	MARTIN
13	MILLER
9	TURNER
1	ALLEN
6	CLARK
5	BLAKE
3	JONES
7	SCOTT
12	FORD
8	KING

---

3. R Programming (R Package)

• order()를 사용하여서 데이터를 정렬한다.

R Programming

%%R

emp[order(emp$sal, decreasing=F), c("ename")]

 

Results

# A tibble: 14 x 1
   ename 
   <chr> 
 1 SMITH 
 2 JAMES 
 3 ADAMS 
 4 WARD  
 5 MARTIN
 6 MILLER
 7 TURNER
 8 ALLEN 
 9 CLARK 
10 BLAKE 
11 JONES 
12 SCOTT 
13 FORD  
14 KING  

 

---

4. R Dplyr Package

• arrange()를 사용하여서 데이터를 정렬한다.

R Programming

%%R
emp %>% 
  dplyr::arrange(sal) %>% 
  dplyr::select(ename,sal)

 

Results

# A tibble: 14 x 2
   ename    sal
   <chr>  <dbl>
 1 SMITH    800
 2 JAMES    950
 3 ADAMS   1100
 4 WARD    1250
 5 MARTIN  1250
 6 MILLER  1300
 7 TURNER  1500
 8 ALLEN   1600
 9 CLARK   2450
10 BLAKE   2850
11 JONES   2975
12 SCOTT   3000
13 FORD    3000
14 KING    5000

 

---

5. R sqldf Package

• Order by 절

R Programming

%%R
sqldf("select ename,sal from emp order by sal")

 

Results

    ename  sal
1   SMITH  800
2   JAMES  950
3   ADAMS 1100
4    WARD 1250
5  MARTIN 1250
6  MILLER 1300
7  TURNER 1500
8   ALLEN 1600
9   CLARK 2450
10  BLAKE 2850
11  JONES 2975
12  SCOTT 3000
13   FORD 3000
14   KING 5000

 

---

6. Python pandasql Package

• Order by 절

Python Programming

ps.sqldf("select ename,sal from emp order by sal")

 

Results

	ename	sal
0	SMITH	800
1	JAMES	950
2	ADAMS	1100
3	WARD	1250
4	MARTIN	1250
5	MILLER	1300
6	TURNER	1500
7	ALLEN	1600
8	CLARK	2450
9	BLAKE	2850
10	JONES	2975
11	SCOTT	3000
12	FORD	3000
13	KING	5000

 

---

7. R data.table Package

• Order() 함수

R Programming

%%R

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

DT[order(sal)][, .(ename, sal)]

 

Results

     ename  sal
 1:  SMITH  800
 2:  JAMES  950
 3:  ADAMS 1100
 4:   WARD 1250
 5: MARTIN 1250
 6: MILLER 1300
 7: TURNER 1500
 8:  ALLEN 1600
 9:  CLARK 2450
10:  BLAKE 2850
11:  JONES 2975
12:  SCOTT 3000
13:   FORD 3000
14:   KING 5000

 

---

8. SAS Proc SQL

• Order by 구문

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    SELECT ename 
    from   emp 
    order by sal;
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;

 

Results

OBS	ename
1	SMITH
2	JAMES
3	ADAMS
4	MARTIN
5	WARD

 

---

9. SAS Data Step

• Proc sort 프로시져

SAS Programming

%%SAS sas

PROC SORT DATA = EMP OUT=STATSAS_2(KEEP=ENAME);
     BY SAL;
RUN;
PROC PRINT data=STATSAS_2(obs=5);RUN;

 

Results

OBS	ename
1	SMITH
2	JAMES
3	ADAMS
4	WARD
5	MARTIN

 

---

10. Python Dfply Package

• Arrange() 함수

Python Programming

emp >> arrange(X.sal, ascending=True)  >> select( X.ename )

 

Results

	ename
0	SMITH
11	JAMES
10	ADAMS
2	WARD
4	MARTIN
13	MILLER
9	TURNER
1	ALLEN
6	CLARK
5	BLAKE
3	JONES
7	SCOTT
12	FORD
8	KING

 

 

---

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE]   SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트