会议相关月报样板
您xx月共参加了xx ×x小时的会议，与xx个人进行过会晤，最近较为关注的公司有xx,xx,xx(公司名)
(数据取自日程，会晤人取日程参与人，关注公司取参与会议的人次较多的前三名公司；会议仅包括：橙子日程中从橙脉和手机日历获取的日程）
参加会议时间
oradt_cloud1520.contact_card_schedule 取会议开始结束时间；1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33select user_id,SUM(meeting_time) as total_schedule_hour from(
select a.id as schedule_id,a.user_id,content,title,
FROM_UNIXTIME(start_time) as start_time,
FROM_UNIXTIME(end_time) as end_time,
(end_time - start_time)/(60*60*24) as day_diff,
case
-- 本月开始结束且一天之内结束
when (end_time - start_time)/(60*60*24) <= 1 and MONTH(FROM_UNIXTIME(start_time)) = 3 and MONTH(FROM_UNIXTIME(end_time))=3
then round((end_time - start_time)/(60*60))
-- 本月开始结束且一天之内没有结束
when (end_time - start_time)/(60*60*24) > 1 and MONTH(FROM_UNIXTIME(start_time)) = 3 and MONTH(FROM_UNIXTIME(end_time))=3
then round((end_time - start_time)/(60*60*24) * 8)
-- 结束时间不是本月，则取月初第一天为结束时间
when (end_time - start_time)/(60*60*24) > 1 and MONTH(FROM_UNIXTIME(start_time)) = 3 and MONTH(FROM_UNIXTIME(end_time))!=3
then round((UNIX_TIMESTAMP(last_day(curdate())) - start_time)/(60*60*24) * 8)
-- 开始时间不是本月，则取月初第一天为开始时间
when (end_time - start_time)/(60*60*24) > 1
and MONTH(FROM_UNIXTIME(start_time)) != 3 and MONTH(FROM_UNIXTIME(start_time)) != 3 and MONTH(FROM_UNIXTIME(end_time))=3
then round((end_time - UNIX_TIMESTAMP(DATE_ADD(curdate(),interval -day(curdate())+1 day)))/(60*60*24) * 8)
end as meeting_time
from oradt_cloud1520.contact_card_schedule a
right join oradt_cloud1520.contact_card_schedule_map b on a.id = b.schedule_id
left join(
select user_id,uuid
from oradt_cloud1520.contact_card
where self='true' and status = 'active'
) as c on c.user_id = a.user_id and c.uuid = b.uuid
WHERE a.start_time >0 and a.end_time > a.start_time
and b.status != 0 and b.uuid != '' and c.uuid is null and a.status = 1
and YEAR(FROM_UNIXTIME(start_time)) = 2017 and YEAR(FROM_UNIXTIME(end_time))=2017
and (MONTH(FROM_UNIXTIME(start_time)) = 3 or MONTH(FROM_UNIXTIME(end_time))=3)
order by user_id,schedule_id
)c GROUP BY user_id;
名片角色总会见人数
先统计每个人参加了几个会议（会议需要去重）1
2
3
4
5
6
7
8
9
10
11
12
13
14
15select user_id, GROUP_CONCAT(DISTINCT(schedule_id)) as meeting_list from(
select a.user_id, a.id as schedule_id
from oradt_cloud1520.contact_card_schedule a
right join oradt_cloud1520.contact_card_schedule_map b on a.id = b.schedule_id
left join(
select user_id,uuid
from oradt_cloud1520.contact_card
where self='true' and status = 'active'
) as c on c.user_id = a.user_id and c.uuid = b.uuid
WHERE a.start_time >0 and a.end_time > a.start_time
and b.status != 0 and b.uuid != '' and c.uuid is null and a.status = 1
and YEAR(FROM_UNIXTIME(start_time)) = 2017 and YEAR(FROM_UNIXTIME(end_time))=2017
and (MONTH(FROM_UNIXTIME(start_time)) = 3 or MONTH(FROM_UNIXTIME(end_time))= 3)
order by schedule_id,user_id
) a GROUP BY user_id;
然后分别统计每个人的会议时长1
2select DISTINCT(count(uuid)) from oradt_cloud1520.contact_card_schedule_map where status != 0 and uuid != '' and
schedule_id in (3260,3890,3895,3915,4249,4252,4254,4255,4280,4822);
关注公司
oradt_cloud1520.contact_card_schedule_map 取uuid，即参与人；
与DM库中公司表关联得到参与人公司,统计公司前三位1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20select user_id, GROUP_CONCAT(companyName ORDER BY com_count desc,last_modified desc) as com_list from(
select user_id,companyName,count(*) as com_count,max(last_modified) as last_modified from(
select d.*,FROM_UNIXTIME(g.last_modified) as last_modified from(
select a.user_id,b.schedule_id,b.uuid,c.companyName
from oradt_cloud1520.contact_card_schedule a
right join oradt_cloud1520.contact_card_schedule_map b on a.id = b.schedule_id
left JOIN(select DISTINCT user_id,card_id,companyName from dm_test.company_info_jsonver) as c on b.uuid = c.card_id
left join(
select user_id,uuid
from oradt_cloud1520.contact_card
where self='true' and status = 'active'
) as f on f.user_id = a.user_id and f.uuid = b.uuid
WHERE a.start_time > 0 and a.end_time > a.start_time
and b.status != 0 and b.uuid != '' and f.uuid is null and a.status = 1
and YEAR(FROM_UNIXTIME(start_time)) = 2017 and YEAR(FROM_UNIXTIME(end_time))=2017
and (MONTH(FROM_UNIXTIME(start_time)) = 3 or MONTH(FROM_UNIXTIME(end_time))= 3)
and c.companyName !='' and c.companyName is NOT null
)d left join oradt_cloud1520.contact_card g on g.uuid = d.uuid) h
GROUP BY user_id,companyName ORDER BY com_count desc,last_modified desc
)e GROUP BY user_id;
相关表结构
1 | CREATE TABLE `contact_card_schedule` ( |
