PostgreSQL大學選課相關性應用實踐

標籤

PostgreSQL , 選課 , UDF , 陣列 , 相關性 , 圖式搜尋

---

背景

大學生選課，一門課程可同時有若干學生選修，一個學生可能同時選多門課程，學生和課程的關係是多對多的關係。

1、如何找出每一門課程相關的課程（即這門課程的學生，還選了其他一些什麼課程？）同時要求按相關性排序。

2、如何找出每一門課程相關的課程（即這門課程的學生，還選了其他一些什麼課程？這些課程的學生又選了一些什麼課程，遞迴）同時要求按相關性排序。

DEMO

對於第一個要求，實現很簡單：

10萬位學生，1000門課，平均每個學生選5門課。

求每門課程的相關課程，並按相關性排序（選了該課程的學生中，還選了什麼課程，按被選次數倒排序）。

1、建表，儲存的是選課資訊：

create table xuanke(  
  std_id int8,  -- 學號  
  cos_id int    -- 課程  
);  

2、插入50萬記錄

insert into xuanke select random()*99999, random()*999 from generate_series(1,500000);  

3、單個學生選了什麼課，使用陣列儲存：

create table xuanke_cos_id (  
  std_id int8 primary key,  
  cos_id int[]  
);  
  
insert into xuanke_cos_id select std_id, array_agg(cos_id) from xuanke group by 1;  

4、每門課，有哪些學生選了，使用陣列儲存：

create table xuanke_std_id (  
  cos_id int primary key,   
  std_id int8[]   
);  
  
insert into xuanke_std_id select cos_id, array_agg(std_id) from xuanke group by 1;  

5、通過某門課的學生，得到這些學生選了什麼課，並聚集，按相關性輸出：

create or replace function get_cos_id2(int8[]) returns text[] as $$  
  select array_agg(unnest||`:`||cnt order by cnt desc) from 
    (select unnest(cos_id) as unnest, count (*) as cnt 
        from xuanke_cos_id where std_id = any ($1) group by 1
  ) t;  
$$ language sql strict;    

6、拿結果：

select cos_id, get_cos_id2(std_id) from xuanke_std_id;   

結果例子：

251 | {251:495,348:9,708:8,372:7,816:7,431:6,184:6,600:6,114:6,649:6, .....  
  
453 | {453:499,519:7,750:7,816:7,375:7,109:7,705:7,650:7,908:7,  .....  

使用平行計算，以上查詢毫秒級。

相關案例

如果是要實現第二個需求，也就是需要遞迴，可以參考如下方法：

《PostgreSQL 圖式搜尋(graph search)實踐 – 百億級圖譜，毫秒響應》

WITH RECURSIVE search_graph(      
  std_id,     -- 點1      
  cos_id,     -- 點2      
  depth,  -- 深度，從1開始      
  path,   -- 路徑，陣列儲存      
  cycle   -- 是否迴圈      
) AS (      
        select std_id,cos_id,depth,path,cycle from (      
          select   
	    std_id,  
	    cos_id,  
	    1 depth,  
	    array[row(std_id,cos_id)] path,   
	    false as cycle   
	  from xuanke   
	    where cos_id=?  
        ) t      
      UNION ALL      
        select std_id,cos_id,depth,path,cycle from (      
	  select   
	    g.std_id,  
	    g.cos_id,  
	    sg.depth+1 depth,  
	    sg.path||array[row(g.std_id,g.cos_id)] path,   
	    (row(g.std_id,g.cos_id) = ANY(path)) as cycle   
	  from xuanke as g, search_graph AS sg   
	    where   
	      g.std_id = sg.std_id  
	      AND NOT cycle  
	      -- and sg.depth <= ?  
	  ) t  
)  
SELECT * FROM search_graph;   

資料量非常龐大，返回時間很久。實際上層級很深後，意義不大。

如果需要限制每個層級的返回數，限制層級，限制相關性權重等，請參考：

《PostgreSQL 圖式搜尋(graph search)實踐 – 百億級圖譜，毫秒響應》