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>
Thank you so much for helping me
Version: unspecified
Severity: critical
From: Hoo man <hoo@online.de>
I'm sorry, but I need further information on what you want: Which wiki, Which bot(s), ...?
From: Hoo man <hoo@online.de>
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>
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>
I ran it on all namespaces... that's not what you wanted, is it? :/
From: Hoo man <hoo@online.de>
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>
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