Page MenuHomePhabricator
Paste P84991

cope with both variable length uri_path (might or might not have /archive/ in) and that size may be NNNpx or prefix-NNNpx
ArchivedPublic

Authored by MatthewVernon on Nov 6 2025, 11:25 AM.
select slice(split(split(slice(split(uri_path, '/'),-1,1)[0], 'px-')[0],'-'),-1,1)[0] as thumbsize, count(*) as hits from wmf.webrequest where webrequest_source = 'upload' and year = 2025 and month = 10 and day = 24 and hour = 10 and http_status = '200' and uri_path like '/wikipedia/%/thumb/%' group by thumbsize order by hits desc limit 10;

Event Timeline

(spark 3.3.0 gains the split_part function, which would make this rather simpler)

We want the final element of uri_path split by / (it's not a fixed length because of archive thumbs).
Then to take the string up to "px-" (which is usually just the size) hence splitting on "px-" and taking the first element.
The complication is that there are a number of prefixes that might come before the size (e.g. langfr-250px for a translated SVG file), so we then want the last element of that string split on '-'.