SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE myTable (
    datetime TEXT,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,   -- to be calculated AFTER INSERT via TRIGGER
    mem_lag REAL,    -- to be calculated AFTER INSERT via TRIGGER
    util_diff REAL,  -- to be calculated AFTER INSERT via TRIGGER
    mem_diff REAL,   -- to be calculated AFTER INSERT via TRIGGER
    util_change TEXT -- to be calculated AFTER INSERT via TRIGGER
);
✓
--bootstrap row
insert into myTable(datetime)
select '-infinity';
--without it, the trigger would have to always consider two cases:
--if there are no rows, insert incoming row without modifying it
--if there are any rows, calculate the relative `_lag` and `_diff` values
--by adding the bootstrap row ahead of time you don't need to consider the first case
✓
CREATE INDEX dtidx ON myTable (datetime);
✓
CREATE VIEW v_myTable AS SELECT * FROM myTable ORDER BY datetime DESC LIMIT 1;
✓
CREATE TRIGGER cust_addr_chng
INSTEAD OF INSERT ON v_myTable 
BEGIN
  INSERT INTO myTable
  SELECT NEW.datetime,
         NEW.sys_id,
         NEW.cputil,
         NEW.memfree,
         NEW.sessnum,
         previous.cputil,--util_lag
         previous.memfree,--mem_lag
         NEW.cputil-previous.cputil,--util_diff 
         NEW.memfree-previous.memfree,--mem_diff
         CASE WHEN NEW.cputil-previous.cputil > 0
              THEN 'Up'
              WHEN NEW.cputil-previous.cputil < 0
              THEN 'Down'
              WHEN NEW.cputil-previous.cputil = 0
              THEN ''
         END --util_change
  FROM myTable AS previous
  ORDER BY datetime DESC
    LIMIT 1;
END;
✓
insert into v_myTable(datetime,sys_id,cputil,memfree,sessnum)values
 ('2019/05/03 08:06:14',100,0.57,0.51,47)
,('2019/05/03 08:11:14',100,0.47,0.62,43)
,('2019/05/03 08:16:14',100,0.56,0.57,62)
,('2019/05/03 08:21:14',100,0.57,0.56,50)
,('2019/05/03 08:26:14',100,0.35,0.46,43)
,('2019/05/03 08:31:14',100,0.41,0.58,48)
,('2019/05/03 08:36:14',100,0.57,0.35,58)
,('2019/05/03 08:41:14',100,0.41,0.4,58)
,('2019/05/03 08:46:14',100,0.53,0.35,62)
,('2019/05/03 08:51:14',100,0.51,0.6,45)
,('2019/05/03 08:56:14',100,0.32,0.37,47)
,('2019/05/03 09:01:14',100,0.62,0.59,60)
,('2019/05/03 09:06:14',100,0.66,0.72,57)
,('2019/05/03 09:11:14',100,0.54,0.54,44)
,('2019/05/03 09:16:14',100,0.29,0.4,47)
,('2019/05/03 09:21:14',100,0.43,0.68,66)
,('2019/05/03 09:26:14',100,0.49,0.66,65)
,('2019/05/03 09:31:14',100,0.64,0.55,66)
,('2019/05/03 09:36:14',100,0.42,0.6,42)
,('2019/05/03 09:41:14',100,0.55,0.59,63);
✓
SELECT * FROM myTable ORDER BY datetime;


datetime
sys_id
cputil
memfree
sessnum
util_lag
mem_lag
util_diff
mem_diff
util_change


-infinity
null
null
null
null
null
null
null
null
null

2019/05/03 08:06:14
100
0.57
0.51
47
null
null
null
null
null

2019/05/03 08:11:14
100
0.47
0.62
43
0.57
0.51
-0.09999999999999998
0.10999999999999999
Down

2019/05/03 08:16:14
100
0.56
0.57
62
0.47
0.62
0.09000000000000008
-0.050000000000000044
Up

2019/05/03 08:21:14
100
0.57
0.56
50
0.56
0.57
0.009999999999999898
-0.009999999999999898
Up

2019/05/03 08:26:14
100
0.35
0.46
43
0.57
0.56
-0.21999999999999997
-0.10000000000000003
Down

2019/05/03 08:31:14
100
0.41
0.58
48
0.35
0.46
0.06
0.11999999999999994
Up

2019/05/03 08:36:14
100
0.57
0.35
58
0.41
0.58
0.15999999999999998
-0.22999999999999998
Up

2019/05/03 08:41:14
100
0.41
0.4
58
0.57
0.35
-0.15999999999999998
0.050000000000000044
Down

2019/05/03 08:46:14
100
0.53
0.35
62
0.41
0.4
0.12000000000000005
-0.050000000000000044
Up

2019/05/03 08:51:14
100
0.51
0.6
45
0.53
0.35
-0.020000000000000018
0.25
Down

2019/05/03 08:56:14
100
0.32
0.37
47
0.51
0.6
-0.19
-0.22999999999999998
Down

2019/05/03 09:01:14
100
0.62
0.59
60
0.32
0.37
0.3
0.21999999999999997
Up

2019/05/03 09:06:14
100
0.66
0.72
57
0.62
0.59
0.040000000000000036
0.13
Up

2019/05/03 09:11:14
100
0.54
0.54
44
0.66
0.72
-0.12
-0.17999999999999994
Down

2019/05/03 09:16:14
100
0.29
0.4
47
0.54
0.54
-0.25000000000000006
-0.14
Down

2019/05/03 09:21:14
100
0.43
0.68
66
0.29
0.4
0.14
0.28
Up

2019/05/03 09:26:14
100
0.49
0.66
65
0.43
0.68
0.06
-0.020000000000000018
Up

2019/05/03 09:31:14
100
0.64
0.55
66
0.49
0.66
0.15000000000000002
-0.10999999999999999
Up

2019/05/03 09:36:14
100
0.42
0.6
42
0.64
0.55
-0.22000000000000003
0.04999999999999993
Down

2019/05/03 09:41:14
100
0.55
0.59
63
0.42
0.6
0.13000000000000006
-0.010000000000000009
Up

datetime	sys_id	cputil	memfree	sessnum	util_lag	mem_lag	util_diff	mem_diff	util_change
-infinity	null	null	null	null	null	null	null	null	null
2019/05/03 08:06:14	100	0.57	0.51	47	null	null	null	null	null
2019/05/03 08:11:14	100	0.47	0.62	43	0.57	0.51	-0.09999999999999998	0.10999999999999999	Down
2019/05/03 08:16:14	100	0.56	0.57	62	0.47	0.62	0.09000000000000008	-0.050000000000000044	Up
2019/05/03 08:21:14	100	0.57	0.56	50	0.56	0.57	0.009999999999999898	-0.009999999999999898	Up
2019/05/03 08:26:14	100	0.35	0.46	43	0.57	0.56	-0.21999999999999997	-0.10000000000000003	Down
2019/05/03 08:31:14	100	0.41	0.58	48	0.35	0.46	0.06	0.11999999999999994	Up
2019/05/03 08:36:14	100	0.57	0.35	58	0.41	0.58	0.15999999999999998	-0.22999999999999998	Up
2019/05/03 08:41:14	100	0.41	0.4	58	0.57	0.35	-0.15999999999999998	0.050000000000000044	Down
2019/05/03 08:46:14	100	0.53	0.35	62	0.41	0.4	0.12000000000000005	-0.050000000000000044	Up
2019/05/03 08:51:14	100	0.51	0.6	45	0.53	0.35	-0.020000000000000018	0.25	Down
2019/05/03 08:56:14	100	0.32	0.37	47	0.51	0.6	-0.19	-0.22999999999999998	Down
2019/05/03 09:01:14	100	0.62	0.59	60	0.32	0.37	0.3	0.21999999999999997	Up
2019/05/03 09:06:14	100	0.66	0.72	57	0.62	0.59	0.040000000000000036	0.13	Up
2019/05/03 09:11:14	100	0.54	0.54	44	0.66	0.72	-0.12	-0.17999999999999994	Down
2019/05/03 09:16:14	100	0.29	0.4	47	0.54	0.54	-0.25000000000000006	-0.14	Down
2019/05/03 09:21:14	100	0.43	0.68	66	0.29	0.4	0.14	0.28	Up
2019/05/03 09:26:14	100	0.49	0.66	65	0.43	0.68	0.06	-0.020000000000000018	Up
2019/05/03 09:31:14	100	0.64	0.55	66	0.49	0.66	0.15000000000000002	-0.10999999999999999	Up
2019/05/03 09:36:14	100	0.42	0.6	42	0.64	0.55	-0.22000000000000003	0.04999999999999993	Down
2019/05/03 09:41:14	100	0.55	0.59	63	0.42	0.6	0.13000000000000006	-0.010000000000000009	Up