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

SQL

use jawiki_p;
select
  en.enwiki_pagename as "enwiki art. title", 
  en.langs as "num of lang version",
  en.en_pagelen as "enwiki art. size",
  ja_page.page_title as "jawiki art. title", 
  ja_cat.cl_to as "FA or GA?",
  ja_page.page_len as "jawiki art. size",
  ROUND( (ja_page.page_len / en.en_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"
left join categorylinks as ja_cat on cl_from = page_id and ( cl_to = "良質な記事" or cl_to = "秀逸な記事" )
join (
  select
    enwiki_FAs.pp_value,
    enwiki_FAs.page_title as en_pagename,
    enwiki_FAs.page_len as en_pagelen,
    count(*) as langs
  from wikidatawiki_p.wb_items_per_site as wd
  join (
    select en_page.page_id, en_page.page_title, en_page.page_len, en_pp.pp_value, substring( en_pp.pp_value, 2) as wdlink
    from enwiki_p.categorylinks as en_cat
    join enwiki_p.page_props as en_pp on en_cat.cl_from = en_pp.pp_page and en_pp.pp_propname = "wikibase_item"
    join enwiki_p.page as en_page on en_cat.cl_from = en_page.page_id
    where
      en_cat.cl_to = "Featured_articles"
    ) as enwiki_FAs on enwiki_FAs.wdlink = wd.ips_item_id and wd.ips_site_id like "%wiki"
  group by page_title, page_len, pp_value
) as en on ja_pp.pp_value = en.pp_value
order by (ja_page.page_len / en.en_pagelen) asc
SQL

Checking query status...
