Postgres自定义函数数据分摊实战

需求背景

因在项目交付过程中，客户需将 某个科室、按月、将总费用、及其他不同类型的费用分摊 并增加到指定的科室里面去，为了防止后续分摊科室发生变动，我们可将部分参数定为入参从而达到自定义函数的可通用性。

建表测试

create table income_summary_month
(
    id             serial not null
        constraint income_summary_month_pk
            primary key,
    stat_date_id   integer,
    stat_date      timestamp,
    dept_code      integer,
    dept_name      varchar(50),
    visit_type     varchar(20),
    total_cost     numeric(12, 4),
    drug_cost      numeric(12, 4),
    material_cost  numeric(12, 4),
    check_lab_cost numeric(12, 4)
);
comment on table income_summary_month is '医疗运营月汇总测试表';
alter table income_summary_month
    owner to postgres;

测试数据如下

---测试数据
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11144, null, '2023-11-01 00:00:00.000000', 9412, '泌尿外科一区', '住院', 1451199.0100, 183348.6100, 224231.9200, 323255.2000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11107, null, '2023-11-01 00:00:00.000000', 9413, '泌尿外科二区', '住院', 1888720.2500, 229872.5800, 349544.0800, 322582.0000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11103, null, '2023-11-01 00:00:00.000000', 9013, '儿科三区', '住院', null, null, null, null);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11175, null, '2023-11-01 00:00:00.000000', 1015, '泌尿外科门诊', '门诊', 498995.9500, 221168.9100, 12126.4400, 165380.6000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11244, null, '2023-11-01 00:00:00.000000', 1088, '眼科门诊', '门诊', 362039.3600, 101187.7600, 144.0000, 182412.6000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11155, null, '2023-11-01 00:00:00.000000', 1012, '心胸外科门诊', '门诊', 120930.8000, 9319.8600, 642.9400, 105045.0000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11133, null, '2023-11-01 00:00:00.000000', 1020, '儿科一区', '住院', 1138973.5600, 251975.8100, 9877.7500, 473203.4000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11132, null, '2023-11-01 00:00:00.000000', 1019, '产科二区', '住院', 1430968.1300, 278930.4100, 79588.3200, 303889.0000);
INSERT INTO public.income_summary_month (id, stat_date_id, stat_date, dept_code, dept_name, visit_type, total_cost, drug_cost, material_cost, check_lab_cost) VALUES (11094, null, '2023-11-01 00:00:00.000000', 9011, '产科一区', '住院', 1275625.4000, 249954.9400, 71193.8600, 300104.0000);

创建函数

create function avg_dept_fee(dept_ids    integer,--定义科室代码字段维度
                             avg_dept    integer, --被均分的科室
                             avg_num     integer, --被均分为几分
                             fee_type    character varying,--定于不同的费用类型
                             starttime   timestamp without time zone,--定义时间维度
                             dept_codes  integer[]) returns numeric--定义科室代码
    language plpgsql
as
$$
BEGIN
    IF (dept_ids = any (dept_codes) and fee_type = 'total1') then
        return (
            select coalesce(total_cost, 0) / avg_num
            from std_dws_hos_workload_income_summary_month
            where stat_date = starttime
              and dept_id in (avg_dept)
        );
    ELSEIF (dept_ids = any (dept_codes) and fee_type = 'drug1') then
        return (
            select coalesce(drug_cost, 0) / avg_num
            from std_dws_hos_workload_income_summary_month
            where stat_date = starttime
              and dept_id in (avg_dept)
        );
    ELSEIF (dept_ids = any (dept_codes) and fee_type = 'check1') then
        return (
            select coalesce(check_lab_cost, 0) / avg_num
            from std_dws_hos_workload_income_summary_month
            where stat_date = starttime
              and dept_id in (avg_dept)
        );
    ELSE
        RETURN 0;
    END IF;
END
$$;

alter function avg_dept_fee(integer, integer, integer, varchar, timestamp, integer[]) owner to postgres;

函数应用

select
dept_code,dept_name,
total_cost,
avg_dept_fee ( dept_code,1088,2,'total1', stat_date,array [1019,9011] ),
total_cost+  avg_dept_fee(dept_code,1088,
    2,'total1', stat_date,array [1019,9011])          as sum_total_cost,
drug_cost,
avg_dept_fee ( dept_code,1088,2,'drug1', stat_date,array [1019,9011] ),
drug_cost+  avg_dept_fee(dept_code,1088,
    2,'drug1', stat_date,array [1019,9011])          as sum_drug_cost,
check_lab_cost,
avg_dept_fee ( dept_code,1088,2,'check1', stat_date,array [1019,9011] ),
check_lab_cost+  avg_dept_fee(dept_code,1088,
    2,'check1', stat_date,array [1019,9011])          as sum_check_lab_cost
from  income_summary_month a
where dept_code in ('9412','9413','1015','1088','1019','9011');

总结：虽然功能很简单，甚至还有很多其他办法可以实现，但是我希望能通过此自定义函数，能做到如下几点：
1）让看到的筒靴、朋友、前辈能不经意间 温习下函数语法
2）能更好的定一些函数解决通用性问题
3）把一些认为好、有价值的分享出来