教研课程讲义

SELECT
        jh.directory_id as '讲义id',
        jh.handouts_name as '讲义名称',
        jh.handouts_describe as '讲义描述',
        (case jh.`status` when 1 then '生成中' when 2 then '创建中' when 3 then '待审核' when 4 then '已驳回' when 5 then '已上线' when 6 then '已下线' else '' end) as '讲义状态',
        (case jh.is_del when 1 then '是' when 0 then '否' else '' end) as '讲义是否删除',
--         jh.principal as '讲义负责人',
--         jh.create_user as '讲义创建人',
--         jh.update_user as '讲义更新人',
        (SELECT `name` FROM view_user_info WHERE user_id=jh.principal) as '讲义负责人',
        (SELECT `name` FROM view_user_info WHERE user_id=jh.create_user) as '讲义创建人',
        (SELECT `name` FROM view_user_info WHERE user_id=jh.update_user) as '讲义更新人',
        jh.create_time as '讲义创建时间',
        jh.update_date as '讲义更新日期',
        jh.update_time as '讲义更新时间',
        jh.paper_id as '好未来的paper_id',
        jh.paper_url as '好未来预览url',
        jh.paper_edit_url as '好未来编辑url',
        jh.reject as '讲义驳回原因',
        jh.number as '讲义第几讲',
        jh.first_online_time as '讲义第一次上线时间',
        jh.score as '讲义打分',
        jd.directory_describe as '目录描述',
        (SELECT `value` FROM tms_ddic WHERE `code`=jd.subject_id and type='JY003') as '课程学科',
        (SELECT `value` FROM tms_ddic WHERE `code`=jd.grade_id and type='JY002' ) as '课程年级',
        (SELECT `value` FROM tms_ddic WHERE `code`=jd.version_id and type='JY001') as '课程教材版本',
        (case jd.course_type when 1 then '试听课' when 2 then '定制课程' when 3 then '专题课程' when 4 then '冲刺课程' when 5 then '竞赛课程' else '' end) as '课程类型',
        (case jd.course_difficulty when 1 then '不限' when 2 then '基础' when 3 then '提高' when 4 then '培优' else '' end) as '课程难度',
        jd.`year` as '年份',
        (case jd.season when 1 then '不限' when 2 then '春季' when 3 then '秋季' when 4 then '暑假' when 5 then '寒假' when 6 then '春季上' when 7 then '春季夏' when 8 then '秋季上' when 9 then '秋季下' when 10 then '暑假上' when 11 then '暑假中' when 12 then '暑假下' else '' end) as '课程季节',
        jd.number as '课程总讲次数',
        jd.effective_number as '课程有效的讲义次数',
--         jd.update_user as '课程创建人',
--         jd.create_user as '课程最后更新人',
        (SELECT `name` FROM view_user_info WHERE user_id=jh.update_user) as '课程创建人',
        (SELECT `name` FROM view_user_info WHERE user_id=jh.create_user) as '课程最后更新人',
        jd.create_time as '课程创建时间',
        jd.update_time as '课程最后更新时间',
        jd.course_name as '课程名称',
        jd.course_url as '附件',
        jd.dir_type as '课程状态',
        jd.course_url_name as '附件名称',
        jd.online_number as '当前已下线讲义数',
        jd.offline_number as '当前已上线讲义数',
        jd.audit_number as '当前待审核讲义数',
        jd.reject_number as '当前驳回讲义数',
        (case jd.competition_type when 1 then '全国初赛' when 2 then '全国复赛' when 3 then '全国联赛一试' when 4 then '全国联赛二试' when 5 then '语文报杯' when 6 then '叶圣陶杯' when 7 then '创新作文' when 8 then '新概念作文' when 9 then '小学奥数' when 10 then '国庆节专题课程' else '' end) as '赛别',
        (case jd.sprint_type when 1 then '小升初' when 2 then '中考' when 3 then '高考' else '' end) as '冲刺阶段',
        (case jd.directory_type when 1 then '讲义' when 2 then '课件' else '' end)as '课程目录类型'
FROM
    jx_handouts jh
LEFT JOIN
        jx_directory jd ON jd.id = jh.directory_id
LEFT JOIn
        view_user_info u on u.user_id=jh.principal
WHERE
        account_type = 1 #正式
        and jh.`status` = 5 #讲义状态为已上线
        and jh.is_del = 0 #讲义没有删除

授课时长

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

# In[8]:


import sys
sys.path.append(r'e:\Desktop\新环境\DataMachine_for_timer\src')
import pandas as pd
import numpy as np
import pymysql
from ML import data_suit,plot_suit
from utils import mysqlhelper,elapse,prestohelper
from config import config
from datetime import datetime , time
from pyhive import presto

## 脱敏库
mysqlinstance_tm_new_hfjy = mysqlhelper(**config.tm_new_hfjy)
mysqlinstance_tm_new_bidata = mysqlhelper(**config.tm_new_bidata)

## dmart库
mysqlinstance_dmart_dmart = mysqlhelper(**config.dmart_dmart)
mysqlinstance_dmart_bidata = mysqlhelper(**config.dmart_bidata)

## hrm库
mysqlinstance_hrm_hrm_sale = mysqlhelper(**config.hrm_hrm_sale)
mysqlinstance_hrm_new_hrm = mysqlhelper(**config.hrm_new_hrm)
mysqlinstance_hrm_jobhunt = mysqlhelper(**config.hrm_jobhunt)

## hive库
prestoinstance = prestohelper(**config.hive_prosto)


# In[9]:


mysql01 =  '''
SELECT (case when jd.grade_id in (1,2,3,4,5,6) then '小学'
                            when jd.grade_id in (7,8,9) then '初中'
                             else '高中'end) grade,
                td.value sub_name,  jh.handouts_name,(case when pct.content_type = 4  then 'DIY' ELSE 'hwl题库' end) content_type,
hwlquizid quiz_id,SUBSTRING(g.answerdetail, INSTR(g.answerdetail,'授课时长】')+5 ,4) 授课时长 
from jx_directory jd 
inner join jx_handouts jh on jh.directory_id = jd.id
INNER JOIN  bidata.hwl_paper_quiz_info pct on pct.paper_id = jh.paper_id
left JOIN view_hwl_quiz_knowledge g  on g.hwlquizid = pct.quiz_id
INNER JOIN tms_ddic td on td.type='JY003' and  td.`code` =jd.subject_id
where jd.season = 2 
and jh.handouts_name not like '%测试%'
and jd.subject_id = {}
and jh.is_del=0
group  by hwlquizid  
'''


mysql02 = '''
SELECT distinct  g.quiz_id, g.avg_use_time
 from ods_hfjydb.jx_directory jd 
inner join ods_hfjydb.jx_handouts jh on jh.directory_id = jd.id
INNER JOIN  ods.hwl_paper_quiz_info pct on pct.paper_id = jh.paper_id
left JOIN edw.quiz_use_info g  on g.quiz_id = pct.quiz_id
where jd.season = 2 
and jh.is_del=0
'''

big_tb = pd.DataFrame()


# In[10]:


conn = presto.connect(host='dw-bigdata.hfjy.red', port='8334')


for i in range(1,11):
    mysql01_df = mysqlinstance_tm_new_hfjy.get_df(mysql01.format(i))
    big_tb = pd.concat([big_tb, mysql01_df], axis=0)

mysql02_df = pd.read_sql(mysql02, conn)

big_tb.head()

mysql02_df.head()


# In[ ]:


part3 = pd.merge(big_tb, mysql02_df, on=["quiz_id"], how='outer')


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