From 4be1ec0cdb382d7b545eeb4c451cc123d9199d95 Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Tue, 23 Jun 2020 20:10:46 -0700 Subject: commit notes and issnl_prefix.py helper script --- notes/longtail_crawl.txt | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) create mode 100644 notes/longtail_crawl.txt (limited to 'notes/longtail_crawl.txt') diff --git a/notes/longtail_crawl.txt b/notes/longtail_crawl.txt new file mode 100644 index 0000000..9d2fe96 --- /dev/null +++ b/notes/longtail_crawl.txt @@ -0,0 +1,21 @@ + + .mode tabs + .output longtail_homepage_urls.tsv + + SELECT homepage.url, homepage.issnl + FROM homepage LEFT JOIN journal ON homepage.issnl = journal.issnl + WHERE homepage.terminal_status_code = 200 AND journal.is_longtail = 1 AND homepage.domain != 'archive.org' AND homepage.host NOT LIKE '%scielo%' AND homepage.domain != 'jst.go.jp' AND homepage.host != 'books.google.com' AND homepage.host != 'www.google.com' AND journal.has_dois = 0; + + +## Test Queries + + SELECT count(distinct homepage.issnl) FROM homepage LEFT JOIN journal ON homepage.issnl = journal.issnl WHERE homepage.terminal_status_code = 200 AND journal.is_longtail = 1; + + SELECT ... FROM homepage LEFT JOIN journal ON homepage.issnl = journal.issnl WHERE homepage.terminal_status_code = 200 AND journal.is_longtail = 1; + + + SELECT homepage.domain, COUNT(*) FROM homepage LEFT JOIN journal ON homepage.issnl = journal.issnl WHERE homepage.terminal_status_code = 200 AND journal.is_longtail = 1 AND homepage.domain != 'archive.org' AND homepage.host NOT LIKE '%scielo%' AND homepage.domain != 'jst.go.jp' AND homepage.host != 'books.google.com' AND homepage.host != 'www.google.com' AND journal.has_dois = 0 + GROUP BY homepage.domain ORDER BY COUNT(*) DESC LIMIT 20; + + SELECT homepage.suffix , COUNT(*) FROM homepage LEFT JOIN journal ON homepage.issnl = journal.issnl WHERE homepage.terminal_status_code = 200 AND journal.is_longtail = 1 AND homepage.domain != 'archive.org' + GROUP BY homepage.suffix ORDER BY COUNT(*) DESC LIMIT 20; -- cgit v1.2.3