diff options
| author | Bryan Newbold <bnewbold@archive.org> | 2020-06-23 20:10:46 -0700 |
|---|---|---|
| committer | Bryan Newbold <bnewbold@archive.org> | 2020-06-23 20:10:46 -0700 |
| commit | 4be1ec0cdb382d7b545eeb4c451cc123d9199d95 (patch) | |
| tree | e1e8131e2caef749496dbda8b83f355ac296d6ce /notes/longtail_crawl.txt | |
| parent | 05f6a1b143a521688ccb3a59f300b44f5c04c6be (diff) | |
| download | chocula-4be1ec0cdb382d7b545eeb4c451cc123d9199d95.tar.gz chocula-4be1ec0cdb382d7b545eeb4c451cc123d9199d95.zip | |
commit notes and issnl_prefix.py helper script
Diffstat (limited to 'notes/longtail_crawl.txt')
| -rw-r--r-- | notes/longtail_crawl.txt | 21 |
1 files changed, 21 insertions, 0 deletions
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; |