使用PostgreSQL資料庫建立人物誌系統的方法

2022-10-22 14:01:13

說起巨量資料中的應用，很多同學可能馬上會想起人物誌。人物誌，英文稱之為User Profile，通過人物誌可以完美地抽象出一個使用者的資訊全貌，通過人物誌資料可以精準地分析使用者的各種行為習慣，如消費習慣、興趣愛好、能力情況等等重要使用者資訊。通常人物誌是通過給使用者建標籤系統來實現的，本文介紹瞭如何使用PostgreSQL的json資料型別來建立人物誌資料庫。

說起巨量資料中的應用，很多同學可能馬上會想起人物誌。
人物誌，英文稱之為User Profile，通過人物誌可以完美地抽象出一個使用者的資訊全貌，通過人物誌資料可以精準地分析使用者的各種行為習慣，如消費習慣、興趣愛好、能力情況等等重要使用者資訊。通常人物誌是通過給使用者建標籤系統來實現的，本文介紹瞭如何使用PostgreSQL的json資料型別來建立人物誌資料庫。

標籤模型

為了說明具體的方法方法，我們建一套簡單的兩級標籤系統：

職業：農民、工人、IT工程師、理髮師、醫生、老師、美工、律師、公務員、官員
愛好：游泳、乒乓球、羽毛球、網球、爬山、高爾夫球、滑雪、爬山、旅遊
學歷：無學歷、小學、初中、高中、中專、專科、本科、碩士、博士
性格：外向、內向、謹慎、穩重、細心、粗心、浮躁、自信

當然你也可以根據實際情況建立三級或更多級的複雜的標籤體系。

建表和造資料

下面使用一個具體的例子來說明如何使用PostgreSQL的json資料型別來建立使用者標籤資料庫。
建使用者標籤表：

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

這個表只有兩個欄位，uid表示使用者ID，而tag欄位中放了使用者的所有標籤，tag欄位型別為jsonb。
為了說明如何使用本方案，需要給表中造一些標籤資料。先建一個函數，在後面的INSERT的SQL中會呼叫此函數生成一些隨機的標籤：

CREATE OR REPLACE FUNCTION f_random_attr(attr text[], max_attr int)
RETURNS text[] AS $$
DECLARE
     i integer := 0; 
     r integer := 0;
     res  text[];
     v text;
     l integer;
     num integer;
 BEGIN
     num := (random()*max_attr)::int;
     IF num < 1 THEN
         num := 1;
     END IF;
     l := array_length(attr, 1);
     WHILE i < num LOOP
          r := round(random()*l)::int + 1;
          v := attr[r];
          IF res @> array[v] THEN
             continue;
          ELSE 
             res := array_append(res, v);
             i := i + 1;
          END IF;
     END LOOP;
     return res;
 END;
$$ LANGUAGE plpgsql;

我們通過下面的INSERT語句就可以造一些隨機的資料了：

INSERT INTO user_tag(uid, tag) 
SELECT seq, 
       json_build_object(
         '職業',
         f_random_attr(array['農民','工人','IT工程師','理髮師','醫生','老師','美工','律師','公務員','官員'], 1),
         '愛好',
         f_random_attr(array['游泳','乒乓球','羽毛球','網球','爬山','高爾夫球','滑雪','爬山','旅遊'], 5),
         '學歷',
         f_random_attr(array['無學歷','小學','初中','高中','中專','專科','本科','碩士','博士'], 1),
         '性格',
         f_random_attr(array['外向','內向','謹慎','穩重','細心','粗心','浮躁','自信'], 3))::jsonb
  FROM generate_series(1, 10000) as t(seq);

建索引和查詢

為了加快查詢，我們在tag列上建GIN索引。GIN索引是PostgreSQL中的一種特殊的索引，可以實現類似全文搜尋的功能：

CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);

這時我們如果想查詢性格為外向和細心的老師，則SQL語句：

osdba=# select * from user_tag where tag @> '{"性格":["外向","細心"]}' and tag @> '{"職業":["老師"]}';
 uid  |                                                         tag
------+----------------------------------------------------------------------------------------------------------------------
  881 | {"學歷": ["中專"], "性格": ["細心", "內向", "外向"], "愛好": ["高爾夫球"], "職業": ["老師"]}
 1031 | {"學歷": [null], "性格": ["外向", "細心"], "愛好": ["爬山"], "職業": ["老師"]}
 3313 | {"學歷": [null], "性格": ["外向", "細心"], "愛好": ["乒乓球"], "職業": ["老師"]}
 4053 | {"學歷": ["本科"], "性格": ["細心", "外向"], "愛好": ["爬山", "滑雪", "游泳"], "職業": ["老師"]}
 4085 | {"學歷": ["初中"], "性格": ["外向", "細心"], "愛好": ["網球"], "職業": ["老師"]}
 4332 | {"學歷": ["碩士"], "性格": ["外向", "細心"], "愛好": ["網球", "羽毛球"], "職業": ["老師"]}
 4997 | {"學歷": ["小學"], "性格": ["外向", "細心", "浮躁"], "愛好": ["乒乓球"], "職業": ["老師"]}
 5231 | {"學歷": ["本科"], "性格": ["外向", "細心"], "愛好": ["高爾夫球"], "職業": ["老師"]}
 5360 | {"學歷": ["無學歷"], "性格": ["浮躁", "外向", "細心"], "愛好": ["爬山", "網球", "旅遊", "羽毛球"], "職業": ["老師"]}
 6281 | {"學歷": ["專科"], "性格": ["細心", "外向", "自信"], "愛好": ["滑雪", null], "職業": ["老師"]}
 7681 | {"學歷": ["小學"], "性格": ["粗心", "外向", "細心"], "愛好": ["乒乓球"], "職業": ["老師"]}
 8246 | {"學歷": ["碩士"], "性格": ["外向", "細心"], "愛好": ["滑雪", "網球", "高爾夫球"], "職業": ["老師"]}
 8531 | {"學歷": ["碩士"], "性格": ["細心", "外向", "粗心"], "愛好": ["滑雪", "爬山"], "職業": ["老師"]}
 8618 | {"學歷": ["小學"], "性格": ["細心", "外向", "浮躁"], "愛好": ["乒乓球"], "職業": ["老師"]}
 9508 | {"學歷": ["小學"], "性格": ["浮躁", "外向", "細心"], "愛好": ["爬山", "旅遊", "高爾夫球"], "職業": ["老師"]}
(15 rows)

Time: 1.495 ms

上面SQL語句中的where條件中的 “tag @> '{"性格":["外向","細心"]}' ”中的“@>”是一個PostgreSQL中jsonb型別的特殊運運算元，意思為“包含”。
我們如果想查詢性格為外向和細心而又喜歡滑雪和游泳的醫生，則SQL語句：

osdba=# select * from user_tag where tag @> '{"性格":["外向","細心"]}' and tag @> '{"職業":["醫生"]}' and tag @>'{"愛好":["滑雪", "游泳"]}';
 uid  |                                               tag
------+--------------------------------------------------------------------------------------------------
 4469 | {"學歷": ["小學"], "性格": ["外向", "細心", "穩重"], "愛好": ["滑雪", "游泳"], "職業": ["醫生"]}
(1 row)

Time: 2.139 ms

從上面可以看出，只需要1~2ms就可以查詢出結果，原因是我們使用GIN索引，可以通過檢視一下執行計劃：

osdba=# explain select * from user_tag where tag @> '{"性格":["外向","細心"]}' and tag @> '{"職業":["醫生"]}' and tag @>'{"愛好":["滑雪", "游泳"]}';
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on user_tag  (cost=48.00..52.02 rows=1 width=153)
   Recheck Cond: ((tag @> '{"性格": ["外向", "細心"]}'::jsonb) AND (tag @> '{"職業": ["醫生"]}'::jsonb) AND (tag @> '{"愛好": ["滑雪", "游泳"]}'::jsonb))
   ->  Bitmap Index Scan on idx_user_tag_tag  (cost=0.00..48.00 rows=1 width=0)
         Index Cond: ((tag @> '{"性格": ["外向", "細心"]}'::jsonb) AND (tag @> '{"職業": ["醫生"]}'::jsonb) AND (tag @> '{"愛好": ["滑雪", "游泳"]}'::jsonb))
(4 rows)

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索引小很多，具體可以見：

osdba=# select pg_relation_size('idx_user_tag_tag');
 pg_relation_size
------------------
           245760
(1 row)

Time: 0.522 ms
osdba=# CREATE INDEX idx_user_tag_tag2 on user_tag using gin(tag jsonb_path_ops);
CREATE INDEX
Time: 46.947 ms
osdba=# select pg_relation_size('idx_user_tag_tag2');
 pg_relation_size
------------------
           147456
(1 row)

jsonb_path_ops索引使用的方法與jsonb_ops基本相同：

osdba=# drop index idx_user_tag_tag;
DROP INDEX
Time: 2.833 ms
osdba=# select * from user_tag where tag @> '{"性格":["外向","細心"]}' and tag @> '{"職業":["醫生"]}' and tag @>'{"愛好":["滑雪", "游泳"]}';
 uid  |                                               tag
------+--------------------------------------------------------------------------------------------------
 4469 | {"學歷": ["小學"], "性格": ["外向", "細心", "穩重"], "愛好": ["滑雪", "游泳"], "職業": ["醫生"]}
(1 row)

Time: 1.401 ms
osdba=# explain select * from user_tag where tag @> '{"性格":["外向","細心"]}' and tag @> '{"職業":["醫生"]}' and tag @>'{"愛好":["滑雪", "游泳"]}';
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on user_tag  (cost=24.00..28.02 rows=1 width=153)
   Recheck Cond: ((tag @> '{"性格": ["外向", "細心"]}'::jsonb) AND (tag @> '{"職業": ["醫生"]}'::jsonb) AND (tag @> '{"愛好": ["滑雪", "游泳"]}'::jsonb))
   ->  Bitmap Index Scan on idx_user_tag_tag2  (cost=0.00..24.00 rows=1 width=0)
         Index Cond: ((tag @> '{"性格": ["外向", "細心"]}'::jsonb) AND (tag @> '{"職業": ["醫生"]}'::jsonb) AND (tag @> '{"愛好": ["滑雪", "游泳"]}'::jsonb))
(4 rows)

Time: 0.634 ms

你可以寫更多的SQL來做人物誌的資料分析，這裡就不再贅述了。
當然也可以使用PostgreSQL的陣列型別建標籤系統，陣列型別也執行GIN索引，但總的來說沒有使用json型別直觀。

到此這篇關於使用PostgreSQL資料庫建立人物誌系統的文章就介紹到這了,更多相關PostgreSQL人物誌系統內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

使用PostgreSQL資料庫建立人物誌系統的方法

目錄

標籤模型

建表和造資料

建索引和查詢

熱門文章