SELECT
tmp.employee_name,
tmp.stat_month,
count( DISTINCT tmp.signature ) signature_cnt,
count( DISTINCT tmp.signature_api ) signature_api_cnt,
count( DISTINCT tmp.signature_rpt ) signature_rpt_cnt,
count( DISTINCT tmp.account_id ) account_id_cnt,
sum( tmp.avg_cost ) / 100 cost_m
FROM
(
SELECT
a.signature signature,
b.signature signature_api,
c.signature signature_rpt,
a.employee_name,
a.media_url,
a.cooperate_cnt,
b.url,
b.account_id,
c.cost / 100 cost,
c.cost / 100 / a.cooperate_cnt avg_cost,
c.stat_month
FROM
(-- SELECT signature, employee_name, media_url, cooperate_cnt FROM dw.fact_material_all_material
SELECT
signature,
employee_name,
collect_list ( media_url ) [ 0 ] media_url,
count( DISTINCT employee_name ) AS cooperate_cnt
FROM
dw.fact_material_all_material
WHERE
employee_name IN ${creator_tp}
GROUP BY
signature,
employee_name
) a
LEFT JOIN dw.fact_material_api_material b ON a.signature = b.signature
LEFT JOIN (
SELECT
account_id,
signature,
sum( cost ) AS cost,
substr( stat_date, 1, 7 ) stat_month
FROM
dw.fact_material_all_material_reports_d
WHERE
-- substr( stat_date, 1, 7 ) IN (
-- '2021-05','2021-06', '2021-07' ,'2021-08', '2021-09' ,
-- '2021-10', '2021-11' )
substr( stat_date, 1, 7 ) = '2021-11'
GROUP BY
account_id,
signature,
substr( stat_date, 1, 7 )
) c ON a.signature = c.signature
) tmp
GROUP BY
tmp.employee_name,
tmp.stat_month
//Hive
SELECT
-- a.signature,
-- a.material_create_time,
-- b.create_time,
-- c.first_rpt_date,
-- concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,'')) flag_a
concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,'')) flag_a ,
count(concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,''))) flag_cnt
FROM
(
SELECT
signature,
group_concat(distinct material_src, '\t') material_src_c,
group_concat(distinct annex_type, '\t') annex_type_c ,
group_concat(distinct employee_name, '\t') employee_name_c,
group_concat(distinct employee_belong_to, '\t') employee_belong_to_c,
group_concat(distinct media_url, '\t') media_url_c,
group_concat(distinct material_create_time, '\t') material_create_time,
group_concat(distinct cast(cooperate_cnt as string), '\t') cooperate_cnt_c,
count(DISTINCT employee_name) employee_name_cnt,
'1' flag
FROM
dw.fact_material_all_material
GROUP BY
signature
) a
LEFT JOIN
(
SELECT
signature signature_api,
group_concat(distinct url, '\t') url_api_c ,
group_concat(distinct create_time, '\t') create_time,
count(DISTINCT account_id) account_id_api_cnt,
'2' flag_api
FROM
dw.fact_material_api_material
GROUP BY
signature
) b ON
a.signature = b.signature_api
LEFT JOIN
(
SELECT
t.signature_rpt,
min(t.stat_date) first_rpt_date,
sum(t.cost / 100) cost,
sum(t.show_cnt) show_cnt,
sum(t.click) click,
sum(t.convert_cnt) convert_cnt,
'3' flag_rpt
FROM
(
SELECT
signature signature_rpt,
CAST(cost as bigint) cost ,
CAST(show_cnt AS bigint) show_cnt,
CAST(click AS bigint) click,
CAST(convert_cnt AS bigint) convert_cnt,
account_id,
stat_date
FROM
dw.fact_material_all_material_reports_d
) t
GROUP BY
t.signature_rpt
) c ON
b.signature_api = c.signature_rpt
-- b.signature = c.signature_rpt
GROUP By concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,''))
| flag_a |
flag_cnt |
| 1 |
496379 |
| 12 |
140767 |
| 123 |
244411 |
SELECT
concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,'')) flag,
count(concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,''))) flag_cnt
-- a.signature,
-- a.material_create_time,
-- b.create_time,
-- c.first_rpt_date,
-- concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,'')) flag_a
FROM
(
SELECT
signature,
group_concat(distinct material_src, '\t') material_src_c,
group_concat(distinct annex_type, '\t') annex_type_c ,
group_concat(distinct employee_name, '\t') employee_name_c,
group_concat(distinct employee_belong_to, '\t') employee_belong_to_c,
group_concat(distinct media_url, '\t') media_url_c,
group_concat(distinct material_create_time, '\t') material_create_time,
group_concat(distinct cast(cooperate_cnt as string), '\t') cooperate_cnt_c,
count(DISTINCT employee_name) employee_name_cnt,
'1' flag
FROM
dw.fact_material_all_material
GROUP BY
signature
) a
FULL OUTER JOIN
(
SELECT
signature signature_api,
group_concat(distinct url, '\t') url_api_c ,
group_concat(distinct create_time, '\t') create_time,
count(DISTINCT account_id) account_id_api_cnt,
'2' flag_api
FROM
dw.fact_material_api_material
GROUP BY
signature
) b ON
a.signature = b.signature_api
FULL OUTER JOIN
(
SELECT
t.signature_rpt,
min(t.stat_date) first_rpt_date,
sum(t.cost / 100) cost,
sum(t.show_cnt) show_cnt,
sum(t.click) click,
sum(t.convert_cnt) convert_cnt,
'3' flag_rpt
FROM
(
SELECT
signature signature_rpt,
CAST(cost as bigint) cost ,
CAST(show_cnt AS bigint) show_cnt,
CAST(click AS bigint) click,
CAST(convert_cnt AS bigint) convert_cnt,
account_id,
stat_date
FROM
dw.fact_material_all_material_reports_d
) t
GROUP BY
t.signature_rpt
) c ON
b.signature_api = c.signature_rpt
-- a.signature = c.signature_rpt
GROUP BY concat_ws('', coalesce(a.flag, ''), coalesce(b.flag_api, ''), coalesce(c.flag_rpt,''))
| flag |
flag_cnt |
| 23 |
3995719 |
| 1 |
496379 |
| 12 |
140767 |
| 2 |
6214754 |
| 123 |
244411 |
| 3 |
1 |
SELECT
total.union_id,
total.employee_name,
total.join_time,
total.effective_date,
total.status,
${stat_month} stat_month ,
sum(total.signature_cnt) signature_cnt,
sum(total.signature_api_cnt) signature_api_cnt,
sum(total.signature_rpt_cnt) signature_rpt_cnt,
sum(total.account_id_cnt) account_id_cnt,
sum(total.creator_cost) creator_cost,
sum(total.m_signature_cnt) m_signature_cnt,
sum(total.m_signature_api_cnt) m_signature_api_cnt,
sum(total.m_signature_rpt_cnt) m_signature_rpt_cnt,
sum(total.m_account_id_cnt) m_account_id_cnt,
sum(total.creator_cost_m) creator_cost_m
FROM (
SELECT
all_m.union_id,
all_m.employee_name,
em.join_time,
em.effective_date, -- invalid_date为9999，status为1，即为离职日期
em.status,
all_m.stat_month,
all_m.signature_cnt,
all_m.signature_api_cnt,
all_m.signature_rpt_cnt,
all_m.account_id_cnt,
all_m.creator_cost,
month_m.m_signature_cnt,
month_m.m_signature_api_cnt,
month_m.m_signature_rpt_cnt,
month_m.m_account_id_cnt,
month_m.creator_cost_m
FROM (
SELECT
tmp.union_id,
tmp.employee_name,
nvl(tmp.stat_month, 'month_null') stat_month,
count( DISTINCT tmp.signature ) signature_cnt,
count( DISTINCT tmp.signature_api ) signature_api_cnt,
count( DISTINCT tmp.signature_rpt ) signature_rpt_cnt,
count( DISTINCT tmp.account_id ) account_id_cnt,
sum( tmp.avg_cost ) creator_cost
FROM
(
SELECT
a.signature signature,
b.signature signature_api,
c.signature signature_rpt,
a.union_id,
a.employee_name,
-- a.media_url,
a.cooperate_cnt,
b.url,
b.account_id,
c.cost / 100 cost,
c.cost / 100 / a.cooperate_cnt avg_cost,
c.stat_month
FROM
(-- SELECT signature, employee_name, media_url, cooperate_cnt
SELECT
signature,
union_id,
employee_name,
-- collect_list ( media_url ) [ 0 ] media_url,
count( DISTINCT employee_name ) AS cooperate_cnt
FROM
dw.fact_material_all_material
--WHERE
-- employee_name IN ${creator_tp}
GROUP BY
signature,
union_id,
employee_name
-- collect_list ( media_url ) [ 0 ]
) a
LEFT JOIN dw.fact_material_api_material b ON a.signature = b.signature
LEFT JOIN (
SELECT
account_id,
signature,
sum( cost ) AS cost,
substr( stat_date, 1, 7 ) stat_month
FROM
dw.fact_material_all_material_reports_d
WHERE
-- substr( stat_date, 1, 7 ) IN ('2021-05','2021-06', '2021-07' ,'2021-08', '2021-09' , '2021-10', '2021-11' )
substr( stat_date, 1, 7 ) = ${stat_month}
GROUP BY
account_id,
signature,
substr( stat_date, 1, 7 )
) c ON a.signature = c.signature
) tmp
GROUP BY
tmp.union_id,
tmp.employee_name,
tmp.stat_month
) all_m LEFT JOIN (
SELECT
tmp.union_id,
tmp.employee_name,
nvl(tmp.stat_month, 'month_null') stat_month,
count( DISTINCT tmp.signature ) m_signature_cnt,
count( DISTINCT tmp.signature_api ) m_signature_api_cnt,
count( DISTINCT tmp.signature_rpt ) m_signature_rpt_cnt,
count( DISTINCT tmp.account_id ) m_account_id_cnt,
sum( tmp.avg_cost ) creator_cost_m
FROM
(
SELECT
a.signature signature,
b.signature signature_api,
c.signature signature_rpt,
a.union_id,
a.employee_name,
-- a.media_url,
a.cooperate_cnt,
b.url,
b.account_id,
c.cost / 100 cost,
c.cost / 100 / a.cooperate_cnt avg_cost,
c.stat_month
FROM
(-- SELECT signature, employee_name, media_url, cooperate_cnt
SELECT
signature,
union_id,
employee_name,
-- collect_list ( media_url ) [ 0 ] media_url,
count( DISTINCT employee_name ) AS cooperate_cnt
FROM
dw.fact_material_all_material
WHERE
-- employee_name IN ${creator_tp} AND
substr( material_create_time, 1, 7 ) = ${stat_month}
GROUP BY
signature,
union_id,
employee_name
-- collect_list ( media_url ) [ 0 ]
) a
LEFT JOIN (SELECT signature, url, account_id FROM dw.fact_material_api_material WHERE substr( create_time, 1, 7 ) = ${stat_month} ) b ON a.signature = b.signature
LEFT JOIN (
SELECT
account_id,
signature,
sum( cost ) AS cost,
substr( stat_date, 1, 7 ) stat_month
FROM
dw.fact_material_all_material_reports_d
WHERE
-- substr( stat_date, 1, 7 ) IN ('2021-05','2021-06', '2021-07' ,'2021-08', '2021-09' , '2021-10', '2021-11' )
substr( stat_date, 1, 7 ) = ${stat_month}
GROUP BY
account_id,
signature,
substr( stat_date, 1, 7 )
) c ON a.signature = c.signature
) tmp
GROUP BY
tmp.union_id,
tmp.employee_name,
tmp.stat_month
) month_m
ON all_m.union_id = month_m.union_id AND all_m.stat_month=month_m.stat_month
LEFT JOIN (SELECT union_id, join_time, status, effective_date, invalid_date FROM dw.dim_crm_employee WHERE invalid_date='9999-12-31' AND employee_belong_to='fs-ecsage-02') em
ON all_m.union_id=em.union_id
) total
GROUP BY total.union_id,
total.employee_name,
total.join_time,
total.effective_date,
total.status