Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Wurgl
.
Siehe https://de.wikipedia.org/wiki/Benutzer_Diskussion:Wurgl#Single_Purpose_Accounts
Toggle Highlighting
SQL
SET @MAX_NEUE_ARTIKEL = 3; # Maximale Anzahl neue Artikel SET @MAX_AKTIVE_TAGE_PROZENT = 10; # Prozentanteil aktive Tage zwischem 1. und letzten Edit im ANR SET @MIN_AKTIVE_TAGE = 5; # Mindestanzahl aktiver Tage SET @MIN_EDIT_COUNT = 5; # Mindestanzahl an Edits SET @MIN_KB_GESCHRIEBEN = 3; # Mindestmenge an Text im ANR in kB SET @MAX_ANTEIL_ARTIKEL = 80; # Prozentanteil aller Bytes im größten Artikel WITH Neuanlagen AS # Neu angelegte Artikel im ANR, keine BKS, keine Weiterleitungen (SELECT rev_actor AS neu_actor, COUNT(*) AS neu_artikel, CONCAT('[[', GROUP_CONCAT(REPLACE(page_title, '_', ' ') SEPARATOR ']], [['), ']]') AS neu_titel FROM revision, page WHERE rev_parent_id = 0 # Neuanlage AND rev_page = page_id AND page_namespace = 0 # ANR AND page_is_redirect = 0 AND NOT EXISTS (SELECT 1 # keine BKS FROM page_props WHERE pp_page = page_id AND pp_propname = 'disambiguation' ) GROUP BY rev_actor HAVING COUNT(*) <= @MAX_NEUE_ARTIKEL ), AktiveTage AS # Aktive Tage: Erster und letzter Edit im ANR # Hier momentan mit BKS und mit Weiterleitungen (SELECT rev_actor AS akt_actor, COUNT(DISTINCT(SUBSTRING(rev_timestamp, 1, 8))) AS akt_tage, MIN(SUBSTRING(rev_timestamp, 1, 8)) AS akt_erster, MAX(SUBSTRING(rev_timestamp, 1, 8)) AS akt_letzter, DATEDIFF(regexp_replace(MAX(SUBSTRING(rev_timestamp, 1, 8)), '^(....)(..)(..)$', '\\1-\\2-\\3'), regexp_replace(MIN(SUBSTRING(rev_timestamp, 1, 8)), '^(....)(..)(..)$', '\\1-\\2-\\3')) + 1 AS akt_diff FROM revision, page WHERE rev_page = page_id AND page_namespace = 0 # ANR /* AND page_is_redirect = 0 AND NOT EXISTS (SELECT 1 # keine BKS FROM page_props WHERE pp_page = page_id AND pp_propname = 'disambiguation' ) */ GROUP BY rev_actor ), Bytediffs AS # Differenz der Länge vom aktuellen Edit (nachher) zur vorherigen Version (vorher) (SELECT nachher.rev_actor AS byte_actor, SUM(CAST(nachher.rev_len AS SIGNED) - CAST(IFNULL(vorher.rev_len, 0) AS SIGNED)) AS byte_plus FROM page, revision AS nachher LEFT JOIN revision AS vorher ON vorher.rev_id = nachher.rev_parent_id AND vorher.rev_page = nachher.rev_page WHERE nachher.rev_page = page_id AND page_is_redirect = 0 # Kein Redirect AND page_namespace = 0 # ANR # Sehr viele Importartikel haben Chaos bei der parent_id # Siehe +/- Bytes in der Versionsgeschichte Erbkrankheiten beim Adel (vor August 2013) AND vorher.rev_timestamp < nachher.rev_timestamp AND NOT EXISTS (SELECT 1 # keine BKS FROM page_props WHERE pp_page = page_id AND pp_propname = 'disambiguation' ) GROUP BY nachher.rev_actor, nachher.rev_page ) SELECT CONCAT('[[User:', user_name, ']]') AS "User", IFNULL(neu_artikel, '–') AS "Anzahl neue Artikel", SUM(byte_plus) AS "Bytes ±", MAX(byte_plus) AS "Bytes größter Artikel", CONCAT(ROUND(MAX(byte_plus) / SUM(byte_plus) * 100, 1), ' %') AS "% größter Artikel", akt_tage AS "Aktive Tage", CONCAT(ROUND(akt_tage / akt_diff * 100, 1), ' %') AS "Prozent aktive Tage", regexp_replace(akt_erster, '^(....)(..)(..)$', '\\1-\\2-\\3') AS "Erster Edit", regexp_replace(akt_letzter, '^(....)(..)(..)$', '\\1-\\2-\\3') AS "Letzer Edit", IFNULL(neu_titel, '–') AS "Neuanlagen" FROM user, AktiveTage, Bytediffs, actor LEFT JOIN Neuanlagen ON neu_actor = actor_id WHERE akt_actor = actor_id AND byte_actor = actor_id AND actor_user = user_id AND user_editcount >= @MIN_EDIT_COUNT AND akt_tage >= @MIN_AKTIVE_TAGE AND akt_tage / akt_diff <= @MAX_AKTIVE_TAGE_PROZENT / 100 GROUP BY actor_id HAVING SUM(byte_plus) >= @MIN_KB_GESCHRIEBEN * 1024 AND MAX(byte_plus) / SUM(byte_plus) >= @MAX_ANTEIL_ARTIKEL / 100 ;
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
.
Checking query status...