邢台好蜘蛛网站建设,福州网站建设服务商,怎样创建购物网站,网站团队人数系列文章目录
线上问诊#xff1a;业务数据采集 线上问诊#xff1a;数仓数据同步 线上问诊#xff1a;数仓开发(一) 线上问诊#xff1a;数仓开发(二) 线上问诊#xff1a;数仓开发(三) 文章目录 系列文章目录前言一、ADS1.交易主题1.交易综合统计2.各医院交易统计3.各性…系列文章目录
线上问诊业务数据采集 线上问诊数仓数据同步 线上问诊数仓开发(一) 线上问诊数仓开发(二) 线上问诊数仓开发(三) 文章目录 系列文章目录前言一、ADS1.交易主题1.交易综合统计2.各医院交易统计3.各性别患者交易统计4.各年龄段患者交易统计 2.医生主题1.医生变动统计 3.用户主题1.用户变动统计 4.评价主题1.评价综合统计2.各医院评价统计5.数据装载脚本 一、报表数据导出1.MySQL建库建表1.创建数据库2.创建表 2.数据导出1.DataX配置文件生成脚本2.执行配置文件生成器3.编写每日导出脚本 总结 前言
这次我们继续进行数仓的开发应该能写完。 一、ADS
1.交易主题
1.交易综合统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_trade_stats
(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 统计周期: 最近1,7,30日,consultation_amount DECIMAL(16, 2) COMMENT 问诊金额,consultation_count BIGINT COMMENT 问诊次数,consultation_pay_suc_amount DECIMAL(16, 2) COMMENT 问诊支付成功金额,consultation_pay_suc_count BIGINT COMMENT 问诊支付成功次数,prescription_amount DECIMAL(16, 2) COMMENT 处方金额,prescription_count BIGINT COMMENT 处方次数,prescription_pay_suc_amount DECIMAL(16, 2) COMMENT 处方支付成功金额,prescription_pay_suc_count BIGINT COMMENT 处方支付成功次数
) COMMENT 交易综合统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_trade_stats;2.各医院交易统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_hospital_trade_stats
(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 统计周期: 最近1,7,30日,hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,consultation_amount DECIMAL(16, 2) COMMENT 问诊金额,consultation_count BIGINT COMMENT 问诊次数,consultation_pay_suc_amount DECIMAL(16, 2) COMMENT 问诊支付成功金额,consultation_pay_suc_count BIGINT COMMENT 问诊支付成功次数,prescription_amount DECIMAL(16, 2) COMMENT 处方金额,prescription_count BIGINT COMMENT 处方次数,prescription_pay_suc_amount DECIMAL(16, 2) COMMENT 处方支付成功金额,prescription_pay_suc_count BIGINT COMMENT 处方支付成功次数
) COMMENT 各医院交易统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_hospital_trade_stats;3.各性别患者交易统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_gender_trade_stats
(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 统计周期: 最近1,7,30日,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,consultation_amount DECIMAL(16, 2) COMMENT 问诊金额,consultation_count BIGINT COMMENT 问诊次数,consultation_pay_suc_amount DECIMAL(16, 2) COMMENT 问诊支付成功金额,consultation_pay_suc_count BIGINT COMMENT 问诊支付成功次数,prescription_amount DECIMAL(16, 2) COMMENT 处方金额,prescription_count BIGINT COMMENT 处方次数,prescription_pay_suc_amount DECIMAL(16, 2) COMMENT 处方支付成功金额,prescription_pay_suc_count BIGINT COMMENT 处方支付成功次数
) COMMENT 各性别患者交易统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_gender_trade_stats;4.各年龄段患者交易统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_age_group_trade_stats
(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 统计周期: 最近1,7,30日,age_group STRING COMMENT 患者年龄段,consultation_amount DECIMAL(16, 2) COMMENT 问诊金额,consultation_count BIGINT COMMENT 问诊次数,consultation_pay_suc_amount DECIMAL(16, 2) COMMENT 问诊支付成功金额,consultation_pay_suc_count BIGINT COMMENT 问诊支付成功次数,prescription_amount DECIMAL(16, 2) COMMENT 处方金额,prescription_count BIGINT COMMENT 处方次数,prescription_pay_suc_amount DECIMAL(16, 2) COMMENT 处方支付成功金额,prescription_pay_suc_count BIGINT COMMENT 处方支付成功次数
) COMMENT 各年龄段患者交易统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_age_group_trade_stats;数据装载
2.医生主题
1.医生变动统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_doctor_change_stats(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 统计周期: 最近1,7,30日,new_doctor_count BIGINT COMMENT 新增医生数,activated_doctor_count BIGINT COMMENT 激活医生数,active_doctor_count BIGINT COMMENT 活跃医生数
) COMMENT 医生变动统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_doctor_change_stats;3.用户主题
1.用户变动统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_user_change_stats(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 统计周期: 最近1,7,30日,new_user_count BIGINT COMMENT 新增用户数,new_patient_count BIGINT COMMENT 新增患者数
) COMMENT 用户变动统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_user_change_stats;4.评价主题
1.评价综合统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_review_stats(dt STRING COMMENT 统计日期,review_user_count BIGINT COMMENT 评价人数,review_count BIGINT COMMENT 评价次数,good_review_rate DECIMAL(16,2) COMMENT 好评率
) COMMENT 用户变动统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_review_stats;2.各医院评价统计
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS ads_hospital_review_stats(dt STRING COMMENT 统计日期,hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,review_user_count BIGINT COMMENT 评价人数,review_count BIGINT COMMENT 评价次数,good_review_rate DECIMAL(16,2) COMMENT 好评率
) COMMENT 各医院评价统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/medical/ads/ads_hospital_review_stats;5.数据装载脚本
vim ~/bin/medical_dws_to_ads.sh
#!/bin/bashAPPmedicalif [ -n $2 ]
then do_date$2
else echo 请传入日期参数exit
fiads_trade_stats
insert overwrite table ${APP}.ads_trade_stats
select dt,recent_days,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from ${APP}.ads_trade_stats
union
select $do_date dt,consul.recent_days,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from (select 1 recent_days,sum(consultation_amount) consultation_amount,sum(consultation_count) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_1dwhere dt $do_dateunionselect recent_days,sum(if(recent_days 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,sum(if(recent_days 7, consultation_count_7d, consultation_count_30d)) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days) consulleft join(select 1 recent_days,sum(consultation_pay_suc_amount) consultation_pay_suc_amount,sum(consultation_pay_suc_count) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1dwhere dt $do_dateunionselect recent_days,sum(if(recent_days 7, consultation_pay_suc_amount_7d,consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,sum(if(recent_days 7, consultation_pay_suc_count_7d,consultation_pay_suc_count_30d)) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days) consul_pay_sucon consul.recent_days consul_pay_suc.recent_daysleft join(select 1 recent_days,sum(prescription_amount) prescription_amount,sum(prescription_count) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_1dwhere dt $do_dateunionselect recent_days,sum(if(recent_days 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,sum(if(recent_days 7, prescription_count_7d, prescription_count_30d)) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days) prescriptionon consul.recent_days prescription.recent_daysleft join(select 1 recent_days,sum(prescription_pay_suc_amount) prescription_pay_suc_amount,sum(prescription_pay_suc_count) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1dwhere dt $do_dateunionselect recent_days,sum(if(recent_days 7, prescription_pay_suc_amount_7d,prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,sum(if(recent_days 7, prescription_pay_suc_count_7d,prescription_pay_suc_count_30d)) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days) prescription_pay_sucon consul.recent_days prescription_pay_suc.recent_days;ads_hospital_trade_stats
insert overwrite table ${APP}.ads_hospital_trade_stats
select dt,recent_days,hospital_id,hospital_name,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from ${APP}.ads_hospital_trade_stats
union
select $do_date dt,consul.recent_days,consul.hospital_id,consul.hospital_name,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from (select 1 recent_days,hospital_id,hospital_name,sum(consultation_amount) consultation_amount,sum(consultation_count) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_1dwhere dt $do_dategroup by hospital_id,hospital_nameunionselect recent_days,hospital_id,hospital_name,sum(if(recent_days 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,sum(if(recent_days 7, consultation_count_7d, consultation_count_30d)) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,hospital_id,hospital_name) consulleft join(select 1 recent_days,hospital_id,hospital_name,sum(consultation_pay_suc_amount) consultation_pay_suc_amount,sum(consultation_pay_suc_count) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1dwhere dt $do_dategroup by hospital_id,hospital_nameunionselect recent_days,hospital_id,hospital_name,sum(if(recent_days 7, consultation_pay_suc_amount_7d,consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,sum(if(recent_days 7, consultation_pay_suc_count_7d,consultation_pay_suc_count_30d)) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,hospital_id,hospital_name) consul_pay_sucon consul.recent_days consul_pay_suc.recent_daysand consul.hospital_id consul_pay_suc.hospital_idand consul.hospital_name consul_pay_suc.hospital_nameleft join(select 1 recent_days,hospital_id,hospital_name,sum(prescription_amount) prescription_amount,sum(prescription_count) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_1dwhere dt $do_dategroup by hospital_id,hospital_nameunionselect recent_days,hospital_id,hospital_name,sum(if(recent_days 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,sum(if(recent_days 7, prescription_count_7d, prescription_count_30d)) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,hospital_id,hospital_name) prescriptionon consul.recent_days prescription.recent_daysand consul.hospital_id prescription.hospital_idand consul.hospital_name prescription.hospital_nameleft join(select 1 recent_days,hospital_id,hospital_name,sum(prescription_pay_suc_amount) prescription_pay_suc_amount,sum(prescription_pay_suc_count) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1dwhere dt $do_dategroup by hospital_id,hospital_nameunionselect recent_days,hospital_id,hospital_name,sum(if(recent_days 7, prescription_pay_suc_amount_7d,prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,sum(if(recent_days 7, prescription_pay_suc_count_7d,prescription_pay_suc_count_30d)) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,hospital_id,hospital_name) prescription_pay_sucon consul.recent_days prescription_pay_suc.recent_daysand consul.hospital_id prescription_pay_suc.hospital_idand consul.hospital_name prescription_pay_suc.hospital_name;ads_gender_trade_stats
insert overwrite table ${APP}.ads_gender_trade_stats
select dt,recent_days,gender_code,gender,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from ${APP}.ads_gender_trade_stats
union
select $do_date dt,consul.recent_days,consul.gender_code,consul.gender,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from (select 1 recent_days,gender_code,gender,sum(consultation_amount) consultation_amount,sum(consultation_count) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_1dwhere dt $do_dategroup by gender_code,genderunionselect recent_days,gender_code,gender,sum(if(recent_days 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,sum(if(recent_days 7, consultation_count_7d, consultation_count_30d)) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,gender_code,gender) consulleft join(select 1 recent_days,gender_code,gender,sum(consultation_pay_suc_amount) consultation_pay_suc_amount,sum(consultation_pay_suc_count) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1dwhere dt $do_dategroup by gender_code,genderunionselect recent_days,gender_code,gender,sum(if(recent_days 7, consultation_pay_suc_amount_7d,consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,sum(if(recent_days 7, consultation_pay_suc_count_7d,consultation_pay_suc_count_30d)) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,gender_code,gender) consul_pay_sucon consul.recent_days consul_pay_suc.recent_daysand consul.gender_code consul_pay_suc.gender_codeand consul.gender consul_pay_suc.genderleft join(select 1 recent_days,gender_code,gender,sum(prescription_amount) prescription_amount,sum(prescription_count) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_1dwhere dt $do_dategroup by gender_code,genderunionselect recent_days,gender_code,gender,sum(if(recent_days 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,sum(if(recent_days 7, prescription_count_7d, prescription_count_30d)) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,gender_code,gender) prescriptionon consul.recent_days prescription.recent_daysand consul.gender_code prescription.gender_codeand consul.gender prescription.genderleft join(select 1 recent_days,gender_code,gender,sum(prescription_pay_suc_amount) prescription_pay_suc_amount,sum(prescription_pay_suc_count) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1dwhere dt $do_dategroup by gender_code,genderunionselect recent_days,gender_code,gender,sum(if(recent_days 7, prescription_pay_suc_amount_7d,prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,sum(if(recent_days 7, prescription_pay_suc_count_7d,prescription_pay_suc_count_30d)) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,gender_code,gender) prescription_pay_sucon consul.recent_days prescription_pay_suc.recent_daysand consul.gender_code prescription_pay_suc.gender_codeand consul.gender prescription_pay_suc.gender;ads_age_group_trade_stats
insert overwrite table ${APP}.ads_age_group_trade_stats
select dt,recent_days,age_group,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from ${APP}.ads_age_group_trade_stats
union
select $do_date dt,consul.recent_days,consul.age_group,consultation_amount,consultation_count,consultation_pay_suc_amount,consultation_pay_suc_count,prescription_amount,prescription_count,prescription_pay_suc_amount,prescription_pay_suc_count
from (select 1 recent_days,age_group,sum(consultation_amount) consultation_amount,sum(consultation_count) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_1dwhere dt $do_dategroup by age_groupunionselect recent_days,age_group,sum(if(recent_days 7, consultation_amount_7d, consultation_amount_30d)) consultation_amount,sum(if(recent_days 7, consultation_count_7d, consultation_count_30d)) consultation_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,age_group) consulleft join(select 1 recent_days,age_group,sum(consultation_pay_suc_amount) consultation_pay_suc_amount,sum(consultation_pay_suc_count) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1dwhere dt $do_dategroup by age_groupunionselect recent_days,age_group,sum(if(recent_days 7, consultation_pay_suc_amount_7d,consultation_pay_suc_amount_30d)) consultation_pay_suc_amount,sum(if(recent_days 7, consultation_pay_suc_count_7d,consultation_pay_suc_count_30d)) consultation_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,age_group) consul_pay_sucon consul.recent_days consul_pay_suc.recent_daysand consul.age_group consul_pay_suc.age_groupleft join(select 1 recent_days,age_group,sum(prescription_amount) prescription_amount,sum(prescription_count) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_1dwhere dt $do_dategroup by age_groupunionselect recent_days,age_group,sum(if(recent_days 7, prescription_amount_7d, prescription_amount_30d)) prescription_amount,sum(if(recent_days 7, prescription_count_7d, prescription_count_30d)) prescription_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,age_group) prescriptionon consul.recent_days prescription.recent_daysand consul.age_group prescription.age_groupleft join(select 1 recent_days,age_group,sum(prescription_pay_suc_amount) prescription_pay_suc_amount,sum(prescription_pay_suc_count) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1dwhere dt $do_dategroup by age_groupunionselect recent_days,age_group,sum(if(recent_days 7, prescription_pay_suc_amount_7d,prescription_pay_suc_amount_30d)) prescription_pay_suc_amount,sum(if(recent_days 7, prescription_pay_suc_count_7d,prescription_pay_suc_count_30d)) prescription_pay_suc_countfrom ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dategroup by recent_days,age_group) prescription_pay_sucon consul.recent_days prescription_pay_suc.recent_daysand consul.age_group prescription_pay_suc.age_group;ads_doctor_change_stats
insert overwrite table ${APP}.ads_doctor_change_stats
select dt,recent_days,new_doctor_count,activated_doctor_count,active_doctor_count
from ${APP}.ads_doctor_change_stats
union
select $do_date dt,new.recent_days,new_doctor_count,activated_doctor_count,active_doctor_count
from (select recent_days,count(*) new_doctor_countfrom ${APP}.dwd_doctor_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_dayswhere dt date_add($do_date, -recent_days 1)group by recent_days) newleft join(select recent_days,count(*) activated_doctor_countfrom ${APP}.dws_trade_doctor_consultation_td lateral view explode(array(1, 7, 30)) tmp as recent_dayswhere dt $do_dateand first_consultation_dt date_add($do_date, -recent_days 1)group by recent_days) activatedon new.recent_days activated.recent_daysleft join(select 1 recent_days,count(*) active_doctor_countfrom ${APP}.dws_trade_doctor_consultation_1dwhere dt $do_dateand consultation_count 2unionselect recent_days,count(*) active_doctor_countfrom ${APP}.dws_trade_doctor_consultation_nd lateral view explode(array(7, 30)) tmp as recent_dayswhere dt $do_dateand ((recent_days 7 and consultation_count_7d 2)or (recent_days 30 and consultation_count_30d 2))group by recent_days) activeon new.recent_days active.recent_days;ads_user_change_stats
insert overwrite table ${APP}.ads_user_change_stats
select dt,recent_days,new_user_count,new_patient_count
from ${APP}.ads_user_change_stats
union
select $do_date dt,new_user.recent_days,new_user_count,new_patient_count
from (select recent_days,count(*) new_user_countfrom ${APP}.dwd_user_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_dayswhere dt date_add($do_date, -recent_days 1)group by recent_days) new_userleft join(select recent_days,count(*) new_patient_countfrom ${APP}.dwd_user_patient_add_inc lateral view explode(array(1, 7, 30)) tmp as recent_dayswhere dt date_add($do_date, -recent_days 1)group by recent_days) new_patienton new_user.recent_days new_patient.recent_days;ads_review_stats
insert overwrite table ${APP}.ads_review_stats
select dt,review_user_count,review_count,good_review_rate
from ${APP}.ads_review_stats
union
select $do_date dt,review_user_count,review_count,good_review_rate
from (select count(distinct user_id) review_user_countfrom ${APP}.dws_interaction_hospital_user_review_tdwhere dt $do_date) user_countleft join(select sum(review_count) review_count,sum(good_review_count) / sum(review_count) good_review_ratefrom ${APP}.dws_interaction_hospital_review_tdwhere dt $do_date) review_stats;ads_hospital_review_stats
insert overwrite table ${APP}.ads_hospital_review_stats
select dt,hospital_id,hospital_name,review_user_count,review_count,good_review_rate
from ${APP}.ads_hospital_review_stats
union
select $do_date dt,user_count.hospital_id,user_count.hospital_name,review_user_count,review_count,good_review_rate
from (select hospital_id,hospital_name,count(user_id) review_user_countfrom ${APP}.dws_interaction_hospital_user_review_tdwhere dt $do_dategroup by hospital_id,hospital_name) user_countleft join(select hospital_id,hospital_name,review_count,good_review_count / review_count good_review_ratefrom ${APP}.dws_interaction_hospital_review_tdwhere dt $do_date) review_statson user_count.hospital_id review_stats.hospital_idand user_count.hospital_name review_stats.hospital_name;
case $1 in ads_trade_stats | ads_hospital_trade_stats | ads_gender_trade_stats | ads_age_group_trade_stats | ads_doctor_change_stats | ads_user_change_stats | ads_review_stats | ads_hospital_review_stats)hive -e ${!1};;all)hive -e $ads_trade_stats$ads_hospital_trade_stats$ads_gender_trade_stats$ads_age_group_trade_stats$ads_doctor_change_stats$ads_user_change_stats$ads_review_stats$ads_hospital_review_stats;;*)echo 非法参数;;
esac数据装载 medical_dws_to_ads.sh all 2023-05-09 找个表看一下数据就行。
一、报表数据导出
1.MySQL建库建表
1.创建数据库
CREATE DATABASE IF NOT EXISTS medical_report DEFAULT CHARSET utf8 COLLATE utf8_general_ci;2.创建表
1.交易综合统计
CREATE TABLE ads_trade_stats (dt date NOT NULL COMMENT 统计日期,recent_days bigint NOT NULL COMMENT 统计周期: 最近1,7,30日,consultation_amount decimal(16,2) DEFAULT NULL COMMENT 问诊金额,consultation_count bigint DEFAULT NULL COMMENT 问诊次数,consultation_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 问诊支付成功金额,consultation_pay_suc_count bigint DEFAULT NULL COMMENT 问诊支付成功次数,prescription_amount decimal(16,2) DEFAULT NULL COMMENT 处方金额,prescription_count bigint DEFAULT NULL COMMENT 处方次数,prescription_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 处方支付成功金额,prescription_pay_suc_count bigint DEFAULT NULL COMMENT 处方支付成功次数,PRIMARY KEY (dt,recent_days)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT交易综合统计;2.各医院交易统计
CREATE TABLE ads_hospital_trade_stats (dt date NOT NULL COMMENT 统计日期,recent_days bigint NOT NULL COMMENT 统计周期: 最近1,7,30日,hospital_id varchar(255) NOT NULL COMMENT 医院ID,hospital_name varchar(255) NOT NULL COMMENT 医院名称,consultation_amount decimal(16,2) DEFAULT NULL COMMENT 问诊金额,consultation_count bigint DEFAULT NULL COMMENT 问诊次数,consultation_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 问诊支付成功金额,consultation_pay_suc_count bigint DEFAULT NULL COMMENT 问诊支付成功次数,prescription_amount decimal(16,2) DEFAULT NULL COMMENT 处方金额,prescription_count bigint DEFAULT NULL COMMENT 处方次数,prescription_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 处方支付成功金额,prescription_pay_suc_count bigint DEFAULT NULL COMMENT 处方支付成功次数,PRIMARY KEY (dt,recent_days,hospital_id,hospital_name)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT各医院交易统计;3.各性别患者交易统计
CREATE TABLE ads_gender_trade_stats (dt date NOT NULL COMMENT 统计日期,recent_days bigint NOT NULL COMMENT 统计周期: 最近1,7,30日,gender_code varchar(255) NOT NULL COMMENT 患者性别编码,gender varchar(255) NOT NULL COMMENT 患者性别,consultation_amount decimal(16,2) DEFAULT NULL COMMENT 问诊金额,consultation_count bigint DEFAULT NULL COMMENT 问诊次数,consultation_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 问诊支付成功金额,consultation_pay_suc_count bigint DEFAULT NULL COMMENT 问诊支付成功次数,prescription_amount decimal(16,2) DEFAULT NULL COMMENT 处方金额,prescription_count bigint DEFAULT NULL COMMENT 处方次数,prescription_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 处方支付成功金额,prescription_pay_suc_count bigint DEFAULT NULL COMMENT 处方支付成功次数,PRIMARY KEY (dt,recent_days,gender_code,gender)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT各性别患者交易统计;4.各年龄段患者交易统计
CREATE TABLE ads_age_group_trade_stats (dt date NOT NULL COMMENT 统计日期,recent_days bigint NOT NULL COMMENT 统计周期: 最近1,7,30日,age_group varchar(255) NOT NULL COMMENT 患者年龄段,consultation_amount decimal(16,2) DEFAULT NULL COMMENT 问诊金额,consultation_count bigint DEFAULT NULL COMMENT 问诊次数,consultation_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 问诊支付成功金额,consultation_pay_suc_count bigint DEFAULT NULL COMMENT 问诊支付成功次数,prescription_amount decimal(16,2) DEFAULT NULL COMMENT 处方金额,prescription_count bigint DEFAULT NULL COMMENT 处方次数,prescription_pay_suc_amount decimal(16,2) DEFAULT NULL COMMENT 处方支付成功金额,prescription_pay_suc_count bigint DEFAULT NULL COMMENT 处方支付成功次数,PRIMARY KEY (dt,recent_days,age_group)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT各年龄段患者交易统计;5.医生变动统计
CREATE TABLE ads_doctor_change_stats (dt date NOT NULL COMMENT 统计日期,recent_days bigint NOT NULL COMMENT 统计周期: 最近1,7,30日,new_doctor_count bigint DEFAULT NULL COMMENT 新增医生数,activated_doctor_count bigint DEFAULT NULL COMMENT 激活医生数,active_doctor_count bigint DEFAULT NULL COMMENT 活跃医生数,PRIMARY KEY (dt,recent_days)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT医生变动统计;6.用户变动统计
CREATE TABLE ads_user_change_stats (dt date NOT NULL COMMENT 统计日期,recent_days bigint NOT NULL COMMENT 统计周期: 最近1,7,30日,new_user_count bigint DEFAULT NULL COMMENT 新增用户数,new_patient_count bigint DEFAULT NULL COMMENT 新增患者数,PRIMARY KEY (dt,recent_days)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户变动统计;7.评价综合统计
CREATE TABLE ads_review_stats (dt date NOT NULL COMMENT 统计日期,review_user_count bigint DEFAULT NULL COMMENT 评价人数,review_count bigint DEFAULT NULL COMMENT 评价次数,good_review_rate decimal(16,2) DEFAULT NULL COMMENT 好评率,PRIMARY KEY (dt)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户变动统计;8.各医院评价统计
CREATE TABLE ads_hospital_review_stats (dt date NOT NULL COMMENT 统计日期,hospital_id varchar(255) NOT NULL COMMENT 医院ID,hospital_name varchar(255) NOT NULL COMMENT 医院名称,review_user_count bigint DEFAULT NULL COMMENT 评价人数,review_count bigint DEFAULT NULL COMMENT 评价次数,good_review_rate decimal(16,2) DEFAULT NULL COMMENT 好评率,PRIMARY KEY (dt,hospital_id,hospital_name)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT各医院评价统计;2.数据导出
1.DataX配置文件生成脚本
vim /opt/module/gen_datax_config/configuration.properties
mysql.usernameroot
mysql.password000000
mysql.hosthadoop102
mysql.port3306
mysql.database.importmedical
# 从HDFS导出进入的 MySQL 数据库名称
mysql.database.exportmedical_report
mysql.tables.importdict,doctor,hospital,medicine,patient,user
# MySQL 库中需要导出的表空串表示导出库的所有表
mysql.tables.export
is.seperated.tables0
hdfs.urihdfs://hadoop102:8020
import_out_dir/opt/module/datax/job/medical/import
# DataX 导出配置文件存放路径
export_out_dir/opt/module/datax/job/medical/export2.执行配置文件生成器
java -jar datax-config-generator-1.0-SNAPSHOT-jar-with-dependencies.jar
3.编写每日导出脚本
vim ~/bin/medical_hdfs_to_mysql.sh
#!/bin/bashDATAX_HOME/opt/module/dataxhandle_path(){for file in hadoop fs -ls -R $1 | awk {print $8}dohadoop fs -test -z $fileif [[ $? -eq 0 ]]then echo 文件 $file 大小为零正在删除...hadoop fs -rm -f -r $filefidone
}export_data(){export_dir$1datax_config$2echo 正在校验目录 $export_dir ...handle_path $export_dircounthadoop fs -count $export_dir | awk {print $2}if [[ $count -eq 0 ]]then echo 目录为空跳过elseecho 正在处理目录 $export_dir ...$DATAX_HOME/bin/datax.py -p-Dexportdir$export_dir $datax_config $DATAX_HOME/job/medical/export.log 21if [[ $? -ne 0 ]]then echo 执行出错日志如下 ...cat $DATAX_HOME/job/medical/export.logfifi
}case $1 inads_trade_stats | ads_hospital_trade_stats | ads_gender_trade_stats | ads_age_group_trade_stats | ads_doctor_change_stats | ads_user_change_stats | ads_review_stats | ads_hospital_review_stats)export_data /warehouse/medical/ads/$1 $DATAX_HOME/job/medical/export/medical_report.$1.json;;all)for tab in ads_trade_stats ads_hospital_trade_stats ads_gender_trade_stats ads_age_group_trade_stats ads_doctor_change_stats ads_user_change_stats ads_review_stats ads_hospital_review_statsdo export_data /warehouse/medical/ads/${tab} $DATAX_HOME/job/medical/export/medical_report.${tab}.jsondone;;*)echo 非法参数;;
esac添加权限 chmod x ~/bin/medical_hdfs_to_mysql.sh 数据装载 medical_hdfs_to_mysql.sh all 总结
数仓开发到这里就结束了。
