设班单试听前成单

SELECT
        sum((case when (select count(contract_id) from view_tms_contract tc 
     where tc.student_intention_id = a.student_intention_id 
     and status in (3,4,5,9) and tc.new_sign = 1 and submit_time < a.apply_time) > 0 then 1
     else 0 end)) as '设班前新签成单数量',
     count(a.student_intention_id) as '当月总学生数量'
FROM
(
SELECT
        lpo.student_intention_id,
        lpo.apply_time
FROM
        view_student s
LEFT JOIN
        lesson_plan_order lpo on s.student_intention_id = lpo.student_intention_id
WHERE
        s.create_time >= '2019-05-01'
        and s.create_time < '2019-06-01'
        and s.account_type = 1
GROUP BY
        lpo.student_intention_id
ORDER BY
        lpo.apply_time
) a

SELECT
        sum((case when (select count(contract_id) from view_tms_contract tc 
     where tc.student_intention_id = a.student_intention_id 
     and status in (3,4,5,9) and tc.new_sign = 1 and submit_time < a.adjust_start_time) > 0 then 1
     else 0 end)) as '试听课前新签成单数量',
     count(a.student_intention_id) as '当月总学生数量'
FROM
(
SELECT
        s.student_intention_id,
        lp.adjust_start_time
FROM
        view_student s
LEFT JOIN
        lesson_plan lp on s.student_id = lp.student_id
WHERE
        s.create_time >= '2019-05-01'
        and s.create_time < '2019-06-01'
        and s.account_type = 1
        and lp.lesson_type = 2
        and lp.status in (3,5)
        and lp.solve_status <> 6
GROUP BY
        s.student_id
ORDER BY
        lp.adjust_start_time
) a

蔡雪栋教师按部门导出

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

# In[8]:


import pandas as pd
import pymysql
from pyhive import presto
conn_mysql = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="hfjydb",charset="utf8")
conn_hive = presto.connect(host='dw-bigdata.hfjy.red', port='8334')


# In[9]:


sql1 = '''
select 
     t.teacher_id 教师ID,
     t.teacher_no 教师编号,
     u.name 教师姓名,
     tt.teacher_qualification_certificate 教师资格证编号,
         (case when tt.teacher_qualification_certificate is not null then '是' else '否' end)是否有教师资格证,

         (select gd.value from  bidata.gen_dict gd where gd.type ='teaching_group' and gd.code = tt.teaching_group) 教学组,
         (select gd.value from  bidata.gen_dict gd where gd.type ='teaching_department' and gd.code = tt.teaching_department) 教学部,
         group_concat(distinct jl.name) 所属团,
         (case t.quarters_type when 1 then '全职授课' when 2 then'全职教研' 
          when 3 then'兼职' when 4 then'实习' when 5 then'体验课老师' end)岗位属性,
             case 
           when u.status=0 then '禁用'
           when u.status=1 then '可用' 
           when u.status=2 then '手机已认证' end  账号状态,
          t.exit_time 出培时间
from 
    view_teacher t
left join 
    view_user_info u on u.user_id = t.teacher_id
left join
    view_tms_teacher_ext tt on tt.id = t.teacher_id
left join
    jx_legion_member jlm on jlm.teacher_id = t.teacher_id
left join
    jx_legion jl on jl.id = jlm.legion_id
where 
    t.is_train in (0,1)
    and t.account_type = 1
    and u.status =1 ##账号状态可用 
    and t.suspend <> 1 and t.quarters_type in (1,3,4)
    and t.teacher_id not in (
               select
                 x.teacher_id
             from
                 (select
                      teacher_id,max(update_time) update_time
                 from arranging_edit_log
                 group by teacher_id) x
             left join 
                 arranging_edit_log ael on ael.teacher_id = x.teacher_id 
                and ael.update_time = x.update_time
             where (ael.reson like '%离职交接%' or ael.reson = 2)
                            )
    and u.name not like '%测试%' and u.name not like '%授课支持%'
        and (select group_concat(subject_name) from subject where subject_id in
              (select subject_id from teacher_subject_rel where teacher_id =t.teacher_id)) not like '%升学%'
group by t.teacher_id
'''

df1 = pd.read_sql(sql1,conn_mysql)

df1.head()


# In[10]:


df1.to_excel(r'e:\desktop\学科教师资格证明细V3.xlsx')


# In[7]:


jsq = 1

for k,v in df1.groupby(['教学部','所属团']):

    name = k[0]+'-'+k[1]

    print(name)

    v.to_excel(r'e:\desktop\教师资格证\{0}.xlsx'.format(name))

    jsq += 1


print(f'执行完毕!共{jsq}个文件')


# In[ ]:

查询hive是否含有update_time

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

# In[1]:


import time
import pandas as pd
import pymysql
from pyhive import presto


# In[14]:


conn_mysql = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="bidata",charset="utf8")
conn_hive = presto.connect(host='dw-bigdata.hfjy.red', port='8334')


# In[8]:


table_sql = '''select table_name from  information_schema.tables where  table_schema = 'ods_db_bidata' '''

update_time_table = pd.DataFrame(columns=['table_name', 'max_update_time'])


# In[9]:


table_name = pd.read_sql(table_sql,conn_hive)

table_name.head()


# In[11]:


#table_name.to_excel(r'e:\desktop\查询表.xlsx')


# In[25]:


p = 1

for name in table_name['table_name']:

    sql_gettime = '''select '{0}' 表名, max(update_time) 最大时间 from {0}'''.format(name)
    try:
        max_update_time = pd.read_sql(sql_gettime,conn_mysql)
        if p > 0:
            res1 = pd.DataFrame()
            p = 0
        res1 = pd.concat([res1,max_update_time], axis=0)
        print(res1)
        print('>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
    except:
        print('出错')


# In[26]:


res1


# In[27]:


table_name.rename(columns={'table_name':'表名'},inplace=True)
table_name


# In[28]:


df_sub = pd.merge(table_name,res1,how='left',on=['表名'])

df_sub


# In[29]:


df_sub.to_excel(r'e:\desktop\查询结果.xlsx')

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