Untitled query #69146

This query is marked as a draft This query has been published by Pegship.
SQL

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DROP TABLE IF EXISTS rep1;
DROP TABLE IF EXISTS rep2;

-- First, capture all potentially interesting stub templates
CREATE TABLE rep1 AS
SELECT page_title, page_is_redirect
FROM   enwiki_p.page
WHERE  page_namespace = 10 
AND    page_title LIKE '%-stub'
AND    page_is_redirect = 0;

-- Add in any templates that redirect to one of these.
ALTER TABLE rep1 ADD COLUMN redir varbinary(255);

INSERT INTO rep1
SELECT p.page_title, p.page_is_redirect, r.page_title
FROM   enwiki_p.page p
INNER JOIN enwiki_p.redirect ON rd_from = p.page_id
INNER JOIN rep1 r ON rd_namespace = 10 AND rd_title = r.page_title
WHERE  p.page_namespace = 10 
AND    p.page_is_redirect = 1
AND    r.page_is_redirect = 0;


-- Now count how many times each is transcluded
alter table rep1 add column trans int(8);

-- Process in chunks - repeat as necessary
UPDATE rep1
SET trans = (
  SELECT count(*)
  FROM   enwiki_p.templatelinks
  WHERE  tl_namespace = 10
  AND    tl_title = page_title )
WHERE trans IS NULL
LIMIT 5000;

alter table rep1 add index( redir );

-- Now we need to credit transclusions of redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir	
SET r1.trans = r1.trans + r2.trans
WHERE r1.page_is_redirect = 0
AND   r2.page_is_redirect = 1;


-- Now find categories of interest
CREATE table rep2 AS
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Stub_category" 
AND page_namespace = 14;

REPLACE INTO rep2
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Parent-only_stub_category" 
AND page_namespace = 14;

REPLACE INTO rep2
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Regional_stub_category" 
AND page_namespace = 14;

-- Index these
ALTER TABLE rep2 ADD INDEX( page_id );
ALTER TABLE rep1 ADD INDEX ( page_title );


-- Now count how many time each interesting template is linked from an interesting category
ALTER TABLE rep1 ADD COLUMN cats int(8);

-- Process in chunks - repeat as necessary
UPDATE rep1 r1
SET cats = (
  SELECT count(*)
  FROM enwiki_p.pagelinks l
  INNER JOIN rep2 r2 ON l.pl_from = r2.page_id
  WHERE l.pl_namespace = 10
  AND l.pl_title = r1.page_title )
WHERE Cats IS NULL
LIMIT 5000;

-- Now we need to credit any category links to redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir	
SET r1.cats = r1.cats + r2.cats
WHERE r1.page_is_redirect = 0
AND   r2.page_is_redirect = 1;


-- Test the results
SELECT count(*) FROM rep1 WHERE page_is_redirect = 0 AND cats = 0 AND trans >= 60;

-- Generate some output
SELECT CONCAT( '*{{tl|', rep1.page_title, '}} - ', trans, ' transclusions' )
FROM rep1
WHERE page_is_redirect = 0
AND cats = 0
AND trans >= 50;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
