一：
SELECT DISTINCT t1.省份,t1.大区,t1.城市类型,t1.城市 FROM (select 全国 ,大区 ,省份 ,城市 ,城市类型 ,年月 ,avg(新员工数量) as 顾问数量_新 ,avg(老员工数量) as 顾问数量_老 ,avg(在职人数) as 顾问数量_整体 ,sum(新员工发起) as 有效发起_新顾问 ,sum(老员工发起) as 有效发起_老顾问 ,sum(有效发起) 有效发起 ,sum(新员工捐单) as 捐单数量_新顾问 ,sum(老员工捐单) as 捐单数量_老顾问 ,sum(捐单数量) 捐单数量 from (select “全国” 全国 ,a.bd_area 大区 ,a.bd_province 省份 ,a.bd_city 城市 ,a.city_type 城市类型 ,substr(a.mon,1,7) 年月 ,a.mon 年月日 ,count(distinct a.unique_code) 在职人数 ,count(distinct case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then a.unique_code else null end) 新员工数量 ,count(distinct case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then null else a.unique_code end) 老员工数量 ,sum(a.valid_case_cnt) 有效发起 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then a.valid_case_cnt else 0 end) 新员工发起 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then 0 else a.valid_case_cnt end) 老员工发起 ,sum(a.tq_donate_cnt) 捐单数量 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then a.tq_donate_cnt else 0 end) 新员工捐单 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then 0 else a.tq_donate_cnt end) 老员工捐单 from dws_cf_bd_performance_full_d a where a.mon >= ‘2020-10-01’ and a.bd_city is not null and a.bd_province is not null and a.bd_area is not null and (a.leave_time = ‘’ or a.leave_time is null or a.leave_time>=a.mon) group by 全国 ,a.bd_area ,a.bd_province ,a.bd_city ,a.city_type ,substr(a.mon,1,7) ,a.mon) t0 group by 全国 ,大区 ,省份 ,城市 ,城市类型 ,年月 ) t1 limit 999999;
二：
SELECT DISTINCT t1.医院省份,t1.医院城市,t1.医院名称 FROM (select stat_dt ,周日 ,周五 ,顾问所属大区 ,BD组织省区 ,BD组织分区 ,BD所属小组 ,顾问所属省份 ,顾问所属城市 ,顾问职级 , 顾问职级中文 ,一级架构 ,case when 一级架构 = 二级架构 then null else 二级架构 end as 二级架构 ,case when 二级架构 = 三级架构 then null else 三级架构 end as 三级架构 ,case when 三级架构 = 四级架构 then null else 四级架构 end as 四级架构 ,case when 四级架构 = 五级架构 then null else 五级架构 end as 五级架构 ,case when 五级架构 = 六级架构 then null else 六级架构 end as 六级架构 ,case when 六级架构 = 七级架构 then null else 七级架构 end as 七级架构 ,渠道 ,volunteer_name ,volunteer_code ,org_tag ,partner_tag ,医院名称 ,医院省份 ,医院城市 ,发起案例数 ,有效案例数_发生口径 ,捐单量_发生口径 ,捐款金额_发生口径 ,有效发起案例数_同期口径 ,捐单量_同期口径 ,捐单金额_同期口径 from (select stat_dt ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-01’),7) day) as 周日 ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-06’),7) day) as 周五 ,bd_area as 顾问所属大区 ,bd_provincial_area as BD组织省区 ,bd_region as BD组织分区 ,bd_group as BD所属小组 ,bd_province as 顾问所属省份 ,bd_city as 顾问所属城市 ,bd_level as 顾问职级 ,bd_level_name as 顾问职级中文 ,substring_index(substring_index(org_name,’-’,2),’-’,-1) as 一级架构 ,substring_index(substring_index(org_name,’-’,3),’-’,-1) as 二级架构 ,substring_index(substring_index(org_name,’-’,4),’-’,-1) as 三级架构 ,substring_index(substring_index(org_name,’-’,5),’-’,-1) as 四级架构 ,substring_index(substring_index(org_name,’-’,6),’-’,-1) as 五级架构 ,substring_index(substring_index(org_name,’-’,7),’-’,-1) as 六级架构 ,substring_index(substring_index(org_name,’-’,8),’-’,-1) as 七级架构 ,case when channel_group = ‘线上’ then ‘线上’ when channel_type = ‘线下自主’ then ‘线下自主’ when channel_type = ‘线上分流线下’ then ‘线上分流线下’ end as 渠道 ,volunteer_name ,volunteer_code ,org_tag ,partner_tag ,hospital_name as 医院名称,vvhospital_code,hospital_province as 医院省份,hospital_city as 医院城市 ,sum(launch_case_num) as 发起案例数 ,sum(valid_case_num) as 有效案例数_发生口径 ,sum(donate_cnt) as 捐单量_发生口径 ,sum(donate_amt) as 捐款金额_发生口径 ,sum(launch_valid_case_num) as 有效发起案例数_同期口径 ,sum(launch_donate_num) as 捐单量_同期口径 ,sum(launch_donate_amt) as 捐单金额_同期口径 from dws_cf_case_stat_full_d where stat_dt >= ‘2021-01-01’ and org_name not like ‘%测试%’ and org_name is not null group by stat_dt ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-01’),7) day) ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-06’),7) day) ,bd_area ,bd_provincial_area ,bd_region ,bd_group ,bd_province ,bd_city ,bd_level ,bd_level_name ,volunteer_name ,volunteer_code ,org_tag ,partner_tag ,hospital_name,vvhospital_code,hospital_province,hospital_city ,一级架构 ,二级架构 ,三级架构 ,四级架构 ,五级架构 ,六级架构 ,七级架构 ,渠道 )t) t1 limit 999999;
三：
SELECT DATE_FORMAT(SELECT_T_1_.cno_start_time, ‘%Y-%m-%d’) AS 外呼日期(day), SELECT_T_1_.clue_type AS clue_type , COUNT(DISTINCT SELECT_T_1_.call_out_id) AS 外呼次数, COUNT(DISTINCT clue_id) AS 外呼线索数 , COUNT(DISTINCT SELECT_T_1_.呼通线索数) AS 呼通线索数, COUNT(DISTINCT SELECT_T_1_.呼通>30s线索数) AS 呼通>30s线索数 , SUM(SELECT_T_1_.呼通时长mins) AS 呼通时长mins , sum(呼通时长mins) / count(DISTINCT if(呼通线索数 <> 0, clue_id, NULL)) AS 单均通话时长 , count(DISTINCT call_out_id) / count(DISTINCT clue_id) AS 单均拨打次数 FROM ( SELECT * , if(connected_status IN (200, ‘预测外呼, 客户接听’) AND date(assgin_time) = date(connected_time), clue_id, 0) AS 当日分配当日呼通线索数 , if(task_status = 6 AND date(assgin_time) = date(last_handle_time) AND (user_tag IN (‘可发起’) OR first_tag = 44), clue_id, 0) AS 当日分配当日流转线索数 , if(task_status = 6 AND (user_tag IN (‘可发起’) OR first_tag = 44), clue_id, 0) AS 流转线索数 , if(connected_status IN (200, ‘预测外呼, 客户接听’), clue_id, 0) AS 呼通线索数 , if(connected_status IN (200, ‘预测外呼, 客户接听’) AND unix_timestamp(cno_end_time) - unix_timestamp(connected_time) >= 30, clue_id, 0) AS 呼通>30s线索数 , if(connected_status IN (200, ‘预测外呼, 客户接听’), unix_timestamp(cno_end_time) - unix_timestamp(connected_time), 0) / 60 AS 呼通时长mins FROM dwb_cf_clue_call_out_detail_full_d ) SELECT_T_1_ GROUP BY DATE_FORMAT(SELECT_T_1_.cno_start_time, ‘%Y-%m-%d’), SELECT_T_1_.clue_type ORDER BY DATE_FORMAT(SELECT_T_1_.cno_start_time, ‘%Y-%m-%d’) DESC, SELECT_T_1_.clue_type ASC LIMIT 0,200;
四：
SELECT
b.pay_month AS pay_month,
agent_id,
agent_name,
team_id,
team_name,
group_id,
group_name,
round(sum(origin), 2) AS origin,
round(
sum(IF(pay_cnt >= 1, origin, 0)) / sum(origin),
4
) AS 1rate,
round(
sum(IF(pay_cnt >= 2, origin, 0)) / sum(origin),
4
) AS 2rate,
round(
sum(IF(pay_cnt >= 3, origin, 0)) / sum(origin),
4
) AS 3rate,
round(sum(IF(pay_cnt >= 4, origin, 0)) / sum(origin), 4) AS 4rate,
round(
sum(IF(pay_cnt >= 5, origin, 0)) / sum(origin),
4
) AS 5rate,
round(
sum(IF(pay_cnt >= 6, origin, 0)) / sum(origin),
4
) AS 6rate,
round(
sum(IF(pay_cnt >= 7, origin, 0)) / sum(origin),
4
) AS 7rate,
round(
sum(IF(pay_cnt >= 8, origin, 0)) / sum(origin),
4
) AS 8rate,
round(
sum(IF(pay_cnt >= 9, origin, 0)) / sum(origin),
4
) AS 9rate,
round(
sum(IF(pay_cnt >= 10, origin, 0)) / sum(origin),
4
) AS 10rate,
round(
sum(IF(pay_cnt >= 11, origin, 0)) / sum(origin),
4
) AS 11rate,
round(
sum(IF(pay_cnt >= 12, origin, 0)) / sum(origin),
4
) AS 12rate
FROM
(
SELECT
agent_id,
agent_name,
team_id,
team_name,
group_id,
group_name,
substr(policy_time, 1, 7) AS pay_month,
order_no,
money,
origin,
origin / 12 AS per_money,
round(money * 12 / origin, 0) AS pay_cnt,
product_name,
policy_time,
STATUS,
CASE
WHEN pay_limit = 10 THEN ‘10’
WHEN pay_limit > 10
AND pay_limit <= 20 THEN ‘20’
WHEN pay_limit > 20 THEN ‘30’
END AS pay_dist
FROM
rpt_sdb_crm_order_stat_history_d
WHERE
STATUS IN (3, 4, 6, 11, 12)
AND valid = 1
AND order_pay_type IN (6, 8, 10)
AND is_long = 1
AND refund_order_no IS NULL
AND money != 0
AND DATE_FORMAT(policy_time, ‘%Y-%m-%d’) >= ‘2021-05-01’
AND DATE_FORMAT(policy_time, ‘%Y-%m-%d’) <= ‘2021-05-31’
AND agent_id IN (
‘JMTYHFZC_202002141816811’,
‘JMTYHFGLSJZC_20210204152751’,
‘DXJMTYHF_202009251619510’
)
AND team_id IN (432, 736, 1072, 560, 1108, 756, 1288, 345, 559)
AND group_id IN (
2952,
4233,
5386,
5899,
3084,
1166,
1167,
1168,
4240,
1169,
1170,
3986,
5267,
3987,
3093,
2837,
3483,
2844,
3101,
5406,
5407,
2719,
2720,
5922,
2722,
1572,
1575,
1576,
2601,
2473,
1577,
1962,
1963,
1964,
3759,
3760,
3761,
1458,
1971,
1459,
1972,
1973,
5431,
2871,
2872,
3897,
2618,
4411,
2500,
5573,
5574,
2886,
3912,
4044,
4560,
1504,
3168,
2400,
4449,
4454,
5609,
4457,
4458,
3050,
4459,
4460,
2669,
3057,
2292,
4469,
2941,
4478,
4479,
3583
)
) b
GROUP BY
agent_id,
agent_name,
team_id,
team_name,
group_id,
group_name,
b.pay_month
ORDER BY
pay_month ASC,
agent_id ASC;
本来还想建议放缓存，现在看来是不行了~
果然够复杂~
这种bug碰上也不算容易吧。 load base split虽然能spLit region但测试感觉效果不大，分裂region还要看分裂的点是否把热点拆开，还得要能region调度到其他的节点，热点region调度现在好像看的是网络流量，即使分裂后也不能把请求分散。 感觉还是建表时auto_random/shard_row_id/hash分区+pre_split_region最有效