Created
June 8, 2017 21:40
-
-
Save tenkabuto/80d997df9c209c590a19d101ab47a8a0 to your computer and use it in GitHub Desktop.
Getting Related Clubs with Data Science Club (`125525`) for Test
Here is my take:
select
m.clubname as primary,
s.clubname as secondary,
count(distinct(kc_m.id)) as relevance_score
from club m
join club s on s.clubid <> m.clubid
join keytoclub kc_m on m.clubid = kc_m.club
join keytoclub kc_s on kc_s.club = s.clubid
where kc_s.id = kc_m.id
group by m.clubname, s.clubname;
This gives the values for all clubs in relationship to other clubs.
This is a dump of my database (I have used PostgreSQL). Since I didn't have the real data, I have used lowercased words from the club names as keywords (including 'of', and 'for'), so the result is a frequency of sharing keywords among clubs.
CREATE TABLE club (
clubid SERIAL,
clubname character varying(60),
primary key (clubid)
);
CREATE TABLE keyword (
keyid SERIAL,
keyword character varying(60),
primary key (keyid)
);
CREATE TABLE keytoclub (
id integer references keyword(keyid),
club integer references club(clubid)
);
COPY club (clubid, clubname) FROM stdin;
2506 Competitive Robotics
134497 Hornet Hyperloop
2479 Accounting Society
2538 Management Information Systems Association
2540 Math Club
2544 National Society of Black Engineers
2569 Society of Hispanic Professional Engineers
75678 American Marketing Association
77818 Sacramento Association of Collegiate Entrepreneurs
96537 Association of Latino Professionals For America
125660 Construction Management Student Association
148785 American Water Resources Association
2490 Association for Computing Machinery
125525 Data Science Club
\.
COPY keyword (keyid, keyword) FROM stdin;
1 accounting
2 america
3 american
4 association
5 black
6 club
7 collegiate
8 competitive
9 computing
10 construction
11 data
12 engineers
13 entrepreneurs
14 for
15 hispanic
16 hornet
17 hyperloop
18 information
19 latino
20 machinery
21 management
22 marketing
23 math
24 national
25 of
26 professional
27 professionals
28 resources
29 robotics
30 sacramento
31 science
32 society
33 student
34 systems
35 water
\.
COPY keytoclub (id, club) FROM stdin;
1 2479
2 96537
3 75678
3 148785
4 2538
4 75678
4 77818
4 96537
4 125660
4 148785
4 2490
5 2544
6 2540
6 125525
7 77818
8 2506
9 2490
10 125660
11 125525
12 2544
12 2569
13 77818
14 96537
14 2490
15 2569
16 134497
17 134497
18 2538
19 96537
20 2490
21 2538
21 125660
22 75678
23 2540
24 2544
25 2544
25 2569
25 77818
25 96537
26 2569
27 96537
28 148785
29 2506
30 77818
31 125525
32 2479
32 2544
32 2569
33 125660
34 2538
35 148785
\.
Here's what I am getting for my data:
primary | secondary | relevance_score |
---|---|---|
Accounting Society | National Society of Black Engineers | 1 |
Accounting Society | Society of Hispanic Professional Engineers | 1 |
American Marketing Association | American Water Resources Association | 2 |
American Marketing Association | Association for Computing Machinery | 1 |
American Marketing Association | Association of Latino Professionals For America | 1 |
American Marketing Association | Construction Management Student Association | 1 |
American Marketing Association | Management Information Systems Association | 1 |
American Marketing Association | Sacramento Association of Collegiate Entrepreneurs | 1 |
American Water Resources Association | American Marketing Association | 2 |
American Water Resources Association | Association for Computing Machinery | 1 |
American Water Resources Association | Association of Latino Professionals For America | 1 |
American Water Resources Association | Construction Management Student Association | 1 |
American Water Resources Association | Management Information Systems Association | 1 |
American Water Resources Association | Sacramento Association of Collegiate Entrepreneurs | 1 |
Association for Computing Machinery | American Marketing Association | 1 |
Association for Computing Machinery | American Water Resources Association | 1 |
Association for Computing Machinery | Association of Latino Professionals For America | 2 |
Association for Computing Machinery | Construction Management Student Association | 1 |
Association for Computing Machinery | Management Information Systems Association | 1 |
Association for Computing Machinery | Sacramento Association of Collegiate Entrepreneurs | 1 |
Association of Latino Professionals For America | American Marketing Association | 1 |
Association of Latino Professionals For America | American Water Resources Association | 1 |
Association of Latino Professionals For America | Association for Computing Machinery | 2 |
Association of Latino Professionals For America | Construction Management Student Association | 1 |
Association of Latino Professionals For America | Management Information Systems Association | 1 |
Association of Latino Professionals For America | National Society of Black Engineers | 1 |
Association of Latino Professionals For America | Sacramento Association of Collegiate Entrepreneurs | 2 |
Association of Latino Professionals For America | Society of Hispanic Professional Engineers | 1 |
Construction Management Student Association | American Marketing Association | 1 |
Construction Management Student Association | American Water Resources Association | 1 |
Construction Management Student Association | Association for Computing Machinery | 1 |
Construction Management Student Association | Association of Latino Professionals For America | 1 |
Construction Management Student Association | Management Information Systems Association | 2 |
Construction Management Student Association | Sacramento Association of Collegiate Entrepreneurs | 1 |
Data Science Club | Math Club | 1 |
Management Information Systems Association | American Marketing Association | 1 |
Management Information Systems Association | American Water Resources Association | 1 |
Management Information Systems Association | Association for Computing Machinery | 1 |
Management Information Systems Association | Association of Latino Professionals For America | 1 |
Management Information Systems Association | Construction Management Student Association | 2 |
Management Information Systems Association | Sacramento Association of Collegiate Entrepreneurs | 1 |
Math Club | Data Science Club | 1 |
National Society of Black Engineers | Accounting Society | 1 |
National Society of Black Engineers | Association of Latino Professionals For America | 1 |
National Society of Black Engineers | Sacramento Association of Collegiate Entrepreneurs | 1 |
National Society of Black Engineers | Society of Hispanic Professional Engineers | 3 |
Sacramento Association of Collegiate Entrepreneurs | American Marketing Association | 1 |
Sacramento Association of Collegiate Entrepreneurs | American Water Resources Association | 1 |
Sacramento Association of Collegiate Entrepreneurs | Association for Computing Machinery | 1 |
Sacramento Association of Collegiate Entrepreneurs | Association of Latino Professionals For America | 2 |
Sacramento Association of Collegiate Entrepreneurs | Construction Management Student Association | 1 |
Sacramento Association of Collegiate Entrepreneurs | Management Information Systems Association | 1 |
Sacramento Association of Collegiate Entrepreneurs | National Society of Black Engineers | 1 |
Sacramento Association of Collegiate Entrepreneurs | Society of Hispanic Professional Engineers | 1 |
Society of Hispanic Professional Engineers | Accounting Society | 1 |
Society of Hispanic Professional Engineers | Association of Latino Professionals For America | 1 |
Society of Hispanic Professional Engineers | National Society of Black Engineers | 3 |
Society of Hispanic Professional Engineers | Sacramento Association of Collegiate Entrepreneurs | 1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This allows me to get data that looks like this for the test club: