Wikipedia

Wikipedia:Merkityt versiot/Tilasto 201507/Monthly

< Wikipedia:Merkityt versiot‎ | Tilasto 201507
Monthly stats
yearmonth All users 1 - 4 edits 5-9 edits 10-24 edits 25 – 49 edits 50-99 edits 100+ edits
200904 2201 1456 265 175 106 73 126
200905 2424 1579 305 215 98 72 155
200906 2313 1484 290 200 122 69 148
200907 2289 1485 263 223 103 69 146
200908 2370 1564 299 195 100 68 144
200909 2290 1520 266 213 105 63 123
200910 2535 1688 325 227 91 74 130
200911 2602 1761 309 218 113 71 130
200912 2370 1557 285 229 99 59 141
201001 2712 1839 298 252 118 59 146
201002 2449 1624 327 196 97 65 140
201003 2687 1827 322 236 94 67 141
201004 2488 1667 294 209 105 70 143
201005 2434 1635 295 198 88 64 154
201006 2298 1503 303 188 79 73 152
201007 2024 1314 253 172 82 64 139
201008 2116 1384 260 181 93 57 141
201009 2221 1475 257 188 98 66 137
201010 2276 1499 279 196 98 65 139
201011 2193 1455 281 172 74 68 143
201012 2149 1437 260 167 73 76 136
201101 2296 1535 275 182 101 63 140
201102 2171 1440 252 212 87 64 116
201103 2261 1497 275 208 85 67 129
201104 2064 1364 240 183 89 66 122
201105 2059 1334 265 195 82 63 120
201106 1929 1279 225 182 69 54 120
201107 1932 1270 221 182 86 47 126
201108 1976 1260 256 193 92 58 117
201109 2008 1322 256 173 84 49 124
201110 2081 1363 265 192 79 66 116
201111 1933 1287 215 183 85 51 112
201112 1920 1257 231 181 69 69 113
201201 2143 1408 245 216 70 79 125
201202 2065 1355 248 187 81 72 122
201203 2089 1381 254 197 71 69 117
201204 1977 1306 229 194 62 63 123
201205 2041 1349 243 197 79 56 117
201206 1882 1226 237 174 89 53 103
201207 1824 1213 198 172 77 59 105
201208 1926 1275 232 164 74 66 115
201209 1898 1275 236 170 59 47 111
201210 1927 1312 225 174 69 40 107
201211 2009 1338 257 178 87 43 106
201212 1808 1211 211 158 72 51 105
201301 2078 1394 260 177 91 44 112
201302 1909 1272 226 167 72 70 102
201303 2023 1363 254 166 77 55 108
201304 1899 1248 233 180 90 56 92
201305 1685 1105 225 151 68 43 93
201306 1536 973 190 171 63 41 98
201307 1511 945 200 143 78 41 104
201308 1620 1052 217 137 62 48 104
201309 1634 1064 205 173 52 45 95
201310 1723 1147 208 165 64 37 102
201311 1731 1168 190 159 63 54 97
201312 1694 1085 233 145 75 52 104
201401 1897 1219 235 189 79 60 115
201402 1808 1190 227 167 65 59 100
201403 1827 1192 233 164 78 47 113
201404 1713 1102 229 164 69 43 106
201405 1740 1144 204 173 80 52 87
201406 1583 1040 180 153 70 50 90
201407 1560 1033 180 140 50 47 110
201408 1640 1121 161 149 60 50 99
201409 1726 1182 200 147 61 35 101
201410 1819 1257 208 147 57 38 112
201411 1763 1209 218 126 78 25 107
201412 1863 1311 204 143 54 46 105
201501 2056 1389 244 196 57 50 120
201502 1825 1271 209 129 63 45 108
201503 2035 1434 228 164 55 42 112
201504 1667 1123 193 154 62 31 104
201505 1650 1110 198 132 62 42 106
201506 1551 1040 191 117 66 42 95
201507 1414 901 178 128 57 41 109

SQL

muokkaa

drop table bot_users;

create table if not exists bot_users (select user_id, user_name from fiwiki_p.user LEFT JOIN (SELECT ug_user FROM fiwiki_p.user_groups WHERE ug_group='bot') as ug ON user_id=ug_user LEFT JOIN (SELECT ufg_user FROM fiwiki_p.user_former_groups WHERE ufg_group='bot') as ufg ON user_id=ufg_user WHERE ug_user IS NOT NULL OR ufg_user IS NOT NULL OR user_name regexp "[bB][-_]?[oO0][-_]?[tT]"); CREATE INDEX i_user ON bot_users (user_id);

select tmp_c1.yearmonth, c1, c2a, c2, c3,c4,c5,c6, (c1-(c2+c2a+c3+c4+c5+c6)) as c7 FROM (select sum(1) as c1, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp ) as tmp2 group by yearmonth) as tmp_c1 LEFT JOIN (select sum(1) as c2a, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp where c < 5) as tmp2 group by yearmonth) as tmp_c2a ON tmp_c1.yearmonth=tmp_c2a.yearmonth LEFT JOIN (select sum(1) as c2, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp where c>4 and c < 10) as tmp2 group by yearmonth) as tmp_c2 ON tmp_c1.yearmonth=tmp_c2.yearmonth LEFT JOIN (select sum(1) as c3, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp where c>9 and c <25) as tmp2 group by yearmonth) as tmp_c3 ON tmp_c1.yearmonth=tmp_c3.yearmonth LEFT JOIN (select sum(1) as c4, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp where c>24 and c <50) as tmp2 group by yearmonth) as tmp_c4 ON tmp_c1.yearmonth=tmp_c4.yearmonth LEFT JOIN (select sum(1) as c5, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp where c>49 and c <100) as tmp2 group by yearmonth) as tmp_c5 ON tmp_c1.yearmonth=tmp_c5.yearmonth LEFT JOIN (select sum(1) as c6, yearmonth from (select * from (select date_format(day, "%Y%m") as yearmonth, sum(contribs) as c from fiwiki_p.user_daily_contribs LEFT join bot_users ON user_daily_contribs.user_id=bot_users.user_id where user_daily_contribs.user_id > 0 AND bot_users.user_id IS NULL group by yearmonth, user_daily_contribs.user_id) as tmp where c>99) as tmp2 group by yearmonth) as tmp_c6 ON tmp_c1.yearmonth=tmp_c6.yearmonth;

Noudettu kohteesta ”https://fi.wikipedia.org/w/index.php?title=Wikipedia:Merkityt_versiot/Tilasto_201507/Monthly&oldid=15103190”