利用es统计的一些从mysql中的数据格式说明 学校统计

数据格式

安装

安装文档在这里

https://github.com/Dooy/DrPHP/tree/main/crontab/elasticsearch

1.用户 pigaimember

SELECT   
m.user_id as _id,m.user_id , FROM_UNIXTIME( m.ctime) as ctime ,m.teacher_or_student as ts,FROM_UNIXTIME(m.lastlogin) as lastlogin , FROM_UNIXTIME( m.end_time) as end_time ,m.lg_cnt,m.score ,m.renzheng,m.pay, FROM_UNIXTIME(m.gt_time) as gt_time,m.school_type as m_school_type,mi.name
,mi.school,mi.request_cnt,mi.essay_cnt ,mi.stu_cnt,mi.rz,mi.pigai_cnt,mi.version_cnt,mi.sex
,s.city,  s.city2,  s.city3 ,s.type2 as school_type
 ,ui.invite_id as invite_uid
FROM 
member as m
left join member_info AS mi on mi.user_id=m.user_id
left join school AS s ON mi.school = s.school  
left join user_invite as ui on ui.user_id=m.user_id
limit 10;

2. 作文题目 pigairequest

select  
e.essay_id as _id,e.essay_id,e.user_id ,e.request_id,FROM_UNIXTIME(e.ctime) as ctime ,e.score,e.sy_score,e.type,e.version 
,mi.name ,mi.school ,mi.student_number as stu_number,mi.class as stu_class 
,s.city,  s.city2,  s.city3 ,s.type2 as school_type   
,ui.invite_id as invite_uid  
,ea.good,ea.teacher_view,ea.view   
,r.user_id as t_user_id,r.essay_type, FROM_UNIXTIME(r.valid_end_time) as valid_end_time, r.manfen,r.teacher_name
,rq.f_request_id 
,ecat.cat_id 
from  eng_essay  as e   
left join   member_info as mi  on e.user_id=mi.user_id    
left join school as s on mi.school=s.school   
left join user_invite as ui on ui.user_id=e.user_id   
left join   eng_essay_attr as ea on e.essay_id=ea.essay_id   
left join  eng_rq_ying as rq on rq.request_id=e.request_id    
left join  eng_essay_request as r on e.request_id =r.request_id 
left join  eng_rq_cat as ecat on e.request_id =ecat.request_id
 where r.request_id>1000  ;

3. 作文文章 pigaiessay

select   
e.essay_id as _id,e.essay_id,e.user_id
,e.request_id,FROM_UNIXTIME(e.ctime) as ctime ,e.score  
,e.sy_score,e.type,e.version  ,mi.school ,s.city,  s.city2,  s.city3 ,s.type2 as school_type  
,ui.invite_id as invite_uid  ,ea.good,ea.teacher_view,ea.view  
,r.user_id as t_user_id,r.essay_type, FROM_UNIXTIME(r.valid_end_time) as valid_end_time,
r.manfen ,rq.f_request_id ,ecat.cat_id
from 
eng_essay  as e  
left join   member_info as mi  on e.user_id=mi.user_id   
left join school as s on mi.school=s.school  
left join user_invite as ui on ui.user_id=e.user_id  
left join   eng_essay_attr as ea on e.essay_id=ea.essay_id  
left join  eng_rq_ying as rq on rq.request_id=e.request_id   
left join  eng_essay_request as r on e.request_id =r.request_id
left join  eng_rq_cat as ecat on e.request_id =ecat.request_id

4.学校统计

学校列表

SELECT school, count(*) as cnt FROM pigaimember
where city='四川' and city2='地级市' and city3='区县'
and school_type in('0','3')
group by school limit 5000

4.1人数统计

4.1.1 注册人数、注册老师、注册学生数

ts 1为老师 2为学生

SELECT school,ts , count(*) as cnt FROM pigaimember where school in('四川大学','电子科技大学')
group by school,ts

4.1.2 认证老师数

SELECT school , count(*) as cnt FROM pigaimember
where school in('四川大学','电子科技大学')
and ts=1 and renzheng=1
group by school

4.1.3 标准版老师数

SELECT school , count(*) as cnt FROM pigaimember
where school in('四川大学','电子科技大学')
and ts=1 and end_time>当前时间戳
group by school

4.1.4 有布置并收到作文的老师数(选做)

SELECT school ,user_id , count(*) as cnt FROM pigairequest
where school in('四川大学','电子科技大学')
and essay_cnt>1
group by school, user_id

4.2布置统计

SELECT school , count(*) as cnt FROM pigairequest
where school in('四川大学','电子科技大学')
group by school

4.3提交作文统计

4.3.1 老师收到的作文数

SELECT school , sum(essay_cnt) as cnt FROM pigairequest
where school in('四川大学','电子科技大学')
group by school

4.3.2 学生提交的作文数、批改次数

SELECT school , sum(version) as version,count(*) as cnt FROM pigaiessay
where school in('四川大学','电子科技大学')
group by school

5 学校管理员面板 统计

5.1 老师布置年分布题目极收上作业

SELECT ctime,count(distinct user_id) as t_user_cnt,count(*) as cnt,sum(essay_cnt) as t_essay_cnt  FROM pigairequest where school in ('贵阳医学院','贵州医科大学') and  ctime>'2012-01-01'   and ctime<now   group by  date_histogram(field='ctime','interval'='1y','alias'='ctime',format='yyyy')
#学生提交的作业统计
SELECT ctime,count(*) as essay_cnt,sum(version) as version  FROM pigaiessay where school in ('贵阳医学院','贵州医科大学') and  ctime>'2012-01-01'   and ctime<now   group by  date_histogram(field='ctime','interval'='1y','alias'='ctime',format='yyyy')
# 加统计单独用户数 和 单独作文数 用时多了 1呗
SELECT ctime,count(distinct user_id) as user_cnt,count(distinct request_id) as rq_cnt, count(*) as essay_cnt,sum(version) as version  FROM pigaiessay where school in ('贵阳医学院','贵州医科大学') and  ctime>'2012-01-01'   and ctime<now   group by  date_histogram(field='ctime','interval'='1y','alias'='ctime',format='yyyy')

5.2 注册人数统计

select ts,rz,count(*) as cnt from pigaimember where school in ('贵阳医学院','贵州医科大学') group by  ts,rz 

5.3 按周、月、年统计