潘金莲改变了历史吗 - PostgreSQL舆情事件分析应用-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

潘金莲改变了历史吗 - PostgreSQL舆情事件分析应用

简介:

标签

PostgreSQL , 独立事件分析 , 舆情分析 , 舆情事件 , 相关事件 , 行为轨迹 , 独立事件的流水相关性分析 , PostgreSQL服务端编程实践


背景

潘金莲改变了历史吗?

pic

网上的段子

潘金莲撑开窗户,撑窗户滴棍子掉下去了,于是西门庆看到了,于是他们相遇了。如果潘金莲同学当时没有开窗,那么她就不会遇到西门庆。   
  
如果没有遇到西门庆,那么她就不会被迫出轨,那样武松哥哥就不会怒发冲冠为红颜,这样他就不会奔上梁山。   
  
武松就不会奔上梁上,不会奔上梁山之后,哪怕水泊梁山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 递归死循环案例及解法》

《PostgreSQL 递归查询一例 - 资金累加链》

《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》

《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

小结

PostgreSQL的递归语法、plpgsql编程,可以很好的满足舆情分析中事件前后事件的分析需求。

再利用PostgreSQL的热点词分析,聚类分析或者使用PostgreSQL提供的MADlib机器学习库,又或者使用plR进行分析。找出事件之间的关联关系。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接