Python 오라클 그룹함수와 비교하기

 번외 chapter. 오라클 vs 파이썬 그룹함수

Tip. 변수초기화 ( 코드가 제대로 작동이 안될 때 사용할 것)

# 변수 초기화 all = [var for var in globals() if var[0] != "_"] for var in all: del globals()[var]

문제 108. 사원테이블에서 최대월급을 출력하시오. (일반, Pandas)

SQL:

select max(sal) from emp;

Python :

#일반 import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [ ] for emp_list in emp_csv: sal_list.append( int(emp_list[5]) ) print(max(sal_list)) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result=max(emp['sal']) print(result)

문제 109. 사원 테이블에서 총월급을 출력하시오.

SQL :

select sum(sal) from emp;

Python :

#일반 import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [ ] for emp_list in emp_csv: sal_list.append( int(emp_list[5]) ) print(sum(sal_list)) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result=sum(emp['sal']) print(result)

문제 110. 평균월급을 출력하시오.

SQL :

select avg(sal) from emp;

Python :

#일반 import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [ ] for emp_list in emp_csv: sal_list.append( int(emp_list[5]) ) def avg(list): return sum(list)/len(list) print(avg(sal_list)) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result=sum(emp['sal'])/len(emp['sal']) print(result) #PANDAS 2 import pandas as pd emp = pd.read_csv("d:\\emp.csv") result = emp['sal'].mean() print(result)

문제 111. 직업이 SALESMAN인 사원들의 최대월급을 출력하시오.

SQL :

select max(sal) from emp where job = 'SALESMAN';

Python :

#일반 import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [ ] for emp_list in emp_csv: if emp_list[2] == 'SALESMAN': sal_list.append( int(emp_list[5]) ) print(max(sal_list)) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result = emp['sal'][ emp['job']=='SALESMAN' ].max() # emp['sal'][조건행].max() print(result)

문제 112. 30번 부서번호인 사원들의 총월급을 출력하시오.

SQL :

select sum(sal) from emp where deptno = 30;

Python :

#일반 import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [ ] for emp_list in emp_csv: if int(emp_list[7]) == 30: sal_list.append( int(emp_list[5]) ) print(sum(sal_list)) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result = emp['sal'][ emp['deptno']==30 ].sum() # emp['sal'][조건행].max() print(result)

문제 113, 114. 직업, 직업별 최대월급을 출력하시오. (일반, Pandas)

SQL :

select job, max(sal) from emp group by job;

Python :

#PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result = emp.groupby('job')['sal'].max() print(result) # step1 job 추출 후 정렬 import csv file1 = open('d:\\emp2.csv','r') emp_csv = csv.reader(file1) job_list = [] for emp_list in emp_csv: job_list.append(emp_list[2]) job = list(set(job_list)) job.sort() print(job) # step2 job에 max_sal을 매칭시켜줌 import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [ ] for i in job: file.seek(0) for emp_list in emp_csv: if emp_list[2] == i: sal_list.append(int(emp_list[5])) print(i,max(sal_list)) sal_list=[]

문제 115, 116. 직업, 직업별 총월급을 출력하는데 직업별 총월급이 높은것부터 출력하시오. (일반, Pandas)

SQL :

select job, sum(sal) as sum_sal from emp group by job order by sum_sal desc;

Python :

#일반 import csv file1 = open('d:\\emp2.csv','r') emp_csv = csv.reader(file1) job_list = [] for emp_list in emp_csv: job_list.append(emp_list[2]) job = list(set(job_list)) import csv file = open('d:\\emp2.csv','r') emp_csv = csv.reader(file) sal_list = [] result = [] for i in job: file.seek(0) for emp_list in emp_csv: if emp_list[2] == i: sal_list.append(int(emp_list[5])) result.append([sum(sal_list), i]) sal_list=[] result.sort() result.reverse() for i in result: print(i[1],i[0]) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result = emp.groupby('job')['job','sal'].sum().sort_values('sal',ascending=False) print ( result ) #PANDAS import pandas as pd emp = pd.read_csv("d:\\emp.csv") result = emp.groupby('job').sum()[['sal']] print(result.sort_values('sal',ascending=False))