由于部分企业数据库从aws迁移到腾讯云,导致有一个定时任务(从详情表汇总数据到统计表中)错过了触发,所以这部分企业的数据需要触发重新刷一下,但是又有规定白天不允许上线,只能把定时任务的逻辑用存储过程(函数)实现一遍,通过这种方式把数据刷正确。下面是完整的存储过程示例:
--删除储存过程
--DROP FUNCTION f_sys_statlog_month(text,text,text)
--新建储存过程
CREATE OR REPLACE FUNCTION "public"."f_sys_statlog_month"(beginTime TEXT,endTime TEXT,monthTime TEXT)
RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
c_record record;
BEGIN
delete from sys_statlog_month where statistics_time = monthTime;
for c_record in
with used_set as (
SELECT
b.user_id,
sum(case when b.client_type='1' THEN 1 else 0 end) as client_1,
sum(case when b.client_type='2' THEN 1 else 0 end) as client_2,
sum(case when b.client_type='4' THEN 1 else 0 end) as client_4,
sum(case when b.client_type='5' THEN 1 else 0 end) as client_5
FROM sys_statlog b
WHERE 1 = 1
and b.occur_time >= to_date(beginTime,'yyyy-MM-dd')
AND b.occur_time < to_date(endTime,'yyyy-MM-dd')
and b.operate_type IN ('1', '2')
and b.user_id != 999
GROUP BY b.user_id
)
select id,
COALESCE(client_1, 0) as client_1,
COALESCE(client_2, 0) as client_2,
COALESCE(client_4, 0) as client_4,
COALESCE(client_5, 0) as client_5 from sys_employee a1
left join used_set a2 on a1.id=a2.user_id
where a1.status in ('1','2')
union ALL
select id,
COALESCE(client_1, 0) as client_1,
COALESCE(client_2, 0) as client_2,
COALESCE(client_4, 0) as client_4,
COALESCE(client_5, 0) as client_5
from sys_employee a1
join used_set a2 on a1.id=a2.user_id
where a1.status= '0'
loop
insert into sys_statlog_month
(
id,
user_id,
creator_id,
create_time,
client_type_1,
client_type_2,
client_type_4,
client_type_5,
statistics_time
)
values
(
cast(RANDOM() * 10000000000000 as int8),
c_record.id,
-10000,
now(),
c_record.client_1,
c_record.client_2,
c_record.client_4,
c_record.client_5,
monthTime
);
end loop;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
---下面sql演示刷2023年11月份的数据
select f_sys_statlog_month('2023-11-01','2023-12-01','202311');