作者 | Craig Kerstiens

译者 | 弯月，责编 | 王晓曼

头图 | CSDN 下载自东方IC

出品 | CSDN（ID：CSDNnews）

以下为译文：

我非常喜欢数据。数据可以告诉你用户在干什么，还可以提供各种深刻的见解。数据应用的一个方面就是根据历史记录或用户做出的类似选择来提供建议。

其实，多年以前，我写了一个很小的应用程序，尝试根据葡萄酒的等级来推荐葡萄酒。这是一个小应用，我只分享给了几个好友，他们中有些人与我有相似的品味，而有些人则有不同的品味。

最初，我主要是为了研究如何编写推荐引擎，但如果能够顺便找到一些喜欢的新酒，那就是意外之喜了。事实证明，尽管只有少数几种葡萄酒的评分，但这款应用的推荐依然超出了我的预期。

另外，我也很喜欢 Postgres（不奇怪吧），最近我一直在想为什么我不能直接在 Postgres 中研究机器学习呢。没错，我们有 madlib，但是我想编写自己的推荐引擎。于是，我做了简单的尝试，结果发现我可以在 Postgres 中做很多事情。下面就让我来介绍一下如何在 Postgres 内快速构建一个推荐引擎。

从现有代码着手

首先，我快速浏览了 Python 推荐的一些示例引擎。为了简单起见，我想建立一款更小巧、更简洁的应用，我并不介意使用其它库。后来，我找到了一个简洁的rec-engine（https://github.com/scottfitzcodes/rec-engine-example）示例，该示例利用了 Pandas 和简单的数据模型大幅降低了构建推荐引擎的难度。

设置初始表结构

我抽取了该示例应用程序使用的数据集，并将其转换为 SQL 进行加载：

CREATE TABLE orders (id int, product_idint);

CREATE TABLE products(id serial, nametext);

INSERT INTO orders

VALUES (1,1),(1,2),(2,3),(2,10),(2,13),(3,3),(4,8),(4,9),(4,12),(5,3),(5,5),(5,7),(5,12),(6,1),(7,5),(7,13),(8,4),(9,3),(10,3),(10,13),(11,1),(11,8),(11,4),(12,8),(12,12),(13,5),(13,2),(13,7),(14,3),(14,13),(14,5),(15,3),(15,13);

INSERT into products ("name")

VALUes ('Baseball Bat'), ('BaseballGlove'), ('Football'), ('Basketball Hoop'), ('Football Helmet'), ('BattingGloves'), ('Baseball'), ('Hockey Stick'), ('Ice Skates'), ('Soccer Ball'),('Goalie Mask'), ('Hockey Puck'), ('Cleats');

加载DataFrame

该 Python 示例直接从 CSV 加载了 DataFrame。但我想在 Postgres 中构建应用。通过上面一步，我将所有数据都存储在了表中，但是如今将其放入DataFrame中……我不是很想再解析成CSV格式。

解决这个问题的方法有很多种（比如创建 JSONB 对象、创建自定义类型等），但我选择了一种非常简单而且很容易实现的方法：将两个数组按照相同的方式排序，然后利用这两个数组创建DataFrame。

为此，我定义了一个函数，并导入了pandas：

CREATE OR REPLACE FUNCTIONgetrecommendations (id integer, orderids int[], orderedproducts int[],productids int[], productnames text[])

RETURNS json

AS $$

import pandas as pd

注意：首先你需要在 PostgreSQL 数据库安装 plpython3u。

你可能注意到，我并没有用一个数组或一个字典传递订单而是传递了两个数组，后面的产品也是两个数组。为了将数据传递到SQL函数中，我创建了如下查询：

(SELECT ARRAY(SELECT id from ordersorder by id))

接下来，我就可以将订单数据加载到 DataFrame 了：

o = {'order_id': orderids, 'product_id':orderedproducts}

orders = pd.DataFrame(data=o)

下一组数据与嵌入到PostgreSQL函数中的Python示例（https://github.com/scottfitzcodes/rec-engine-example/blob/master/engine.py）相同：

orders_for_product =orders[orders.product_id == id].order_id.unique;

relevant_orders =orders[orders.order_id.isin(orders_for_product)]

accompanying_products_by_order =relevant_orders[relevant_orders.product_id != id]

num_instance_by_accompanying_product =accompanying_products_by_order.groupby("product_id")["product_id"].count.reset_index(name="instances")

num_orders_for_product =orders_for_product.size

product_instances = pd.DataFrame(num_instance_by_accompanying_product)

product_instances["frequency"]= product_instances["instances"]/num_orders_for_product

recommended_products =pd.DataFrame(product_instances.sort_values("frequency",ascending=False).head(3))

产品部分的处理与订单相同：创建字典，然后加载 DataFrame。最后，将结果集作为 JSONB 对象返回。两部分结合到一起，完整的函数如下所示：

CREATE OR REPLACE FUNCTIONgetrecommendations (id integer, orderids int[], orderedproducts int[],productids int[], productnames text[])

RETURNS json

AS $$

import pandas as pd

o = {'order_id': orderids, 'product_id': orderedproducts}

orders = pd.DataFrame(data=o)

orders_for_product = orders[orders.product_id == id].order_id.unique;

relevant_orders = orders[orders.order_id.isin(orders_for_product)]

accompanying_products_by_order =relevant_orders[relevant_orders.product_id != id]

num_instance_by_accompanying_product =accompanying_products_by_order.groupby("product_id")["product_id"].count.reset_index(name="instances")

num_orders_for_product = orders_for_product.size

product_instances = pd.DataFrame(num_instance_by_accompanying_product)

product_instances["frequency"] =product_instances["instances"]/num_orders_for_product

recommended_products = pd.DataFrame(product_instances.sort_values("frequency",ascending=False).head(3))

p = {'product_id': productids, 'name': productnames}

products = pd.DataFrame(data=p)

recommended_products = pd.merge(recommended_products, products,on="product_id")

return recommended_products.to_json(orient="table")

$$ LANGUAGE 'plpython3u';

运行推荐引擎

直接在 SQL 中调用该函数，就可以获得推荐结果：

SELECT json_pretty(getrecommendations(

3,

(SELECT ARRAY(SELECT id from orders order by id)),

(SELECT ARRAY(SELECT product_id from orders order by id)),

(SELECT ARRAY(SELECT id from products order by id)),

(SELECT ARRAY(SELECT name from products order by id))

));

{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"product_id","type":"integer"},{"name":"instances","type":"integer"},{"name":"frequency","type":"number"},{"name":"name","type":"string"}],"primaryKey":["index"],"pandas_version":"0.20.0"},"data":[{"index":0,"product_id":13,"instances":4,"frequency":0.5714285714,"name":"Cleats"},{"index":1,"product_id":5,"instances":2,"frequency":0.2857142857,"name":"FootballHelmet"},{"index":2,"product_id":7,"instances":1,"frequency":0.1428571429,"name":"Baseball"}]}

虽然这种做法可行，但我并不推荐。直接将所有应用程序逻辑嵌入数据库，会导致跟踪迁移和发布的难度加大。同时，它还需要一个复杂的流水线每晚提取数据并加载到 Spark 中，生成结果，然后将其反馈到数据库，这个工作量可不容小觑。对于 Plpython3u 和 Pandas 来说，每天使用 pg_cron 运行上述代码可能是一个更简单的解决方案。

原文：https://info.crunchydata.com/blog/recommendation_engine_in_postgres_with_pandas_and_python

本文为 CSDN 翻译，转载请注明来源出处。