[데이터 정렬 - 다중 변수 기준 정렬] 여러 개의 정렬 키를 기준으로 데이터 정렬 - 39 (오라클 SQL, R, Python, SAS)

39. Display empno, ename, deptno, and sal. Sort the output first based on name and within name by deptno and within deptno by Sal.

 

* 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 * 
from   emp 
order 
   by  ename,deptno,sal;

 

---

2. 파이썬(Pandas)

• Sort_valutes()

Python Programming

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

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	Annual
10	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	13200.0
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	19500.0
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	34200.0
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10	29400.0
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	36000.0
11	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30	11400.0
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	35700.0
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	60000.0
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	16400.0
13	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10	15600.0
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	36000.0
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	9600.0
9	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30	18000.0
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	15500.0

 

---

3. R Programming (R Package)

• Order 함수

R Programming

%%R

emp[order(emp$ename,emp$deptno,emp$sal), ]

 

Results

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

 

---

4. R Dplyr Package

• Dplyr::arrange()

R Programming

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

 

Results

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

 

---

5. R sqldf Package

• Order by

R Programming

%%R

sqldf("select * from emp order by ename,deptno,sal")

 

Results

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

 

---

6. Python pandasql Package

• Order by

Python Programming

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

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	Annual
0	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	13200.0
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	19500.0
2	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	34200.0
3	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10	29400.0
4	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	36000.0
5	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30	11400.0
6	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	35700.0
7	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	60000.0
8	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	16400.0
9	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10	15600.0
10	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	36000.0
11	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	9600.0
12	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30	18000.0
13	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	15500.0

 

---

7. R data.table Package

• Order 함수

R Programming

%%R

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

DT[order(ename, deptno, sal)]

 

Results

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

 

---

8. SAS Proc SQL

• Order by 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    SELECT * 
    FROM   EMP 
    ORDER 
       BY  ENAME,DEPTNO,SAL;
QUIT;
PROC PRINT data=STATSAS_1(obs=3);RUN;

 

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
2	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
3	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30

 

---

9. SAS Data Step

• Proc sort 프로시져

SAS Programming

%%SAS sas

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

 

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
2	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
3	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30

 

---

10. Python Dfply Package

• Arrange()

Python Programming

emp >> arrange(X.ename, X.deptno, X.sal, ascending=[True,True,True])

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
10	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20
11	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
13	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
9	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30

 

---

 

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