muokkausten määrä artikkeleissa joissa on auktoriteettitunnisteet

- arvo on muokkauksia/muokkaajia päivässä kuukausittain
- rev_id > 19061900  (kuukausi 2020-07 )  on raja-arvona, koska tämän jälkeen oli mahdollista suodattaa pois kumoukset ja kumotut muokkaukset
- Käyttäjätunnukset ipr1Bot ja ipr1, Fiwiki-tools-bot, Zache on suodatettu pois, jotta mallineiden lisäykset eivät tule mukaan tuloksiin
- 202305, 202304, 202303 lisäykset ovat Ipr1Bot:n ja 202011 Fiwiki-tools-bot:n ja

SQL

SELECT tt.*, ttt.added_templates FROM
(SELECT 
	floor(d/100) as yearmonth, 
    floor(avg(c)) as avg_edits, 
    floor(avg(pc)) as avg_edited_pages, 
    floor(avg(uc)) as avg_registered_users, 
    floor(avg(ac)) as avg_all_users  
FROM (
    SELECT 
  		floor(rev_timestamp/1000000) as d, sum(1) as c, 
  		count(distinct(actor_user)) as uc, 
  		count(distinct(actor_id)) as ac, 
  		count(distinct(rev_page)) as pc 
    FROM templatelinks, linktarget, actor, revision 
    LEFT JOIN (
        SELECT ct_rev_id 
        FROM change_tag, change_tag_def 
        WHERE ct_tag_id=ctd_id AND ctd_name IN ('rv', 'mw-manual-revert', 'mw-reverted', 'mw-rollback', 'mw-undo') 
        GROUP BY ct_rev_id
    ) as t ON ct_rev_id=rev_id, 
    (
        SELECT cl_from 
        FROM categorylinks 
        WHERE cl_to IN (
          	"Wikipedia-artikkelit_Uppslagsverket_Finland_-tunnisteilla", 
         	"Wikipedia-artikkelit_Biografiasampo-tunnisteilla", 
          	"Wikipedia-artikkelit_Kenraalit ja_amiraalit_-tunnisteilla", 
          	"Wikipedia-artikkelit_Ritva-tunnisteilla", 
          	"Wikipedia-artikkelit_Ylioppilasmatrikkeli_1640–1852_-tunnisteilla", 
          	"Wikipedia-artikkelit_Ylioppilasmatrikkeli_1853–1899_-tunnisteilla", 
          	"Wikipedia-artikkelit_BLF-tunnisteilla"
        ) 
        GROUP BY cl_from
    ) as foo 
    WHERE foo.cl_from=rev_page 
    AND ct_rev_id IS NULL 
    AND (actor_name!="Ipr1Bot" AND actor_name !="Ipr1") 
    AND actor_id=rev_actor 
    AND rev_page=tl_from 
    AND tl_target_id = lt_id 
    AND tl_from_namespace = 0 
    AND lt_title = "Auktoriteettitunnisteet" 
    AND lt_namespace = 10 
    AND rev_id>19061900 
    GROUP BY d
) as t 
      GROUP BY floor(d/100)
) as tt,
 ( 
  SELECT 
  	floor(rev_timestamp/100000000) as yearmonth, 
    count(distinct(rev_page)) as added_templates 
  FROM revision, 
    comment_revision, 
  	templatelinks, 
    linktarget 
  WHERE
  	tl_from_namespace=0 
  	AND lt_namespace=10 
  	AND lt_title="Auktoriteettitunnisteet" 
  	AND  lt_id=tl_target_id 
  	AND tl_from=rev_page 
  	AND rev_comment_id=comment_id 
  	AND comment_text LIKE "%uktoriteettitun%"  
  	AND rev_id>19061900 
  GROUP BY yearmonth
) 
AS ttt
WHERE tt.yearmonth=ttt.yearmonth;

SQL

Checking query status...