某医院患者住院信息包括两个方面的内容（Oracle数据库）
表1 收费明细表：charge_detail
患者ID 住院次数 收费类别 费用

表2 住院记录表：pat_visit
患者ID 住院次数 入院时间 出院时间 入院科室 出院科室

问题1：基于以上两张表统计如下数据，结果按月份（降序），出院科室排序。

需求结果：
出院科室 出院月份 总费用 药品占比

思路：从住院记录表（pat_visit）开始，左连接收费明细表（charge_detail），通过患者ID和住院次数确保所有住院记录都被包括，即使没有收费记录。然后，按出院科室和住院次数进行分组，计算总费用和药品占比。药品占比是通过DECODE函数实现的：当收费类别为“药品”时，累加费用，否则为0，再除以总费用。结果会显示出院科室、住院次数、总费用和药品占比，但代码中没有明确指定排序方式，理论上需要按月份降序和出院科室排序，但这里使用了住院次数（visit_no）作为分组字段之一。

1
2
3
4
5
6
7
8
-- 问题1：统计各科室每月总费用和药品占比
SELECT p.DEPT_DISCHARGE_FROM, -- 选择出院科室字段
       p.visit_no, -- 选择住院次数字段（这里可能应该是月份字段）
       sum(c.costs), -- 计算总费用
       sum(decode(c.charge_type,'药品',c.costs,0))/sum(c.costs) -- 计算药品占比：药品费用除以总费用
FROM pat_visit p -- 从住院记录表开始查询
left join CHARGE_DETAIL c on c.patient_id=p.patient_id and c.visit_no=p.visit_no -- 左连接收费明细表，通过患者ID和住院次数关联
group by P.DEPT_DISCHARGE_FROM, p.visit_no -- 按出院科室和住院次数分组

问题2：返回2023年3月出院科室为心内科的本期药品收入，同期药品收入及同比增长率。

需求结果：
出院科室 出院月份 本期药品费用 同期药品费用 同比增长率

思路：使用两个公共表表达式（CTE）：tmp和tmp1。tmp查询2023年3月出院的心内科患者的药品费用总和，通过左连接收费明细表，过滤出院时间、科室和收费类别，并按科室和出院月份分组。tmp1类似地查询2022年3月的数据。主查询则左连接这两个CTE，按科室匹配，计算本期药品费用、同期药品费用以及同比增长率（（本期-同期）/同期）。代码意图是通过比较两个时间段的药品收入来评估增长情况，但连接条件中有一个拼写错误（tmpl），可能影响结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 问题2：计算心内科2023年3月与2022年3月的药品收入对比及增长率
with tmp as( -- 创建临时表tmp，查询2023年3月心内科的药品费用
    select p.dept_discharge_from, -- 选择出院科室
           to_char(p.discharge_date_time,'yyyymm') ch_date, -- 将出院时间格式化为年月格式
           sum(c.costs) costs -- 计算药品费用总和
    from pat_visit p -- 从住院记录表开始查询
    left join CHARGE_DETAIL c on c.patient_id=p.patient_id and c.visit_no=p.visit_no -- 左连接收费明细表
    where to_char(p.discharge_date_time,'yyyymm')='202303' -- 筛选2023年3月出院的数据
          and p.dept_discharge_from='心内科' -- 筛选心内科的数据
          and c.charge_type='药品' -- 筛选药品类别的费用
    group by p.dept_discharge_from,to_char(p.discharge_date_time,'yyyymm') -- 按科室和出院月份分组
),tmp1 as ( -- 创建临时表tmp1，查询2022年3月心内科的药品费用
    select p.dept_discharge_from, -- 选择出院科室
           to_char(p.discharge_date_time,'yyyymm') ch_date, -- 将出院时间格式化为年月格式
           sum(c.costs) costs -- 计算药品费用总和
    from pat_visit p -- 从住院记录表开始查询
    left join CHARGE_DETAIL c on c.patient_id=p.patient_id and c.visit_no=p.visit_no -- 左连接收费明细表
    where to_char(p.discharge_date_time,'yyyymm')='202203' -- 筛选2022年3月出院的数据
          and p.dept_discharge_from='心内科' -- 筛选心内科的数据
          and c.charge_type='药品' -- 筛选药品类别的费用
    group by p.dept_discharge_from,to_char(p.discharge_date_time,'yyyymm') -- 按科室和出院月份分组
)
-- 主查询：连接两个临时表，计算同比增长率
select tmp.dept_discharge_from, -- 选择科室
       tmp.ch_date, -- 选择月份
       tmp.costs, -- 本期药品费用
       tmp1.costs, -- 同期药品费用
       (tmp.costs-tmp1.costs)/tmp1.costs -- 计算同比增长率：(本期-同期)/同期
from tmp 
left join tmp1 on tmp.dept_discharge_from=tmp1.dept_discharge_from -- 按科室左连接两个临时表

问题3：将问题1中查询出的2022年3月份数据插入到指标表kpi

字段：年月、科室、指标编码、指标值、创建人、创建时间
注：指标编码为固定值“K001”，指标名称为固定值“药品占比”，创建人为自己名字，创建时间为当前时间

思路：首先指定插入的字段，包括年月、科室、指标编码、指标名称、指标值、创建人和创建时间。然后，从住院记录表左连接收费明细表，过滤出2022年3月出院的数据，按出院科室和住院次数分组，计算药品占比（使用DECODE函数识别药品费用）。固定值如年月、指标编码、指标名称、创建人和当前时间被直接插入。代码意图是将计算出的药品占比作为指标记录保存，以便后续监控和分析，但分组依据包括住院次数，可能导致数据粒度较细。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 问题3：将2022年3月的药品占比数据插入指标表
insert into kpi ( -- 插入数据到kpi表
    MON, -- 年月字段
    DEPT, -- 科室字段
    KPI_CODE, -- 指标编码字段
    KPI_NAME, -- 指标名字段
    KPI_VALUE, -- 指标值字段
    CREATE_BY, -- 创建人字段
    CREATE_TIME -- 创建时间字段
)
SELECT '2022-03', -- 固定值：2022年3月
       P.DEPT_DISCHARGE_FROM, -- 选择出院科室
       'K001', -- 固定指标编码
       '药品占比', -- 固定指标名称
       sum(decode(c.charge_type,'药品',c.costs,0))/sum(c.costs), -- 计算药品占比
       'hwz', -- 固定创建人名称
       sysdate -- 当前系统时间
FROM pat_visit p -- 从住院记录表开始查询
left join CHARGE_DETAIL c on c.patient_id=p.patient_id and c.visit_no=p.visit_no -- 左连接收费明细表
where to_char(p.discharge_date_time,'yyyymm')='202203' -- 筛选2022年3月出院的数据
group by p.DEPT_DISCHARGE_FROM,p.visit_no -- 按出院科室和住院次数分组