Conducting a Pivotal Analysis of Multiple Streams for Both Human and Robot Service Channels

Pivot Analysis: How Does It Work

For these, for reference, the “one-time resolution rate” refers to the ratio of problems solved through a specific channel, such as with the phone robot, robot-to-human service, or the human-to-robot service at a time. If the problem is transferred multiple times, the one-time resolution rate will be low as a result.

Scenario: The Arrival Time for Multiple Data Streams Problem

Insert on conflict do update xx = exclude. xx where xx <> excluded. xx

Demo: Conducting a Pivotal Analysis

Developing the Pivotal Analysis

Session streams (multiple tables) > Stream merging table > Conversion table > (Conversion table + Metadata table) pivoting result table
create table tbl_robot (  
caseid int8, -- Session ID
crt_time timestamp, -- Message time
message text, -- Interaction information
custom_id int8, -- Consumer ID
pro_id int, -- Problem ID
others text -- Other fields
);
create table tbl_human (  
caseid int8, -- Session ID
crt_time timestamp, -- Message time
message text, -- Interaction information
custom_id int8, -- Consumer ID
xiao2_id int8, -- Attendant ID
pro_id int, -- Problem ID
others text -- Other fields
);
create table tbl_session (  
caseid int8, -- Session ID
crt_time timestamp, -- Message time
message text, -- Interaction information
custom_id int8, -- Consumer ID
xiao2_id int8, -- Attendant ID
pro_id int, -- Problem ID
stream_id int, -- Stream ID. The value 1 indicates the robot service, and 2 indicates the human service.
others1 text, -- Other fields of stream 1
others2 text -- Other fields of stream 2
);

create index idx_tbl_session_1 on tbl_session (crt_time);
create index idx_tbl_session_2 on tbl_session (caseid, crt_time);
create or replace rule r1 as on insert to tbl_robot do instead   
insert into tbl_session
(caseid, crt_time, message, custom_id, pro_id, others1, stream_id)
values (NEW.caseid, NEW.crt_time, NEW.message, NEW.custom_id, NEW.pro_id, NEW.others, 1);

create or replace rule r1 as on insert to tbl_human do instead
insert into tbl_session
(caseid, crt_time, message, custom_id, pro_id, others2, xiao2_id, stream_id)
values (NEW.caseid, NEW.crt_time, NEW.message, NEW.custom_id, NEW.pro_id, NEW.others, NEW.xiao2_id, 2);
create table tbl_session_etl (  
caseid int8 primary key, -- Session ID
s_crt_time timestamp, -- Start time of the session
e_crt_time timestamp, -- Time of the last record of the session
robot_to_human boolean, -- Whether the robot-to-human service is included
human_to_robot boolean -- Whether the human-to-robot service is included
);
select caseid, max(s_crt_time) s_crt_time, max(e_crt_time) e_crt_time,   
bool_or(lag=1 and stream_id=2) as robot_to_human,
bool_or(lag=2 and stream_id=1) as human_to_robot
from
(
select caseid, min(crt_time) over w1 as s_crt_time, max(crt_time) over w1 as e_crt_time,
(case when (row_number() over w1) = 1 then stream_id else lag(stream_id) over w1 end) as lag,
stream_id
from tbl_session
where crt_time > now() - interval '10 min' -- Session data within 10 minutes. You can adjust this window as desired.
window w1 as (partition by caseid order by crt_time)
) t
group by caseid;
insert into tbl_session_etl (caseid, s_crt_time, e_crt_time, robot_to_human, human_to_robot)  
select caseid, max(s_crt_time) s_crt_time, max(e_crt_time) e_crt_time,
bool_or(lag=1 and stream_id=2) as robot_to_human,
bool_or(lag=2 and stream_id=1) as human_to_robot
from
(
select caseid, min(crt_time) over w1 as s_crt_time, max(crt_time) over w1 as e_crt_time,
(case when (row_number() over w1) = 1 then stream_id else lag(stream_id) over w1 end) as lag,
stream_id
from tbl_session
where crt_time > now() - interval '10 min' -- Session data within 10 minutes. You can adjust this window as desired.
window w1 as (partition by caseid order by crt_time) -- Use the window function for query.
) t
group by caseid
on conflict (caseid)
do update set
s_crt_time = excluded.s_crt_time,
e_crt_time = excluded.e_crt_time,
robot_to_human = excluded.robot_to_human,
human_to_robot = excluded.human_to_robot
where -- When the values change after data conversion, the data is merged and written.
tbl_session_etl.s_crt_time<>excluded.s_crt_time
or
tbl_session_etl.e_crt_time<>excluded.e_crt_time
or
tbl_session_etl.robot_to_human<>excluded.robot_to_human
or
tbl_session_etl.human_to_robot<>excluded.human_to_robot
;
create or replace function f_tbl_session_etl(interval) returns void as 
$$

insert into tbl_session_etl (caseid, s_crt_time, e_crt_time, robot_to_human, human_to_robot)
select caseid, max(s_crt_time) s_crt_time, max(e_crt_time) e_crt_time,
bool_or(lag=1 and stream_id=2) as robot_to_human,
bool_or(lag=2 and stream_id=1) as human_to_robot
from
(
select caseid, min(crt_time) over w1 as s_crt_time, max(crt_time) over w1 as e_crt_time,
(case when (row_number() over w1) = 1 then stream_id else lag(stream_id) over w1 end) as lag,
stream_id
from tbl_session
where crt_time > now() - $1 -- Session data within n minutes. You can adjust this window as desired.
window w1 as (partition by caseid order by crt_time) -- Use the window function for query.
) t
group by caseid
on conflict (caseid)
do update set
s_crt_time = excluded.s_crt_time,
e_crt_time = excluded.e_crt_time,
robot_to_human = excluded.robot_to_human,
human_to_robot = excluded.human_to_robot
where -- When the values change after data conversion, the data is merged and written.
tbl_session_etl.s_crt_time<>excluded.s_crt_time
or
tbl_session_etl.e_crt_time<>excluded.e_crt_time
or
tbl_session_etl.robot_to_human<>excluded.robot_to_human
or
tbl_session_etl.human_to_robot<>excluded.human_to_robot
;
$$
language sql strict;
create table tbl_session_stat_day (  
stat_dim text primary key,
robot_to_human_cnt int8,
human_to_robot_cnt int8
);
create table tbl_session_stat_min (  
stat_dim text primary key,
robot_to_human_cnt int8,
human_to_robot_cnt int8
);
select to_char(s_crt_time, 'yyyymmdd') as stat_dim,   
sum(case when robot_to_human then 1 else 0 end) robot_to_human_cnt,
sum(case when human_to_robot then 1 else 0 end) human_to_robot_cnt
from tbl_session_etl
group by 1;
select to_char(s_crt_time, 'yyyymmddhh24mi') as stat_dim,   
sum(case when robot_to_human then 1 else 0 end) robot_to_human_cnt,
sum(case when human_to_robot then 1 else 0 end) human_to_robot_cnt
from tbl_session_etl
group by 1;
insert into tbl_session_stat_day   
select to_char(s_crt_time, 'yyyymmdd') as stat_dim,
sum(case when robot_to_human then 1 else 0 end) robot_to_human_cnt,
sum(case when human_to_robot then 1 else 0 end) human_to_robot_cnt
from tbl_session_etl
group by 1
on conflict (stat_dim) do update
set
robot_to_human_cnt = excluded.robot_to_human_cnt,
human_to_robot_cnt = excluded.human_to_robot_cnt
where
tbl_session_stat_day.robot_to_human_cnt <> excluded.robot_to_human_cnt
or
tbl_session_stat_day.human_to_robot_cnt <> excluded.human_to_robot_cnt
;




insert into tbl_session_stat_min
select to_char(s_crt_time, 'yyyymmddhh24mi') as stat_dim,
sum(case when robot_to_human then 1 else 0 end) robot_to_human_cnt,
sum(case when human_to_robot then 1 else 0 end) human_to_robot_cnt
from tbl_session_etl
group by 1
on conflict (stat_dim) do update
set
robot_to_human_cnt = excluded.robot_to_human_cnt,
human_to_robot_cnt = excluded.human_to_robot_cnt
where
tbl_session_stat_min.robot_to_human_cnt <> excluded.robot_to_human_cnt
or
tbl_session_stat_min.human_to_robot_cnt <> excluded.human_to_robot_cnt
;
create or replace function f_tbl_session_stat_day() returns void as 
$$

insert into tbl_session_stat_day
select to_char(s_crt_time, 'yyyymmdd') as stat_dim,
sum(case when robot_to_human then 1 else 0 end) robot_to_human_cnt,
sum(case when human_to_robot then 1 else 0 end) human_to_robot_cnt
from tbl_session_etl
group by 1
on conflict (stat_dim) do update
set
robot_to_human_cnt = excluded.robot_to_human_cnt,
human_to_robot_cnt = excluded.human_to_robot_cnt
where
tbl_session_stat_day.robot_to_human_cnt <> excluded.robot_to_human_cnt
or
tbl_session_stat_day.human_to_robot_cnt <> excluded.human_to_robot_cnt
;
$$
language sql strict;



create or replace function f_tbl_session_stat_min() returns void as
$$

insert into tbl_session_stat_min
select to_char(s_crt_time, 'yyyymmddhh24mi') as stat_dim,
sum(case when robot_to_human then 1 else 0 end) robot_to_human_cnt,
sum(case when human_to_robot then 1 else 0 end) human_to_robot_cnt
from tbl_session_etl
group by 1
on conflict (stat_dim) do update
set
robot_to_human_cnt = excluded.robot_to_human_cnt,
human_to_robot_cnt = excluded.human_to_robot_cnt
where
tbl_session_stat_min.robot_to_human_cnt <> excluded.robot_to_human_cnt
or
tbl_session_stat_min.human_to_robot_cnt <> excluded.human_to_robot_cnt
;
$$
language sql strict;

Stress Testing the Performance

vi test.sql  

\set caseid1 random(1,1000000)
\set caseid2 random(1,1000000)
\set custom_id1 random(1,100000)
\set pro_id1 random(1,1000)
\set custom_id2 random(1,100000)
\set pro_id2 random(1,1000)
\set xiao2_id random(1,100)
insert into tbl_robot values (:caseid1, now(), 'test', :custom_id1, :pro_id1, 'test');
insert into tbl_human values (:caseid2, now(), 'test', :custom_id2, :xiao2_id, :pro_id2, 'test');
\sleep 500 us
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120
transaction type: ./test.sql  
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 10655120
latency average = 0.360 ms
latency stddev = 0.466 ms
tps = 88792.101825 (including connections establishing)
tps = 88804.892722 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set caseid1 random(1,1000000)
0.001 \set caseid2 random(1,1000000)
0.000 \set custom_id1 random(1,100000)
0.000 \set pro_id1 random(1,1000)
0.000 \set custom_id2 random(1,100000)
0.000 \set pro_id2 random(1,1000)
0.000 \set xiao2_id random(1,100)
0.178 insert into tbl_robot values (:caseid1, now(), 'test', :custom_id1, :pro_id1, 'test');
0.178 insert into tbl_human values (:caseid2, now(), 'test', :custom_id2, :xiao2_id, :pro_id2, 'test');
psql  

select f_tbl_session_etl(interval '5 sec');

\watch 1

Sat 09 Dec 2017 07:05:42 PM CST (every 1s)

f_tbl_session_etl
-------------------

(1 row)

Time: 4515.817 ms (00:04.516)
postgres=# select f_tbl_session_stat_day();  
f_tbl_session_stat_day
------------------------

(1 row)

Time: 926.839 ms
postgres=# select f_tbl_session_stat_min();
f_tbl_session_stat_min
------------------------

(1 row)

Time: 1162.713 ms (00:01.163)
postgres=# select count(*) from tbl_session;  
count
-----------
179639156
(1 row)

Time: 1635.908 ms (00:01.636)

postgres=# select count(*) from tbl_session_etl;
count
---------
1000000
(1 row)

Time: 47.540 ms
postgres=# select * from tbl_session_stat_day ;  
stat_dim | robot_to_human_cnt | human_to_robot_cnt
----------+--------------------+--------------------
20171209 | 80160 | 80453
(1 row)

Time: 6.476 ms

postgres=# select * from tbl_session_stat_min;
stat_dim | robot_to_human_cnt | human_to_robot_cnt
--------------+--------------------+--------------------
201712091758 | 56558 | 56531
201712091800 | 4 | 4
201712091759 | 509 | 501
201712091757 | 236638 | 236657
201712091802 | 7273 | 7177
201712091817 | 8336 | 8358
201712091812 | 0 | 0
201712091814 | 12 | 8
201712091815 | 127 | 144
201712091813 | 1 | 1
201712091816 | 1688 | 1761
201712091905 | 56645 | 57046
201712091904 | 411 | 391
201712091906 | 23104 | 23015
201712091902 | 0 | 1
(15 rows)

Time: 6.695 ms

Summary

Original Source

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store