温州市移动用户激活人手机号码：

select DISTINCT mobile from report.t_login_first_log_new where uid in (SELECT c.uid FROM shinemo_cube.t_org_base_info a
LEFT JOIN (SELECT DISTINCT org_id,uid from shinemo_im.t_org_uid) b ON a.org_id = b.org_id
LEFT JOIN (select DISTINCT uid from report.t_login_first_log_new where operators_type >=1) c on b.uid = c.uid
where a.city = ‘温州’ and c.uid !=’null’)

温州市移动用户7月份活跃人手机号码：

select DISTINCT mobile from report.t_login_first_log_new where uid in (SELECT c.uid FROM shinemo_cube.t_org_base_info a
LEFT JOIN (SELECT DISTINCT org_id,uid from shinemo_im.t_org_uid) b ON a.org_id = b.org_id
LEFT JOIN (select DISTINCT uid from report.t_login_log_202007 where operators_type >=1) c on b.uid = c.uid
where a.city = ‘温州’ and c.uid !=’null’)

4-7月新注册用户清单

select a.gmt_create as 注册时间, b.city as 地市,b.area as 区域,b.org_id as 企业编码,b.ext_org_id as 外部编码,b.name as 集团名称,d.mobile1 as 用户电话,c.name as 用户名字 from (select distinct uid,gmt_create,org_id from shinemo_im.t_org_uid) a
left join (select distinct org_id,name,city,area,ext_org_id from shinemo_cube.t_org_base_info) b on a.org_id = b.org_id
left join (select distinct uid,name from shinemo_im.t_org_user) c on a.uid = c.uid
left join (select distinct uid,mobile1 from shinemo_cmdata.t_user_mobile) d on a.uid = d.uid
where a.gmt_create between ‘20120-04-01 00:00:00.0’ and ‘2020-07-31 23:59:59.9’

4-7月新激活用户清单

select h.gmt_create as 注册时间,a.gmt_create as 激活时间, b.city as 地市,b.area as 区域,b.org_id as 企业编码,b.ext_org_id as 外部编码,b.name as 集团名称,d.mobile1 as 用户电话,c.name as 用户名字 from (select distinct uid,gmt_create from report.t_login_first_log_new) a
left join (select distinct uid,org_id,gmt_create from shinemo_im.t_org_uid) h on h.uid = a.uid
left join (select distinct org_id,name,city,area,ext_org_id from shinemo_cube.t_org_base_info) b on h.org_id = b.org_id
left join (select distinct uid,name from shinemo_im.t_org_user) c on a.uid = c.uid
left join (select distinct uid,mobile1 from shinemo_cmdata.t_user_mobile) d on a.uid = d.uid
where a.gmt_create between ‘20120-04-01 00:00:00.0’ and ‘2020-07-31 23:59:59.9’

8月客户经理登录彩云 pv uv

SELECT 八月, COUNT(DISTINCT i.uid) AS uv, COUNT(i.uid) AS pv
FROM (
SELECT uid
FROM report.t_login_log202008
) i
JOIN (
SELECT DISTINCT uid
FROM shinemo_cmdata.t_user_mobile o
JOIN (
SELECT DISTINCT customer_phone
FROM shinemo_im.t_org_customer_manager
WHERE customer_phone != ‘null’
AND customer_phone != ‘’
) n
ON n.customer_phone = o.mobile1
) m
ON m.uid = i.uid

8月客户经理查询工作台的各部分功能的打点pv uv

SELECT a.name, ifnull(b.uv, 0) AS uv
, ifnull(b.pv, 0) AS pv
FROM (
SELECT ‘电子通行证’ AS name
UNION
(SELECT ‘疫情地图’)
UNION
(SELECT ‘每日健康打卡’)
UNION
(SELECT ‘社区疫情筛选’)
UNION
(SELECT ‘疫情信息填报’)
UNION
(SELECT ‘新肺能量表’)
UNION
(SELECT ‘浙江健康导航’)
UNION
(SELECT ‘线索上报’)
UNION
(SELECT ‘疫情防护’)
UNION
(SELECT ‘反馈报告’)
UNION
(SELECT ‘任务派发’)
UNION
(SELECT ‘会议通知’)
UNION
(SELECT ‘安全聊天’)
UNION
(SELECT ‘讯盟福利社’)
UNION
(SELECT ‘常用号码’)
UNION
(SELECT ‘签到’)
UNION
(SELECT ‘工作轨迹’)
UNION
(SELECT ‘外出’)
UNION
(SELECT ‘出差’)
UNION
(SELECT ‘签到补签’)
UNION
(SELECT ‘任务’)
UNION
(SELECT ‘工作报告’)
UNION
(SELECT ‘邮件’)
UNION
(SELECT ‘云盘’)
UNION
(SELECT ‘电话会议’)
UNION
(SELECT ‘投票’)
UNION
(SELECT ‘工作圈’)
UNION
(SELECT ‘审批’)
UNION
(SELECT ‘公文基础版’)
UNION
(SELECT ‘云视讯’)
UNION
(SELECT ‘公文专业版’)
UNION
(SELECT ‘讯盟灵犀’)
UNION
(SELECT ‘督查督办’)
UNION
(SELECT ‘语音笔记’)
UNION
(SELECT ‘公告’)
UNION
(SELECT ‘会议预定’)
UNION
(SELECT ‘公车调度’)
UNION
(SELECT ‘访客系统’)
UNION
(SELECT ‘神州专车’)
UNION
(SELECT ‘滴滴出行’)
UNION
(SELECT ‘同城商旅’)
UNION
(SELECT ‘中国银行’)
UNION
(SELECT ‘京东采购’)
UNION
(SELECT ‘优办商城’)
UNION
(SELECT ‘咪咕联合会员’)
UNION
(SELECT ‘集合大学’)
UNION
(SELECT ‘报销’)
UNION
(SELECT ‘工资条’)
UNION
(SELECT ‘发票抬头’)
UNION
(SELECT ‘复工易·疫扫’)
) a
LEFT JOIN (
SELECT i.event_number AS name, COUNT(DISTINCT i.uid) AS uv, COUNT(i.uid) AS pv
FROM (
SELECT event_number, uid
FROM shinemo_log.t_dot_log_202008
WHERE mid = ‘107’
) i
JOIN (
SELECT DISTINCT uid
FROM shinemo_cmdata.t_user_mobile o
JOIN (
SELECT DISTINCT customer_phone
FROM shinemo_im.t_org_customer_manager
WHERE customer_phone != ‘null’
AND customer_phone != ‘’
) n
ON n.customer_phone = o.mobile1
) m
ON m.uid = i.uid
GROUP BY i.event_number
) b
ON a.name = b.name
ORDER BY b.uv DESC

//彩云注册用户数 //1371696

select count(distinct b.uid) as 彩云注册用户数 from
(select * from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join shinemo_im.t_org_uid b
on a.org_id=b.org_id

//彩云用户安装数 //308614

select count(distinct b.uid) as 彩云用户安装数 from
(select * from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join shinemo_im.t_org_uid b on a.org_id=b.org_id
join report.t_login_first_log_new c on b.uid=c.uid

//10月月活用户数 //75240

select count(distinct b.uid) as 10月月活用户数 from
(select * from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join report.t_login_log_202010 b on a.org_id=b.org_id

//累计订单量 //13568

select count(b.id) as 累计订单量 from
(select * from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join shinemo_cmmc.t_order b on a.org_id=b.org_id

//累计友好客户建档企业数,累计友好客户数 //5238 //5804

select count(distinct a.org_id) as 累计友好客户建档企业数,count(distinct b.uid) 累计友好客户数 from
(select * from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join shinemo_cmmc.t_friendly_user b on a.org_id=b.org_id
where b.status = 0

//AB类集团友好客户覆盖率、CD类集团友好客户覆盖率

select city 城市,count(distinct a.org_id) num from
shinemo_cube.t_org_base_info a
join shinemo_cmmc.t_friendly_user b on a.org_id=b.org_id
join shinemo_im.t_org c on a.org_id=c.id
group by a.city

//各城市的AB类集团友好客户覆盖率、CD类集团友好客户覆盖率

select m.city,k.num1,m.num2 from (select a.city city,count(distinct a.org_id) num1 from
shinemo_cube.t_org_base_info a
join shinemo_cmmc.t_friendly_user b on a.org_id=b.org_id
join (select id from shinemo_im.t_org where scale_id like ‘%C%’ or scale_id like ‘%D%’) c on a.org_id=c.id
group by a.city ) k join (select a.city city,count(distinct a.org_id) num2 from
shinemo_cube.t_org_base_info a
join shinemo_cmmc.t_friendly_user b on a.org_id=b.org_id
join shinemo_im.t_org c on a.org_id=c.id
group by a.city) m on k.city=m.city

//嘉兴市ABC类集团成员

select count(distinct c.uid) from
(select * from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join (select id from shinemo_im.t_org where scale_id like ‘%A%’ or scale_id like ‘%B%’ or scale_id like ‘%C%’) b on a.org_id=b.id
join shinemo_im.t_org_uid c on c.org_id=a.org_id

//嘉兴市友好客户订单量>0有boss编码企业数

select count(distinct a.org_id) from
(select from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join (select from shinemo_im.t_org where external_org_id != 0) b on a.org_id=b.id
join (select org_id,count(id) num from shinemo_cmmc.t_order where status>=1 group by org_id) d on d.org_id=a.org_id
join shinemo_cmmc.t_friendly_user e on a.org_id=e.org_id
where d.num>0

//嘉兴市友好客户订单量>5有boss编码企业数

select count(distinct a.org_id) from
(select from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join (select from shinemo_im.t_org where external_org_id != 0) b on a.org_id=b.id
join (select org_id,count(id) num from shinemo_cmmc.t_order where status>=1 group by org_id) d on d.org_id=a.org_id
join shinemo_cmmc.t_friendly_user e on a.org_id=e.org_id
where d.num>5

//嘉兴市友好客户订单量=0没boss编码企业数

select count(distinct a.org_id) from
(select from shinemo_cube.t_org_base_info where city=’嘉兴’) a
join (select from shinemo_im.t_org where external_org_id = 0 or external_org_id is null) b on a.org_id=b.id
join shinemo_cmmc.t_friendly_user e on a.org_id=e.org_id
left join (select distinct org_id from shinemo_cmmc.t_order where status>=1 ) d on d.org_id=a.org_id
where d.org_id is null

//2020年日最高活跃数

select max(b) from (select max(a) b from (select count(distinct uid) a from report.t_login_log_202001 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202002 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202003 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202004 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202005 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202006 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202007 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202008 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202009 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202010 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202011 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’))
UNION ALL
select max(a) b from (select count(distinct uid) a from report.t_login_log_202012 group by DATE_FORMAT(gmt_create,’YYYY-MM-dd’)))

//每月活跃数

select ‘1’ ,count(distinct uid) a from report.t_login_log_202001
UNION ALL
select ‘2’ , count(distinct uid) a from report.t_login_log_202002
UNION ALL
select ‘3’ , count(distinct uid) a from report.t_login_log_202003
UNION ALL
select ‘4’ , count(distinct uid) a from report.t_login_log_202004
UNION ALL
select ‘6’ , count(distinct uid) a from report.t_login_log_202006
UNION ALL
select ‘7’ , count(distinct uid) a from report.t_login_log_202007
UNION ALL
select ‘8’ , count(distinct uid) a from report.t_login_log_202008
UNION ALL
select ‘9’ , count(distinct uid) a from report.t_login_log_202009
UNION ALL
select ‘10’ , count(distinct uid) a from report.t_login_log_202010
UNION ALL
select ‘11’ , count(distinct uid) a from report.t_login_log_202011

//esop活跃企业数

select count(a.id) from shinemo_im.t_org a join (select distinct org_id from report.t_login_log_202001
UNION ALL
select distinct org_id from report.t_login_log_202002
UNION ALL
select distinct org_id from report.t_login_log_202003
UNION ALL
select distinct org_id from report.t_login_log_202004
UNION ALL
select distinct org_id from report.t_login_log_202006
UNION ALL
select distinct org_id from report.t_login_log_202007
UNION ALL
select distinct org_id from report.t_login_log_202008
UNION ALL
select distinct org_id from report.t_login_log_202009
UNION ALL
select distinct org_id from report.t_login_log_202010
UNION ALL
select distinct org_id from report.t_login_log_202011) b on a.id=b.org_id where a.level_id like ‘%A%’ and a.external_org_id != ‘’ and a.external_org_id !=0

esop-A类活跃企业数

select count(distinct a.id) from shinemo_im.t_org a join (select distinct org_id from report.t_login_log_202001
UNION ALL
select distinct org_id from report.t_login_log_202002
UNION ALL
select distinct org_id from report.t_login_log_202003
UNION ALL
select distinct org_id from report.t_login_log_202004
UNION ALL
select distinct org_id from report.t_login_log_202006
UNION ALL
select distinct org_id from report.t_login_log_202007
UNION ALL
select distinct org_id from report.t_login_log_202008
UNION ALL
select distinct org_id from report.t_login_log_202009
UNION ALL
select distinct org_id from report.t_login_log_202010
UNION ALL
select distinct org_id from report.t_login_log_202011) b on a.id=b.org_id where a.level_id like ‘%A%’ and a.external_org_id != ‘’ and a.external_org_id !=0

年度活跃用户数

select count(distinct uid) from (select distinct uid from report.t_login_log_202001
UNION ALL
select distinct uid from report.t_login_log_202002
UNION ALL
select distinct uid from report.t_login_log_202003
UNION ALL
select distinct uid from report.t_login_log_202004
UNION ALL
select distinct uid from report.t_login_log_202006
UNION ALL
select distinct uid from report.t_login_log_202007
UNION ALL
select distinct uid from report.t_login_log_202008
UNION ALL
select distinct uid from report.t_login_log_202009
UNION ALL
select distinct uid from report.t_login_log_202010
UNION ALL
select distinct uid from report.t_login_log_202011)

开放注册企业成员共计

select count(distinct a.uid) from shinemo_im.t_org_uid a join (select id from shinemo_im.t_org where external_org_id = ‘’ or external_org_id =0) b on a.org_id=b.id

各地市11月活跃人数(渝企信)

select d.city,count(distinct a.uid) from shinemo_cmdata.t_user_mobile a join shinemo_im.t_org_uid b on a.uid = b.uid join report.t_login_log_202011 c on a.uid=c.uid join shinemo_cube.t_org_base_info d on a.org_id=d.org_id where b.gmt_create>’2020-07-01 00:00:00.0’ and a.cm_mobile_num >= 1 group by d.city

各地市激活总人数(渝企信)

select d.city,count(distinct a.uid) from shinemo_cmdata.t_user_mobile a join report.t_login_first_log_new b on a.uid = b.uid join shinemo_cube.t_org_base_info d on a.org_id=d.org_id where b.gmt_create>’2020-07-01 00:00:00.0’ and a.cm_mobile_num >= 1 group by d.city

各地市总成员数(渝企信)

select d.city,count(distinct a.uid) from shinemo_cmdata.t_user_mobile a join shinemo_im.t_org_uid b on a.uid = b.uid join shinemo_cube.t_org_base_info d on a.org_id=d.org_id where b.gmt_create>’2020-07-01 00:00:00.0’ and a.cm_mobile_num >= 1 group by d.city

11月功能使用企业数据

SELECT a.type as 功能模块,b.name AS 企业名称, c.name AS 管理员姓名, c.mobile AS 管理员手机号, d.dept_name AS 部门名称, d.title AS 职务
FROM (
SELECT DISTINCT org_id, ‘工资条’ AS type
FROM shinemo_im.t_salary_publish
WHERE to_date(gmt_create) BETWEEN ‘2020-11-01’ AND ‘2020-11-30’
UNION ALL
SELECT DISTINCT org_id, event_number AS type
FROM shinemo_log.t_dot_log_202011
WHERE mid = 137
AND event_number IN (‘签到’, ‘审批’, ‘每日健康打卡’, ‘工作报告’)
) a
JOIN shinemo_cube.t_org_base_info b ON a.org_id = b.org_id
JOIN shinemo_im.t_org_admin c ON c.org_id = a.org_id
LEFT JOIN (
SELECT DISTINCT uid, title, dept_name
FROM shinemo_zjm.t_dept_title
) d
ON d.uid = c.uid order by a.type

提供累计装机激活用户明细能够取到的最新日期，并剔除异网客户，一个sheet大概10万用户。统计字段：地市、区县、集团编码 、集团名称、用户号码

SELECT a.city AS 地市, a.area AS 区县, d.external_org_id AS 集团编码, a.name AS 集团名称, e.mobile1 AS 用户号码
FROM shinemo_cube.t_org_base_info a
JOIN shinemo_im.t_org_uid b ON a.org_id = b.org_id
JOIN report.t_login_first_log_new c ON b.uid = c.uid
JOIN (
SELECT
FROM shinemo_im.t_org
WHERE external_org_id != ‘’
AND external_org_id IS NOT NULL
AND external_org_id != 0
) d
ON d.id = a.org_id
JOIN (
SELECT
FROM shinemo_cmdata.t_user_mobile
WHERE cm_mobile_num < 1
) e
ON a.org_id = e.org_id

11月月活用户明细（剔除异网客户）统计字段：地市、区县、集团编码 、集团名称、用户号码

SELECT a.city AS 地市, a.area AS 区县, d.external_org_id AS 集团编码, a.name AS 集团名称, e.mobile1 AS 用户号码
FROM shinemo_cube.t_org_base_info a
JOIN shinemo_im.t_org_uid b ON a.org_id = b.org_id
JOIN report.t_login_log_202011 c ON b.uid = c.uid
JOIN (
SELECT
FROM shinemo_im.t_org
WHERE external_org_id != ‘’
AND external_org_id IS NOT NULL
AND external_org_id != 0
) d
ON d.id = a.org_id
JOIN (
SELECT
FROM shinemo_cmdata.t_user_mobile
WHERE cm_mobile_num < 1
) e
ON a.org_id = e.org_id

//云盘点击企业数 group by 地市

SELECT c.city as ‘地市’, count(DISTINCT a.org_id) as ‘企业数’
FROM (
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202001
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202002
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202003
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202004
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202005
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202006
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202007
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202008
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202009
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202010
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202011
WHERE event_number = ‘云盘’
UNION ALL
SELECT DISTINCT uid
FROM shinemo_log.t_dot_log_202012
WHERE event_number = ‘云盘’
) a
JOIN shinemo_im.t_org_uid b ON a.uid = b.uid
join shinemo_cube.t_org_base_info c on b.org_id = c.org_id
GROUP BY c.city

//云盘使用记录数量(企业)

SELECT b.city, count(DISTINCT a.org_id)
FROM
shinemo_im.t_clouddisk_file_org a
JOIN shinemo_cube.t_org_base_info b ON a.org_id = b.org_id
GROUP BY b.city

//公文使用记录数量(企业)

SELECT b.city, count(DISTINCT a.org_id)
FROM
shinemo_cube.t_document_common_info a
JOIN shinemo_cube.t_org_base_info b ON a.org_id = b.org_id
GROUP BY b.city

//党建使用记录数量(企业)

SELECT b.city, count(DISTINCT a.org_id)
FROM
shinemo_business.t_party_building_member a
JOIN shinemo_cube.t_org_base_info b ON a.org_id = b.org_id
GROUP BY b.city

湖州11、12、1月活跃电信手机

SELECT DISTINCT a.uid, a.mobile FROM ( SELECT org_id, uid, mobile FROM report.t_login_log_202011 UNION ALL SELECT org_id, uid, mobile FROM report.t_login_log_202012 UNION ALL SELECT org_id, uid, mobile FROM report.t_login_log_202101 ) a JOIN ( SELECT FROM shinemo_cmdata.t_user_mobile WHERE ct_mobile_num >= 1 ) b ON a.uid = b.uid JOIN ( SELECT FROM shinemo_cube.t_org_base_info WHERE city = ‘湖州’ ) c ON a.org_id = c.org_id