Last modified: 2014-01-03 16:09:23 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-179. Summary: Database Query Issue type: Task - A task that needs to be done. Priority: Critical Status: Done Assignee: Hoo man <hoo@online.de> ------------------------------------------------------------------------------- From: Franz Herbach <franzherbach@yahoo.de> Date: Tue, 03 Apr 2012 22:21:27 ------------------------------------------------------------------------------- Thank you so much for helping me
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Tue, 03 Apr 2012 22:40:13 ------------------------------------------------------------------------------- I'm sorry, but I need further information on what you want: Which wiki, Which bot(s), ...?
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Tue, 03 Apr 2012 23:10:49 ------------------------------------------------------------------------------- Ok, so you only need the number of edits? That's pretty easy, but I need a list of bots, cause I can't just look for bot edits (as those are only tagged in the recentchanges table which only goes back 30 days)... can you give me that list or should I compile it myself from that page (http://en.wikipedia.org/wiki/Wikipedia:Bots/Status)
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Wed, 04 Apr 2012 12:09:05 ------------------------------------------------------------------------------- Things needed longer than I thought cause the queries I originally wanted to run were quite long, which doesn't work well together with a query killer ![][1] But due to that I got additional information, which might be useful for you as well... a file including the number of edits a bot did in which year (for all bots). I guess you don't need the queries (as they were rather trivial) so I didn't list them, feel free to ask if you need them. Results: 1: http://toolserver.org/~hoo/dbq/dbq-179_1.txt (plain text) http://toolserver.org/~hoo/dbq/dbq-179_1.csv (csv) 2: http://toolserver.org/~hoo/dbq/dbq-179_2.txt (plain text) http://toolserver.org/~hoo/dbq/dbq-179_2.csv (csv) 3 (the additional data): http://toolserver.org/~hoo/dbq/dbq-179_3.txt (plain text) http://toolserver.org/~hoo/dbq/dbq-179_3.csv (csv) Good luck with your thesis ![][2] [1]: https://jira.toolserver.org/images/icons/emoticons/tongue.gif [2]: https://jira.toolserver.org/images/icons/emoticons/wink.gif
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Wed, 04 Apr 2012 14:25:57 ------------------------------------------------------------------------------- I ran it on all namespaces... that's not what you wanted, is it? :/
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Wed, 04 Apr 2012 14:31:23 ------------------------------------------------------------------------------- Yes, but it will make stuff slower... I hope I can get that done today, hang on ![][1] [1]: https://jira.toolserver.org/images/icons/emoticons/wink.gif
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Wed, 04 Apr 2012 18:06:37 ------------------------------------------------------------------------------- The fixed results can be found under the links from the original results, which I posted earlier. How I did it: I compiled a list of all bots on enwiki (from the lists you gave me) and run the following query in a loop for all of them (this is number 3): SELECT /* SLOW_OK */ LEFT(rev_timestamp, 4) as year, rev_user_text as user, COUNT(*) as edits FROM revision INNER JOIN page ON page_id = rev_page WHERE rev_user_text = 'USER' AND page_namespace = 0 GROUP BY LEFT(rev_timestamp, 4); Than I imported that result to a new table called 'dbq179_1' (on a different server, cause I couldn't use INSERT SELECT as the user tables on s1 atm AFAIK aren't available). And then I ran: SELECT year, SUM(edits) FROM dbq179_1 GROUP BY year; for 1 and SELECT user, SUM(edits) FROM dbq179_1 GROUP BY user; for 2. If you know SQL, you'll see that those are pretty trivial, that's why I didn't list them first, sorry ![][1] [1]: https://jira.toolserver.org/images/icons/emoticons/wink.gif
This bug was imported as RESOLVED. The original assignee has therefore not been set, and the original reporters/responders have not been added as CC, to prevent bugspam. If you re-open this bug, please consider adding these people to the CC list: Original assignee: hoo@online.de CC list: hoo@online.de
