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 = 10; # Prozentanteil aktive Tage zwischem 1. und letzten Edit im ANR
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
    (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 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
    (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')) AS akt_diff
       FROM revision, page
      WHERE rev_page = page_id
        AND page_namespace = 0 # ANR
        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
    (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
        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",
       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",
       neu_titel AS "Neuanlagen"
  FROM user, actor, Neuanlagen, AktiveTage, Bytediffs
 WHERE akt_actor = actor_id
   AND byte_actor = actor_id
   AND neu_actor = actor_id
   AND actor_user = user_id
   AND akt_tage / akt_diff <= @MAX_AKTIVE_TAGE / 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.

SQL

Checking query status...