PostgreSQL Practices of Elective Selection Relation in Colleges

Background

  1. How to find the relevant electives of a specific elective and sort these electives by relevancy ( namely, other electives also selected by a student who selects this specific elective)?
  2. How to find the relevant electives of a specific elective and sort these electives by relevancy ( namely, use recursion to find other electives also selected by a student who selects this specific elective and electives selected by the students who have selected the “other electives”)?

Demo

create table xuanke(  
std_id int8, -- Student ID
cos_id int -- Course
);
insert into xuanke select random()*99999, random()*999 from generate_series(1,500000);
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;
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;
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;
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, .....

Related Case

WITH RECURSIVE search_graph(      
std_id, -- point 1
cos_id, -- point 2
depth, -- depth, starting from 1
path, -- path, stored as an array
cycle -- cycle or not 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;

Original Source

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Eventbus written in Python based on Kafka

Configuring & Optimizing tempdb

Taager’s Foray in Messaging Part 1; Apache Kafka vs Apache Pulsar

Kafka Quickstart