Currently browsing category

PostgreSQL

以Server的模式运行pgAdmin4

pgAdmin4 是随PostgreSQL9.6推出的新一代PostgreSQL管理工具。 pgAdmin4默认是以python的server端和qtwebkit的客户端的组合但桌面工具发布的,README中也说了可以以独立的Server模式运行,本文(以python2.7为例)就介绍下Ubunte(CentOS相差不多)下如何编译并以Server模式运行它。 首先安装virtualenv虚拟环境

下载pgAdmin4的源代码包,两张模式

或者

requirements_py2.txt有个小bug,fix先,安装前确保PostgreSQL 开发库已经安装,否则报pg_config未找到的错误,具体安装方法如下,

然后安装依赖库

安装pgAdmin4,按提示输入邮箱地址和密码,

到这一步,就算基本安装完成,可以运行了,

然后你会发现,只能本机访问,端口是5050,这不是我想要的,

  …

PostgreSQL下查找与去除重复数据

有时候难免会导入重复的数据(一行所有的列都重复),问题是如何找出来呢? 假设有表 tbl,结构如下,

如何判断是否存在id, href,title和image都重复的数据呢(这种情况一般发生在导入重复)?

如何判断是否存在重复href,title和image, 但id不同的数据呢(这种一般是OLTP的新增重复)?

接下来就是这样的数据如何只保留最早插入的数据,而删除后面重复插入的数据?

    reference, https://wiki.postgresql.org/wiki/Deleting_duplicates

PostgreSQL与交叉表查询

PostgreSQL提供了一个tablefunc 模块,内置了多个函数,其中就有crosstab(交叉表,又叫行转列,或者长表转宽表),具体使用参见PostgreSQL文档(中文,英文)。 如果不清楚生成的宽表有多少列,或者列太多,手工敲很容易吃力不讨好,那么可以借助这个函数(pivotcode)来简化工作,

但这个函数,有如下的缺点, 如果返回的列有空格,需要自己手工修改 可能会有大量的Null值,而不是0 返回的是一个SQL,你需要copy出来后自己再执行(虽然也可以改进下,直接生成表) 下面这个plpython函数,pivotmytable,可以很好地解决上面的问题,python 2和python3(只需讲函数里的plpythonu替换为plpython3u即可)均支持,

 

postgres_fdw-PostgreSQL外部数据封装器的使用

我的PostgreSQL数据库在Windows上,MADlib不支持,于是使用Docker部署了个Linux版本的PostgreSQL,并且安装了MADlib插件,利用postgres_fdw就可以方便分析Windows下的数据库进行分析了。 postgres_fdw模块提供外部数据封装器的功能,PostgreSQL通过 它可以访问存储在外部的 PostgreSQL服务器上的数据。 本模块提供的功能不但涵盖老版本中dblink模块实现的功能, 而且postgres_fdw提供更加透明和符合标准的语法来访问远程表,并在许多情况下 提供更好的性能。 使用postgres_fdw模块做远程访问的准备: 使用CREATE EXTENSION语句安装postgres_fdw.

  使用CREATE SERVER语句,为每个需要连接的远程数据库 创建一个外部服务器对象。指定除了user和password 以外的连接信息作为服务器对象的选项。

  使用CREATE USER MAPPING语句,为每个需要通过外部服务器 访问的数据库创建用户映射。指定远程的和密码作为映射用户的user …

PostgreSQL-利用递归的方法获得一个数组的所有子数组

例子,

实际测试下来,存在性能问题,不知道是否仍然有优化空间, 另外一个实现

回头比较下两个性能差异(目前看来两个都满足我做关联规则时实现多推多的情况)。   P.S. PL/Python 版本,

 

PostgreSQL-关联规则的纯SQL实现

该SQL是基于Apriori算法的一个尝试(代码比较粗糙),并且只实现了一推一的情况,对照MadLib的结果做了验证,没有问题。

关于transaction数据的准备,customer_id 可以是order_id(购物篮), 也可以基于customer_id, brand可以是品牌,产品,品类中的一种,或者品类和品牌的组合(如果需要的话,比如category||’~’||brand)。 结果,可以根据需要对confidence, support和lift进行筛选。 Todo,后续可能会依据PostgreSQL的Array来把所有可能的子集都查出来,然后生成完整的关联规则。  

csv2table让PostgreSQL下导入csv不再那么麻烦

PostgreSQL下使用Copy命令导入csv,速度很快,但你得先分析CSV,创建表,有了csv2table,自动创建表并导入数据,一气呵成。

P.S. 其实对于大的CSV文件,有时我会用

这样的命令来取一部分进行分析 并用R读取并来导入(自动创建表),然后再用copy来快速导入。