作者 | 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 翻譯，轉載請註明來源出處。