Difference between revisions of "User:Qshadow"
(New page: .) |
|||
| Line 1: | Line 1: | ||
| − | . | + | == Stats scripts == |
| + | |||
| + | # Get All Authors | ||
| + | SELECT DISTINCT | ||
| + | a.author_id, a.author_canonical, | ||
| + | MIN(DATE_FORMAT(t.title_copyright,'%Y')), MAX(DATE_FORMAT(t.title_copyright,'%Y')) | ||
| + | FROM titles t, canonical_author ca, authors a | ||
| + | WHERE ca.title_id = t.title_id | ||
| + | AND a.author_id = ca.author_id | ||
| + | AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') | ||
| + | AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" | ||
| + | GROUP BY a.author_id | ||
| + | INTO OUTFILE 'C:/ISFDB_AllAuthors.csv' | ||
| + | FIELDS TERMINATED BY ',' | ||
| + | ENCLOSED BY '"' | ||
| + | LINES TERMINATED BY '\n'; | ||
| + | |||
| + | |||
| + | # Get All Titles | ||
| + | SELECT DISTINCT | ||
| + | a.author_id, a.author_canonical, | ||
| + | t.title_id, t.title_title, t.title_ttype, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views | ||
| + | FROM canonical_author ca, authors a, titles t | ||
| + | WHERE ca.title_id = t.title_id | ||
| + | AND a.author_id = ca.author_id | ||
| + | AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" | ||
| + | AND t.title_parent = '0' # This is the canonical name | ||
| + | AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') | ||
| + | INTO OUTFILE 'C:/ISFDB_TitlesBest.txt' | ||
| + | FIELDS TERMINATED BY '^' | ||
| + | LINES TERMINATED BY '\n'; | ||
| + | |||
| + | |||
| + | #!!! Get All titles that have Awards | ||
| + | SELECT DISTINCT | ||
| + | a.author_id, a.author_canonical, | ||
| + | IF(t.title_parent=0,t.title_id,t.title_parent), t.title_title, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views, | ||
| + | awards.award_id, DATE_FORMAT(awards.award_year,'%Y'), awards.award_ttype, awards.award_atype, awards.award_level | ||
| + | FROM titles t, awards, title_awards, canonical_author ca, authors a | ||
| + | WHERE t.title_id = title_awards.title_id | ||
| + | AND awards.award_id = title_awards.award_id | ||
| + | AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" | ||
| + | AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') | ||
| + | AND ca.title_id = t.title_id | ||
| + | AND a.author_id = ca.author_id | ||
| + | INTO OUTFILE 'C:/ISFDB_TitleAwards.txt' | ||
| + | FIELDS TERMINATED BY '^' | ||
| + | LINES TERMINATED BY '\n'; | ||
| + | |||
| + | |||
| + | #!!! Get All Awards | ||
| + | SELECT DISTINCT | ||
| + | awards.award_id, DATE_FORMAT(awards.award_year,'%Y') | ||
| + | FROM awards | ||
| + | WHERE DATE_FORMAT(awards.award_year,'%Y') < "1990" | ||
| + | INTO OUTFILE 'C:/Awards.txt' | ||
| + | FIELDS TERMINATED BY '^' | ||
| + | LINES TERMINATED BY '\n'; | ||
| + | |||
| + | |||
| + | #!!! Get Magazine titles | ||
| + | SELECT DISTINCT | ||
| + | DATE_FORMAT(p.pub_year,'%Y.%m'), p.pub_id, p.pub_title, | ||
| + | t.title_id, t.title_parent, IF(t.title_parent=0,t.title_id,t.title_parent), | ||
| + | t.title_title, a.author_id, a.author_canonical, t.title_ttype, p.pub_ptype, t.title_views, a.author_views | ||
| + | FROM pubs p, pub_content pc, authors a, titles t, canonical_author ca | ||
| + | WHERE p.pub_ctype = 'MAGAZINE' | ||
| + | AND ca.title_id = t.title_id | ||
| + | AND a.author_id = ca.author_id | ||
| + | AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') | ||
| + | AND p.pub_id = pc.pub_id | ||
| + | AND t.title_id = pc.title_id | ||
| + | AND DATE_FORMAT(p.pub_year,'%Y') < "1990" | ||
| + | INTO OUTFILE 'C:/MagazinesContents.txt' | ||
| + | FIELDS TERMINATED BY '^' | ||
| + | ENCLOSED BY '"' | ||
| + | LINES TERMINATED BY '\n'; | ||
| + | |||
| + | |||
| + | #Get All Anthologies Titles | ||
| + | SELECT DISTINCT | ||
| + | a.author_id, a.author_canonical, | ||
| + | t.title_id, t.title_title, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views | ||
| + | FROM canonical_author ca, authors a, titles t | ||
| + | WHERE ca.title_id = t.title_id | ||
| + | AND a.author_id = ca.author_id | ||
| + | AND t.title_parent = '0' # This is the canonical name of the Anthology | ||
| + | AND t.title_ttype IN ('ANTHOLOGY','OMNIBUS') | ||
| + | AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" | ||
| + | INTO OUTFILE 'C:/AntholgiesTitles.txt' | ||
| + | FIELDS TERMINATED BY '^' | ||
| + | LINES TERMINATED BY '\n'; | ||
Latest revision as of 17:15, 24 July 2012
Stats scripts
- Get All Authors
SELECT DISTINCT a.author_id, a.author_canonical, MIN(DATE_FORMAT(t.title_copyright,'%Y')), MAX(DATE_FORMAT(t.title_copyright,'%Y')) FROM titles t, canonical_author ca, authors a WHERE ca.title_id = t.title_id AND a.author_id = ca.author_id AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" GROUP BY a.author_id INTO OUTFILE 'C:/ISFDB_AllAuthors.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- Get All Titles
SELECT DISTINCT a.author_id, a.author_canonical, t.title_id, t.title_title, t.title_ttype, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views FROM canonical_author ca, authors a, titles t WHERE ca.title_id = t.title_id AND a.author_id = ca.author_id AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" AND t.title_parent = '0' # This is the canonical name AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') INTO OUTFILE 'C:/ISFDB_TitlesBest.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
- !!! Get All titles that have Awards
SELECT DISTINCT a.author_id, a.author_canonical, IF(t.title_parent=0,t.title_id,t.title_parent), t.title_title, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views, awards.award_id, DATE_FORMAT(awards.award_year,'%Y'), awards.award_ttype, awards.award_atype, awards.award_level FROM titles t, awards, title_awards, canonical_author ca, authors a WHERE t.title_id = title_awards.title_id AND awards.award_id = title_awards.award_id AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') AND ca.title_id = t.title_id AND a.author_id = ca.author_id INTO OUTFILE 'C:/ISFDB_TitleAwards.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
- !!! Get All Awards
SELECT DISTINCT awards.award_id, DATE_FORMAT(awards.award_year,'%Y') FROM awards WHERE DATE_FORMAT(awards.award_year,'%Y') < "1990" INTO OUTFILE 'C:/Awards.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
- !!! Get Magazine titles
SELECT DISTINCT DATE_FORMAT(p.pub_year,'%Y.%m'), p.pub_id, p.pub_title, t.title_id, t.title_parent, IF(t.title_parent=0,t.title_id,t.title_parent), t.title_title, a.author_id, a.author_canonical, t.title_ttype, p.pub_ptype, t.title_views, a.author_views FROM pubs p, pub_content pc, authors a, titles t, canonical_author ca WHERE p.pub_ctype = 'MAGAZINE' AND ca.title_id = t.title_id AND a.author_id = ca.author_id AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') AND p.pub_id = pc.pub_id AND t.title_id = pc.title_id AND DATE_FORMAT(p.pub_year,'%Y') < "1990" INTO OUTFILE 'C:/MagazinesContents.txt' FIELDS TERMINATED BY '^' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- Get All Anthologies Titles
SELECT DISTINCT a.author_id, a.author_canonical, t.title_id, t.title_title, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views FROM canonical_author ca, authors a, titles t WHERE ca.title_id = t.title_id AND a.author_id = ca.author_id AND t.title_parent = '0' # This is the canonical name of the Anthology AND t.title_ttype IN ('ANTHOLOGY','OMNIBUS') AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" INTO OUTFILE 'C:/AntholgiesTitles.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';