[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리와 Cross join

포스팅 목차

115. Display all employees names with total Sal of company with employee name.

* 전체 직원명 명단과 함께 전체 직원의 급여 합계를 함께 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
스칼라 서브쿼리(Scala Subquery) , Cross(full) Join
94번 서브쿼리
[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리(Scala Subquery)과 Cross join
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 스칼라 서브쿼리(Scala Subquery)과 Cross join, sum()
파이썬 Pandas : pd.merge()의 Cross Join(카테시안곱)
R 프로그래밍 : merge()의 Cross Join(카테시안곱)
R Dplyr Package : dplyr::mutate(), dplyr::full_join()의 Cross Join(카테시안곱)
R sqldf Package : 스칼라 서브쿼리(Scala Subquery)과 Cross join
Python pandasql Package : 스칼라 서브쿼리(Scala Subquery)과 Cross join
R data.table Package : sum()
SAS Proc SQL : 스칼라 서브쿼리(Scala Subquery)과 Cross join, sum()
SAS Data Step : CROSS JOIN (카테시안 곱) 구현, Macro 변수, Proc sql의 into, CALL SYMPUT()
Python Dfply Package : full_join()의 Cross Join(카테시안곱)
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

사원명과 더불어 서브쿼리를 사용하여 전체 직원의 급여 합계를 추가하여 출력한다.

Oracle Programming

SELECT ENAME,(SELECT SUM(SAL) FROM EMP) sal_total
FROM   EMP

전체 직원의 급여 합계(‘sal_tot’)를 산출 한 후 emp 테이블과 Cross Join(카테시안곱)을 수행하여 emp테이블의 전체 리스트에 급여 합계(‘sal_tot’)를 추가하여 출력한다.

Oracle Programming

SELECT ENAME,b.sal_tot 
FROM   emp a 
       cross join (SELECT SUM(SAL) sal_tot FROM emp) b

2. Python Pandas(파이썬)

전체 직원의 급여 합계(‘sal_total’)를 산출 한 후 emp 테이블과 Cross Join(카테시안곱)을 수행하여 emp테이블의 전체 리스트에 급여 합계(‘sal_total’)를 추가하여 출력한다.

Python Programming

pd.merge(emp.assign(foo=1), emp[['sal']].sum().to_frame(name='sal_total').assign(foo=1), on ='foo').head(5)

Results

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

3. R Programming (R Package)

전체 직원의 급여 합계를 산출 한 후 emp 테이블과 Cross Join(카테시안곱)을 수행하여 emp테이블의 전체 리스트에 급여 합계(‘y’)를 추가하여 출력한다.

R Programming

%%R

merge(emp, (sum(emp$sal)),by= character(),all.x=TRUE, all.y=TRUE)[1:5, ]

Results

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

4. R Dplyr Package

전체 직원의 급여 합계를 산출한 후 dplyr::muate() 함수를 사용하여서 emp테이블의 전체 리스트에 급여 합계(‘sal_tot’)를 추가하여 출력한다.

count()에 대한 작업은 157번 참조(add_tally(), add_count())

R Programming

%%R

emp %>% 
  dplyr::mutate(sal_tot = sum(sal)) %>%
  head(5)

Results

# A tibble: 5 x 9
  empno ename  job        mgr hiredate     sal  comm deptno sal_tot
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>   <dbl>
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20   29025
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30   29025
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30   29025
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20   29025
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30   29025

전체 직원의 급여 합계(‘sal_tot’)를 산출 한 후 Full_join 함수를 사용하여 emp 테이블과 Cross Join(카테시안곱)을 수행하여 emp테이블의 전체 리스트에 급여 합계(‘sal_tot’)를 추가하여 출력한다.

Dplyr에서는 full_join에서 cross Join 기능(character()) 구현

R Programming

%%R

emp %>%
  dplyr::full_join( emp %>% dplyr::summarise(sal_tot = sum(sal)) , by = character())  %>%
  head(5)

Results

# A tibble: 5 x 9
  empno ename  job        mgr hiredate     sal  comm deptno sal_tot
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>   <dbl>
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20   29025
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30   29025
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30   29025
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20   29025
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30   29025

5. R sqldf Package

사원명과 더불어 서브쿼리를 사용하여 전체 직원의 급여 합계를 추가하여 출력한다.

R Programming

%%R

sqldf(" SELECT ENAME,(SELECT SUM(SAL) FROM emp) sal_tot 
        FROM emp") %>% head(5)

Results

   ename sal_tot
1  SMITH   29025
2  ALLEN   29025
3   WARD   29025
4  JONES   29025
5 MARTIN   29025

사원명과 전체 직원의 급여 합계를 계산 후 인라인뷰(Inline view)를 지정 후 Cross join 을 수행하여 급여 합계(‘sas_tot’)를 추가하여 출력한다.

R Programming

%%R

sqldf("SELECT ENAME,b.sal_tot 
       FROM   emp a 
              cross join (SELECT SUM(SAL) sal_tot FROM emp) b") %>% head(5)

Results

   ename sal_tot
1  SMITH   29025
2  ALLEN   29025
3   WARD   29025
4  JONES   29025
5 MARTIN   29025

6. Python pandasql Package

사원명과 더불어 서브쿼리를 사용하여 전체 직원의 급여 합계를 추가하여 출력한다.

Python Programming

ps.sqldf("SELECT ENAME,(SELECT SUM(SAL) FROM emp) sal_total FROM emp").head(5)

Results

	ename	sal_total
0	SMITH	29025
1	ALLEN	29025
2	WARD	29025
3	JONES	29025
4	MARTIN	29025

Table Join 방식으로 처리(Cross join은 on 조건을 지정 안 함)

ps.sqldf("SELECT ENAME,b.sal_tot \
           FROM emp a cross join (SELECT SUM(SAL) sal_tot FROM emp) b").head(5)

Results

	ename	sal_tot
0	SMITH	29025
1	ALLEN	29025
2	WARD	29025
3	JONES	29025
4	MARTIN	29025

7. R data.table Package

data.table 에서 그룹 함수의 계산 결과를 바로 칼럼으로 추가 할 수 있다.

R Programming

%%R

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

DT[, sal_tot :=sum(sal),][1:5, ]

Results

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

8. SAS Proc SQL

사원명과 더불어 서브쿼리를 사용하여 전체 직원의 급여 합계를 추가하여 출력한다.

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    SELECT ENAME,
           (SELECT SUM(SAL) FROM EMP) AS SAL_TOTAL 
    FROM EMP;
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;

Results

OBS	ename	SAL_TOTAL
1	SMITH	29025
2	ALLEN	29025
3	WARD	29025
4	JONES	29025
5	MARTIN	29025

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    SELECT ENAME,b.sal_tot 
    FROM   emp a 
           cross join (SELECT SUM(SAL) AS sal_tot FROM emp) B;
QUIT;
PROC PRINT data=STATSAS_2(obs=5);RUN;

Results

OBS	ename	sal_tot
1	SMITH	29025
2	ALLEN	29025
3	WARD	29025
4	JONES	29025
5	MARTIN	29025

9. SAS Data Step

DATA STEP(CROSS JOIN-카테시안 곱);

SAS Programming

%%SAS sas

PROC SUMMARY DATA=emp NWAY;
     VAR   SAL;
     OUTPUT OUT=TOT_SAL(DROP=_:) SUM=TOT_SAL;
QUIT;

DATA STATSAS_4; 
 SET TOT_SAL;
     DO I=1 TO KOBS; 
        SET EMP NOBS=KOBS POINT=I;
        OUTPUT;
     END;    
RUN;
PROC PRINT data=STATSAS_4(obs=5);RUN;

Results

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

Macro 변수 할당 방식

SAS Programming

%%SAS sas

PROC SQL noprint;
  select SUM(SAL) into :TOT_SAL 
  from   emp A;
QUIT;
%put TNote:  TOT_SAL  = &TOT_SAL;

DATA STATSAS_4;
 SET emp;
     TOT_SAL = &TOT_SAL;
RUN;
PROC PRINT data=STATSAS_4(obs=5);RUN;

Results

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

SAS Programming

%%SAS sas

DATA _NULL_;
 SET EMP NOBS=TOT_CNT;
     SAL_TOT + SAL;
     IF _N_ = TOT_CNT THEN CALL SYMPUT('SAL_TOT',SAL_TOT);
RUN;
%put TNote:  SAL_TOT  = &SAL_TOT;

DATA STATSAS_5;
 SET emp;
     SAL_TOT = &SAL_TOT;
RUN;
PROC PRINT data=STATSAS_5(obs=5);RUN;

Results

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

10. Python Dfply Package

Python Programming

emp.assign(foo=1) >> \
  full_join( (emp >> summarize(sal_tot = X.sal.sum())).assign(foo=1) , by="foo" ) >> \
  head()

Results

empno	ename	job	mgr	hiredate	sal	comm	deptno	foo	sal_tot
7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	1	29025
7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1	29025
7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	1	29025
7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	1	29025
7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	1	29025

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

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[데이터 전처리] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리 - 117 (오라클 SQL, R, Python, SAS) (0)	2022.12.10
[데이터 추출] 하위 Top 5 추출 - 윈도우 함수 - 116 (오라클 SQL, R, Python, SAS) (0)	2022.12.10
[데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product) - 114 (0)	2022.12.07
[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113 (0)	2022.12.07
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리, 평균 - 112 (0)	2022.12.07

[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리와 Cross join - 115 (오라클 SQL, R, Python, SAS)

115. Display all employees names with total Sal of company with employee name.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리와 Cross join - 115 (오라클 SQL, R, Python, SAS)

115. Display all employees names with total Sal of company with employee name.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바