------------------------------
-------函数入口在最后---------
------------------------------
drop table if exists remote_analyze;
create table remote_analyze(--统计表
timeBegin timestamp not null primary key ,
controlNum int default 0,
controlSuccessNum int default 0,
controlFailseNum int default 0,
controlFailseRate real default 0,
---具体功能-------------------------------
cmd_00101 INTEGER,--总数
cmd_00101_T INTEGER,--成功次数
cmd_00101_F INTEGER,--车门上锁 1
cmd_00101_falseRate real--车门上锁 失败率
);
-------注释
COMMENT ON COLUMN remote_analyze.timeBegin IS '时间';
COMMENT ON COLUMN remote_analyze.controlNum IS '远控次数';
--建立索引
--create index index_ on remote_send_cmd_history(send_id);
--功能实现
create or replace FUNCTION sear(IN startTime timestamp, IN endTime timestamp, IN str text )
returns void
AS
$$
declare
timeAt timestamp; --时间戳类型
cur refcursor;--动态游标，查找更改，类似指针
rec_cur RECORD;--记录，从游标取出一条记录
--静态游标
cur1 cursor(
star timestamp
)for select count(send_id) from remote_send_cmd_history
where remote_time between star and star + cast(str||'' as interval);
---------------变量-------------------------------
--远控次数
_controlNum remote_analyze.controlNum%type;--%type 类型引用
--远控成功次数
_controlSuccessNum remote_analyze.controlSuccessNum%type;
--远控失败次数
_controlFailseNum remote_analyze.controlFailseNum%type;
--远控失败率
_controlFailseRate remote_analyze.controlFailseRate%type;
------------------------具体功能-------------------------------
_cmd_00101 INTEGER;--总数
_cmd_00101_T INTEGER;--成功次数
_cmd_00101_F INTEGER;--车门上锁 1
_cmd_00101_F_falseRate real;--车门上锁 失败率
num INTEGER;
begin
timeAt := startTime;
while timeAt < endTime loop --循环体 多条件：exp AND/OR exp
--变量清零
_controlNum := 0;
_controlSuccessNum := 0;
_controlFailseNum := 0;
_controlFailseRate := 0;
_cmd_00101_F := 0;--车门上锁 1
_cmd_00101 := 0;--总数
_cmd_00101_T := 0;--成功次数
--打开游标
open cur for select * from remote_send_cmd_history
--cast( str ||'' as interval )为时间间隔 可有：timeAt + ‘1 DAY’
where remote_time between timeAt and timeAt + cast( str ||'' as interval );
--统计数值
loop
fetch cur into rec_cur; --取出下条记录
exit when NOT FOUND;
--远控次数
_controlNum := _controlNum + 1;
--远控成功次数
if rec_cur.status = 'CMD_SUCCEED' then
_controlSuccessNum := _controlSuccessNum + 1;
end if;
--远控失败次数
if rec_cur.status != 'CMD_SUCCEED' then
_controlFailseNum := _controlFailseNum + 1;
end if;
-----------具体功能----失败次数---------------------------
--车门上锁 1
if rec_cur.cmd_code = '00101' then
_cmd_00101 := _cmd_00101 + 1;
if rec_cur.status != 'CMD_SUCCEED' then
_cmd_00101_F := _cmd_00101_F + 1;
else _cmd_00101_T := _cmd_00101_T + 1;
end if;
end if;
---------------------------具体功能-------------------------------
--车门上锁 失败率
if _controlNum != 0 then
_cmd_00101_F_falseRate := _cmd_00101_F * 1.0 / _controlNum;
else _cmd_00101_F_falseRate := 0;
end if;
---=========================插入统计数据============================---
insert into remote_analyze(
timeBegin ,
controlNum ,
controlSuccessNum ,
controlFailseNum,
controlFailseRate,
-----------具体功能-------------------------------
cmd_00101 ,--总数
cmd_00101_T ,--成功次数
cmd_00101_F ,--车门上锁 1
cmd_00101_falseRate --车门上锁 失败率
)values(
timeAt,
_controlNum,
_controlSuccessNum,
_controlFailseNum,
_controlFailseRate,
-----------具体功能-------------------------------
_cmd_00101 ,--总数
_cmd_00101_T ,--成功次数
_cmd_00101_F ,--车门上锁 1
_cmd_00101_F_falseRate --车门上锁 失败率
);
timeAt := timeAt + cast( str ||'' as interval);
end loop;
end;
$$
language plpgsql;
--功能：统计param1到param2的每小时内的远控记录，
--@param startTime : 开始时间
--@param endTime : 结束时间
--@param str : 时间间隔
select sear('2019-09-01 00:00:00', '2019-10-17 00:00:00', '1 HOUR');
