This issue was converted from https://jira.toolserver.org/browse/DBQ-72.
Summary: distinct articles per user
Issue type: Task - A task that needs to be done.
Priority: Major
Status: Done
Assignee: (none)
From: Nuno Tavares <nunotavares@hotmail.com>
Date: Wed, 19 Aug 2009 02:48:41
We are trying to provide some statistics for studying. I've reached a query that may be used (by us) for that analysis, but it's pretty heavy.
SELECT a.rev_user_text,p.page_namespace,COUNT(a.rev_page) AS artigos_distintos
FROM (SELECT r.rev_user,r.rev_page,r.rev_user_text
FROM wikidb_revision r
GROUP BY r.rev_user,r.rev_page) AS a
JOIN wikidb_user u ON u.user_id = a.rev_user
JOIN wikidb_page p ON a.rev_page = p.page_id
GROUP BY rev_user,p.page_namespace
Can this be run in the Query service? I've made some tests, and it seems it's better to use an external table, like this:
create table teste select r.rev_user, p.page_namespace, r.rev_page, count(1) AS edits
from revision r
JOIN page p ON r.rev_page = p.page_id
GROUP BY r.rev_user,p.page_namespace,r.rev_page;
alter table teste add key idx_u (rev_user,page_namespace);
select straight_join u.user_name, page_namespace,count(1) as edits from teste
join user u on u.user_id = rev_user
group by rev_user,page_namespace;
Hope that helps.
Version: unspecified
Severity: major