使用PostgreSQL資料庫建立用戶畫像系統

来源:https://www.cnblogs.com/88223100/archive/2022/10/19/Use-PostgreSQL-database-to-establish-user-portrait-system.html
-Advertisement-
Play Games

說起大數據中的應用，很多同學可能馬上會想起用戶畫像。 用戶畫像，英文稱之為User Profile，通過用戶畫像可以完美地抽象出一個用戶的信息全貌，通過用戶畫像數據可以精準地分析用戶的各種行為習慣，如消費習慣、興趣愛好、能力情況等等重要用戶信息。 通常用戶畫像是通過給用戶建標簽系統來實現的，本文介紹... ...


說起大數據中的應用，很多同學可能馬上會想起用戶畫像。
用戶畫像，英文稱之為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類型直觀。




    
本文來自博客園，作者：古道輕風，轉載請註明原文鏈接：https://www.cnblogs.com/88223100/p/Use-PostgreSQL-database-to-establish-user-portrait-system.html






              
	   




	

	
您的分享是我們最大的動力!




			 	

	
	

    


-Advertisement-
Play Games
     


	


    

    

    




	       	  

              
更多相關文章  

              

                   
    


				
  • 
				
    
			    	
    
				    	Winform框架中多語言的處理
			    	
    
					
    Winform界面中，有時候，需要對界面進行一些多語言的處理，以適應客戶場景的使用，如有時候需要考慮英文、日文、韓文等客戶的使用，那麼在Winform界面如何實現這種多語言的正常處理呢，本篇基於Winform開發框架的基礎上介紹使用多語言的處理過程。 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	基於SqlSugar的開發框架循序漸進介紹（16）-- 工作流模塊的功能介紹
			    	
    
					
    工作流是集成系統的模塊應用，使用許可權管理系統的身份認證登錄後即可使用。本篇隨筆介紹工作流模塊的界面功能效果。 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	如何用Virtualbox搭建一個虛擬機
			    	
    
					
    Virtualbox是Oracle公司出品的，質量還是有保證的。其次，作為虛擬機，它該有的功能也都有，功能夠用，備份，共用，虛擬化技術支持一個都不少。最重要的是，它還是免費的。 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	自動化管理軟體與Ansible安裝
			    	
    
					
    自動化管理軟體與Ansible安裝 常見的自動化管理軟體 常見的自動化管理軟體有哪些？ Linux中，常見的配置管理工具有 puppet saltstack ansible Puppet puppet是什麼 puppet是一種Linux/Unix平臺下的集中配置管理系統，使用自有的puppet描述語 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	Windows開機自動同步時間
			    	
    
					
    前言 有些Windows客戶端因主板電池沒電或其他原因，每次啟動系統後，讀取到BIOS的時間是初始時間（1970年）或錯誤的時間，這時需要系統啟動後立即向時間伺服器同步一次時間。 該方法是添加一個Windows任務計劃，讓系統啟動後立即向時間伺服器同步時間，所以不需要藉助任何插件。 環境 Windo ...
    
				
    
		    
  • 
				
    
			    	
    
				    	nginx配置文件，rewrite，if
			    	
    
					
    nginx配置文件，rewrite，if rewrit 語法：rewrite regex replacement flag; 比如 rewrite ^/images/(.*\.jpg)$ /imgs/$1 break; 又如 rewrite ^/bbs/(.*)$ http://www.idfsof ...
    
				
    
		    
  • 
				
    
			    	
    
				    	子查詢
			    	
    
					
    定義：select語句中嵌套select語句，被嵌套的select語句是子查詢。 子查詢可以出現在： select ....(select).. from ....(select).. where ....(select).. 1.where後面嵌套子查詢 select * from emp whe ...
    
				
    
		    
  • 
				
    
			    	
    
				    	一句口訣教你辨別索引失效七大場景
			    	
    
					
    摘要：本文將通過一句口訣，教你如何辨別索引失效。 本文分享自華為雲社區《虛竹哥教你一句口訣辨別索引失效七大場景》，作者：小虛竹 。 一、口訣 教你一句功法口訣：模 型 數 或 運 最 快 二、初始化數據 創建存儲引擎為InnoDB的學生表 drop table if exists student;  ...
    
				
    
		    
    • 
				 
		  
  
                  

              

          



		
        

        
        

            
 







    
一周排行

    

         
    

 
    -Advertisement-
    Play Games
    	   
	
  • 
			        	
    
			    	        
    
				    	        移動開發（一）：使用.NET MAUI開發第一個安卓APP
			            	
    
				        	
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf ToggleButton選中效果和一個登錄界面
			            	
    
				        	
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        彈幕樹洞項目功能新增篇
			            	
    
				        	
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第27篇 sqlserver2022詳細安裝步驟
			            	
    
				        	
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 開源高性能 MQTT 類庫
			            	
    
				        	
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
			            	
    
				        	
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
			            	
    
				        	
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
			            	
    
				        	
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
			            	
    
				        	
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第28篇 如何.net中實現高效可靠數據同步api
			            	
    
				        	
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類