Category talk:Corrected articles
Add topicLatest comment: 13 years ago by Bawolff in topic better query
In case anyone cares, here is the by month break down of corrected articles (as of 21:40, 7 April 2011 (UTC)) (Using cl_timestamp field, which is same as DPL uses, so if anyone removed and re-added the cat, it'd be off):
- Per year
mysql> select left(cl_timestamp, 4) as year, count(*) as "Number of articles" from categorylinks where cl_to='Corrected_articles' group by left(cl_timestamp, 4); +------+--------------------+ | year | Number of articles | +------+--------------------+ | 2007 | 20 | | 2008 | 10 | | 2009 | 7 | | 2010 | 19 | | 2011 | 5 | +------+--------------------+ 5 rows in set (0.01 sec)
- Breakdown per month
mysql> select left(cl_timestamp, 4) as Year, substring(cl_timestamp, 6, 2) as month, count(*) "Number of articles" from categorylinks where cl_to='Corrected_articles' group by left(cl_timestamp, 7); +------+-------+--------------------+ | Year | month | Number of articles | +------+-------+--------------------+ | 2007 | 02 | 16 | | 2007 | 04 | 1 | | 2007 | 05 | 1 | | 2007 | 06 | 1 | | 2007 | 07 | 1 | | 2008 | 01 | 3 | | 2008 | 02 | 2 | | 2008 | 03 | 1 | | 2008 | 05 | 2 | | 2008 | 12 | 2 | | 2009 | 01 | 3 | | 2009 | 06 | 2 | | 2009 | 11 | 2 | | 2010 | 04 | 1 | | 2010 | 05 | 1 | | 2010 | 07 | 1 | | 2010 | 08 | 11 | | 2010 | 09 | 1 | | 2010 | 10 | 2 | | 2010 | 11 | 1 | | 2010 | 12 | 1 | | 2011 | 01 | 1 | | 2011 | 03 | 4 | +------+-------+--------------------+ 23 rows in set (0.00 sec)
Bawolff ☺☻ 21:40, 7 April 2011 (UTC)
- I take it this is based on when articles were added to the category, rather than when they were published. E.g., of the five articles that have been added to the category in 2011, three of them were published in 2011, one in 2007, and one in 2005. --Pi zero (talk) 21:53, 7 April 2011 (UTC)
- Good point - yes it is. Here's an attempt that should use the first revision to each article as the date:
better query
[edit]mysql> select year, count(article) as "Number of articles" from (select cl_from as article, left(min(rev.rev_timestamp), 4) as year from categorylinks c1, revision rev where c1.cl_from = rev.rev_page and c1.cl_to='Corrected_articles' group by c1.cl_from) foo group by year ; +------+--------------------+ | year | Number of articles | +------+--------------------+ | 2005 | 7 | | 2006 | 13 | | 2007 | 11 | | 2008 | 6 | | 2009 | 6 | | 2010 | 15 | | 2011 | 3 | +------+--------------------+ 7 rows in set (0.00 sec)
by month/year:
mysql> select left(year, 4) as Yearr, substring(year, 5, 2) as Month, count(article) as "Number of articles" from (select cl_from as article, left(min(rev.rev_timestamp), 6) as year from categorylinks c1, revision rev where c1.cl_from = rev.rev_page and c1.cl_to='Corrected_articles' group by c1.cl_from) foo group by year ; +-------+-------+--------------------+ | Yearr | Month | Number of articles | +-------+-------+--------------------+ | 2005 | 03 | 1 | | 2005 | 04 | 2 | | 2005 | 05 | 1 | | 2005 | 07 | 1 | | 2005 | 08 | 1 | | 2005 | 12 | 1 | | 2006 | 01 | 2 | | 2006 | 05 | 2 | | 2006 | 07 | 1 | | 2006 | 08 | 3 | | 2006 | 09 | 3 | | 2006 | 11 | 2 | | 2007 | 01 | 3 | | 2007 | 02 | 1 | | 2007 | 04 | 1 | | 2007 | 07 | 1 | | 2007 | 09 | 2 | | 2007 | 10 | 1 | | 2007 | 12 | 2 | | 2008 | 02 | 1 | | 2008 | 03 | 1 | | 2008 | 04 | 1 | | 2008 | 10 | 1 | | 2008 | 12 | 2 | | 2009 | 01 | 2 | | 2009 | 03 | 1 | | 2009 | 06 | 1 | | 2009 | 10 | 1 | | 2009 | 11 | 1 | | 2010 | 01 | 1 | | 2010 | 03 | 1 | | 2010 | 05 | 1 | | 2010 | 07 | 3 | | 2010 | 08 | 5 | | 2010 | 09 | 1 | | 2010 | 10 | 2 | | 2010 | 12 | 1 | | 2011 | 02 | 1 | | 2011 | 03 | 2 | +-------+-------+--------------------+ 39 rows in set (0.00 sec)