标签
PostgreSQL , 独立事件分析 , 舆情分析 , 舆情事件 , 相关事件 , 行为轨迹 , 独立事件的流水相关性分析 , PostgreSQL服务端编程实践
背景
潘金莲改变了历史吗？
网上的段子
潘金莲撑开窗户，撑窗户滴棍子掉下去了，于是西门庆看到了，于是他们相遇了。如果潘金莲同学当时没有开窗，那么她就不会遇到西门庆。
如果没有遇到西门庆，那么她就不会被迫出轨，那样武松哥哥就不会怒发冲冠为红颜，这样他就不会奔上梁山。
武松就不会奔上梁上，不会奔上梁山之后，哪怕水泊梁山107将依旧轰轰烈烈，但是宋江和方腊的战役，方腊也不会被武松单臂擒住。
只要武松治不了方腊，枭雄方腊就能取得大宋的江山。
只要方腊取得了大宋的江山，就不会有靖康耻，不会有偏安一隅，不会有金兵入关。
金兵不入关，就不会有后来的大清朝。
没有大清朝，当然也不会有后来的闭关锁国，没有慈禧太后。
没有慈禧太后，没有闭关锁国，自然也不会有八国联军侵略中国啊，不会有神马鸦片战争啊。
没有这些杀千刀的战争和不平等条约，中国说不定凭借五千年的文化首先就发展资本主义了。
发展了资本主义，发展到今天，说不定中国早就超过了美国、小日本神马的，赶超了几百年了。已经是最发达的最强悍的国家了。
所以，谁穿越一下告诉潘金莲，你有事没事开神马窗户啊！！！
类似蝴蝶效应，历史是很有趣的事情。
实际上在我们的生活中也有这样的现象或者需求，比如某些业务系统，会记录事件，流水账那样。
然后业务可能想知道某类事件发生后，接下来发生了什么，比如说房价调控措施出台后的事件，是不是有大量资金涌入股票市场？
说道这里，我要把PostgreSQL数据库搬出来，到底怎么实现上述需求呢？
模拟现实
1. 构建事件输入的表结构
create table event(
id serial8 primary key, -- 自增序列，用于区分事件的先后顺序
class text, -- 事件类型
info text, -- 事件描述
crt_time timestamp -- 事件发生的时间
);
2. 插入1000万事件记录，其中5000个事件的种类。
insert into event (class, info , crt_time) select (5000*random())::int::text, 'test', clock_timestamp() from generate_series(1,10000000);
3. 针对事件种类，创建索引
create index idx_event_class on event (class);
需求
查询某个事件发生后，若干个接下来发生的事件
使用函数可以轻松实现这个需求
create or replace function f(
sql text, -- 查询要分析的目标事件对应的ID
v_class text, -- 目标事件的类别
v_n int8, -- 要查看多少个接下来发生的事件
v_limit int -- 分析几次事件，如果要输出所有的，那么可以输入一个较大值。
) returns setof event as $$
declare
v_id int8;
begin
for v_id in execute sql loop
return query select * from event where id>=v_id order by id limit v_n+1;
v_limit := v_limit - 1;
if v_limit<=0 then
return;
end if;
end loop;
end;
$$ language plpgsql strict;
查询举例
查询事件类别为1的事件，它后面发生的2个事件，输出10次分析结果。
postgres=# select * from f('select id from event where class=$$1$$', '1', 2, 10);
id | class | info | crt_time
-------+-------+------+----------------------------
1592 | 1 | test | 2017-03-31 15:07:23.77348
1593 | 3032 | test | 2017-03-31 15:07:23.773483
1594 | 3409 | test | 2017-03-31 15:07:23.773486
2784 | 1 | test | 2017-03-31 15:07:23.777265
2785 | 2819 | test | 2017-03-31 15:07:23.777268
2786 | 87 | test | 2017-03-31 15:07:23.777271
12176 | 1 | test | 2017-03-31 15:07:23.807489
12177 | 2586 | test | 2017-03-31 15:07:23.807491
12178 | 4101 | test | 2017-03-31 15:07:23.807494
19398 | 1 | test | 2017-03-31 15:07:23.83072
19399 | 1179 | test | 2017-03-31 15:07:23.830723
19400 | 4237 | test | 2017-03-31 15:07:23.830726
19571 | 1 | test | 2017-03-31 15:07:23.831296
19572 | 4368 | test | 2017-03-31 15:07:23.831299
19573 | 2313 | test | 2017-03-31 15:07:23.831302
24708 | 1 | test | 2017-03-31 15:07:23.847794
24709 | 1327 | test | 2017-03-31 15:07:23.847797
24710 | 4584 | test | 2017-03-31 15:07:23.847799
29756 | 1 | test | 2017-03-31 15:07:23.864234
29757 | 4386 | test | 2017-03-31 15:07:23.864237
29758 | 3044 | test | 2017-03-31 15:07:23.864239
30224 | 1 | test | 2017-03-31 15:07:23.865765
30225 | 4704 | test | 2017-03-31 15:07:23.865768
30226 | 332 | test | 2017-03-31 15:07:23.865771
32004 | 1 | test | 2017-03-31 15:07:23.871554
32005 | 219 | test | 2017-03-31 15:07:23.871557
32006 | 3548 | test | 2017-03-31 15:07:23.871559
36472 | 1 | test | 2017-03-31 15:07:23.886097
36473 | 640 | test | 2017-03-31 15:07:23.8861
36474 | 1139 | test | 2017-03-31 15:07:23.886103
(30 rows)
利用以上结果，我们就可以知道发生了事件1之后，都发生了其他什么事件。
至于你要拿这些结果进行接下来的分析，可以使用类似关键词的热点分析方法，或者使用PostgreSQL提供的MADlib机器学习库，又或者使用plR进行分析。
总之你肯定有方法找出事件之间的关联关系。
非独立事件的相关性分析
因为前面分析的都是独立事件，即本身就没有相关性的，所以需要通过函数的方法来输出结果。
对于非独立事件，比如说用户逛淘宝的点鼠标的行为，在点了某个商品后，又点了哪些其他商品。
对于非独立事件，在结构设计上，就有关联字段，比如USERID，所以我们可以很方便的进行关联。
使用PostgreSQL递归调用，就可以对非独立事件进行轨迹分析。
例子
《PostgreSQL 递归查询CASE - 树型路径分组输出》
《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》
《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》
《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》
《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》
《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》
小结
PostgreSQL的递归语法、plpgsql编程，可以很好的满足舆情分析中事件前后事件的分析需求。
再利用PostgreSQL的热点词分析，聚类分析或者使用PostgreSQL提供的MADlib机器学习库，又或者使用plR进行分析。找出事件之间的关联关系。