Wikipedia:Merkityt versiot/Tilasto 201507/Monthly
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 wikitekstiä]
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;