[데이터 정렬 - 문자변수] 문자형 데이터를 기준으로 오름차순 정렬 - 38 (오라클 SQL, R, Python, SAS)

 

38. Display the details from emp table in order of emp name.

 

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

---

• 파이썬 & 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)

 

Oracle Programming

select ename 
from   emp 
order 
   by  ename

 

---

2. 파이썬(Pandas)

• Sort_values 함수

Python Programming

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

 

Results

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

 

---

3. R Programming (R Package)

• Order 함수

R Programming

%%R

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

 

Results

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

 

---

4. R Dplyr Package

• Arrange 함수

R Programming

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

 

Results

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

 

---

5. R sqldf Package

• Order by 함수

R Programming

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

 

Results

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

 

---

6. Python pandasql Package

• Order by 함수

Python Programming

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

 

Results

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

 

---

7. R data.table Package

• Order 함수

R Programming

%%R

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

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

 

Results

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

 

---

• Order 함수

R Programming

%%R

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

 

Results

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

 

---

8. SAS Proc SQL

• Order by 함수

SAS Programming

%%SAS sas

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

 

Results

OBS	ename
1	ADAMS
2	ALLEN
3	BLAKE
4	CLARK
5	FORD

 

---

9. SAS Data Step

• Proc sort 프로시져

SAS Programming

%%SAS sas

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

 

Results

OBS	ename
1	ADAMS
2	ALLEN
3	BLAKE
4	CLARK
5	FORD

 

---

10. Python Dfply Package

• Arragne 함수

Python Programming

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

 

Results

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

 

 

---

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