Einwegsocken identifizieren

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, SUM(rev_len) as neu_len
       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
        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 actor_id, user_name, neu_artikel, neu_len, SUM(byte_plus), MAX(byte_plus), akt_tage, akt_diff, akt_erster, akt_letzter,
       ROUND(akt_tage / akt_diff * 100, 1) AS "Prozent Aktiv"
  FROM user, Neuanlagen, AktiveTage, actor, 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
   AND SUM(byte_plus) >= @MIN_KB_GESCHRIEBEN * 1024
   AND SUM(byte_plus) / MAX(byte_plus) >= @MAX_ANTEIL_ARTIKEL / 100
 GROUP BY actor_id
 LIMIT 500;
SQL

Checking query status...
