Toolserver:GlobalUsage weekly stats

From mediawiki.org

This page was moved from the Toolserver wiki.
Toolserver has been replaced by Toolforge. As such, the instructions here may no longer work, but may still be of historical interest.
Please help by updating examples, links, template links, etc. If a page is still relevant, move it to a normal title and leave a redirect.

GlobalUsage weekly stats for selected images are stored in p_globalusage_weekly_p on sql-s2.

Table layout[edit]

CREATE TABLE stats (
        st_image varbinary(255) not null,
        st_wiki varbinary(32) not null,
        st_timestamp binary(14) not null,
        st_count int default null,

        PRIMARY KEY (st_image, st_timestamp, st_wiki)
);

CREATE TABLE categorylinks (
        cl_category int not null,
        cl_image varbinary(255) not null,
        cl_timestamp binary(14) not null,

        PRIMARY KEY (cl_category, cl_timestamp, cl_image)
);

-- Id <> name mapping for now, but may be altered to support templatelinks
CREATE TABLE categories (
        cat_id int not null auto_increment,
        cat_name varbinary(255) not null,

        PRIMARY KEY (cat_id),
        UNIQUE INDEX (cat_name)
);

Useful queries[edit]

  • Last update: SELECT MAX(cl_timestamp) FROM categorylinks;
  • Number of images in a certain category: SELECT COUNT(*) FROM categorylinks JOIN categories ON cl_category = cat_id WHERE cl_timestamp = <timestamp> AND cat_name = <category name>
  • Total usage for a certain category: SELECT SUM(st_count) FROM stats JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp JOIN categories ON cl_category = cat_id WHERE cl_timestamp = <timestamp> AND cat_name = <category name>
  • Total use for a certain category over time: SELECT cl_timestamp AS timestamp,SUM(st_count) AS count FROM stats JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp WHERE cl_category = <cat_id> GROUP BY cl_timestamp
  • Number of images for a certain category not in use: SELECT COUNT(*) FROM stats RIGHT JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp JOIN categories ON cl_category = cat_id WHERE cl_timestamp = <timestamp> AND cat_name = <category name> AND st_image IS NULL
  • Usage for all images in a certain category per wiki: SELECT st_wiki, SUM(st_count) AS c FROM stats JOIN categorylinks ON cl_image = st_image AND cl_timestamp = st_timestamp JOIN categories ON cl_category = cat_id WHERE cl_timestamp = <timestamp> AND cat_name = <category name> GROUP BY st_wiki;
  • When a category is renamed at Commons: REPLACE INTO categories (cat_id, cat_name) VALUES (<cat_id>, '<new category name>')

Reporting[edit]

We are looking for somebody who wants to create nice web pages and reports from the data we collect. If you are interested please contact Bryan or Multichill. Also if you would like to contribute other statistics please contact us and we can have you added to the MMP

Contact[edit]

  • Bryan <bryan@toolserver.org>