说起大数据中的应用,很多同学可能马上会想起用户画像。

用户画像,英文称之为User Profile,通过用户画像可以完美地抽象出一个用户的信息全貌,通过用户画像数据可以精准地分析用户的各种行为习惯,如消费习惯、兴趣爱好、能力情况等等重要用户信息。

通常用户画像是通过给用户建标签系统来实现的,本文介绍了如何使用PostgreSQL的json数据类型来建立用户画像数据库。

1. 标签模型

为了说明具体的方法方法,我们建一套简单的两级标签系统:

  • 职业: 农民、工人、IT工程师、理发师、医生、老师、美工、律师、公务员、官员
  • 爱好:游泳、乒乓球、羽毛球、网球、爬山、高尔夫球、滑雪、爬山、旅游
  • 学历:无学历、小学、初中、高中、中专、专科、本科、硕士、博士
  • 性格:外向、内向、谨慎、稳重、细心、粗心、浮躁、自信

当然你也可以根据实际情况建立三级或更多级的复杂的标签体系。

2. 建表和造数据

下面使用一个具体的例子来说明如何使用PostgreSQL的json数据类型来建立用户标签数据库。

建用户标签表:

  1. CREATE TABLE user_tag(uid serial primary key, tag jsonb);

这个表只有两个字段,uid表示用户ID,而tag字段中放了用户的所有标签,tag字段类型为jsonb。

为了说明如何使用本方案,需要给表中造一些标签数据。

先建一个函数,在后面的INSERT的SQL中会调用此函数生成一些随机的标签:

  1. CREATE OR REPLACE FUNCTION f_random_attr(attr text[], max_attr int)
  2. RETURNS text[] AS $$
  3. DECLARE
  4. i integer := 0;
  5. r integer := 0;
  6. res text[];
  7. v text;
  8. l integer;
  9. num integer;
  10. BEGIN
  11. num := (random()*max_attr)::int;
  12. IF num < 1 THEN
  13. num := 1;
  14. END IF;
  15. l := array_length(attr, 1);
  16. WHILE i < num LOOP
  17. r := round(random()*l)::int + 1;
  18. v := attr[r];
  19. IF res @> array[v] THEN
  20. continue;
  21. ELSE
  22. res := array_append(res, v);
  23. i := i + 1;
  24. END IF;
  25. END LOOP;
  26. return res;
  27. END;
  28. $$ LANGUAGE plpgsql;

我们通过下面的INSERT语句就可以造一些随机的数据了:

  1. INSERT INTO user_tag(uid, tag)
  2. SELECT seq,
  3. json_build_object(
  4. '职业',
  5. f_random_attr(array['农民','工人','IT工程师','理发师','医生','老师','美工','律师','公务员','官员'], 1),
  6. '爱好',
  7. f_random_attr(array['游泳','乒乓球','羽毛球','网球','爬山','高尔夫球','滑雪','爬山','旅游'], 5),
  8. '学历',
  9. f_random_attr(array['无学历','小学','初中','高中','中专','专科','本科','硕士','博士'], 1),
  10. '性格',
  11. f_random_attr(array['外向','内向','谨慎','稳重','细心','粗心','浮躁','自信'], 3))::jsonb
  12. FROM generate_series(1, 10000) as t(seq);

查看生成的数据:

  1. osdba=# select * from user_tag limit 5;
  2. uid | tag
  3. -----+--------------------------------------------------------------------------------------------------
  4. 1 | {"学历": ["博士"], "性格": [null, "外向"], "爱好": ["羽毛球", "滑雪", "爬山"], "职业": ["老师"]}
  5. 2 | {"学历": ["中专"], "性格": ["浮躁", "内向", "自信"], "爱好": ["旅游"], "职业": ["美工"]}
  6. 3 | {"学历": ["无学历"], "性格": ["细心"], "爱好": ["羽毛球", null, "爬山"], "职业": ["律师"]}
  7. 4 | {"学历": [null], "性格": ["谨慎", "浮躁"], "爱好": ["爬山"], "职业": ["律师"]}
  8. 5 | {"学历": ["博士"], "性格": ["浮躁", "稳重"], "爱好": ["游泳"], "职业": ["理发师"]}
  9. (5 rows)

3. 建索引和查询

为了加快查询,我们在tag列上建GIN索引。GIN索引是PostgreSQL中的一种特殊的索引,可以实现类似全文搜索的功能:

  1. CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);

这时我们如果想查询性格为外向和细心的老师,则SQL语句:

  1. osdba=# select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["老师"]}';
  2. uid | tag
  3. ------+----------------------------------------------------------------------------------------------------------------------
  4. 881 | {"学历": ["中专"], "性格": ["细心", "内向", "外向"], "爱好": ["高尔夫球"], "职业": ["老师"]}
  5. 1031 | {"学历": [null], "性格": ["外向", "细心"], "爱好": ["爬山"], "职业": ["老师"]}
  6. 3313 | {"学历": [null], "性格": ["外向", "细心"], "爱好": ["乒乓球"], "职业": ["老师"]}
  7. 4053 | {"学历": ["本科"], "性格": ["细心", "外向"], "爱好": ["爬山", "滑雪", "游泳"], "职业": ["老师"]}
  8. 4085 | {"学历": ["初中"], "性格": ["外向", "细心"], "爱好": ["网球"], "职业": ["老师"]}
  9. 4332 | {"学历": ["硕士"], "性格": ["外向", "细心"], "爱好": ["网球", "羽毛球"], "职业": ["老师"]}
  10. 4997 | {"学历": ["小学"], "性格": ["外向", "细心", "浮躁"], "爱好": ["乒乓球"], "职业": ["老师"]}
  11. 5231 | {"学历": ["本科"], "性格": ["外向", "细心"], "爱好": ["高尔夫球"], "职业": ["老师"]}
  12. 5360 | {"学历": ["无学历"], "性格": ["浮躁", "外向", "细心"], "爱好": ["爬山", "网球", "旅游", "羽毛球"], "职业": ["老师"]}
  13. 6281 | {"学历": ["专科"], "性格": ["细心", "外向", "自信"], "爱好": ["滑雪", null], "职业": ["老师"]}
  14. 7681 | {"学历": ["小学"], "性格": ["粗心", "外向", "细心"], "爱好": ["乒乓球"], "职业": ["老师"]}
  15. 8246 | {"学历": ["硕士"], "性格": ["外向", "细心"], "爱好": ["滑雪", "网球", "高尔夫球"], "职业": ["老师"]}
  16. 8531 | {"学历": ["硕士"], "性格": ["细心", "外向", "粗心"], "爱好": ["滑雪", "爬山"], "职业": ["老师"]}
  17. 8618 | {"学历": ["小学"], "性格": ["细心", "外向", "浮躁"], "爱好": ["乒乓球"], "职业": ["老师"]}
  18. 9508 | {"学历": ["小学"], "性格": ["浮躁", "外向", "细心"], "爱好": ["爬山", "旅游", "高尔夫球"], "职业": ["老师"]}
  19. (15 rows)
  20. Time: 1.495 ms

上面SQL语句中的where条件中的 “tag @> ‘{“性格”:[“外向”,”细心”]}’ ”中的“@>”是一个PostgreSQL中jsonb类型的特殊运算符,意思为“包含”。

我们如果想查询性格为外向和细心而又喜欢滑雪和游泳的医生,则SQL语句:

  1. osdba=# select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["医生"]}' and tag @>'{"爱好":["滑雪", "游泳"]}';
  2. uid | tag
  3. ------+--------------------------------------------------------------------------------------------------
  4. 4469 | {"学历": ["小学"], "性格": ["外向", "细心", "稳重"], "爱好": ["滑雪", "游泳"], "职业": ["医生"]}
  5. (1 row)
  6. Time: 2.139 ms

从上面可以看出,只需要1~2ms就可以查询出结果,原因是我们使用GIN索引,可以通过查看一下执行计划:

  1. osdba=# explain select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["医生"]}' and tag @>'{"爱好":["滑雪", "游泳"]}';
  2. QUERY PLAN
  3. --------------------------------------------------------------------------------------------------------------------------------------------------------------
  4. Bitmap Heap Scan on user_tag (cost=48.00..52.02 rows=1 width=153)
  5. Recheck Cond: ((tag @> '{"性格": ["外向", "细心"]}'::jsonb) AND (tag @> '{"职业": ["医生"]}'::jsonb) AND (tag @> '{"爱好": ["滑雪", "游泳"]}'::jsonb))
  6. -> Bitmap Index Scan on idx_user_tag_tag (cost=0.00..48.00 rows=1 width=0)
  7. Index Cond: ((tag @> '{"性格": ["外向", "细心"]}'::jsonb) AND (tag @> '{"职业": ["医生"]}'::jsonb) AND (tag @> '{"爱好": ["滑雪", "游泳"]}'::jsonb))
  8. (4 rows)
  9. Time: 1.736 ms

GIN索引是PostgreSQL中的核武器,大家从上面就可以看出GIN索引的强大了。GIN索除了可以用在json数据类型上,也可以使用到数组类型中。

jsonb为GIN索引提供了两类索引操作符(你可以认为是两种索引):

  • jsonb_ops,这是默认,上面我们建的索引就是这个类型的,
  • jsonb_path_ops

jsonb_ops可以支持“@>”、“?”、“?&”、“?|”等操作符进行查询,这些操作符的意思请见:官方文档

而jsonb_path_ops只支持“@>”操作符,当然jsonb_path_ops索引要比json_ops索引小很多,具体可以见:

  1. osdba=# select pg_relation_size('idx_user_tag_tag');
  2. pg_relation_size
  3. ------------------
  4. 245760
  5. (1 row)
  6. Time: 0.522 ms
  7. osdba=# CREATE INDEX idx_user_tag_tag2 on user_tag using gin(tag jsonb_path_ops);
  8. CREATE INDEX
  9. Time: 46.947 ms
  10. osdba=# select pg_relation_size('idx_user_tag_tag2');
  11. pg_relation_size
  12. ------------------
  13. 147456
  14. (1 row)

jsonb_path_ops索引使用的方法与jsonb_ops基本相同:

  1. osdba=# drop index idx_user_tag_tag;
  2. DROP INDEX
  3. Time: 2.833 ms
  4. osdba=# select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["医生"]}' and tag @>'{"爱好":["滑雪", "游泳"]}';
  5. uid | tag
  6. ------+--------------------------------------------------------------------------------------------------
  7. 4469 | {"学历": ["小学"], "性格": ["外向", "细心", "稳重"], "爱好": ["滑雪", "游泳"], "职业": ["医生"]}
  8. (1 row)
  9. Time: 1.401 ms
  10. osdba=# explain select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["医生"]}' and tag @>'{"爱好":["滑雪", "游泳"]}';
  11. QUERY PLAN
  12. --------------------------------------------------------------------------------------------------------------------------------------------------------------
  13. Bitmap Heap Scan on user_tag (cost=24.00..28.02 rows=1 width=153)
  14. Recheck Cond: ((tag @> '{"性格": ["外向", "细心"]}'::jsonb) AND (tag @> '{"职业": ["医生"]}'::jsonb) AND (tag @> '{"爱好": ["滑雪", "游泳"]}'::jsonb))
  15. -> Bitmap Index Scan on idx_user_tag_tag2 (cost=0.00..24.00 rows=1 width=0)
  16. Index Cond: ((tag @> '{"性格": ["外向", "细心"]}'::jsonb) AND (tag @> '{"职业": ["医生"]}'::jsonb) AND (tag @> '{"爱好": ["滑雪", "游泳"]}'::jsonb))
  17. (4 rows)
  18. Time: 0.634 ms

5. 结尾

你可以写更多的SQL来做用户画像的数据分析,这里就不再赘述了。

当然也可以使用PostgreSQL的数组类型建标签系统,数组类型也运行GIN索引,但总的来说没有使用json类型直观。

0 评论  
添加一条新评论