SQL
x
USE ukwiki_p;
SET SESSION group_concat_max_len = 10000;
SELECT @rownum := @rownum + 1 AS rank,
IFNULL(actor_name, 'Всього'),
created,
total_len,
articles
FROM
(
SELECT
CONCAT('[[Користувач:', actor_name, '|', actor_name, ']]') as actor_name,
COUNT(1) AS created,
SUM(article.page_len) as total_len,
GROUP_CONCAT(
DISTINCT
-- [[Назва статті]]
CONCAT('[[',
REPLACE(article.page_title, '_', ' '), -- замінюємо у назві '_' на ' '
']]', ' (', article.page_len, ')')
-- список впорядкований за назвою статті
ORDER BY article.page_len DESC
-- розділений пробілом
SEPARATOR ' · '
) AS articles
FROM page article
JOIN revision
ON rev_page = article.page_id AND
rev_parent_id = 0 AND
20190601 * 1000000 <= rev_timestamp AND
rev_timestamp < 20190628 * 20000000
JOIN page talk
ON talk.page_title = article.page_title AND
talk.page_namespace IN (0, 1)
JOIN templatelinks ON
tl_from = talk.page_id AND
tl_title = '2019Sweden-week-new' AND
tl_namespace = 10
join actor on revision.rev_actor = actor_id and actor_user is not null
WHERE
article.page_namespace = 0 AND
article.page_is_redirect = 0
GROUP BY rev_actor WITH ROLLUP) t,
(SELECT @rownum := -1) r
ORDER BY created DESC, total_len DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.