个性化推荐系统是如何搭建的？阿里算法专家带你3步get！

背景信息

个性化推荐系统概述

个性化推荐系统中数据库表结构设计

CREATE TABLE news (
  news_id bigint,
  create_time timestamp,
  title varchar(100),
  content varchar(200),
  keywords varchar(50),
  click_times bigint,
  two_hour_click_times bigint,
  news_vector real[],
  primary key (news_id)
) distributed by (news_id);

CREATE TABLE browses_history (
  browse_id bigint,
  news_id bigint,
  person_id bigint,
  browse_time timestamp,
  primary key (browse_id)
) distributed by (browse_id);

CREATE TABLE person(
  person_id bigint,
  age bigint,
  star float,
  primary key (person_id)
) distributed by (person_id);

三步实现一个个性化推荐系统

select feature_extractor('text', 'ADB For PG is very good!');

insert into news(news_id, create_time, title, content,
                 keywords, click_times,two_hour_click_times)
values(1, now(),'韩国军方:朝鲜在平安北道一带向东发射不明飞行物','据韩国联合参谋本部消息，当地时间今天下午16时30分左右，朝鲜在其平安北道一带向东发射不明飞行物。', '韩国 朝鲜 不明飞行物', 123, 3);

select keywords
from Person p, Browses_History bh, News n
where p.person_id = bh.person_id and bh.news_id = n.news_id and p.person_id = 9527;
（2）将用户浏览关键词转换为用户特征向量。

select feature_extractor('text', 'NBA 体育 总决赛 热火 火箭'));

select news_id, title, content, (extract(epoch from (now()-create_time)) * w1 + click_times/extract(epoch from (now()-create_time)) * w2 + two_hour_click_times/extract(epoch from (now()-create_time)) * w3 + ann_distance * w4) as rank_score
from (select *, l2_distance(news_vector, feature_extractor('textf', 'NBA 体育 总决赛 热火 火箭')) as ann_distance from news order by ann_distance desc limit 500) S
order by rank_score desc;

• ann_distance：用户与新闻的相关度。

• create_time：新闻的创建时间。

• click_times/(now()-create_time)：新闻热度点击率。

• two_hour_click_times/(now()-create_time)：新闻近期热度点击率。

• w1、w2、w3、w4：逻辑回归模型学习中各个属性的权重。

小结