分析网站的关键词,封面型网站布局,营销方案设计思路,ai生成图片在线制作由于部分企业数据库从aws迁移到腾讯云#xff0c;导致有一个定时任务#xff08;从详情表汇总数据到统计表中#xff09;错过了触发#xff0c;所以这部分企业的数据需要触发重新刷一下#xff0c;但是又有规定白天不允许上线#xff0c;只能把定时任务的逻辑用存储过程导致有一个定时任务从详情表汇总数据到统计表中错过了触发所以这部分企业的数据需要触发重新刷一下但是又有规定白天不允许上线只能把定时任务的逻辑用存储过程函数实现一遍通过这种方式把数据刷正确。下面是完整的存储过程示例 --删除储存过程--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$
DECLAREc_record record;BEGINdelete from sys_statlog_month where statistics_time monthTime;for c_record inwith used_set as (SELECTb.user_id,sum(case when b.client_type1 THEN 1 else 0 end) as client_1, sum(case when b.client_type2 THEN 1 else 0 end) as client_2, sum(case when b.client_type4 THEN 1 else 0 end) as client_4,sum(case when b.client_type5 THEN 1 else 0 end) as client_5FROM sys_statlog bWHERE 1 1and 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 ! 999GROUP 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 a1left join used_set a2 on a1.ida2.user_idwhere a1.status in (1,2)union ALLselect 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 a1join used_set a2 on a1.ida2.user_idwhere a1.status 0loopinsert 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 VOLATILECOST 100---下面sql演示刷2023年11月份的数据
select f_sys_statlog_month(2023-11-01,2023-12-01,202311);
