实时透视分析wwwlehu6.vip乐虎官网

标签

PostgreSQL , 多输入流 , 人工客服 , 机器客服 , 服务质地 , 多维透视 ,
实时透视 , 窗口查询 , 流式总结 , 流式SQL , 阅后即焚 , 上下文相关 ,
流式窗口 , 到达时间错乱 , 窗口保持 , 可重算 , 幂等

背景

数见不鲜一个服务型的成品,面向广大用户时,都会提供多种服务渠道:

对讲机、WEB、人工客服。机器人客服。

什么样从各类维度(问题分类、地区分类。。。。)了解问题的 :
解决率、解决时长柱状图、一回解决率(例如电话机器人、转人工、转机器人等,假诺反复流离失所表明五遍解决率太低)。

其一透视分析,能够用作检验客户服务质地,提高用户体验的一个第一参考。

以电商为例,两遍问题,可能波及:

1、多股数量流表:

电话机、WEB、人工客服。机器人客服。

2、数据流的始末提到:

卖家ID

买家ID

商品ID

小二ID

时间。

3、四个元数据表可能包括:

买家、买家、商品、问题、小二 属性、标签。

4、透视维度包括:

时间。

地区。

货物档次。

题目项目。

等。

5、透视目标或者包括:

解决率、解决时长柱状图、一遍解决率(例如电话机器人、转人工、转机器人等,假设反复流浪表达一回解决率太低)。

什么完成实时透视?可以运用PostgreSQL的流式处理(实时、或异步阅后即焚)效率,以及insert
on conflict幂等操作的效用。

多股数据流的抵达时刻问题

常常在一个重型的店堂中,业务会拆得很细,甚至对于客服那样的体系,人工处理和电话处理都属于六个工作线。

而数据流转往往通过MQ平台,也就是说不同业务线的数据流可能存在到达的时间差,或者花费的时刻差。

为了透视客户服务质量相关数据,需要用到六个数据流,势必面临到达时刻差的题材。

比如说,对于某个CASE:

事在人为劳动是9点发生的。

机械服务是9点01分暴发的。

唯独她们属于多少个数据流,最后的到达时间反过来了。那么在总计一解率时,可能出现误差。

为了缓解那些误差,需要做窗口保持的重新计算操作,做到实时总计结果可调动,可增量重算。(那个可以选取insert
on conflict do update xx=exclude.xx where xx<>excluded.xx来实现)

demo

以一解率维度为例,其他维度可以参考模仿。

数据流如下:

会话流(多表) -> 流合并表 -> 转换表 -> (转换表+元数据表)
透视结果表

1、会话数据流

流1,机器人

create table tbl_robot (

caseid int8,            — 会话ID

crt_time timestamp,      — 音信时间

message text,            — 交互信息

custom_id int8,          — 消费者ID

pro_id int,              — 问题ID

others text              — 其他字段

);

流2,人工

create table tbl_human (

caseid int8,            — 会话ID

crt_time timestamp,      — 信息时间

message text,            — 交互音信

custom_id int8,          — 消费者ID

xiao2_id  int8,          — 小二ID

pro_id int,              — 问题ID

others text              — 其他字段

);

2、合并数据流表(这里可以拔取分区表,便于维护)

create table tbl_session (

caseid int8,            — 会话ID

crt_time timestamp,      — 信息时间

message text,            — 交互信息

custom_id int8,          — 消费者ID

xiao2_id  int8,          — 小二ID

pro_id int,              — 问题ID

stream_id int,          — 流ID, 1 表示robot, 2 表示human

others1 text,            — 流1,其他字段

others2 text            — 流2,其他字段

);

create index idx_tbl_session_1 on tbl_session (crt_time);

create index idx_tbl_session_2 on tbl_session (caseid, crt_time);

3、创造源头规则,自动将会话数据流,合并到统一数据流表单表。

透过规则,自动将数据统一到合并表。

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);

4、元数据表

略。

5、会话状态转换表(批量可重算转换表)

打个会话会涉及多条记下,这多少个DEMO的目标是找到人工复苏后,用户从机器人找答案,从而识别人工苏醒的频率。

也可以反之,求机器人回复的频率。

create table tbl_session_etl (

caseid int8 primary key,  — 会话ID

s_crt_time timestamp,      — 会话最开端的命宫

e_crt_time timestamp,      — 会话最终一条记下的岁月

robot_to_human boolean,    — 是否带有 从机器人切到人工

human_to_robot boolean    — 是否含有 从人工切到机器人

);

对话转换SQL,调度,可以重复执行。

窗口大小可调动,容忍不同数据流的到达时间差距。

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’          —
10分钟内的对话数据, 能够无限制调整这一个窗口

window w1 as (partition by caseid order by crt_time)

) t

group by caseid;

集合写入,使用如下SQL,可以重复执行。

窗口大小可调动,容忍不同数据流的抵达时刻距离。

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’            —
10分钟内的对话数据, 可以无限制调整那个窗口

window w1 as (partition by caseid order by crt_time)  — 开窗查询

) 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      — 当数码转换后的值,发送变化时,合并写入。

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            — n分钟内的对话数据,
可以随心所欲调整那些窗口

window w1 as (partition by caseid order by crt_time)  — 开窗查询

) 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      — 当数码转换后的值,发送变化时,合并写入。

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;

调度格局如下,完成机关更正:

每10秒,总计10分钟内的多寡。(不同流的到达时刻距离,容忍度为10分钟。)

每小时,总计全天内的数目。(不同流的到达时间差别,容忍度为全天。)

6、会话总结表,总计一解率 (可选,如若不总结以来,就直接询问)

天维度表

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

);

总结调度SQL,可以重复执行。

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;

特性压测

1、高并发写入会话信息

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

单条写入,约17.6万行/s.

假若批量写入,可以做到100万+ 行/s

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’);

2、实时转换调度

再者拉开写入,(写入速度14.2万行/s。)

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)

拍卖如今71万行, 耗时4.5秒。处理速度约15.7万行/s。

3、实时总括调度

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)

4、数据量

1.79亿。

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

5、性能目标:

并发度写入吞吐写入延迟

3217.6万行/s0.178毫秒

1.79亿数量打散到全天写入的话,响应速度会更快。

并发度转换吞吐转换延迟

115.7万行/s1秒

并发度总括吞吐总结延迟

11000000行1秒

计算音讯查询性能,纳秒级延迟

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

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图