网站推广应该怎么做?,wordpress 转织梦,网页制作怎么制作,好看的wordpress图片主题系列文章目录
线上问诊#xff1a;业务数据采集 线上问诊#xff1a;数仓数据同步 线上问诊#xff1a;数仓开发(一) 线上问诊#xff1a;数仓开发(二) 文章目录 系列文章目录前言一、DWS1.最近1日汇总表1.交易域医院患者性别年龄段粒度问诊最近1日汇总表2.交易域医院患者…系列文章目录
线上问诊业务数据采集 线上问诊数仓数据同步 线上问诊数仓开发(一) 线上问诊数仓开发(二) 文章目录 系列文章目录前言一、DWS1.最近1日汇总表1.交易域医院患者性别年龄段粒度问诊最近1日汇总表2.交易域医院患者性别年龄段粒度问诊支付成功最近1日汇总表3.交易域医院患者性别年龄段粒度处方开单最近1日汇总表4.交易域医院患者性别年龄段粒度处方开单支付成功最近1日汇总表5.交易域医生粒度问诊最近1日汇总表6.首日装载脚本7.每日数据装载 2.最近n日汇总表1.交易域医院患者性别年龄段粒度问诊最近n日汇总表2.交易域医院患者性别年龄段粒度问诊支付成功最近n日汇总表3.交易域医院患者性别年龄段粒度处方开单最近n日汇总表4.交易域医院患者性别年龄段粒度处方开单支付成功最近n日汇总表5.交易域医生粒度问诊最近n日汇总表6.首日装载脚本 3.历史至今汇总表1.交易域医生粒度问诊历史至今汇总表2.互动域医院用户粒度用户评价历史至今汇总表3.互动域医院粒度用户评价历史至今汇总表4.首日数据装载5.每日数据装载 总结 前言
我们这次博客继续完成数仓的开发 一、DWS
1.最近1日汇总表
1.交易域医院患者性别年龄段粒度问诊最近1日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_consultation_1d
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-122]老年期,consultation_amount DECIMAL(16, 2) COMMENT 问诊金额,consultation_count BIGINT COMMENT 问诊次数
) COMMENT 交易域医院患者性别年龄段粒度问诊最近1日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_consultation_1dTBLPROPERTIES (orc.compress snappy);2.交易域医院患者性别年龄段粒度问诊支付成功最近1日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-]老年期,consultation_pay_suc_amount DECIMAL(16, 2) COMMENT 问诊支付成功金额,consultation_pay_suc_count BIGINT COMMENT 问诊支付成功次数
) COMMENT 交易域医院患者性别年龄段粒度问诊支付成功最近1日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_consultation_pay_suc_1dTBLPROPERTIES (orc.compress snappy);3.交易域医院患者性别年龄段粒度处方开单最近1日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_prescription_1d
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-]老年期,prescription_amount DECIMAL(16, 2) COMMENT 处方开单金额,prescription_count BIGINT COMMENT 处方开单次数
) COMMENT 交易域医院患者性别年龄段粒度处方开单最近1日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_prescription_1dTBLPROPERTIES (orc.compress snappy);4.交易域医院患者性别年龄段粒度处方开单支付成功最近1日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-]老年期,prescription_pay_suc_amount DECIMAL(16, 2) COMMENT 处方开单支付成功金额,prescription_pay_suc_count BIGINT COMMENT 处方开单支付成功次数
) COMMENT 交易域医院患者性别年龄段粒度处方开单支付成功最近1日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_prescription_pay_suc_1dTBLPROPERTIES (orc.compress snappy);5.交易域医生粒度问诊最近1日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_doctor_consultation_1d
(doctor_id STRING COMMENT 医生ID,doctor_name STRING COMMENT 医生姓名,consultation_count BIGINT COMMENT 接诊次数
) COMMENT 交易域医生粒度问诊最近1日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_doctor_consultation_1dTBLPROPERTIES (orc.compress snappy);6.首日装载脚本
vim ~/bin/medical_dwd_to_dws_1d_init.sh
#!/bin/bashAPPmedicalif [ -n $2 ]
then do_date$2
elseecho 请传入日期参数exit
fidws_trade_hospital_gender_age_group_consultation_1d
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_consultation_1dpartition (dt)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(consultation_fee) consultation_amount,count(*) consultation_count,dt
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,consultation_fee,dtfrom (select doctor_id,patient_id,consultation_fee,dtfrom ${APP}.dwd_trade_consultation_inc) consulleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group,dt;
dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1dpartition (dt)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(consultation_fee) consultation_pay_suc_amount,count(*) consultation_pay_suc_count,dt
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,consultation_fee,dtfrom (select doctor_id,patient_id,consultation_fee,dtfrom ${APP}.dwd_trade_consultation_pay_suc_inc) consulleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group,dt;
dws_trade_hospital_gender_age_group_prescription_1d
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_prescription_1dpartition (dt)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(total_amount) prescription_amount,count(*) prescription_count,dt
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,total_amount,dtfrom (select max(doctor_id) doctor_id,max(patient_id) patient_id,max(total_amount) total_amount,max(dt) dtfrom ${APP}.dwd_trade_prescription_incgroup by prescription_id) prescrleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group,dt;
dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1dpartition (dt)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(total_amount) prescription_pay_suc_amount,count(*) prescription_pay_suc_count,dt
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,total_amount,dtfrom (select max(doctor_id) doctor_id,max(patient_id) patient_id,max(total_amount) total_amount,max(dt) dtfrom ${APP}.dwd_trade_prescription_pay_suc_incgroup by prescription_id) prescrleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group,dt;
dws_trade_doctor_consultation_1d
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table ${APP}.dws_trade_doctor_consultation_1dpartition (dt)
select doctor_id,name doctor_name,consultation_count,dt
from (select doctor_id,dt,count(*) consultation_countfrom ${APP}.dwd_trade_consultation_incgroup by doctor_id,dt) avgleft join (select id,namefrom ${APP}.dim_doctor_fullwhere dt $do_date) docon avg.doctor_id doc.id;
case $1 indws_trade_hospital_gender_age_group_consultation_1d | dws_trade_hospital_gender_age_group_consultation_pay_suc_1d | dws_trade_hospital_gender_age_group_prescription_1d | dws_trade_hospital_gender_age_group_prescription_pay_suc_1d | dws_trade_doctor_consultation_1d)hive -e ${!1};;all)hive -e $dws_trade_hospital_gender_age_group_consultation_1d$dws_trade_hospital_gender_age_group_consultation_pay_suc_1d$dws_trade_hospital_gender_age_group_prescription_1d$dws_trade_hospital_gender_age_group_prescription_pay_suc_1d$dws_trade_doctor_consultation_1d;;
esac添加权限 chmod x ~/bin/medical_dwd_to_dws_1d_init.sh 数据载入 medical_dwd_to_dws_1d_init.sh all 2023-05-09 随便找一个查看一下最后的日期
7.每日数据装载
vim ~/bin/medical_dwd_to_dws_1d.sh
#!/bin/bashAPPmedicalif [ -n $2 ]
then do_date$2
elseecho 请传入日期参数exit
fidws_trade_hospital_gender_age_group_consultation_1d
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_consultation_1dpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(consultation_fee) consultation_amount,count(*) consultation_count
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,consultation_feefrom (select doctor_id,patient_id,consultation_feefrom ${APP}.dwd_trade_consultation_incwhere dt $do_date) consulleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group;dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1dpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(consultation_fee) consultation_pay_suc_amount,count(*) consultation_pay_suc_count
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,consultation_feefrom (select doctor_id,patient_id,consultation_feefrom ${APP}.dwd_trade_consultation_pay_suc_incwhere dt $do_date) consulleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group;dws_trade_hospital_gender_age_group_prescription_1d
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_prescription_1dpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(total_amount) prescription_amount,count(*) prescription_count
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,total_amountfrom (select max(doctor_id) doctor_id,max(patient_id) patient_id,max(total_amount) total_amountfrom ${APP}.dwd_trade_prescription_incwhere dt $do_dategroup by prescription_id) prescrleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group;dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1dpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(total_amount) prescription_pay_suc_amount,count(*) prescription_pay_suc_count
from (select hospital_id,hospital_name,gender_code,gender,casewhen age 0 and age 2 then 婴儿期when age 3 and age 5 then 幼儿期when age 6 and age 11 then 小学阶段when age 12 and age 17 then 青少年期中学阶段when age 18 and age 29 then 青年期when age 30 and age 59 then 中年期when age 60 and age 122 then 老年期else 年龄异常 end age_group,total_amountfrom (select max(doctor_id) doctor_id,max(patient_id) patient_id,max(total_amount) total_amountfrom ${APP}.dwd_trade_prescription_incwhere dt $do_dategroup by prescription_id) prescrleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon doctor_id doc.idleft join (select id,name hospital_namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson doc.hospital_id hos.idleft join(select id,gender_code,gender,year($do_date) - year(birthday) agefrom ${APP}.dim_patient_fullwhere dt $do_date) patienton patient_id patient.id) with_group
group by hospital_id,hospital_name,gender_code,gender,age_group;dws_trade_doctor_consultation_1d
insert overwrite table ${APP}.dws_trade_doctor_consultation_1dpartition (dt $do_date)
select doctor_id,name doctor_name,consultation_count
from (select doctor_id,count(*) consultation_countfrom ${APP}.dwd_trade_consultation_incwhere dt $do_dategroup by doctor_id) avgleft join (select id,namefrom ${APP}.dim_doctor_fullwhere dt $do_date) docon avg.doctor_id doc.id;case $1 indws_trade_hospital_gender_age_group_consultation_1d | dws_trade_hospital_gender_age_group_consultation_pay_suc_1d | dws_trade_hospital_gender_age_group_prescription_1d | dws_trade_hospital_gender_age_group_prescription_pay_suc_1d | dws_trade_doctor_consultation_1d)hive -e ${!1};;all)hive -e $dws_trade_hospital_gender_age_group_consultation_1d$dws_trade_hospital_gender_age_group_consultation_pay_suc_1d$dws_trade_hospital_gender_age_group_prescription_1d$dws_trade_hospital_gender_age_group_prescription_pay_suc_1d$dws_trade_doctor_consultation_1d;;
esac添加权限 chmod x ~/bin/medical_dwd_to_dws_1d.sh
2.最近n日汇总表
1.交易域医院患者性别年龄段粒度问诊最近n日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_consultation_nd
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-122]老年期,consultation_amount_7d DECIMAL(16, 2) COMMENT 最近 7 日问诊金额,consultation_count_7d BIGINT COMMENT 最近 7 日问诊次数,consultation_amount_30d DECIMAL(16, 2) COMMENT 最近 30 日问诊金额,consultation_count_30d BIGINT COMMENT 最近 30 日问诊次数
) COMMENT 交易域医院患者性别年龄段粒度问诊最近n日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_consultation_ndTBLPROPERTIES (orc.compress snappy);2.交易域医院患者性别年龄段粒度问诊支付成功最近n日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_consultation_pay_suc_nd
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-]老年期,consultation_pay_suc_amount_7d DECIMAL(16, 2) COMMENT 最近 7 日问诊支付成功金额,consultation_pay_suc_count_7d BIGINT COMMENT 最近 7 日问诊支付成功次数,consultation_pay_suc_amount_30d DECIMAL(16, 2) COMMENT 最近 30 日问诊支付成功金额,consultation_pay_suc_count_30d BIGINT COMMENT 最近 30 日问诊支付成功次数
) COMMENT 交易域医院患者性别年龄段粒度问诊支付成功最近n日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_consultation_pay_suc_ndTBLPROPERTIES (orc.compress snappy);3.交易域医院患者性别年龄段粒度处方开单最近n日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_prescription_nd
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-]老年期,prescription_amount_7d DECIMAL(16, 2) COMMENT 最近 7 日处方开单金额,prescription_count_7d BIGINT COMMENT 最近 7 日处方开单次数,prescription_amount_30d DECIMAL(16, 2) COMMENT 最近 30 日处方开单金额,prescription_count_30d BIGINT COMMENT 最近 30 日处方开单次数
) COMMENT 交易域医院患者性别年龄段粒度处方开单最近n日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_prescription_ndTBLPROPERTIES (orc.compress snappy);4.交易域医院患者性别年龄段粒度处方开单支付成功最近n日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_hospital_gender_age_group_prescription_pay_suc_nd
(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,gender_code STRING COMMENT 患者性别编码,gender STRING COMMENT 患者性别,age_group STRING COMMENT 年龄段[0,2]婴儿期, [3,5]幼儿期, [6,11]小学阶段, [12,17]青少年期(中学阶段), [18-29]青年期, [30-59]中年期, [60-]老年期,prescription_pay_suc_amount_7d DECIMAL(16, 2) COMMENT 最近 7 日处方开单支付成功金额,prescription_pay_suc_count_7d BIGINT COMMENT 最近 7 日处方开单支付成功次数,prescription_pay_suc_amount_30d DECIMAL(16, 2) COMMENT 最近 30 日处方开单支付成功金额,prescription_pay_suc_count_30d BIGINT COMMENT 最近 30 日处方开单支付成功次数
) COMMENT 交易域医院患者性别年龄段粒度处方开单支付成功最近n日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_hospital_gender_age_group_prescription_pay_suc_ndTBLPROPERTIES (orc.compress snappy);5.交易域医生粒度问诊最近n日汇总表
建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS dws_trade_doctor_consultation_nd
(doctor_id STRING COMMENT 医生ID,doctor_name STRING COMMENT 医生姓名,consultation_count_7d BIGINT COMMENT 最近 7 日接诊次数,consultation_count_30d BIGINT COMMENT 最近 30 日接诊次数
) COMMENT 交易域医生粒度问诊最近n日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_doctor_consultation_ndTBLPROPERTIES (orc.compress snappy);6.首日装载脚本
vim ~/bin/medical_dws_1d_to_dws_nd.sh
#!/bin/bashAPPmedicalif [ -n $2 ]
then do_date$2
elseecho 请传入日期参数exit
fidws_trade_hospital_gender_age_group_consultation_nd
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_consultation_ndpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(if(dt date_add($do_date, -6), consultation_amount, 0)) consultation_amount_7d,sum(if(dt date_add($do_date, -6), consultation_count, 0)) consultation_count_7d,sum(consultation_amount) consultation_amount_30d,sum(consultation_count) consultation_count_30d
from ${APP}.dws_trade_hospital_gender_age_group_consultation_1d
where dt date_add($do_date, -29)
group by hospital_id,hospital_name,gender_code,gender,age_group;
dws_trade_hospital_gender_age_group_consultation_pay_suc_nd
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_ndpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(if(dt date_add($do_date, -6), consultation_pay_suc_amount, 0)) consultation_pay_suc_amount_7d,sum(if(dt date_add($do_date, -6), consultation_pay_suc_count, 0)) consultation_pay_suc_count_7d,sum(consultation_pay_suc_amount) consultation_pay_suc_amount_30d,sum(consultation_pay_suc_count) consultation_pay_suc_count_30d
from ${APP}.dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
where dt date_add($do_date, -29)
group by hospital_id,hospital_name,gender_code,gender,age_group;
dws_trade_hospital_gender_age_group_prescription_nd
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_prescription_ndpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(if(dt date_add($do_date, -6), prescription_amount, 0)) prescription_amount_7d,sum(if(dt date_add($do_date, -6), prescription_count, 0)) prescription_count_7d,sum(prescription_amount) prescription_amount_30d,sum(prescription_count) prescription_count_30d
from ${APP}.dws_trade_hospital_gender_age_group_prescription_1d
where dt date_add($do_date, -29)
group by hospital_id,hospital_name,gender_code,gender,age_group;
dws_trade_hospital_gender_age_group_prescription_pay_suc_nd
insert overwrite table ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_ndpartition (dt $do_date)
select hospital_id,hospital_name,gender_code,gender,age_group,sum(if(dt date_add($do_date, -6), prescription_pay_suc_amount, 0)) prescription_pay_suc_amount_7d,sum(if(dt date_add($do_date, -6), prescription_pay_suc_count, 0)) prescription_pay_suc_count_7d,sum(prescription_pay_suc_amount) prescription_pay_suc_amount_30d,sum(prescription_pay_suc_count) prescription_pay_suc_count_30d
from ${APP}.dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
where dt date_add($do_date, -29)
group by hospital_id,hospital_name,gender_code,gender,age_group;
dws_trade_doctor_consultation_nd
insert overwrite table ${APP}.dws_trade_doctor_consultation_ndpartition (dt $do_date)
select doctor_id,doctor_name,sum(if(dt date_add($do_date, -6), consultation_count, 0)) consultation_count_7d,sum(consultation_count) consultation_count_30d
from ${APP}.dws_trade_doctor_consultation_1d
where dt date_add($do_date, -29)
group by doctor_id,doctor_name;
case $1 indws_trade_hospital_gender_age_group_consultation_nd | dws_trade_hospital_gender_age_group_consultation_pay_suc_nd | dws_trade_hospital_gender_age_group_prescription_nd | dws_trade_hospital_gender_age_group_prescription_pay_suc_nd | dws_trade_doctor_consultation_nd)hive -e ${!1};;all)hive -e $dws_trade_hospital_gender_age_group_consultation_nd$dws_trade_hospital_gender_age_group_consultation_pay_suc_nd$dws_trade_hospital_gender_age_group_prescription_nd$dws_trade_hospital_gender_age_group_prescription_pay_suc_nd$dws_trade_doctor_consultation_nd;;*)echo 非法参数;;
esac添加权限 chmod x ~/bin/medical_dws_1d_to_dws_nd.sh 数据装载 medical_dws_1d_to_dws_nd.sh all 2023-05-09
3.历史至今汇总表
1.交易域医生粒度问诊历史至今汇总表
建表语句
CREATE TABLE IF NOT EXISTS dws_trade_doctor_consultation_td(doctor_id STRING COMMENT 医生ID,doctor_name STRING COMMENT 医生姓名,first_consultation_dt STRING COMMENT 首次接诊日期
) COMMENT 交易域医生粒度问诊历史至今汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_trade_doctor_consultation_tdTBLPROPERTIES (orc.compress snappy);2.互动域医院用户粒度用户评价历史至今汇总表
建表语句
CREATE TABLE IF NOT EXISTS dws_interaction_hospital_user_review_td(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,user_id STRING COMMENT 用户ID,username STRING COMMENT 用户姓名,first_review_dt STRING COMMENT 首次评价日期
) COMMENT 互动域医院用户粒度用户评价历史至今汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_interaction_hospital_user_review_tdTBLPROPERTIES (orc.compress snappy);3.互动域医院粒度用户评价历史至今汇总表
建表语句
CREATE TABLE IF NOT EXISTS dws_interaction_hospital_review_td(hospital_id STRING COMMENT 医院ID,hospital_name STRING COMMENT 医院名称,review_count BIGINT COMMENT 评价次数,good_review_count BIGINT COMMENT 好评次数
) COMMENT 互动域医院粒度用户评价历史至今汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/medical/dws/dws_interaction_hospital_review_tdTBLPROPERTIES (orc.compress snappy);4.首日数据装载
vim ~/bin/medical_dws_1d_to_dws_td_init.sh
#!/bin/bashAPPmedicalif [ -n $2 ]
then do_date$2
elseecho 请传入日期参数exit
fidws_trade_doctor_consultation_td
insert overwrite table ${APP}.dws_trade_doctor_consultation_tdpartition (dt $do_date)
select doctor_id,doctor_name,min(dt) first_consultation_dt
from ${APP}.dws_trade_doctor_consultation_1d
group by doctor_id,doctor_name;
dws_interaction_hospital_user_review_td
insert overwrite table ${APP}.dws_interaction_hospital_user_review_tdpartition (dt $do_date)
select hospital_id,name hospital_name,user_id,username,first_review_dt
from (select hospital_id,user_id,min(review.dt) first_review_dtfrom (select doctor_id,user_id,dtfrom ${APP}.dwd_interaction_review_inc) reviewleft join (select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon review.doctor_id doc.idgroup by hospital_id,user_id) avgleft join (select id,namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson avg.hospital_id hos.idleft join (select id,usernamefrom ${APP}.dim_user_fullwhere dt $do_date) \user\on avg.user_id \user\.id;
dws_interaction_hospital_review_td
insert overwrite table ${APP}.dws_interaction_hospital_review_tdpartition (dt $do_date)
select hospital_id,name hospital_name,review_count,good_review_count
from (select hospital_id,count(*) review_count,sum(if(rating 5, 1, 0)) good_review_countfrom (select doctor_id,ratingfrom ${APP}.dwd_interaction_review_inc) reviewleft join(select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docon review.doctor_id doc.idgroup by hospital_id) avgleft join (select id,namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson hospital_id hos.id;
case $1 indws_trade_doctor_consultation_td | dws_interaction_hospital_user_review_td | dws_interaction_hospital_review_td)hive -e ${!1};;all)hive -e $dws_trade_doctor_consultation_td$dws_interaction_hospital_user_review_td$dws_interaction_hospital_review_td;;*)echo 非法参数;;
esac添加权限 chmod x ~/bin/medical_dws_1d_to_dws_td_init.sh 数据装载 medical_dws_1d_to_dws_td_init.sh all 2023-05-09
5.每日数据装载
vim ~/bin/medical_dws_1d_to_dws_td.sh
#!/bin/bashAPPmedicalif [ -n $2 ]
then do_date$2
elseecho 请传入时间参数exit
fidws_trade_doctor_consultation_td
insert overwrite table ${APP}.dws_trade_doctor_consultation_tdpartition (dt $do_date)
select nvl(old.doctor_id, new.doctor_id) doctor_id,nvl(old.doctor_name, new.doctor_name) doctor_name,if(old.doctor_id is null, $do_date, first_consultation_dt) first_consultation_dt
from (select doctor_id,doctor_name,first_consultation_dtfrom ${APP}.dws_trade_doctor_consultation_tdwhere dt date_add($do_date, -1)) oldfull outer join(select doctor_id,doctor_namefrom ${APP}.dws_trade_doctor_consultation_1dwhere dt $do_date) newon old.doctor_id new.doctor_idand old.doctor_name new.doctor_name;
dws_interaction_hospital_user_review_td
insert overwrite table ${APP}.dws_interaction_hospital_user_review_tdpartition (dt $do_date)
select hospital_id,hospital_name,user_id,username,min(first_review_dt) first_review_dt
from (select hospital_id,hospital_name,user_id,username,first_review_dtfrom ${APP}.dws_interaction_hospital_user_review_tdwhere dt date_add($do_date, -1)unionselect hospital_id,name hospital_name,user_id,username,first_reveiw_dtfrom (select hospital_id,user_id,$do_date first_reveiw_dtfrom (select doctor_id,user_idfrom ${APP}.dwd_interaction_review_incwhere dt $do_date) reivewleft join (select id,hospital_idfrom ${APP}.dim_doctor_full) docon reivew.doctor_id doc.idgroup by user_id,hospital_id) avgleft join (select id,namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson avg.hospital_id hos.idleft join (select id,usernamefrom ${APP}.dim_user_fullwhere dt $do_date) \user\on avg.user_id \user\.id) \all\
group by hospital_id,hospital_name,user_id,username;
dws_interaction_hospital_review_td
insert overwrite table ${APP}.dws_interaction_hospital_review_tdpartition (dt $do_date)
select hospital_id,hospital_name,sum(review_count) review_count,sum(good_review_count) good_review_count
from (select hospital_id,hospital_name,review_count,good_review_countfrom ${APP}.dws_interaction_hospital_review_tdwhere dt date_add($do_date, -1)unionselect hospital_id,name hospital_name,review_count,good_review_countfrom (select hospital_id,count(*) review_count,sum(if(rating 5, 1, 0)) good_review_countfrom (select doctor_id,ratingfrom ${APP}.dwd_interaction_review_incwhere dt $do_date) reviewleft join (select id,hospital_idfrom ${APP}.dim_doctor_fullwhere dt $do_date) docgroup by hospital_id) avgleft join (select id,namefrom ${APP}.dim_hospital_fullwhere dt $do_date) hoson hospital_id hos.id) \all\
group by hospital_id,hospital_name;
case $1 indws_trade_doctor_consultation_td | dws_interaction_hospital_user_review_td | dws_interaction_hospital_review_td)hive -e ${!1};;all)hive -e $dws_trade_doctor_consultation_td$dws_interaction_hospital_user_review_td$dws_interaction_hospital_review_td;;*)echo 非法参数;;
esac添加权限 chmod x ~/bin/medical_dws_1d_to_dws_td.sh 总结
内容有点多可能还要一次才能完成。
