Page MenuHomePhabricator

DBQ-72 distinct articles per user
Closed, ResolvedPublic

Description

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

Details

Reference
bz59327

Event Timeline

bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 2:23 AM
bzimport set Reference to bz59327.

From: mauro742 <mauro742@gmail.com>

Date: Tue, 27 Oct 2009 13:22:13

Can you explain what do you obtain? I understand you want a list that reports foreach user the distinct numbers of edits foreach namespace. Is it right?


From: Nuno Tavares <nunotavares@hotmail.com>

Date: Wed, 28 Oct 2009 20:22:20

This issue has already been answered.... by myself. You can close this one.

Mauro, reference and explanation is at:
http://gpshumano.blogs.dri.pt/2009/09/28/importing-wikimedia-dumps/

in portuguese, though. In english: you are right.

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: (none)
CC list: nunotavares@hotmail.com