Einwegsocken identifizieren

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

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
SET @MAX_ANZAHL_GEAENDERTE_ARTIKEL = 500; # Maximale Anzahl Arikel die geändert wurden
SET @MIN_EDITS_PRO_ARTIKEL = 5; # Durchschnittliche Mindestanzahl an Edits je Artikel (user_editcount / Anzahl geänderte 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,
            COUNT(DISTINCT nachher.rev_page) AS byte_pages,
            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",
       SUM(byte_pages) AS "Anzahl geänderte Artikel",
       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 "Prozent größter Artikel",
       akt_tage AS "Aktive Tage",
       CONCAT(ROUND(akt_tage / akt_diff * 100, 1), ' %') AS "Prozent aktive Tage",
       user_editcount AS "Anzahl Edits (Datenbank)",
       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",
       IFNULL((SELECT "verifiziert"
                 FROM page, templatelinks
                WHERE page_namespace = 2
                  AND page_title = REPLACE(user_name, ' ', '_')
                  AND page_id = tl_from
                  AND tl_title = 'Benutzerkonto_verifiziert'
                  AND tl_namespace = 10
              ), '–') AS "Verifiziert"
  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
   # Bots rausnehmen. Aka's Liste von aktiven und ehemaligen Bots
   AND NOT EXISTS (SELECT 1
                     FROM page, pagelinks
                    WHERE page_title = 'Beitragszahlen/Bots'
                      AND page_namespace = 4
                      AND pl_from = page_id
                      AND pl_namespace = 2
                      AND pl_title = REPLACE(user_name, ' ', '_')
                  )
 GROUP BY actor_id
 HAVING SUM(byte_plus) >= @MIN_KB_GESCHRIEBEN * 1024
    AND MAX(byte_plus) / SUM(byte_plus) >= @MAX_ANTEIL_ARTIKEL / 100
    AND SUM(byte_pages) <= @MAX_ANZAHL_GEAENDERTE_ARTIKEL
    AND (user_editcount / SUM(byte_pages)) >= @MIN_EDITS_PRO_ARTIKEL
 ;

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.

SQL

Checking query status...