线上问诊：数仓开发(二)

系列文章目录

线上问诊：业务数据采集
线上问诊：数仓数据同步
线上问诊：数仓开发(一)
线上问诊：数仓开发(二)

---

---

前言

我们这次博客继续完成数仓的开发

---

一、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_1d'TBLPROPERTIES ('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_1d'TBLPROPERTIES ('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_1d'TBLPROPERTIES ('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_1d'TBLPROPERTIES ('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_1d'TBLPROPERTIES ('orc.compress' = 'snappy');

6.首日装载脚本

vim ~/bin/medical_dwd_to_dws_1d_init.sh

#!/bin/bashAPP=medicalif [ -n $2 ]
then do_date=$2
elseecho "请传入日期参数！！！"exit
fidws_trade_hospital_gender_age_group_consultation_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
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.mode=nonstrict;
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.mode=nonstrict;
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.mode=nonstrict;
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.mode=nonstrict;
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/bashAPP=medicalif [ -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_date'group 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_date'group 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_date'group 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_nd'TBLPROPERTIES ('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_nd'TBLPROPERTIES ('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_nd'TBLPROPERTIES ('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_nd'TBLPROPERTIES ('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_nd'TBLPROPERTIES ('orc.compress' = 'snappy');

6.首日装载脚本

vim ~/bin/medical_dws_1d_to_dws_nd.sh

#!/bin/bashAPP=medicalif [ -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_td'TBLPROPERTIES ('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_td'TBLPROPERTIES ('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_td'TBLPROPERTIES ('orc.compress' = 'snappy');

4.首日数据装载

vim ~/bin/medical_dws_1d_to_dws_td_init.sh

#!/bin/bashAPP=medicalif [ -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/bashAPP=medicalif [ -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

---

总结

内容有点多，可能还要一次才能完成。