enwikiのFA記事のjawikiにおける現状一覧

SQL

use jawiki_p;
select
  enwiki_pagename as "enwiki art. title", 
  langs as "num of lang version",
  enwiki_pagelen as "enwiki art. size",
  ja_page.page_title as "jawiki art. title", 
  ja_cat.cl_to as "FA or GA?",
  (case when group_concat(pp_propname) like "%disambiguation%" then "disambiguation" end ) as "disambiguation?",
  ja_page.page_len as "jawiki art. size",
  ROUND( (ja_page.page_len / enwiki_pagelen), 2) as "size_ratio"
from page as ja_page
join page_props as ja_pp on page_id = pp_page and ( pp_propname = "wikibase_item" or pp_propname = "disambiguation" )
left join categorylinks as ja_cat on cl_from = page_id and ( cl_to = "良質な記事" or cl_to = "秀逸な記事" )
join (
  select
    pp_value,
    page_title as enwiki_pagename,
    page_len as enwiki_pagelen,
    count(*) as langs
  from wikidatawiki_p.wb_items_per_site
  join (
    select page_id, page_title, page_len, pp_value, substring( pp_value, 2) as wikidatalink
    from enwiki_p.categorylinks
    join enwiki_p.page_props on cl_from = pp_page and pp_propname = "wikibase_item"
    join enwiki_p.page on cl_from = page_id
    where
      cl_to = "Featured_articles"
    ) as enwiki_FAs on wikidatalink = ips_item_id
  group by page_title, page_len, pp_value
) as en on ja_pp.pp_value = en.pp_value
group enwiki_pagename, langs, enwiki_pagelen, ja_page.page_title, ja_cat.cl_to, ja_page.page_len
order by (ja_page.page_len / enwiki_pagelen) asc
SQL

Checking query status...
