[데이터 정렬 - 내림차순 정렬] 수치형 데이터를 기준으로 내림차순 정렬 - 37 (오라클 SQL, R, Python, SAS)

 

37. Display the names of employees in descending order of salary.

 

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

---

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

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

 

---

1. 오라클(Oracle)

• 내림차순 정렬(Sorting)

Oracle Programming

select ename from emp 
order 
   by  sal desc;

 

---

2. 파이썬(Pandas)

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

Python Programming

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

 

Results

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

 

---

3. R Programming (R Package)

• Order 함수

R Programming

%%R

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

 

Results

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

 

---

4. R Dplyr Package

• Arrange 함수

R Programming

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

 

Results

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

 

---

5. R sqldf Package

• Order by 구문

R Programming

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

 

Results

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

 

---

6. Python pandasql Package

• Order by 구문

Python Programming

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

 

Results

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

 

---

7. R data.table Package

• Order 함수

R Programming

%%R

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

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

 

Results

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

 

---

8. SAS Proc SQL

• Order by 구문

SAS Programming

%%SAS sas

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

 

Results

OBS	ename
1	KING
2	FORD
3	SCOTT
4	JONES
5	BLAKE

 

---

9. SAS Data Step

• Proc sort 프로시져

SAS Programming

%%SAS sas

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

 

Results

OBS	ename
1	KING
2	SCOTT
3	FORD
4	JONES
5	BLAKE

 

---

10. Python Dfply Package

• Arrange 구문

Python Programming

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

 

Results

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

 

---

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