join 优化（疑问1的效率为什么比2高：1用时115s，2用时170s）
一般来说join优化有三种方法1，mapjoin；2小表join大表；3join非驱动表添加索引
优化使用了mapjoin，小表join大表（给join表添加索引没有使用）
1
WITH
ins
AS (
SELECT
ins_id, ins_code, ins_name
, TO_DATE(formal_time, 'yyyy/mm/dd') AS formal_time, ins_area_level
, ins_emp_number_level, 1 AS flag, ins_emp_number
FROM dm_ins_info
GROUP BY ins_id,
ins_code,
ins_name,
formal_time,
ins_area_level,
ins_emp_number_level,
ins_emp_number
),
t_marketing_metrics_book_rank
AS (
SELECT ins.ins_id, ins.ins_code, ins.ins_name, tmmbrc.year, tmmbrc.period
, CASE
WHEN book.nums > 1000 THEN '教材等级S'
WHEN book.nums > 500
AND book.nums <= 1000 THEN '教材等级A'
WHEN book.nums > 200
AND book.nums <= 500 THEN '教材等级B'
WHEN book.nums > 20
AND book.nums <= 200 THEN '教材等级C'
WHEN to_char(ins.formal_time, 'yyyymmdd') < tmmbrc.ins_formal_limiting_time
AND COALESCE(book.nums, 0) <= 20 THEN '教材等级D'
WHEN to_char(ins.formal_time, 'yyyymmdd') >= tmmbrc.ins_formal_limiting_time
AND COALESCE(book.nums, 0) <= 20 THEN '教材等级X'
ELSE '-'
END AS book_rank, ins.ins_area_level, ins.ins_emp_number_level
, COALESCE(book.nums, 0) AS book_nums, ins.ins_emp_number
FROM
-- (
-- SELECT ins_id, ins_code, ins_name
-- , TO_DATE(formal_time, 'yyyy/mm/dd') AS formal_time, ins_area_level
-- , ins_emp_number_level, 1 AS flag, ins_emp_number
-- FROM dm_ins_info
-- GROUP BY ins_id,
-- ins_code,
-- ins_name,
-- formal_time,
-- ins_area_level,
-- ins_emp_number_level,
-- ins_emp_number
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, ins_formal_limiting_time, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
FROM (
SELECT *, 1 AS flag
FROM dm_textbook_order
) dtoi
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON dtoi.flag = tmmbrc.flag
WHERE dtoi.line = '主线'
AND dtoi.status != '已取消'
AND tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.goods_name NOT LIKE '%外研英语%'
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period
) book
ON ins.ins_id = book.ins_id
AND tmmbrc.year = book.year
AND tmmbrc.period = book.period
),
-------------------------------------------------------------------------------------------
-------临时表营销指标体系平均每周授课时长分类(tmp_marketing_metrics_sk_duration_week)------------
t_marketing_metrics_sk_duration_week
AS (
SELECT ins.ins_id, tmmbrc.year, tmmbrc.period
, CASE
WHEN COALESCE(duration.avg_duration_hour, 0) < 1 THEN '平均老师周授课时长小于1小时'
WHEN duration.avg_duration_hour >= 1
AND duration.avg_duration_hour < 3 THEN '平均老师周授课时长1-3小时'
WHEN duration.avg_duration_hour >= 3
AND duration.avg_duration_hour < 5 THEN '平均老师周授课时长3-5小时'
WHEN duration.avg_duration_hour >= 5
AND duration.avg_duration_hour < 8 THEN '平均老师周授课时长5-8小时'
WHEN duration.avg_duration_hour >= 8 THEN '平均老师周授课时长8小时以上'
ELSE '-'
END AS avg_duration_rank
, round(COALESCE(duration.avg_duration_hour, 0), 2) AS avg_duration_hour
, round(COALESCE(duration.avg_duration_minute, 0), 0) AS avg_duration_minute
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
AND start_time >= '20170901'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT ins_id, year, period
, SUM(duration_hour) / COUNT(1) AS avg_duration_hour, SUM(duration_minute) / COUNT(1) AS avg_duration_minute
FROM (
SELECT /*+ mapjoin(tmmbrc) */
dbd.ins_id, dbd.user_id, dbd.user_phone, dbd.user_name, dbd.week_of_year
, tmmbrc.year, tmmbrc.period, SUM(duration) / 3600 AS duration_hour, SUM(duration) / 60 as duration_minute
FROM (
select * from dm_beishouke_duration
WHERE page_type = '授课'
AND cooperate_code IN ('合作中', '合作结束')
AND institution_type = '正式'
)
dbd
LEFT OUTER JOIN tmp_marketing_metrics_book_rank_config tmmbrc
ON CAST(dbd.year AS BIGINT) = tmmbrc.year
AND dbd.day >= tmmbrc.start_time
AND dbd.day < tmmbrc.end_time
GROUP BY dbd.ins_id,
dbd.user_id,
dbd.user_phone,
dbd.user_name,
dbd.week_of_year,
tmmbrc.year,
tmmbrc.period
) tmp1
GROUP BY ins_id,
year,
period
) duration
ON ins.ins_id = duration.ins_id
AND tmmbrc.year = duration.year
AND tmmbrc.period = duration.period
),
-------------------------------------------------------------------------------------
----------临时表营销指标体系--健康度(tmp_marketing_metrics_health_degree)--------------
tmp_book_info AS (
SELECT dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
, dtoi.subject_name
FROM (
SELECT *, 1 AS flag
FROM dm_textbook_order
WHERE line = '主线'
AND status != '已取消'
AND goods_name NOT LIKE '%外研英语%'
) dtoi
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON dtoi.flag = tmmbrc.flag
WHERE tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period,
dtoi.subject_name
),
t_marketing_metrics_health_degree
AS (
SELECT ins.ins_id, tmmbrc.year, tmmbrc.period
, CASE
WHEN effect.sp_nums = 1 THEN '购买正式学科数为1'
WHEN total.total_nums <= 20
OR total.total_nums IS NULL
OR effect.sp_nums IS NULL THEN '购买正式学科数为0'
ELSE concat(round((1 - max.max_nums / effect.total_nums) * 100, 1), '%')
END AS expand_rate
, CASE
WHEN ins.ins_emp_number = 0 THEN '机构规模为0'
WHEN total.total_nums IS NULL THEN '该学期教材为0'
ELSE concat(round((total.total_nums / ins.ins_emp_number) * 100, 1) , '%')
END AS permeate_rate
, CASE
WHEN last.last_nums IS NULL THEN '对比学期教材为0'
WHEN total.total_nums IS NULL THEN '-100%'
ELSE concat(round((total.total_nums / last.last_nums - 1) * 100, 1) , '%')
END AS incre_rate
FROM
-- (
-- SELECT ins_id, ins_emp_number, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id,
-- ins_emp_number
-- )
ins
LEFT OUTER JOIN
(
SELECT *, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT ins_id, year, period, SUM(nums) AS total_nums
, COUNT(1) AS sp_nums
FROM tmp_book_info
WHERE nums > 20
GROUP BY ins_id,
year,
period
) effect
ON ins.ins_id = effect.ins_id
AND tmmbrc.year = effect.year
AND tmmbrc.period = effect.period
LEFT OUTER JOIN (
SELECT ins_id, year, period, SUM(nums) AS total_nums
FROM tmp_book_info
GROUP BY ins_id,
year,
period
) total
ON ins.ins_id = total.ins_id
AND tmmbrc.year = total.year
AND tmmbrc.period = total.period
LEFT OUTER JOIN (
SELECT ins_id, year, period, SUM(nums) AS last_nums
FROM tmp_book_info
GROUP BY ins_id,
year,
period
) last
ON ins.ins_id = last.ins_id
AND last.year = tmmbrc.last_year
AND last.period = tmmbrc.last_period
LEFT OUTER JOIN (
SELECT ins_id, year, period, MAX(nums) AS max_nums
FROM tmp_book_info
WHERE nums > 20
GROUP BY ins_id,
year,
period
) max
ON ins.ins_id = max.ins_id
AND max.year = tmmbrc.year
AND max.period = tmmbrc.period
) ,
-------------------------------------------------------------------------------------------
-----------临时表营销指标体系--服务度--师训服务（tmp_marketing_metrics_service_degree_tt）----
tmp_marketing_metrics_tt_nums
as (
-- 获取机构每学期参加师训教师人数
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, tmp1.ins_id,
COUNT(DISTINCT user_id) AS nums
FROM (
SELECT user_id, ins_id
, CASE
WHEN course_type = '现场' THEN course_start_time
WHEN course_type = '点播' THEN apply_time
WHEN course_type = '直播'
AND course_start_time <= apply_time THEN apply_time
WHEN course_type = '直播'
AND course_start_time > apply_time THEN course_start_time
ELSE NULL
END AS join_time
FROM dm_train_course_user
WHERE apply_status_desc IN ('已报名成功', '后台补报')
AND institution_type = '正式'
AND ins_cooperate_state = '合作中'
) tmp1
LEFT OUTER JOIN tmp_marketing_metrics_book_rank_config tmmbrc
ON TO_CHAR(join_time, 'yyyymmdd') >= tmmbrc.start_time
AND TO_CHAR(join_time, 'yyyymmdd') < tmmbrc.end_time
GROUP BY tmmbrc.year,
tmmbrc.period,
tmp1.ins_id
),
t_marketing_metrics_acc_nums AS (
--机构老师账号数量统计
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, daa.institution_id,
COUNT(DISTINCT teacher_id) AS nums
FROM dm_account_all daa
LEFT OUTER JOIN tmp_marketing_metrics_book_rank_config tmmbrc
ON to_char(daa.end_time, 'yyyymmdd') >= tmmbrc.start_time
AND to_char(daa.start_time, 'yyyymmdd') < tmmbrc.end_time
WHERE daa.statistical_status = 1
GROUP BY daa.institution_id,
tmmbrc.year,
tmmbrc.period
),
t_marketing_metrics_service_degree_tt
AS
(
SELECT ins.ins_id, tmmbrc.year, tmmbrc.period
, CASE
WHEN tmmtn.nums / tmman.nums < 0.5 THEN '一星服务'
WHEN tmmtn.nums / tmman.nums >= 0.5
AND tmmtn.nums / tmman.nums < 1 THEN '二星服务'
WHEN tmmtn.nums / tmman.nums >= 1
AND tmmtn.nums / tmman.nums < 1.5 THEN '三星服务'
WHEN tmmtn.nums / tmman.nums >= 1.5
AND tmmtn.nums / tmman.nums < 2 THEN '四星服务'
WHEN tmmtn.nums / tmman.nums >= 2 THEN '五星服务'
WHEN tmmtn.nums IS NULL AND tmman.nums IS not NULL THEN '未参加师训'
WHEN tmman.nums IS NULL AND tmmtn.nums IS NOT NULL THEN '无教师账号'
WHEN tmman.nums IS NULL AND tmmtn.nums IS NULL THEN '无账号未师训'
ELSE '-'
END AS tt_rank
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN tmp_marketing_metrics_tt_nums tmmtn
ON ins.ins_id = tmmtn.ins_id
AND tmmbrc.year = tmmtn.year
AND tmmbrc.period = tmmtn.period
LEFT OUTER JOIN t_marketing_metrics_acc_nums tmman
ON ins.ins_id = tmman.institution_id
AND tmmbrc.year = tmman.year
AND tmmbrc.period = tmman.period
),
--------------------------------------------------------------------------------------------
-----临时表营销指标体系--AS服务度机构数量分布及占比(tmp_marketing_metrics_service_degree_as)----
t_marketing_metrics_service_degree_as
AS (
SELECT /*+ mapjoin(ins,tmmbrc) */
ins.ins_id, tmmbrc.year, tmmbrc.period,
CASE
WHEN as_user.nums IS NULL AND book.nums IS NULL THEN '未使用AS及教材'
WHEN as_user.nums IS NULL AND book.nums IS NOT NULL THEN '未使用AS'
WHEN book.nums IS NULL AND as_user.nums IS NOT NULL THEN '未使用教材'
WHEN as_user.nums / book.nums < 0.5 THEN '一星服务'
WHEN as_user.nums / book.nums >= 0.5
AND as_user.nums / book.nums < 1 THEN '二星服务'
WHEN as_user.nums / book.nums >= 1
AND as_user.nums / book.nums < 1.5 THEN '三星服务'
WHEN as_user.nums / book.nums >= 1.5
AND as_user.nums / book.nums < 2 THEN '四星服务'
WHEN as_user.nums / book.nums >= 2 THEN '五星服务'
ELSE '-'
END AS as_rank
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
FROM
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
LEFT OUTER JOIN
(
SELECT *, 1 AS flag
FROM dm_textbook_order
WHERE line = '主线'
AND status != '已取消'
AND goods_name NOT LIKE '%外研英语%'
) dtoi
ON dtoi.flag = tmmbrc.flag
WHERE tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period
) book
ON ins.ins_id = book.ins_id
AND tmmbrc.year = book.year
AND tmmbrc.period = book.period
LEFT OUTER JOIN (
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, dshin.institution_id,
COUNT(DISTINCT dshin.student_id) AS nums
FROM(
select institution_id,student_id,period_name,time
from dm_student_homework_info_new
where day = '${bdp.system.bizdate}'
) dshin
LEFT OUTER JOIN
(
select * from
tmp_marketing_metrics_book_rank_config
where period IS NOT NULL
) tmmbrc
ON SUBSTR(tmmbrc.period, 1, 1) = dshin.period_name
AND to_char(time, 'yyyymmdd') >= tmmbrc.start_time
AND to_char(time, 'yyyymmdd') < tmmbrc.end_time
-- WHERE dshin.day = '${bdp.system.bizdate}' AND tmmbrc.period IS NOT NULL
GROUP BY tmmbrc.year,
tmmbrc.period,
dshin.institution_id
) as_user
ON ins.ins_id = as_user.institution_id
AND tmmbrc.year = as_user.year
AND tmmbrc.period = as_user.period
),
--------------------------------------------------------------------------------------------
--临时表营销指标体系--答题器服务度机构数量分布及占比（tmp_marketing_metrics_service_degree_i_clicker）
t_marketing_metrics_service_degree_i_clicker
AS (
SELECT /*+ mapjoin(ins,tmmbrc) */
ins.ins_id, tmmbrc.year, tmmbrc.period,
CASE
WHEN i_clicker.nums IS NULL AND book.nums IS NULL THEN '未使用答题器及教材'
WHEN i_clicker.nums IS NULL AND book.nums IS NOT NULL THEN '未使用答题器'
WHEN book.nums IS NULL AND i_clicker.nums IS NOT NULL THEN '未使用教材'
WHEN i_clicker.nums / book.nums < 0.5 THEN '一星服务'
WHEN i_clicker.nums / book.nums >= 0.5
AND i_clicker.nums / book.nums < 1 THEN '二星服务'
WHEN i_clicker.nums / book.nums >= 1
AND i_clicker.nums / book.nums < 1.5 THEN '三星服务'
WHEN i_clicker.nums / book.nums >= 1.5
AND i_clicker.nums / book.nums < 2 THEN '四星服务'
WHEN i_clicker.nums / book.nums >= 2 THEN '五星服务'
ELSE '-'
END AS i_clicker_rank
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT /*+ mapjoin(tmmbrc)*/
dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
FROM (
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
LEFT OUTER JOIN
(
SELECT *, 1 AS flag
FROM dm_textbook_order
WHERE line = '主线'
AND status != '已取消'
AND goods_name NOT LIKE '%外研英语%'
) dtoi
ON dtoi.flag = tmmbrc.flag
WHERE tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period
) book
ON ins.ins_id = book.ins_id
AND tmmbrc.year = book.year
AND tmmbrc.period = book.period
LEFT OUTER JOIN(
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, ddsa.ins_id,
COUNT(DISTINCT ddsa.student_id) AS nums
FROM (
select * from tmp_marketing_metrics_book_rank_config
where period IS NOT NULL
) tmmbrc
LEFT OUTER JOIN dm_dt_schedule_answer ddsa
ON SUBSTR(tmmbrc.period, 1, 1) = ddsa.period
AND to_char(ddsa.start_time, 'yyyymmdd') >= tmmbrc.start_time
AND to_char(ddsa.start_time, 'yyyymmdd') < tmmbrc.end_time
GROUP BY tmmbrc.year,
tmmbrc.period,
ddsa.ins_id,
ddsa.period
) i_clicker
ON ins.ins_id = i_clicker.ins_id
AND tmmbrc.year = i_clicker.year
AND tmmbrc.period = i_clicker.period
)
--------------------------------------营销指标体系-------------------------------------------
INSERT OVERWRITE TABLE bi_marketing_metrics_ins_info
SELECT
book.ins_id, book.ins_code, book.ins_name, book.year, book.period
, book.book_rank, book.ins_area_level, book.ins_emp_number_level
, COALESCE(sk_duration.avg_duration_rank, '-'), health.expand_rate
, health.permeate_rate, health.incre_rate, tt.tt_rank, asd.as_rank, i_clicker.i_clicker_rank
, book.book_nums, cast(book.ins_emp_number as bigint) as ins_emp_number, COALESCE(sk_duration.avg_duration_hour, 0) AS avg_duration_hour
,cast(COALESCE(sk_duration.avg_duration_minute, 0) as bigint)AS avg_duration_minute
FROM t_marketing_metrics_book_rank book
LEFT OUTER JOIN t_marketing_metrics_sk_duration_week sk_duration
ON book.ins_id = sk_duration.ins_id
AND book.year = sk_duration.year
AND book.period = sk_duration.period
LEFT OUTER JOIN t_marketing_metrics_health_degree health
ON book.ins_id = health.ins_id
AND book.year = health.year
AND book.period = health.period
LEFT OUTER JOIN t_marketing_metrics_service_degree_tt tt
ON book.ins_id = tt.ins_id
AND book.year = tt.year
AND book.period = tt.period
LEFT OUTER JOIN t_marketing_metrics_service_degree_as asd
ON book.ins_id = asd.ins_id
AND book.year = asd.year
AND book.period = asd.period
LEFT OUTER JOIN t_marketing_metrics_service_degree_i_clicker i_clicker
ON book.ins_id = i_clicker.ins_id
AND book.year = i_clicker.year
AND book.period = i_clicker.period;
2
WITH
ins
AS (
SELECT
ins_id, ins_code, ins_name
, TO_DATE(formal_time, 'yyyy/mm/dd') AS formal_time, ins_area_level
, ins_emp_number_level, 1 AS flag, ins_emp_number
FROM dm_ins_info
GROUP BY ins_id,
ins_code,
ins_name,
formal_time,
ins_area_level,
ins_emp_number_level,
ins_emp_number
),
t_marketing_metrics_book_rank
AS (
SELECT ins.ins_id, ins.ins_code, ins.ins_name, tmmbrc.year, tmmbrc.period
, CASE
WHEN book.nums > 1000 THEN '教材等级S'
WHEN book.nums > 500
AND book.nums <= 1000 THEN '教材等级A'
WHEN book.nums > 200
AND book.nums <= 500 THEN '教材等级B'
WHEN book.nums > 20
AND book.nums <= 200 THEN '教材等级C'
WHEN to_char(ins.formal_time, 'yyyymmdd') < tmmbrc.ins_formal_limiting_time
AND COALESCE(book.nums, 0) <= 20 THEN '教材等级D'
WHEN to_char(ins.formal_time, 'yyyymmdd') >= tmmbrc.ins_formal_limiting_time
AND COALESCE(book.nums, 0) <= 20 THEN '教材等级X'
ELSE '-'
END AS book_rank, ins.ins_area_level, ins.ins_emp_number_level
, COALESCE(book.nums, 0) AS book_nums, ins.ins_emp_number
FROM
-- (
-- SELECT ins_id, ins_code, ins_name
-- , TO_DATE(formal_time, 'yyyy/mm/dd') AS formal_time, ins_area_level
-- , ins_emp_number_level, 1 AS flag, ins_emp_number
-- FROM dm_ins_info
-- GROUP BY ins_id,
-- ins_code,
-- ins_name,
-- formal_time,
-- ins_area_level,
-- ins_emp_number_level,
-- ins_emp_number
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, ins_formal_limiting_time, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
FROM (
SELECT *, 1 AS flag
FROM dm_textbook_order
) dtoi
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON dtoi.flag = tmmbrc.flag
WHERE dtoi.line = '主线'
AND dtoi.status != '已取消'
AND tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.goods_name NOT LIKE '%外研英语%'
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period
) book
ON ins.ins_id = book.ins_id
AND tmmbrc.year = book.year
AND tmmbrc.period = book.period
),
-------------------------------------------------------------------------------------------
-------临时表营销指标体系平均每周授课时长分类(tmp_marketing_metrics_sk_duration_week)------------
t_marketing_metrics_sk_duration_week
AS (
SELECT ins.ins_id, tmmbrc.year, tmmbrc.period
, CASE
WHEN COALESCE(duration.avg_duration_hour, 0) < 1 THEN '平均老师周授课时长小于1小时'
WHEN duration.avg_duration_hour >= 1
AND duration.avg_duration_hour < 3 THEN '平均老师周授课时长1-3小时'
WHEN duration.avg_duration_hour >= 3
AND duration.avg_duration_hour < 5 THEN '平均老师周授课时长3-5小时'
WHEN duration.avg_duration_hour >= 5
AND duration.avg_duration_hour < 8 THEN '平均老师周授课时长5-8小时'
WHEN duration.avg_duration_hour >= 8 THEN '平均老师周授课时长8小时以上'
ELSE '-'
END AS avg_duration_rank
, round(COALESCE(duration.avg_duration_hour, 0), 2) AS avg_duration_hour
, round(COALESCE(duration.avg_duration_minute, 0), 0) AS avg_duration_minute
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
AND start_time >= '20170901'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT ins_id, year, period
, SUM(duration_hour) / COUNT(1) AS avg_duration_hour, SUM(duration_minute) / COUNT(1) AS avg_duration_minute
FROM (
SELECT /*+ mapjoin(tmmbrc) */
dbd.ins_id, dbd.user_id, dbd.user_phone, dbd.user_name, dbd.week_of_year
, tmmbrc.year, tmmbrc.period, SUM(duration) / 3600 AS duration_hour, SUM(duration) / 60 as duration_minute
FROM (
select * from dm_beishouke_duration
WHERE page_type = '授课'
AND cooperate_code IN ('合作中', '合作结束')
AND institution_type = '正式'
)
dbd
LEFT OUTER JOIN tmp_marketing_metrics_book_rank_config tmmbrc
ON CAST(dbd.year AS BIGINT) = tmmbrc.year
AND dbd.day >= tmmbrc.start_time
AND dbd.day < tmmbrc.end_time
GROUP BY dbd.ins_id,
dbd.user_id,
dbd.user_phone,
dbd.user_name,
dbd.week_of_year,
tmmbrc.year,
tmmbrc.period
) tmp1
GROUP BY ins_id,
year,
period
) duration
ON ins.ins_id = duration.ins_id
AND tmmbrc.year = duration.year
AND tmmbrc.period = duration.period
),
-------------------------------------------------------------------------------------
----------临时表营销指标体系--健康度(tmp_marketing_metrics_health_degree)--------------
tmp_book_info AS (
SELECT dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
, dtoi.subject_name
FROM (
SELECT *, 1 AS flag
FROM dm_textbook_order
WHERE line = '主线'
AND status != '已取消'
AND goods_name NOT LIKE '%外研英语%'
) dtoi
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON dtoi.flag = tmmbrc.flag
WHERE tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period,
dtoi.subject_name
),
t_marketing_metrics_health_degree
AS (
SELECT ins.ins_id, tmmbrc.year, tmmbrc.period
, CASE
WHEN effect.sp_nums = 1 THEN '购买正式学科数为1'
WHEN total.total_nums <= 20
OR total.total_nums IS NULL
OR effect.sp_nums IS NULL THEN '购买正式学科数为0'
ELSE concat(round((1 - max.max_nums / effect.total_nums) * 100, 1), '%')
END AS expand_rate
, CASE
WHEN ins.ins_emp_number = 0 THEN '机构规模为0'
WHEN total.total_nums IS NULL THEN '该学期教材为0'
ELSE concat(round((total.total_nums / ins.ins_emp_number) * 100, 1) , '%')
END AS permeate_rate
, CASE
WHEN last.last_nums IS NULL THEN '对比学期教材为0'
WHEN total.total_nums IS NULL THEN '-100%'
ELSE concat(round((total.total_nums / last.last_nums - 1) * 100, 1) , '%')
END AS incre_rate
FROM
-- (
-- SELECT ins_id, ins_emp_number, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id,
-- ins_emp_number
-- )
ins
LEFT OUTER JOIN
(
SELECT *, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT ins_id, year, period, SUM(nums) AS total_nums
, COUNT(1) AS sp_nums
FROM tmp_book_info
WHERE nums > 20
GROUP BY ins_id,
year,
period
) effect
ON ins.ins_id = effect.ins_id
AND tmmbrc.year = effect.year
AND tmmbrc.period = effect.period
LEFT OUTER JOIN (
SELECT ins_id, year, period, SUM(nums) AS total_nums
FROM tmp_book_info
GROUP BY ins_id,
year,
period
) total
ON ins.ins_id = total.ins_id
AND tmmbrc.year = total.year
AND tmmbrc.period = total.period
LEFT OUTER JOIN (
SELECT ins_id, year, period, SUM(nums) AS last_nums
FROM tmp_book_info
GROUP BY ins_id,
year,
period
) last
ON ins.ins_id = last.ins_id
AND last.year = tmmbrc.last_year
AND last.period = tmmbrc.last_period
LEFT OUTER JOIN (
SELECT ins_id, year, period, MAX(nums) AS max_nums
FROM tmp_book_info
WHERE nums > 20
GROUP BY ins_id,
year,
period
) max
ON ins.ins_id = max.ins_id
AND max.year = tmmbrc.year
AND max.period = tmmbrc.period
) ,
-------------------------------------------------------------------------------------------
-----------临时表营销指标体系--服务度--师训服务（tmp_marketing_metrics_service_degree_tt）----
tmp_marketing_metrics_tt_nums
as (
-- 获取机构每学期参加师训教师人数
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, tmp1.ins_id,
COUNT(DISTINCT user_id) AS nums
FROM (
SELECT user_id, ins_id
, CASE
WHEN course_type = '现场' THEN course_start_time
WHEN course_type = '点播' THEN apply_time
WHEN course_type = '直播'
AND course_start_time <= apply_time THEN apply_time
WHEN course_type = '直播'
AND course_start_time > apply_time THEN course_start_time
ELSE NULL
END AS join_time
FROM dm_train_course_user
WHERE apply_status_desc IN ('已报名成功', '后台补报')
AND institution_type = '正式'
AND ins_cooperate_state = '合作中'
) tmp1
LEFT OUTER JOIN tmp_marketing_metrics_book_rank_config tmmbrc
ON TO_CHAR(join_time, 'yyyymmdd') >= tmmbrc.start_time
AND TO_CHAR(join_time, 'yyyymmdd') < tmmbrc.end_time
GROUP BY tmmbrc.year,
tmmbrc.period,
tmp1.ins_id
),
t_marketing_metrics_acc_nums AS (
--机构老师账号数量统计
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, daa.institution_id,
COUNT(DISTINCT teacher_id) AS nums
FROM dm_account_all daa
LEFT OUTER JOIN tmp_marketing_metrics_book_rank_config tmmbrc
ON to_char(daa.end_time, 'yyyymmdd') >= tmmbrc.start_time
AND to_char(daa.start_time, 'yyyymmdd') < tmmbrc.end_time
WHERE daa.statistical_status = 1
GROUP BY daa.institution_id,
tmmbrc.year,
tmmbrc.period
),
t_marketing_metrics_service_degree_tt
AS
(
SELECT ins.ins_id, tmmbrc.year, tmmbrc.period
, CASE
WHEN tmmtn.nums / tmman.nums < 0.5 THEN '一星服务'
WHEN tmmtn.nums / tmman.nums >= 0.5
AND tmmtn.nums / tmman.nums < 1 THEN '二星服务'
WHEN tmmtn.nums / tmman.nums >= 1
AND tmmtn.nums / tmman.nums < 1.5 THEN '三星服务'
WHEN tmmtn.nums / tmman.nums >= 1.5
AND tmmtn.nums / tmman.nums < 2 THEN '四星服务'
WHEN tmmtn.nums / tmman.nums >= 2 THEN '五星服务'
WHEN tmmtn.nums IS NULL AND tmman.nums IS not NULL THEN '未参加师训'
WHEN tmman.nums IS NULL AND tmmtn.nums IS NOT NULL THEN '无教师账号'
WHEN tmman.nums IS NULL AND tmmtn.nums IS NULL THEN '无账号未师训'
ELSE '-'
END AS tt_rank
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN tmp_marketing_metrics_tt_nums tmmtn
ON ins.ins_id = tmmtn.ins_id
AND tmmbrc.year = tmmtn.year
AND tmmbrc.period = tmmtn.period
LEFT OUTER JOIN t_marketing_metrics_acc_nums tmman
ON ins.ins_id = tmman.institution_id
AND tmmbrc.year = tmman.year
AND tmmbrc.period = tmman.period
),
--------------------------------------------------------------------------------------------
-----临时表营销指标体系--AS服务度机构数量分布及占比(tmp_marketing_metrics_service_degree_as)----
t_marketing_metrics_service_degree_as
AS (
SELECT /*+ mapjoin(ins,tmmbrc) */
ins.ins_id, tmmbrc.year, tmmbrc.period,
CASE
WHEN as_user.nums IS NULL AND book.nums IS NULL THEN '未使用AS及教材'
WHEN as_user.nums IS NULL AND book.nums IS NOT NULL THEN '未使用AS'
WHEN book.nums IS NULL AND as_user.nums IS NOT NULL THEN '未使用教材'
WHEN as_user.nums / book.nums < 0.5 THEN '一星服务'
WHEN as_user.nums / book.nums >= 0.5
AND as_user.nums / book.nums < 1 THEN '二星服务'
WHEN as_user.nums / book.nums >= 1
AND as_user.nums / book.nums < 1.5 THEN '三星服务'
WHEN as_user.nums / book.nums >= 1.5
AND as_user.nums / book.nums < 2 THEN '四星服务'
WHEN as_user.nums / book.nums >= 2 THEN '五星服务'
ELSE '-'
END AS as_rank
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
FROM
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
LEFT OUTER JOIN
(
SELECT *, 1 AS flag
FROM dm_textbook_order
WHERE line = '主线'
AND status != '已取消'
AND goods_name NOT LIKE '%外研英语%'
) dtoi
ON dtoi.flag = tmmbrc.flag
WHERE tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period
) book
ON ins.ins_id = book.ins_id
AND tmmbrc.year = book.year
AND tmmbrc.period = book.period
LEFT OUTER JOIN (
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, dshin.institution_id,
COUNT(DISTINCT dshin.student_id) AS nums
FROM(
select institution_id,student_id,period_name,time
from dm_student_homework_info_new
where day = '${bdp.system.bizdate}'
) dshin
LEFT OUTER JOIN
(
select * from
tmp_marketing_metrics_book_rank_config
where period IS NOT NULL
) tmmbrc
ON SUBSTR(tmmbrc.period, 1, 1) = dshin.period_name
AND to_char(time, 'yyyymmdd') >= tmmbrc.start_time
AND to_char(time, 'yyyymmdd') < tmmbrc.end_time
-- WHERE dshin.day = '${bdp.system.bizdate}' AND tmmbrc.period IS NOT NULL
GROUP BY tmmbrc.year,
tmmbrc.period,
dshin.institution_id
) as_user
ON ins.ins_id = as_user.institution_id
AND tmmbrc.year = as_user.year
AND tmmbrc.period = as_user.period
),
--------------------------------------------------------------------------------------------
--临时表营销指标体系--答题器服务度机构数量分布及占比（tmp_marketing_metrics_service_degree_i_clicker）
t_marketing_metrics_service_degree_i_clicker
AS (
SELECT /*+ mapjoin(ins,tmmbrc) */
ins.ins_id, tmmbrc.year, tmmbrc.period,
CASE
WHEN i_clicker.nums IS NULL AND book.nums IS NULL THEN '未使用答题器及教材'
WHEN i_clicker.nums IS NULL AND book.nums IS NOT NULL THEN '未使用答题器'
WHEN book.nums IS NULL AND i_clicker.nums IS NOT NULL THEN '未使用教材'
WHEN i_clicker.nums / book.nums < 0.5 THEN '一星服务'
WHEN i_clicker.nums / book.nums >= 0.5
AND i_clicker.nums / book.nums < 1 THEN '二星服务'
WHEN i_clicker.nums / book.nums >= 1
AND i_clicker.nums / book.nums < 1.5 THEN '三星服务'
WHEN i_clicker.nums / book.nums >= 1.5
AND i_clicker.nums / book.nums < 2 THEN '四星服务'
WHEN i_clicker.nums / book.nums >= 2 THEN '五星服务'
ELSE '-'
END AS i_clicker_rank
FROM
-- (
-- SELECT ins_id, 1 AS flag
-- FROM dm_ins_info
-- GROUP BY ins_id
-- )
ins
LEFT OUTER JOIN
(
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
ON ins.flag = tmmbrc.flag
LEFT OUTER JOIN (
SELECT /*+ mapjoin(tmmbrc)*/
dtoi.ins_id, SUM(dtoi.num) AS nums, tmmbrc.year, tmmbrc.period
FROM (
SELECT year, period, 1 AS flag
FROM tmp_marketing_metrics_book_rank_config
WHERE start_time <= '${bdp.system.bizdate}'
)
tmmbrc
LEFT OUTER JOIN
(
SELECT *, 1 AS flag
FROM dm_textbook_order
WHERE line = '主线'
AND status != '已取消'
AND goods_name NOT LIKE '%外研英语%'
) dtoi
ON dtoi.flag = tmmbrc.flag
WHERE tmmbrc.period IN (dtoi.spring_season, dtoi.summer_season, dtoi.autumn_season, dtoi.winter_season)
AND dtoi.textbook_order_year = tmmbrc.year
GROUP BY dtoi.ins_id,
tmmbrc.year,
tmmbrc.period
) book
ON ins.ins_id = book.ins_id
AND tmmbrc.year = book.year
AND tmmbrc.period = book.period
LEFT OUTER JOIN(
SELECT /*+ mapjoin(tmmbrc) */
tmmbrc.year, tmmbrc.period, ddsa.ins_id,
COUNT(DISTINCT ddsa.student_id) AS nums
FROM (
select * from tmp_marketing_metrics_book_rank_config
where period IS NOT NULL
) tmmbrc
LEFT OUTER JOIN dm_dt_schedule_answer ddsa
ON SUBSTR(tmmbrc.period, 1, 1) = ddsa.period
AND to_char(ddsa.start_time, 'yyyymmdd') >= tmmbrc.start_time
AND to_char(ddsa.start_time, 'yyyymmdd') < tmmbrc.end_time
GROUP BY tmmbrc.year,
tmmbrc.period,
ddsa.ins_id,
ddsa.period
) i_clicker
ON ins.ins_id = i_clicker.ins_id
AND tmmbrc.year = i_clicker.year
AND tmmbrc.period = i_clicker.period
)
--------------------------------------营销指标体系-------------------------------------------
INSERT OVERWRITE TABLE bi_marketing_metrics_ins_info
SELECT
book.ins_id, book.ins_code, book.ins_name, book.year, book.period
, book.book_rank, book.ins_area_level, book.ins_emp_number_level
, COALESCE(sk_duration.avg_duration_rank, '-'), health.expand_rate
, health.permeate_rate, health.incre_rate, tt.tt_rank, asd.as_rank, i_clicker.i_clicker_rank
, book.book_nums, cast(book.ins_emp_number as bigint) as ins_emp_number, COALESCE(sk_duration.avg_duration_hour, 0) AS avg_duration_hour
,cast(COALESCE(sk_duration.avg_duration_minute, 0) as bigint)AS avg_duration_minute
FROM t_marketing_metrics_book_rank book
LEFT OUTER JOIN t_marketing_metrics_sk_duration_week sk_duration
ON book.ins_id = sk_duration.ins_id
AND book.year = sk_duration.year
AND book.period = sk_duration.period
LEFT OUTER JOIN t_marketing_metrics_health_degree health
ON book.ins_id = health.ins_id
AND book.year = health.year
AND book.period = health.period
LEFT OUTER JOIN t_marketing_metrics_service_degree_tt tt
ON book.ins_id = tt.ins_id
AND book.year = tt.year
AND book.period = tt.period
LEFT OUTER JOIN t_marketing_metrics_service_degree_as asd
ON book.ins_id = asd.ins_id
AND book.year = asd.year
AND book.period = asd.period
LEFT OUTER JOIN t_marketing_metrics_service_degree_i_clicker i_clicker
ON book.ins_id = i_clicker.ins_id
AND book.year = i_clicker.year
AND book.period = i_clicker.period;
1593
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
