Mark Bell wrote:

Folks,

This may be answered somewhere but i have not found a reference.

I would like stats or a graph that shows:

Number of unique editors per article X number of articles with that many unique editors.

So for example

number of unique editors Number of articles with that many unique editors 15 356 30 3455

MAYBE this will work (haven't tried it):

SELECT editorcount as 'Number of unique editors', COUNT(rev_page) as 'Number of articles with that many unique editors' FROM ( SELECT rev_page, COUNT(DISTINCT rev_user_text) as editorcount FROM revisions GROUP BY rev_page ) AS editorcounts GROUP BY editorcount ORDER BY 'Number of unique editors';

If this doesn't work (maybe MySQL doesn't support this particular way of nesting queries), then I don't think you can do it in a single SQL query, but here's how you could do it in several:

First, create a new table:

CREATE TABLE editorcounts ( page_id int not null, editorcount int not null );

Now fill the table with data that tells you how many unique editors each article has:

INSERT INTO editorcounts (page_id, count) SELECT rev_page, COUNT(DISTINCT rev_user_text) FROM revisions GROUP BY rev_page;

And finally, query the new table for the data you actually want:

SELECT editorcount as 'Number of unique editors', COUNT(page_id) as 'Number of articles with that many unique editors' FROM editorcounts GROUP BY editorcount ORDER BY editorcount;

You may have to create indexes to speed up this process. You may also have to correct syntax errors if I made any, because I haven't tested this :-)

Timwi

any chance you can send me the data?

On 8/4/06, Timwi timwi@gmx.net wrote:

...

MAYBE this will work (haven't tried it):

 SELECT editorcount as 'Number of unique editors', COUNT(rev_page)
     as 'Number of articles with that many unique editors'
 FROM (
     SELECT rev_page, COUNT(DISTINCT rev_user_text) as editorcount
     FROM revisions GROUP BY rev_page
 ) AS editorcounts
 GROUP BY editorcount
 ORDER BY 'Number of unique editors';

I've just tested this, and it works perfectly, except that "revisions" should have been "revision". :-)

Timwi

---

Wikitech-l mailing list Wikitech-l@wikimedia.org http://mail.wikipedia.org/mailman/listinfo/wikitech-l