231128 Python 문제

import sqlite3
import pandas as pd

 

[문제1] 근무일수가 가장 많은 10위 까지 직원들의 employee_id, last_name, department_name, 근무일수를 출력해 주세요. 단. sqlite를 이용하세요.  

conn = sqlite3.connect(':memory:') 
c = conn.cursor() 
data = pd.read_csv("c:/data/emp.csv")
dept_table = pd.read_csv("c:/data/dept.csv")
emp_table.to_sql("emp", conn, index=False)
dept_table.to_sql("dept", conn, index=False)
c.execute("select * from emp")
c.fetchall()
c.execute("select * from dept")
c.fetchall()

# 1.근무일수 -> 2.rank() -> 3. join 

(내가 작성한 코드)

c.execute("""select * from (
             select rank() over(order by (strftime("%s", "now") - strftime("%s", date(hire_date)))/86400) rank, 
             e.employee_id, e.last_name, d.department_name
             from emp e join dept d
             on e.department_id = d.department_id)
             where rank <= 10 """)
c.fetchall()

 

[선생님 작성법]

import sqlite3
conn = sqlite3.connect('c:/data/insa.db')
c = conn.cursor()
c.execute('pragma table_info(emp)')
c.fetchall()

 

(방법1)

c.execute(""" select (strftime("%s", "now") - strftime("%s", date(hire_date))/86400) working_day , 
                     employee_id, last_name, department_id
              from emp """)
c.fetchall()

# inline view

c.execute(""" select employee_id, last_name, department_id, working_day, dense_rank() over(order by working_day desc) rank
              from ( select (strftime("%s", "now") - strftime("%s", date(hire_date))/86400) working_day , 
                     employee_id, last_name, department_id
              from emp) """)
c.fetchall()

# rank() 

c.execute(""" select * from (
              select employee_id, last_name, department_id, working_day, dense_rank() over(order by working_day desc) rank
              from (
              select (strftime("%s", "now") - strftime("%s", date(hire_date))/86400) working_day , 
                     employee_id, last_name, department_id
              from emp)) 
              where rank <= 10
              """)
c.fetchall()

# 사원테이블에서 rank() → join 

c.execute(""" select e.employee_id, e.last_name, d.department_name, e.working_day
              from (
              select * from (
              select employee_id, last_name, department_id, working_day, dense_rank() over(order by working_day desc) rank
              from (
              select (strftime("%s", "now") - strftime("%s", date(hire_date))/86400) working_day , 
                     employee_id, last_name, department_id
              from emp)) 
              where rank <= 10 ) e, dept d
              where e.department_id = d.department_id
              """)
c.fetchall()

(방법2)
# with문 - 근무일수

c.execute("""with
                 emp_working as (select employee_id, last_name, department_id,
                                        (strftime('%s', 'now') - strftime('%s', date(hire_date)))/86400 working_day
                                 from emp )
             select * from emp_working
          """)
c.fetchall()

# with문 - rank 

c.execute("""with
                 emp_working as (select employee_id, last_name, department_id,
                                        (strftime('%s', 'now') - strftime('%s', date(hire_date)))/86400 working_day
                                 from emp ),
                 emp_rank as (select employee_id, last_name, department_id, working_day, 
                                      dense_rank() over(order by working_day desc) rank
                              from emp_working )
             select * from emp_rank
          """)
c.fetchall()

# with문 - 순위 10위까지 출력

c.execute("""with
                 emp_working as (select employee_id, last_name, department_id,
                                        (strftime('%s', 'now') - strftime('%s', date(hire_date)))/86400 working_day
                                 from emp ),
                 emp_rank as (select employee_id, last_name, department_id, working_day, 
                                      dense_rank() over(order by working_day desc) rank
                              from emp_working ),
                 top_10 as (select * from emp_rank where rank <= 10)
            select * from top_10             
          """)
c.fetchall()

# 최종 join 

c.execute("""with
                 emp_working as (select employee_id, last_name, department_id,
                                        (strftime('%s', 'now') - strftime('%s', date(hire_date)))/86400 working_day
                                 from emp ),
                 emp_rank as (select employee_id, last_name, department_id, working_day, 
                                      dense_rank() over(order by working_day desc) rank
                              from emp_working ),
                 top_10 as (select * from emp_rank where rank <= 10)
            select t.employee_id, t.working_day, t.rank, d.department_name
            from top_10 t, dept d
            where t.department_id = d.department_id
            order by 3
          """)
c.fetchall()

 

[문제2] 근무일수가 가장 많은 10위 까지 직원들의 employee_id, last_name, department_name, 근무일수를 출력해 주세요. 단. pandas 이용하세요.
# pandas식 으로 표현

(내가 작성한 코드)

a = pd.read_sql_query("""select * from (
                         select rank() over(order by (strftime("%s", "now") - strftime("%s", date(hire_date)))/86400) rank, 
                         e.employee_id, e.last_name, d.department_name
                         from emp e join dept d
                         on e.department_id = d.department_id)
                         where rank <= 10  """,conn)
a

[선생님 작성법]

import pandas as pb
from pandas import Series, DataFrame
emp = pd.read_csv('c:/data/emp.csv')
dept = pd.read_csv('c:/data/dept.csv')

# select (strftime("%s", "now") - strftime("%s", date(hire_date))/86400) working_day, employee_id, last_name, department_id from emp

x = DataFrame({'사번': emp.EMPLOYEE_ID,
               '이름': emp.LAST_NAME,
               '부서코드': emp.DEPARTMENT_ID,
               '근무일수': (pd.Timestamp.now() - pd.to_datetime(emp.HIRE_DATE)).dt.days})

# 순위 출력

x['순위'] = x['근무일수'].rank(ascending=False, method='dense').astype(int)
x

# 10위까지의 순위

top_10 = x[x['순위'] <= 10 ]
top_10

result = pd.merge(top_10, dept, left_on='부서코드', right_on='DEPARTMENT_ID')
result

result[['사번', '이름', 'DEPARTMENT_NAME', '근무일수', '순위']].sort_values(by='순위')