[4일차] python&sql

Nth Highest Salary

다음 테이블에서 n번째로 높은 급여를 찾는 함수를 작성하시오. (단, n번째로 높은 급여가 없으면 null을 반환)

 

Employees

 

Python

 

class SalaryAnalyzer:
    def __init__(self, df):
        self.df = df
    
    @staticmethod
    def ordinal(n):
        if 10 <= n % 100 <= 20:
            suffix = 'th'
        else:
            suffix = {1: 'st', 2: 'nd', 3: 'rd'}.get(n % 10, 'th')
        return f"{n}{suffix}"
    
    def nth_highest_salary(self, n):
        column_name = f"{self.ordinal(n)}_highest_salary"
        if n > len(self.df):
            return pd.DataFrame({column_name : [None]})
        else:
            nth_salary = self.df['salary'].nlargest(n).iloc[-1]
            return pd.DataFrame({column_name : [nth_salary]})
        
analyzer = SalaryAnalyzer(Employee)        
print(analyzer.nth_highest_salary(2))

 

오늘 풀어본 문제는 정규식과는 다른 의미로, def 함수를 선언하지 않으면 풀 수없어서 다소 난이도가 있는 문제였다. 단순 solution을 위해서는 nth_highest_salary 함수만 사용하면 되지만, 문제를 풀다보니 결과로 출력되는 dataframe의 column 이름에 등수를 함께 표시하면 더 유용한 함수로 사용할 수 있다고 판단해서 class instance를 선언해보았다. 내용을 하나씩 살펴보자.

 

 

인스턴스 메서드(Instance method)

 

Python의 class 안에서 정의된 함수를 "메서드(method)"라고 부른다. 메서드들은 보통 첫 번째 인자로 self를 받는데, 여기서 self는 현재 instance를 가리킨다. 따라서, 메서드를 호출할 때는 self를 전달할 필요가 없다. python에서 자동으로 현재 객체(instance)를 전달하기 때문이다. 예시를 통해 알아보자.

 

class MyClass:
    def example_method(self):
        print("This is a method of MyClass.")
        
obj = MyClass()
obj.example_method()

 

obj.example_method()를 호출할 때, python은 자동으로 'obj'를 'self'로 example_method에 전달한다. 그런데, 우리가 만든 class를 보면 ordinal 메서드에서는 self를 사용하지 않기 때문에, 이 메서드를 정적 메서드로 호출해야 한다.

 

 

정적 메서드(Static Method)

 

정적 메서드는 특정 instance에 종속되지 않은 메서드이다. 이 메서드는 '@staticmethod' 데코레이터(decorator)로 정의된다. 정적 메서드는 self 인자를 전달받지 않기 때문에, instance나 class의 어떤 상태도 수정하거나 접근할 수 없다. 간단한 예제로 확인해보자.

 

class MyClass:
  
    def __init__(self, value):
        self.instance_variable = value

    def instance_method(self):
        return f"Called instance method of {self.instance_variable}"

    @staticmethod
    def static_method():
        return "Called static method"
    
# 인스턴스 생성
obj = MyClass("Example Instance")

# 인스턴스 메서드 호출: 주로 인스턴스를 통해 호출
print(obj.instance_method())

# 정적 메서드 호출: 클래스 이름을 사용하여 직접 호출
print(MyClass.static_method())

# 인스턴스 메서드를 클래스 이름으로 호출하려면, 인스턴스를 명시적으로 전달해야 함
print(MyClass.instance_method(obj))

 

정적 메서드도 인스턴스 메서드처럼 class의 이름을 사용해서 호출할 수 있다. 하지만, 인스턴드 메서드와 달리 일관된 값을 반환한다는 차이점이 있다. solution의 ordinal method 역시 n의 값으로만 결과가 출력됨을 확인할 수 있다. 다음으로는 사용한 함수들에 대해 정리해보자.

 

• nlargest() : n개의 가장 큰 값들을 큰 순서대로 list로 반환한다. 예를 들어, n=2일 경우 list의 첫 번쨰 항목은 가장 큰 값(1위)이고, 두 번째 항목은 두 번째로 큰 값(2위) 이다. 
  
  
• iloc[-1] : 리스트의 마지막 항목(n번째로 큰 값)을 선택하는데 사용한다.
   
  1. loc
     • 라벨(label) 기반의 인덱싱 사용
     • 행과 열의 라벨 이름을 사용하여 데이터를 선택
     • 슬라이싱 시, 시작점과 끝점을 모두 포함
     • ex) Employee.loc[1, 'id'] => index 1번의 ID value를 출력
  2. iloc
     • 정수 기반의 인덱싱 사용
     • 행과 열의 정수 인덱스를 사용하여 데이터 선택
     • 슬라이싱 시, python의 list 슬라이싱과 유사하게 작동하여 시작점은 포함되지만 끝점은 포함되지 않음
     • ex) Employee.iloc[0:2,1] => index 0과 1의 salary를 출력
       
       
• {1: 'st', 2: 'nd', 3: 'rd'}.get(n % 10, 'th') : get() 메서드는 dict에서 주어진 key에 해당하는 값을 반환한다. 메서드의 두 번째 인수는 dict에 해당 key가 없을 때 반환될 기본값이다. 따라서, n의 마지막 숫자를 기준으로 서수의 접미사를 결정하는 역할을 수행한다. 만약 마지막 숫자가 1,2,3이 아니라면 기본값으로 'th'가 사용된다.
  
  
• SalaryAnalyzer Class
  • Dataframe을 초기화 할 때 instance 변수로 저장하기 위해 __init__ method 사용
  • ordinal function은 instance 변수에 의존하지 않기 때문에 정적 메서드 @staticmethod 사용
  • nth_highest_salary는 self.df를 사용하여 instance의 dataframe에 접근

 

SQL

 

select salary as nth_highest_salary
from(
    select distinct salary
    from Employee
    union
    select null
    order by salary desc
    limit 2
) as subquery
order by nth_highest_salary asc
limit 1;

 

여기서 subquery 안의 limit 뒤의 숫자가 원하는 순위이다. 만약 2번째로 높은 급여를 찾고 싶다면, 위의 code 처럼 2를 사용하면 된다. 중복 급여를 제외하고 n번째로 높은 급여를 return 하였는데, 만약 중복 급여도 순위에 포함하려면 distinct를 제거하면 된다. 또한 union을 사용하여 기본 테이블의 고유한 급여 value와 null 값을 결합하여, n번째 급여를 선택하고 만약 없는 경우 null 값을 반환하도록 하였다.

 

---

Second Highest Salary

다음 테이블에서 2번째로 높은 급여를 찾는 함수를 작성하시오. (단, 2번째로 높은 급여가 없으면 null(None)을 반환)

 

Employee

 

Python

 

def second_highest_salary(df, n):
    if n > len(df):
        return pd.DataFrame({second_highest_salary.__name__ : [None]})
    else:
        nth_salary = df['salary'].nlargest(n).iloc[-1]
        return pd.DataFrame({second_highest_salary.__name__ : [nth_salary]})

print(second_highest_salary(Employee,2))

 

python은 이전에 풀었던 문제와 유사해서 define function만 가져와서 문제를 해결해 보았다.

 

SQL

 

select salary as second_highest_salary
from(
    select distinct salary
    from Employee
    union
    select null
    order by salary desc
    limit 2
) as subquery
order by second_highest_salary asc
limit 1;

 

SQL 역시 거의 유사하다. 단, 현재 ipynb의 pandasql 환경에서 sql문 실습을 진행하고 있는데, alias의 첫 글자가 숫자이면 오류가 발생하기 때문에 문자로 진행해야 한다. 

 

---

Department Highest Salary

다음 2개의 테이블을 보고, 각 부서에서 연봉이 가장 높은 직원을 찾으시오.

 

Employee&Department

 

Python

 

merge_df = pd.merge(Employee, Department, left_on = 'departmentId', right_on = 'id', suffixes=('','_department'))
max_salary = merge_df.groupby('departmentId')['salary'].max().reset_index()
result = pd.merge(merge_df, max_salary, on = ['departmentId', 'salary'])
print(result[['name_department','name','salary']])

 

• pd.merge() : 두 개의 테이블을 동일한 key로 결합한다. 이 때, suffixes 매개변수를 사용해 동일한 열 이름이 충돌하지 않게 처리한다.
• df.groupby() : 부서 번호 별 최대 급여 table을 생성한다.

 

SQL

 

select D.name as Department, E.name, salary
from Department D
inner join Employee E
on D.id = E.departmentId
where (E.departmentId, E.salary) in (
    select 
    departmentID, max(salary) as salary
    from Employee
    group by departmentID

 

• where절에 서브쿼리를 사용하여 각 부서의 최고 급여를 알아내고, 메인 쿼리에서 서브 쿼리의 결과와 부서 ID 및 급여를 일치시켜 결과를 추출