杨佳舒课时数

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd
import pymysql


# In[2]:


conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="hfjydb",charset="utf8")


# In[3]:


data_no = pd.read_excel(r'E:\Desktop\需求文档\3204.xlsx')
data_no.head()


# In[4]:


sql1 = '''
SELECT
        b.*
FROM
        (
        SELECT
                a.*
        FROM
                (
                SELECT
                        s.student_no 学生编号,
                        SUBSTR(sub.subject_name,3,10) 学科,
                        sum(lp.class_period) as '课时数',
                        max(lp.adjust_start_time) '上课时间',
                        lp.teacher_id
                FROM
                        lesson_plan lp
                LEFT JOIN
                        view_student s on s.student_id = lp.student_id
                LEFT JOIN
                        subject sub on sub.subject_id = lp.subject_id
                WHERE
                        lp.lesson_type = 1 and lp.status in (3,5) and solve_status<>6 and s.student_no in {0} and SUBSTR(sub.subject_name,3,10) in {1}
                        and lp.adjust_start_time >='2019-03-01' and lp.adjust_start_time < '2019-06-01'
                GROUP BY 
                        学生编号,学科,lp.teacher_id
                ) a
        ORDER BY
                a.课时数 DESC,a.上课时间 DESC
        ) b
GROUP BY
        b.学生编号,b.学科
'''.format(tuple(data_no['学员编号']),tuple(data_no['学科']))


df1 = pd.read_sql(sql1, conn)
df1.head()


# In[5]:


#### 查出所有老师的课程id
sql2 = '''
SELECT
        lp.lesson_plan_id,
        s.student_no,
        lp.teacher_id,
        SUBSTR(sub.subject_name,3,10) 学科
FROM
        lesson_plan lp
LEFT JOIN
        view_student s On s.student_id = lp.student_id
LEFT JOIN
        subject sub on sub.subject_id = lp.subject_id
WHERE
        lp.lesson_type = 1 and lp.status in (3,5) and solve_status<>6 and s.student_no in {0}
        and lp.teacher_id in {1} and SUBSTR(sub.subject_name,3,10) in {2}
        and lp.adjust_start_time >='2019-03-01' and lp.adjust_start_time < '2019-06-01'
'''.format(tuple(df1['学生编号']),tuple(df1['teacher_id']),tuple(df1['学科']))

df2 = pd.read_sql(sql2, conn)
df2.head()


# In[6]:


sql3 = '''
SELECT
        a.teacher_id,
        sum(a.备课时长)/count(a.lesson_plan_id) '平均备课时长'
FROM
(
SELECT
        lp.lesson_plan_id,
        lp.teacher_id,
        sum(udt.time)/60000 '备课时长',
        udt.type
FROM
        lesson_plan lp
LEFT JOIN
        userdoingtime udt ON udt.sourceId = lp.lesson_plan_id
WHERE
        lp.lesson_plan_id in {0} and udt.type = 'LESSON'
GROUP BY
        lp.lesson_plan_id
) a
GROUP BY
        a.teacher_id
'''.format(tuple(df2['lesson_plan_id']))

df3 = pd.read_sql(sql3, conn)
df3.head()


# In[7]:


df4 = pd.merge(df1,df3,how='left',on=['teacher_id'])
df4.head()


# In[8]:


sql5 = '''
SELECT
        t.teacher_id,
        (case t.quarters_type when 1 then '全职授课' when 2 then'全职教研' when 3 then'兼职' when 4 then'实习' when 5 then'体验课老师' end) '岗位属性'
FROM
        view_teacher t
WHERE
        t.teacher_id in {0}
'''.format(tuple(df1['teacher_id']))

df5 = pd.read_sql(sql5, conn)
df5.head()


# In[9]:


df_sub = pd.merge(df4,df5,how='left',on=['teacher_id'])
df_sub.head()


# In[10]:


df_sub.to_excel(r'e:\Desktop\新3-5月学生编号对应最多消课老师.xlsx',index=False,encoding='GB18030')

文档更新时间: 2019-08-19 11:45   作者:admin