最后的几天[by Mr.No]

近期因前期工作过于放松,导致项目后期任务繁重。通过这段时间的努力,深刻体会到一分耕耘一分收获的道理。

这几天真是事情多,难度大,这都是以前的遗留下的问题,前面的日子过的太放松了,结果到后面就需要做很多的事情了。当然,如果不想把项目做得更好一些就不需要这么辛苦了,但是世界上总是这样的,一分耕耘一分收获.

INSERT OVERWRITE TABLE dwd_zy_item_usage SELECT CAST(a.sautoid AS VARCHAR) AS meta_id, h.hospital_key AS hospital_sk, p.patient_key AS patient_sk, d.doctor_key AS doctor_sk, dept_order.dept_key AS order_dept_sk, dept_exec.dept_key AS exec_dept_sk, hi.hosp_item_key AS hosp_item_sk, it.item_key AS item_sk, dd.date_key AS date_sk, a.hisid, a.ipd_no, a.item_id_hosp AS drug_code, a.item_name_hosp AS drug_name, a.spec, a.dosage_form, a.package_unit, TO_DATE(a.usage_date) AS usage_date, SUM(CAST(a.num AS DECIMAL(18,4))) AS usage_quantity, -- 项目使用数量 COUNT(DISTINCT CONCAT(a.hisid, DATE_FORMAT(a.usage_date, '%Y-%m-%d'), a.bill_date)) AS usage_frequency, -- 某患者某药品在同一天的不同账单出现次数 AVG(CAST(a.unit_price AS DECIMAL(18,4))) AS price, -- 单价平均值 SUM(CAST(a.cost AS DECIMAL(18,2))) AS amount, -- 总金额 SUM(CAST(a.bmi_convered_amount AS DECIMAL(18,2))) AS bmi_convered_amount, -- 纳入医保统筹额 MAX(CAST(a.limit_pay AS DECIMAL(18,2))) AS limit_pay, -- 最高限价 NULL AS drug_day_rank, NULL AS visit_day_rank, 'ods_zy_fee_detail' AS meta_source_table, CURRENT_TIMESTAMP() AS meta_extract_time FROM ods_zy_fee_detail a LEFT JOIN dim_hospital h ON a.hospital_id = h.hospital_id LEFT JOIN dim_patient p ON a.social_id = p.social_id LEFT JOIN dim_doctor d ON a.doctor_name = d.doctor_name AND a.hospital_id = d.hospital_id LEFT JOIN dim_department dept_order ON a.bill_dept_name = dept_order.dept_name AND a.hospital_id = dept_order.hospital_id LEFT JOIN dim_department dept_exec ON a.execute_dept_name = dept_exec.dept_name AND a.hospital_id = dept_exec.hospital_id LEFT JOIN dim_hosp_item hi ON a.item_id_hosp = hi.hosp_item_id AND a.hospital_id = hi.hospital_id LEFT JOIN dim_item it ON a.item_id = it.item_id LEFT JOIN dim_date dd ON DATE_FORMAT(a.usage_date, '%Y%m%d') = dd.date_key GROUP BY h.hospital_key, p.patient_key, d.doctor_key, dept_order.dept_key, dept_exec.dept_key, hi.hosp_item_key, it.item_key, dd.date_key, a.sautoid, a.hisid, a.ipd_no, a.item_id_hosp, a.item_name_hosp, a.spec, a.dosage_form, a.package_unit, TO_DATE(a.usage_date)那这个sql要怎么去优化啊,能执行就行啊,慢也可以啊
11-21
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值