使用PostgreSQL數(shù)據(jù)庫(kù)建立用戶畫(huà)像系統(tǒng)的方法
說(shuō)起大數(shù)據(jù)中的應(yīng)用,很多同學(xué)可能馬上會(huì)想起用戶畫(huà)像。用戶畫(huà)像,英文稱之為User Profile,通過(guò)用戶畫(huà)像可以完美地抽象出一個(gè)用戶的信息全貌,通過(guò)用戶畫(huà)像數(shù)據(jù)可以精準(zhǔn)地分析用戶的各種行為習(xí)慣,如消費(fèi)習(xí)慣、興趣愛(ài)好、能力情況等等重要用戶信息。 通常用戶畫(huà)像是通過(guò)給用戶建標(biāo)簽系統(tǒng)來(lái)實(shí)現(xiàn)的,本文介紹了如何使用PostgreSQL的json數(shù)據(jù)類型來(lái)建立用戶畫(huà)像數(shù)據(jù)庫(kù)。
說(shuō)起大數(shù)據(jù)中的應(yīng)用,很多同學(xué)可能馬上會(huì)想起用戶畫(huà)像。
用戶畫(huà)像,英文稱之為User Profile,通過(guò)用戶畫(huà)像可以完美地抽象出一個(gè)用戶的信息全貌,通過(guò)用戶畫(huà)像數(shù)據(jù)可以精準(zhǔn)地分析用戶的各種行為習(xí)慣,如消費(fèi)習(xí)慣、興趣愛(ài)好、能力情況等等重要用戶信息。 通常用戶畫(huà)像是通過(guò)給用戶建標(biāo)簽系統(tǒng)來(lái)實(shí)現(xiàn)的,本文介紹了如何使用PostgreSQL的json數(shù)據(jù)類型來(lái)建立用戶畫(huà)像數(shù)據(jù)庫(kù)。
標(biāo)簽?zāi)P?/h2>
為了說(shuō)明具體的方法方法,我們建一套簡(jiǎn)單的兩級(jí)標(biāo)簽系統(tǒng):
- 職業(yè): 農(nóng)民、工人、IT工程師、理發(fā)師、醫(yī)生、老師、美工、律師、公務(wù)員、官員
- 愛(ài)好:游泳、乒乓球、羽毛球、網(wǎng)球、爬山、高爾夫球、滑雪、爬山、旅游
- 學(xué)歷:無(wú)學(xué)歷、小學(xué)、初中、高中、中專、??啤⒈究?、碩士、博士
- 性格:外向、內(nèi)向、謹(jǐn)慎、穩(wěn)重、細(xì)心、粗心、浮躁、自信
當(dāng)然你也可以根據(jù)實(shí)際情況建立三級(jí)或更多級(jí)的復(fù)雜的標(biāo)簽體系。
建表和造數(shù)據(jù)
下面使用一個(gè)具體的例子來(lái)說(shuō)明如何使用PostgreSQL的json數(shù)據(jù)類型來(lái)建立用戶標(biāo)簽數(shù)據(jù)庫(kù)。
建用戶標(biāo)簽表:
CREATE TABLE user_tag(uid serial primary key, tag jsonb);
這個(gè)表只有兩個(gè)字段,uid表示用戶ID,而tag字段中放了用戶的所有標(biāo)簽,tag字段類型為jsonb。
為了說(shuō)明如何使用本方案,需要給表中造一些標(biāo)簽數(shù)據(jù)。 先建一個(gè)函數(shù),在后面的INSERT的SQL中會(huì)調(diào)用此函數(shù)生成一些隨機(jī)的標(biāo)簽:
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;
我們通過(guò)下面的INSERT語(yǔ)句就可以造一些隨機(jī)的數(shù)據(jù)了:
INSERT INTO user_tag(uid, tag) SELECT seq, json_build_object( '職業(yè)', f_random_attr(array['農(nóng)民','工人','IT工程師','理發(fā)師','醫(yī)生','老師','美工','律師','公務(wù)員','官員'], 1), '愛(ài)好', f_random_attr(array['游泳','乒乓球','羽毛球','網(wǎng)球','爬山','高爾夫球','滑雪','爬山','旅游'], 5), '學(xué)歷', f_random_attr(array['無(wú)學(xué)歷','小學(xué)','初中','高中','中專','???,'本科','碩士','博士'], 1), '性格', f_random_attr(array['外向','內(nèi)向','謹(jǐn)慎','穩(wěn)重','細(xì)心','粗心','浮躁','自信'], 3))::jsonb FROM generate_series(1, 10000) as t(seq);
建索引和查詢
為了加快查詢,我們?cè)趖ag列上建GIN索引。GIN索引是PostgreSQL中的一種特殊的索引,可以實(shí)現(xiàn)類似全文搜索的功能:
CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);
這時(shí)我們?nèi)绻氩樵冃愿駷橥庀蚝图?xì)心的老師,則SQL語(yǔ)句:
osdba=# select * from user_tag where tag @> '{"性格":["外向","細(xì)心"]}' and tag @> '{"職業(yè)":["老師"]}'; uid | tag ------+---------------------------------------------------------------------------------------------------------------------- 881 | {"學(xué)歷": ["中專"], "性格": ["細(xì)心", "內(nèi)向", "外向"], "愛(ài)好": ["高爾夫球"], "職業(yè)": ["老師"]} 1031 | {"學(xué)歷": [null], "性格": ["外向", "細(xì)心"], "愛(ài)好": ["爬山"], "職業(yè)": ["老師"]} 3313 | {"學(xué)歷": [null], "性格": ["外向", "細(xì)心"], "愛(ài)好": ["乒乓球"], "職業(yè)": ["老師"]} 4053 | {"學(xué)歷": ["本科"], "性格": ["細(xì)心", "外向"], "愛(ài)好": ["爬山", "滑雪", "游泳"], "職業(yè)": ["老師"]} 4085 | {"學(xué)歷": ["初中"], "性格": ["外向", "細(xì)心"], "愛(ài)好": ["網(wǎng)球"], "職業(yè)": ["老師"]} 4332 | {"學(xué)歷": ["碩士"], "性格": ["外向", "細(xì)心"], "愛(ài)好": ["網(wǎng)球", "羽毛球"], "職業(yè)": ["老師"]} 4997 | {"學(xué)歷": ["小學(xué)"], "性格": ["外向", "細(xì)心", "浮躁"], "愛(ài)好": ["乒乓球"], "職業(yè)": ["老師"]} 5231 | {"學(xué)歷": ["本科"], "性格": ["外向", "細(xì)心"], "愛(ài)好": ["高爾夫球"], "職業(yè)": ["老師"]} 5360 | {"學(xué)歷": ["無(wú)學(xué)歷"], "性格": ["浮躁", "外向", "細(xì)心"], "愛(ài)好": ["爬山", "網(wǎng)球", "旅游", "羽毛球"], "職業(yè)": ["老師"]} 6281 | {"學(xué)歷": ["專科"], "性格": ["細(xì)心", "外向", "自信"], "愛(ài)好": ["滑雪", null], "職業(yè)": ["老師"]} 7681 | {"學(xué)歷": ["小學(xué)"], "性格": ["粗心", "外向", "細(xì)心"], "愛(ài)好": ["乒乓球"], "職業(yè)": ["老師"]} 8246 | {"學(xué)歷": ["碩士"], "性格": ["外向", "細(xì)心"], "愛(ài)好": ["滑雪", "網(wǎng)球", "高爾夫球"], "職業(yè)": ["老師"]} 8531 | {"學(xué)歷": ["碩士"], "性格": ["細(xì)心", "外向", "粗心"], "愛(ài)好": ["滑雪", "爬山"], "職業(yè)": ["老師"]} 8618 | {"學(xué)歷": ["小學(xué)"], "性格": ["細(xì)心", "外向", "浮躁"], "愛(ài)好": ["乒乓球"], "職業(yè)": ["老師"]} 9508 | {"學(xué)歷": ["小學(xué)"], "性格": ["浮躁", "外向", "細(xì)心"], "愛(ài)好": ["爬山", "旅游", "高爾夫球"], "職業(yè)": ["老師"]} (15 rows) Time: 1.495 ms
上面SQL語(yǔ)句中的where條件中的 “tag @> '{"性格":["外向","細(xì)心"]}' ”中的“@>”是一個(gè)PostgreSQL中jsonb類型的特殊運(yùn)算符,意思為“包含”。
我們?nèi)绻氩樵冃愿駷橥庀蚝图?xì)心而又喜歡滑雪和游泳的醫(yī)生,則SQL語(yǔ)句:
osdba=# select * from user_tag where tag @> '{"性格":["外向","細(xì)心"]}' and tag @> '{"職業(yè)":["醫(yī)生"]}' and tag @>'{"愛(ài)好":["滑雪", "游泳"]}'; uid | tag ------+-------------------------------------------------------------------------------------------------- 4469 | {"學(xué)歷": ["小學(xué)"], "性格": ["外向", "細(xì)心", "穩(wěn)重"], "愛(ài)好": ["滑雪", "游泳"], "職業(yè)": ["醫(yī)生"]} (1 row) Time: 2.139 ms
從上面可以看出,只需要1~2ms就可以查詢出結(jié)果,原因是我們使用GIN索引,可以通過(guò)查看一下執(zhí)行計(jì)劃:
osdba=# explain select * from user_tag where tag @> '{"性格":["外向","細(xì)心"]}' and tag @> '{"職業(yè)":["醫(yī)生"]}' and tag @>'{"愛(ài)好":["滑雪", "游泳"]}'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on user_tag (cost=48.00..52.02 rows=1 width=153) Recheck Cond: ((tag @> '{"性格": ["外向", "細(xì)心"]}'::jsonb) AND (tag @> '{"職業(yè)": ["醫(yī)生"]}'::jsonb) AND (tag @> '{"愛(ài)好": ["滑雪", "游泳"]}'::jsonb)) -> Bitmap Index Scan on idx_user_tag_tag (cost=0.00..48.00 rows=1 width=0) Index Cond: ((tag @> '{"性格": ["外向", "細(xì)心"]}'::jsonb) AND (tag @> '{"職業(yè)": ["醫(yī)生"]}'::jsonb) AND (tag @> '{"愛(ài)好": ["滑雪", "游泳"]}'::jsonb)) (4 rows) Time: 1.736 ms
GIN索引是PostgreSQL中的核武器,大家從上面就可以看出GIN索引的強(qiáng)大了。GIN索除了可以用在json數(shù)據(jù)類型上,也可以使用到數(shù)組類型中。
jsonb為GIN索引提供了兩類索引操作符(你可以認(rèn)為是兩種索引):
- jsonb_ops,這是默認(rèn),上面我們建的索引就是這個(gè)類型的,
- jsonb_path_ops
jsonb_ops可以支持“@>”、“?”、“?&”、“?|”等操作符進(jìn)行查詢,這些操作符的意思請(qǐng)見(jiàn):官方文檔
而jsonb_path_ops只支持“@>”操作符,當(dāng)然jsonb_path_ops索引要比json_ops索引小很多,具體可以見(jiàn):
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 @> '{"性格":["外向","細(xì)心"]}' and tag @> '{"職業(yè)":["醫(yī)生"]}' and tag @>'{"愛(ài)好":["滑雪", "游泳"]}'; uid | tag ------+-------------------------------------------------------------------------------------------------- 4469 | {"學(xué)歷": ["小學(xué)"], "性格": ["外向", "細(xì)心", "穩(wěn)重"], "愛(ài)好": ["滑雪", "游泳"], "職業(yè)": ["醫(yī)生"]} (1 row) Time: 1.401 ms osdba=# explain select * from user_tag where tag @> '{"性格":["外向","細(xì)心"]}' and tag @> '{"職業(yè)":["醫(yī)生"]}' and tag @>'{"愛(ài)好":["滑雪", "游泳"]}'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on user_tag (cost=24.00..28.02 rows=1 width=153) Recheck Cond: ((tag @> '{"性格": ["外向", "細(xì)心"]}'::jsonb) AND (tag @> '{"職業(yè)": ["醫(yī)生"]}'::jsonb) AND (tag @> '{"愛(ài)好": ["滑雪", "游泳"]}'::jsonb)) -> Bitmap Index Scan on idx_user_tag_tag2 (cost=0.00..24.00 rows=1 width=0) Index Cond: ((tag @> '{"性格": ["外向", "細(xì)心"]}'::jsonb) AND (tag @> '{"職業(yè)": ["醫(yī)生"]}'::jsonb) AND (tag @> '{"愛(ài)好": ["滑雪", "游泳"]}'::jsonb)) (4 rows) Time: 0.634 ms
你可以寫(xiě)更多的SQL來(lái)做用戶畫(huà)像的數(shù)據(jù)分析,這里就不再贅述了。
當(dāng)然也可以使用PostgreSQL的數(shù)組類型建標(biāo)簽系統(tǒng),數(shù)組類型也運(yùn)行GIN索引,但總的來(lái)說(shuō)沒(méi)有使用json類型直觀。
到此這篇關(guān)于使用PostgreSQL數(shù)據(jù)庫(kù)建立用戶畫(huà)像系統(tǒng)的文章就介紹到這了,更多相關(guān)PostgreSQL用戶畫(huà)像系統(tǒng)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Postgresql 跨庫(kù)同步表及postgres_fdw的用法說(shuō)明
這篇文章主要介紹了Postgresql 跨庫(kù)同步表及postgres_fdw的用法說(shuō)明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01PostgreSQL自動(dòng)更新時(shí)間戳實(shí)例代碼
最近有這么一個(gè)工程,需要使用postgresql數(shù)據(jù)庫(kù),在數(shù)據(jù)庫(kù)中的好幾個(gè)表中都需要時(shí)間戳這個(gè)字段,這篇文章主要給大家介紹了關(guān)于PostgreSQL自動(dòng)更新時(shí)間戳的相關(guān)資料,需要的朋友可以參考下2021-11-11PostgreSQL報(bào)錯(cuò) 解決操作符不存在的問(wèn)題
這篇文章主要介紹了PostgreSQL報(bào)錯(cuò) 解決操作符不存在的問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01postgresql行轉(zhuǎn)列與列轉(zhuǎn)行圖文教程
PostgreSQL是一種開(kāi)源的關(guān)系型數(shù)據(jù)庫(kù),它提供了多種管理工具來(lái)操作數(shù)據(jù)庫(kù),下面這篇文章主要給大家介紹了關(guān)于postgresql行轉(zhuǎn)列與列轉(zhuǎn)行的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06PostgreSQL 默認(rèn)權(quán)限查看方式
這篇文章主要介紹了PostgreSQL 默認(rèn)權(quán)限查看方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01PostgreSQL利用遞歸優(yōu)化求稀疏列唯一值的方法
這篇文章主要介紹了PostgreSQL利用遞歸優(yōu)化求稀疏列唯一值的方法,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01基于PostgreSQL pg_hba.conf 配置參數(shù)的使用說(shuō)明
這篇文章主要介紹了基于PostgreSQL pg_hba.conf 配置參數(shù)的使用說(shuō)明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01