monthly statistics of jawiki

SQL

use jawiki_p;
select
  a.month,
  ifnull( Neditor_include_a_user, 0 ) as "編集者数 （匿名利用者を含む）",
  ifnull( Neditor_exclude_a_user, 0 ) as "編集者数 （匿名利用者を含まない）",
  ifnull( Nedits, 0 ) as "編集回数",
  ifnull( Nedits_by_r_user, 0 ) as "登録利用者による 編集回数",
  ifnull( Nedits_by_a_user, 0 ) as "匿名利用者による 編集回数"
  
# 編集者数 （匿名利用者を含む）
from (
  select
    left( rev_timestamp, 6 ) as month,
    count( distinct rev_user_text ) as Neditor_include_a_user
  from revision
  group by left(rev_timestamp, 6)
) as a

# 編集者数 （匿名利用者を含まない）
left outer join (
  select
    left( rev_timestamp, 6 ) as month,
    count( distinct rev_user ) as Neditor_exclude_a_user
  from revision
  where
    rev_user != 0
  group by left(rev_timestamp, 6)
) as b on a.month = b.month

# 編集回数
left outer join (
  select
    left( rev_timestamp, 6 ) as month,
    count(*) as Nedits
  from revision
  group by left(rev_timestamp, 6)
) as c on a.month = c.month

# 登録利用者による 編集回数
left outer join (
  select
    left( rev_timestamp, 6 ) as month,
    count(*) as Nedits_by_r_user
  from revision
  where
    rev_user != 0
  group by left(rev_timestamp, 6)
) as d on a.month = d.month

# 匿名利用者による 編集回数
left outer join (
  select
    left( rev_timestamp, 6 ) as month,
    count(*) as Nedits_by_a_user
  from revision
  where
    rev_user = 0
  group by left(rev_timestamp, 6)
) as e on a.month = e.month
SQL

Checking query status...
