教研讲义

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 '是' else '否' end) as '讲义是否删除',
        jh.principal as '讲义负责人',
        jh.create_user as '讲义创建人',
        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 '讲义打分'
FROM
        jx_handouts jh

SELECT
        jd.directory_describe as '目录描述',
        jd.subject_id as '课程学科',
        jd.grade_id as '课程年级',
        jd.version_id 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 '课程最后更新人',
        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_directory jd

学而思题目数

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

# In[1]:


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[2]:


sql1 = '''
SELECT
        lq.knowledge_id,
        sjt.subject_name,
        lq.difficult_factor,
        count(lq.lesson_plan_id) use_count
FROM
        ods_hfjydb.lesson_plan_quiz_total lq
LEFT JOIN
        ods_hfjydb.lesson_plan lp on lp.lesson_plan_id = lq.lesson_plan_id
LEFT JOIN
        ods_hfjydb.view_student s on s.student_id = lp.student_id
INNER JOIN
        ods_hfjydb.subject sjt on sjt.subject_id = lp.subject_id
WHERE
        s.account_type = 1
        and lq.type = 8 
        and lp.status in (3,5) 
        and lp.solve_status <> 6
GROUP BY
        lq.knowledge_id,lq.difficult_factor,sjt.subject_name
'''

df1 = prestoinstance.get_df(sql1)

df1.head()


# In[ ]:


sql2 = '''
SELECT
        id,
        knowledgename
FROM
        xrs_knowledge
'''

df2 = mysqlinstance_tm_new_hfjy.get_df(sql2)

df2.head()


# In[ ]:


df_sub = pd.merge(df2,df1,left_on='knowledge_id',right_on='id',how='left')
df_sub.drop(['id'], axis=1, inplace=True)
df_sub.head()


# In[ ]:


sql3 = '''
SELECT
        hw.knowledge_id,
        hw.difficult_factor,
        sjt.subject_name,
        sum(case when hw.status = 0 then 1 else 0 end) homework_wrong_count ,
        sum(case when hw.status = 1 then 1 else 0 end) homework_right_count,
        sum(case when hw.status = 3 then 1 else 0 end) homework_half_count
FROM
        ods_hfjydb.home_work_detail_info hw
LEFT JOIN
        ods_hfjydb.home_work h on h.home_work_id = hw.home_work_id
LEFT JOIN
        ods_hfjydb.view_student s on s.student_id = h.student_id
INNER JOIN
        ods_hfjydb.subject sjt on sjt.subject_id = h.subject_id
WHERE
        s.account_type = 1
GROUP BY
        hw.knowledge_id,hw.difficult_factor,sjt.subject_name
'''
df3 = prestoinstance.get_df(sql3)

df3.head()


# In[ ]:


df_sub = pd.merge(df_sub,df3,how='left',on=['knowledge_id','difficult_factor','subject_name'])

df_sub.head()


# In[ ]:


df_sub.to_excel(r'e:\Desktop\学而思题目数.xlsx',index=False,encoding='GB18030')

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