Last modified: 2014-01-03 15:54:54 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-101. Summary: Templates transcluded on the most User pages Issue type: Task - A task that needs to be done. Priority: Minor Status: Done Assignee: EdoDodo <dodo.wikipedia@gmail.com> ------------------------------------------------------------------------------- From: Paolo Massa <massa@fbk.eu> Date: Thu, 09 Sep 2010 12:31:26 ------------------------------------------------------------------------------- I've seen the list of Templates with the most transclusions at http://en.wikipedia.org/wiki/Wikipedia:Database_reports/Templates_transcluded_on_the_most_pages Very interesting! The SQL query is at http://en.wikipedia.org/wiki/Wikipedia:Database_reports/Templates_transcluded_on_the_most_pages/Configuration and I copy and paste it here: SELECT tl_title, COUNT![][1] FROM templatelinks WHERE tl_namespace = 10 GROUP BY tl_title ORDER BY COUNT![][1] DESC LIMIT 1000; I would like to see the results of a very similar query, for getting Templates with the most transclusions on pages in namespace User: , and in namespace User_talk: (separately), i.e. two separate lists. I modify a bit the SQL for the previous query, basically just by changing tl=namespace = 2 (and 3). I don't know if this is correct so I ask you to check . Query for User: SELECT tl_title, COUNT![][1] FROM templatelinks WHERE tl_namespace = 2 GROUP BY tl_title ORDER BY COUNT![][1] DESC LIMIT 1000; Query for User_talk: SELECT tl_title, COUNT![][1] FROM templatelinks WHERE tl_namespace = 3 GROUP BY tl_title ORDER BY COUNT![][1] DESC LIMIT 1000; The format can be the same as the original query or a CVS file, it is the same. [1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Sun, 12 Sep 2010 16:54:56 ------------------------------------------------------------------------------- Hmm... Actually, I think that wouldn't work. That is changing the query to look for transcluded user pages, instead of transcluded templates. I think this query should work: SELECT tl_title, COUNT![][1] FROM templatelinks LEFT JOIN page ON tl_from = page_id WHERE tl_namespace = 10 AND page_namespace = 2 GROUP BY tl_title ORDER BY COUNT![][1] DESC LIMIT 1000; Running it now (along with a slightly modified one for user talk), will post results in a CSV file when it is done. [1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Sun, 12 Sep 2010 17:03:40 ------------------------------------------------------------------------------- Query results are attached. Will close this issue, if there is something wrong with the results, or my query, feel free to reopen.
------------------------------------------------------------------------------- From: Paolo Massa <massa@fbk.eu> Date: Tue, 14 Sep 2010 07:05:59 ------------------------------------------------------------------------------- Thanks EdoDodo! This is precisely what I needed, I already started digging in the results! Thanks! The query sparked an additional curiosity: on toolserver, you have access to databases for every wikipedia in every language, right? If yes, would it be possible to run the same query in other wikipedias, for example in the it.wikipedia, es.wikipedia, fr.wikipedia, de.wikipedia, ja.wikipedia, zh.wikipedia? Thanks!
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Wed, 15 Sep 2010 12:02:20 ------------------------------------------------------------------------------- Hey, yeah, I do have access to all the databases, so I could run it for those other Wikipedias. I'm not entirely sure if the namespace numbers are the same across all Wikipedias, though. Anyway, if you give me a list of the wikis you'd like me to run the query on I'll look into it and attach the results.
------------------------------------------------------------------------------- From: DaB. <dab@ts.wikimedia.org> Date: Wed, 15 Sep 2010 12:14:19 ------------------------------------------------------------------------------- Every namespace below 100 is identical on all wikis AFAIK.
------------------------------------------------------------------------------- From: Paolo Massa <massa@fbk.eu> Date: Wed, 15 Sep 2010 12:43:21 ------------------------------------------------------------------------------- Great! So i would like to see results for it es fr de ja zh, and, if possible, also for vec (wikipedia in venetian, a local Italian dialect), that is a small wikipedia but that I did some research about. Both for user: and user_talk: would be great! Thanks!
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Wed, 15 Sep 2010 13:31:02 ------------------------------------------------------------------------------- Okay, working on it, will post the upload the results later today, or tomorrow.
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Wed, 15 Sep 2010 17:08:40 ------------------------------------------------------------------------------- All of the results are in the attached zip file.
------------------------------------------------------------------------------- From: Paolo Massa <massa@fbk.eu> Date: Thu, 16 Sep 2010 09:29:01 ------------------------------------------------------------------------------- Thanks, you are amazing! Thanks!
------------------------------------------------------------------------------- From: EdoDodo <dodo.wikipedia@gmail.com> Date: Thu, 16 Sep 2010 14:41:20 ------------------------------------------------------------------------------- You're welcome.
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: dodo.wikipedia@gmail.com CC list: dodo.wikipedia@gmail.com, wikimedia-bugzilla@dabpunkt.eu
