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

SQL

​x
 
-- このSQL文はふたつクエリがある
​
/* 仅条目 */
WITH pages_vg AS (
  WITH pages_vg_talk AS (
    SELECT
      page.page_namespace,
      page.page_title
    FROM
      page
      JOIN categorylinks ON cl_to IN (
        '极高重要度电子游戏条目',
        '高重要度电子游戏条目',
        '中重要度电子游戏条目',
        '低重要度电子游戏条目',
        '未知重要度电子游戏条目'
      )
      AND cl_type = 'page'
      AND cl_from = page.page_id
  )
  SELECT
    page.page_id,
    page.page_title
  FROM
    page
    JOIN pages_vg_talk ON page.page_title = pages_vg_talk.page_title
    AND page.page_namespace + 1 = pages_vg_talk.page_namespace
),
actor_with_editing AS (
  SELECT
    revision.rev_actor,
    COUNT(*) AS edits
  FROM
    pages_vg
    JOIN revision ON pages_vg.page_id = revision.rev_page
    AND revision.rev_timestamp >= 20230701000000 -- 统计开始时间
    AND revision.rev_timestamp < 20231001000000 -- 统计结束时间
  GROUP BY
    revision.rev_actor
)
SELECT
  actor.actor_name AS user_name, CONCAT(
    '* {{User|',
    actor.actor_name,
    '}}',
    ' <small>[',
    FORMAT(actor_with_editing.edits, 'N'),
    ']</small>'
  ) AS result
FROM
  actor_with_editing
  JOIN actor ON actor_with_editing.rev_actor = actor.actor_id
  AND actor_with_editing.edits >= 5
  -- AND actor.actor_user IS NOT NULL -- remove IP users
  -- AND actor.actor_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') -- remove bots
ORDER BY
  actor_with_editing.edits DESC,
  actor.actor_name ASC;
​
/* 含非条目 */
WITH pages_vg AS (
  WITH pages_vg_talk AS (
    SELECT
      page.page_namespace,
      page.page_title
    FROM
      page
      JOIN categorylinks ON cl_to = '电子游戏条目'
      AND cl_type = 'page'
      AND cl_from = page.page_id
  )
  SELECT
    page.page_id
  FROM
    page
    JOIN pages_vg_talk ON page.page_title = pages_vg_talk.page_title
    AND page.page_namespace + 1 = pages_vg_talk.page_namespace
),
actor_with_editing AS (
  SELECT
    revision.rev_actor,
    COUNT(*) AS edits
  FROM
    pages_vg
    JOIN revision ON pages_vg.page_id = revision.rev_page
    AND revision.rev_timestamp >= 20230701000000 -- 统计开始时间
    AND revision.rev_timestamp < 20231001000000 -- 统计结束时间
  GROUP BY
    revision.rev_actor
)
SELECT
  actor.actor_name AS user_name, CONCAT(
    '* {{User|',
    actor.actor_name,
    '}}',
    ' <small>[',
    FORMAT(actor_with_editing.edits, 'N'),
    ']</small>'
  ) AS result
FROM
  actor_with_editing
  JOIN actor ON actor_with_editing.rev_actor = actor.actor_id
  AND actor_with_editing.edits >= 5
  -- AND actor.actor_user IS NOT NULL -- remove IP users
  -- AND actor.actor_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') -- remove bots
ORDER BY
  actor_with_editing.edits DESC,
  actor.actor_name ASC;
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
All SQL code is licensed under CC0 License.

Checking query status...